Tom Kyte

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

Oracle SQL version of Index and Match in Excel

Tue, 2019-01-15 22:06
Hi Everyone, I have a challenge to use ORACLE SQL to mimic what Excel does matching 2 tables based on the header and content. Details are follows. TABLE 1 cid CName Age Height 001 Mary E40 E22 002 Cat E22 E40 TABLE 2 Data CODE MEANING A...
Categories: DBA Blogs

How to repeat date in other rows

Tue, 2019-01-15 22:06
Hi, I am looking for some help on the SQL given in Live SQL Link (https://livesql.oracle.com/apex/f?p=590:43:100370634308121:::43:P43_ID:169399633855075789353470383313660811797). Below is the SQL which I have created. There is one column named STR...
Categories: DBA Blogs

How to retrieve data from multiset cast

Tue, 2019-01-15 22:06
here is the sample query , now how can i retrive col1 data (select col1 from below query) select 1,2,3,cast(multiset(select col1,col2 from table A) as object type ) from table a ;
Categories: DBA Blogs

Update Additional_Info Column of a Job's Run Log Details

Tue, 2019-01-15 22:06
I have successfully created and scheduled my first job through the Scheduler that calls a stored procedure. One thing I noticed is that when the job ran was that if it failed, the ora-xxxxx error was in the addtional_info column of the log details (...
Categories: DBA Blogs

Order of triggers

Tue, 2019-01-15 22:06
Hi Tom, I have a table and 2 or more after insert/after delete triggers Can you tell me what will be the order of firing after insert triggers. I have 2-3 after insert triggers on the same table. I have tried by finding this using timestamps ...
Categories: DBA Blogs

Retrieve the id of the last inserted row with some combination of values and store it in current row

Tue, 2019-01-15 22:06
I have a table '<b>raw_traffic</b>' with approximately 35 columns titled id, status, insert_date, change_date, device_name, device_port, class, prev_id, col_1, col_2, col_3, . . . col_N Whenever I receive fresh traffic data, I ne...
Categories: DBA Blogs

Using Java code in PLSQL to use checksum function

Tue, 2019-01-15 03:46
Hi Tom, Thanks for being with us and all your support. Suppose I have a File Name :- "MPR_YES_CDC_11122018_V1.csv" Checksum of the given file as per below java code :- cab5f886bba15048a6f7180665871a2fa4ad4917 In the same manner I have to u...
Categories: DBA Blogs

regexp_replace inconsistent

Tue, 2019-01-15 03:46
I've just discovered a strange inconsistency in regexp_replace; I'm not sure if this is a bug or a feature I don't understand. <code>select regexp_replace('x1234567y8z90', '^\D*\d*\D(\d{1,2}).*$', '\1') "\D" , regexp_replace('x1234567y8z90', '...
Categories: DBA Blogs

Oracle Entity Framework Core

Tue, 2019-01-15 03:46
When and from where can we download the dot net core entity framework driver?
Categories: DBA Blogs

Pivot or group (multiple columns transpose)

Mon, 2019-01-14 09:26
Hi, I try to transpose multiple columns but i couldn't. Here is my table. This data create automatically. <code>BODY_ID EQUPMENT DATA_NO DATA_TYPE RESULT DATA PRG DATETIME 52972 t000001119 1 1 @ 381 1120000000000000...
Categories: DBA Blogs

Getting ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Sun, 2019-01-13 15:06
HI happy new year... i have a procedure which executes a big big big query...i will share the behavior of my procedure <code>CREATE OR REPLACE PROCEDURE DUMMY_PROC(DATASET OUT SYS_REFCURSOR,FSID NUMBER) as STR VARCHAR2(32000); BEGIN STR:=' ...
Categories: DBA Blogs

RAMN configuration for daily backup

Sun, 2019-01-13 15:06
hi.. we have a production database (Archive log mode) which generates a huge number of archived redo log files(size is getting near 118 GB per day for this files)and we are running out of space in the machine. Before, we were taking backup of the d...
Categories: DBA Blogs

Inserting rows with BULK BIND

Sun, 2019-01-13 15:06
Hi, I want insert 500002 rows to my table by applying LIMIT with 10k , now how many times my LIMIT is iterate. Is it 5 times or 6 times. Could plz give a sample example ?
Categories: DBA Blogs

Index creation time estimation with XML Type data in the index

Sun, 2019-01-13 15:06
We have a few tables, each of them has a structure something like this: <code> CREATE TABLE "myTABLE" ( "FIELD1" CHAR(36 BYTE) NOT NULL ENABLE, "FIELD2_XML" "SYS"."XMLTYPE" , "FIELD3" NUMBER DEFAULT 0 NOT NULL ENABLE, "FIELD4" NUMBER(...
Categories: DBA Blogs

UTL_MAIL with AUTHENTICATION OF SENDER

Sun, 2019-01-13 15:06
i m trying to send an email with utl_mail using this procedure: <code>BEGIN UTL_MAIL.send(sender => 'o.adahanifi@teletic.dz', recipients => 'ao_ada_hanifi@esi.dz', subject => 'UTL_MAIL Test', ...
Categories: DBA Blogs

SQL solution to read file with one line content and format them

Sat, 2019-01-12 02:26
Hello, Is it possible to write a control file to read a file in the below format (only one line containing multiple records) and split into multiple rows and simply dump into a table (containing only one column TEXT) and load/store these lines ? ...
Categories: DBA Blogs

Sequence of selecting from ordered inner query

Sat, 2019-01-12 02:26
Hi Tom, Does selecting from an inner query ,which has an order by clause, guarantee that the final output to be sorted by the order by column in inner query? <code>Select * from ( select * ...
Categories: DBA Blogs

Output to large xml file

Fri, 2019-01-11 08:06
Dear all, I need to output a large xml file. I created a procedure and it outputs the file correctly when the number of records is small. Code: DECLARE l_file UTL_FILE.file_type; l_xmltype xmltype; BEGIN SELECT XMLRoot( ...
Categories: DBA Blogs

Real Time SQL monitoring doesn't show my query

Fri, 2019-01-11 08:06
Hi TOM, I've got a big query, which normally takes to complete around 90 seconds. I'd like to see the execution details using Real Tim SQL monitoring. Unfortunately, it didn't appear there. Even when I specifically added /*+ MONITOR */ to the qu...
Categories: DBA Blogs

Create a Concurrent Program Manager

Fri, 2019-01-11 08:06
Hi Tom, I am trying to develop a concurrent program manager (same Oracle EBS does). My Question is which options you recomend to do it and why? Advanced queue Library, pure PL/SQL, java, OS Level, OCI ??? Thanks in advance. Best Regards...
Categories: DBA Blogs

Pages