Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 2 hours 1 min ago

How to write a SQL to join with multiple tables and select results in a query from any on of the table (like in queue)

Thu, 2017-10-19 20:06
Hi , I have following tables . I am getting orders data from various sources in table test_orders. Then joining the items to various table for revenue percentage and then send the orders along with revenue information to another 3rd party tool for re...
Categories: DBA Blogs

Dynamic SQL in CURSOR

Thu, 2017-10-19 01:46
Hi, I am trying to create a procedure that will display logs. It has an IN parameter which is the table nam, the cursor will SELECT data based on that parameter. I cannot compile my procedure. Hope you can help me. :) <code>CREATE OR REPLACE PROCE...
Categories: DBA Blogs

Instead of Triggers - where to use?

Thu, 2017-10-19 01:46
Dear Mr. Tom, 1) First of all when I tried creating an INSTEAD OF Trigger it gave me ORA-00439: feature not enabled: error Kindly explain why so....? 2) Where exactly would u advise me to use Instead Of trigger ...
Categories: DBA Blogs

How to enforce conditional unique on multiple columns

Thu, 2017-10-19 01:46
Hi, Tom, I have a table create table project (project_ID number primary key, teamid number, job varchar2(100), status number(1)); status=1 means it is an active project, otherwise it is archiv...
Categories: DBA Blogs

Merging 55 millions of records along with hash key

Thu, 2017-10-19 01:46
Hi Tom, I need to load my table with 55 millions of records using Merge statement 1. Those 55 millions records are being fetched from many different tables. For example T1,T2,T3 etc. 2. i have a date table from where i fetch business weeks ba...
Categories: DBA Blogs

Calling a linked procedure with out cursor parameter in another procedure

Thu, 2017-10-19 01:46
Hello, I need to have a procedure with out cursor parameter which I am calling it via a db link, inside a procedure, how can I implement that? procedure Myouterprocedure(..., my_out_cursor out cursor, ...
Categories: DBA Blogs

sqlcl command line tool warning

Wed, 2017-10-18 07:26
Hi I am using sqlcl quite a while and constantly get this null pointer exception. Any Idea Apr 15, 2016 9:28:22 AM oracle.dbtools.plusplus.JDBCHelper getOH WARNING: oracle.dbtools.plusplus.JDBCHelper.getOH(JDBCHelper.java:83) java.lang.NullP...
Categories: DBA Blogs

Merge statement

Wed, 2017-10-18 07:26
Hi Tom, consider this: create table test ( id number(10), username varchar2(100), reason varchar2(100), timestamp_ number(10), cnt number(10), CONSTRAINT test_pk primary key (id) ); insert into test(id,username,reason,timestamp_,cn...
Categories: DBA Blogs

Optimize Query

Wed, 2017-10-18 07:26
I want to optimize the following query SELECT t.merchant_id, t.brand_id, t.transaction_type, t.trns_currency_code, <b>COUNT (*) AS total_no_tx, SUM (t.mv_is_trnx_approved) AS approved_no_tx, ...
Categories: DBA Blogs

DDL for tables and constraints

Wed, 2017-10-18 07:26
Team, I am using SQL Developer 17.3 and SQLCL 17.3 for this demo. while doing DDL from SQLCL we got this <code> demo@ORA11G> show ddl STORAGE : ON INHERIT : ON SQLTERMINATOR : ON OID : ON SPECIFICATION : ON TABLESPACE : ON SIZE_BYTE_K...
Categories: DBA Blogs

Is there a way I can press a button on home page just after logging automatically in apex application

Wed, 2017-10-18 07:26
Hi, I have an apex application https://apex.oraclecorp.com/pls/apex/f?p=32888:1:4894003287217::::: and I want to click Search button on home page automatically every time once a user login to this application. I need to do this to submit the val...
Categories: DBA Blogs

Select and duplicates

Wed, 2017-10-18 07:26
Hello, I'd like to remove duplicates from ma table let say V1 is the key column. the key should be max 5 digits as a length Example: V1 01254T 50300 50300W 45662 Here I should keep the first one (it has no duplicates) , ...
Categories: DBA Blogs

Different in costs for selecting sysdate from dual and using directly as sysdate

Wed, 2017-10-18 07:26
The below 2 queries returns the same results. The first one takes very long time and the cost of fetching the records from the custdata is around 82000. I cancelled the query since it takes longer. But the second query takes the cost of only 2 an...
Categories: DBA Blogs

Converting compressed securefile LOBS to basicfile

Wed, 2017-10-18 07:26
Hello Tom, We are migrating a database which is using Advance compression for LOBS. We do not have licenses for the new environment to replicate the as-is. What are the available options we have and how to convert compressed securefile LOBS to s...
Categories: DBA Blogs

DBMS_CRYPTO.HASH HASH_SH256 does not match openssl

Tue, 2017-10-17 13:06
OM - I need to create a (large) hash both inside the database and outside of it that yields the same result. Everything I am reading says SHA256 will satisfy the requirement, but I am not able to write SQL that achieves the same answer as the Li...
Categories: DBA Blogs

Hidden parameters

Tue, 2017-10-17 13:06
Will the below hidden parameter have any issues. Database version : 11.2.0.4.0 _always_anti_join off _always_semi_join off _disk_sector_size_override TRUE _hash_join_enabled FALSE _kgl_large_heap_warning_threshold 20485760...
Categories: DBA Blogs

Contains operator with "not" or "no" query terms does not work

Tue, 2017-10-17 13:06
I have a large free-text field that I am trying to query for specific occurrences. I have three query terms that need to be near each other, so I want to use the contains near operator. But one of the query terms is either "no" or "not". When I t...
Categories: DBA Blogs

Getting word count from context text index by joining to indexed rows

Tue, 2017-10-17 13:06
Hello Tom, I would like to be able to roll up a word count (for a WordCloud control in a UI) based on a query ( a spatial query in this case). I have a table as such <code> create table items (id raw(16), text clob, geom sdo_geometry ) </code...
Categories: DBA Blogs

end loop commit; on the same line gives no error

Tue, 2017-10-17 13:06
Hello, I created a simple table. <code>create table test(t number);</code> I tried to insert values in it. I created o simple PLSQL block to insert values. <code>declare i integer; begin for me in 1..2 loop insert into test(t) va...
Categories: DBA Blogs

Parallel DML Update without any parallel hint or parallel degree

Mon, 2017-10-16 18:46
Hello All, In our production environment, one DML update is running in parallel and which is sporadic in nature. Please note the session is enabled for parallel dml. Find below one pseudo code of that update statement. --------------- <co...
Categories: DBA Blogs

Pages