Tom Kyte

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

External table concepts

Fri, 2017-09-29 05:06
Hi All, I am new to oracle external table concepts. Have a very basic query - if i have a csv with the below columns Col1, Col2, Col3 Col4 .... Coln and i want to insert only Col3 & Col4 into an oracle external table , what would be my ...
Categories: DBA Blogs

sql query to update a table based on data from other table

Fri, 2017-09-29 05:06
Hi, Looks like my other similar questions got closed, so asking a new question. I have a cust_bug_data table with 2 columns(ROOT_CAUSE, BUG_NUMBER) like as follows: <code>create table cust_bug_data(ROOT_CAUSE VARCHAR(250), BUG_NUMBER NUMBER N...
Categories: DBA Blogs

Updating records with many-to-1 linked table relationship

Thu, 2017-09-28 10:46
I have an MS_ACCESS Query to convert to Oracle SQL. Access Query <code>UPDATE target_table T INNER JOIN source_table S ON T.linkcolumn = S.linkColumn SET T.field1 = S.field1, T.field2 = S.field2, T.field3 = S.field3;</code> Note: T...
Categories: DBA Blogs

Native dynamic sql - Refcursor

Thu, 2017-09-28 10:46
Tom, Here is an example...that i want to change one function to avoid redundant information. create or replace package p_ref_cursor is type ret_ref_cursor is ref cursor; end p_ref_cursor; / drop table "tab1"; create table "tab1" ...
Categories: DBA Blogs

ORA-06502 with CHAR parameter. What am I missing?

Thu, 2017-09-28 10:46
Sorry to bother you with a ORA-06502 error. But I'm not understanding this behavior. As I saw, the length fits (see the dbms_output in result showing that the length is 16). The only thing i can think is that in the procedure proc, pl/sql is...
Categories: DBA Blogs

ORA-00060 : Deadlock detected while waiting for resource in Multi-threaded Java Batch process

Wed, 2017-09-27 16:26
Hi Tom I have a multi-threaded batch process running on Production that fails due to "ORA-00060 : Deadlock detected while waiting for resource". I am getting following error message: <code>02:25:25,899 [CobolThread 34] ERROR Error executing upd...
Categories: DBA Blogs

Materialized Views: Refresh Statistics History - How can I see data in user_mvref_stmt_stats

Wed, 2017-09-27 16:26
Hi Masters, sorry for another question :-) and again a big, big thank You for Your hard work and the answers to our questions!! Could You please have a look into my test case? Some of the statement fail, because I do not have all the privs in l...
Categories: DBA Blogs

Using JSON_TABLE with aggregate functions

Wed, 2017-09-27 16:26
Hi Team DB Version: 12.2.0.0.3 On a high level, on successful completion of the scripts (from LiveSQL) you would have: 1) Loaded 10k users data in JSON format in STAGE_USER table. 2) Loaded 500 roles data in JSON format in STAGE_ROLE table. ...
Categories: DBA Blogs

ORA-00937 for aggregate in scalar subquery

Wed, 2017-09-27 16:26
<code>HI CAN ANY BODY EXPLAIN IN 1ST QUERY WHY IT IS ERROR BUT IN 2ND QUERY IT GIVES RESULT ? SQL> SELECT SUM(SAL), 2 ( 3 SELECT SUM(SAL) FROM EMP 4 ) 5 FROM EMP; SELECT SUM(SAL) FROM EMP * ERROR at line 3: ORA-00937: n...
Categories: DBA Blogs

TNS could not resolve the service name

Mon, 2017-09-25 09:26
I have been trying to connect SQL Plus, Report Builder but getting error "TNS could not resolve the service name " In SQL developer i tried with two connections name it worked ,below is the detail : Connection name : MyConn user : system passs:...
Categories: DBA Blogs

Is there a risk in continuing to develop using plsql web toolkit?

Mon, 2017-09-25 09:26
Hi Tom, I hope you can help or point me in the right direction. I have been asked to evaluate an in-house developed system that is written entirely in plsql/web toolkit using mod_plsql. This is a heavily used service, sometimes with very high conc...
Categories: DBA Blogs

String buffer into Oracle (utl, xml)

Mon, 2017-09-25 09:26
<code>I have procedure to create xml file and save it on disc directory: create or replace procedure test_write_xml_data_to_file (p_directory varchar2, p_file_name varchar2) as v_file UTL_FILE.FILE_TYPE; v_amount INTEGER := 32767; v_xml_dat...
Categories: DBA Blogs

Error while inserting data through SQL Loader

Mon, 2017-09-25 09:26
Hi All, We are using SQL loader in our .Net application to insert data into oracle Data base. We are inserting through bulk insert process. We have another Stored procedure in Oracle which deletes data from the same table where insertion happe...
Categories: DBA Blogs

Using Interval Datatype

Mon, 2017-09-25 09:26
I have the following query where TS_END and TS_START are DATE columns. I used to run this query to give me the average response time in seconds: SELECT COUNT(*) system_process_count, TRUNC(AVG(lgrt.ts_end - lgrt.ts_start)*24*60*60, 1) FRO...
Categories: DBA Blogs

how the SQL query is processed

Sun, 2017-09-24 15:06
How the SQL query is processed in detail
Categories: DBA Blogs

Range constraint

Sun, 2017-09-24 15:06
Hi! First i want to thank you for the many Responses that where usefull for me. I need a constraint or trigger that controls if the row inserted or updated contains a range of numbers that is already contained in another row of the same table. ...
Categories: DBA Blogs

oracle plsql data migration

Sun, 2017-09-24 15:06
Hi Tom, i need to write a plsql program, Suppose there are 10 sup tables with different columns in each table and 1 target table which have columns same as combined all source columns. i need to write a procedure which will extracts the data from...
Categories: DBA Blogs

temp undo sizing

Sun, 2017-09-24 15:06
Hi team, I have One question - 1. Suppose i have 1TB DB Size so What our recommendation in term of -sizing of database memory. -Temporary tablespace -Undo tablespace Thanks
Categories: DBA Blogs

Convert sys_guid to number

Sun, 2017-09-24 15:06
Hi Tom I read some articles about to_number(sys_guid()), but You suggest not to use it. Why Apex QuickSQL suggests to_number(sys_guid()) for unique object-ID ? Example create or replace trigger country_biu before insert or update ...
Categories: DBA Blogs

Query taking more time to split,compare against lookup table and to insert

Sat, 2017-09-23 02:26
We have a dimension table in which one column has mutlipul values like an Array. I have to get all this individual values and should compare against a lookup table, if the value does not exist in the look up table then i have to insert this value ...
Categories: DBA Blogs

Pages