Tom Kyte

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

How to save and show real executions Plans without EE resources

Tue, 2018-03-20 10:26
Hi - I would like to know if it is possible to save (in a plan table, say) the real execution plan of a given SQL present in the SQL cache and later show it via DBMS_XPLAN - first please note, I'm talking about the real execution plan (and including ...
Categories: DBA Blogs

SQL problem [ORA-01722: invalid number]

Tue, 2018-03-20 10:26
Hi Tom, Could you please help me with the below SQL. I have 2 users "dev" and "admin", both are in same database. DEV>DESC TB_CMA086_US_CITY Name Null? Type --------------------------------...
Categories: DBA Blogs

database sql performance with histograms

Tue, 2018-03-20 10:26
Hi Team, is their any way to find old histogram on column . in my situation , i see table having column status having histogram height based , but earlier it was freq. based , in this situation i know this because we have collected data , but in ...
Categories: DBA Blogs

Different ways of writing SQL for no particular reason

Tue, 2018-03-20 10:26
Find highest salary in each department without using MAX function Use a single SELECT statement only. For an added complexity (optional): try not using ANY functions at all (neither group, nor analytic, not even scalar) ...
Categories: DBA Blogs

Creating dates with date ranges

Tue, 2018-03-20 10:26
Hi Tom, i have a table MY_DATES (START_DATE DATE, END_DATE DATE) with date like : <code> START_DATE END_DATE --------------------------- 18-DEC-17 07-JAN-18 27-JAN-18 06-FEB-18 08-MAR-18 18-MAR-18 </code> ...
Categories: DBA Blogs

Covert clob data into JSON format

Tue, 2018-03-20 10:26
I have two clob columns in my table KEY & VAL. Key column data: ACTION|COCODE|SNCODE|SPCODE|PARAMETER_ID|PARAMETER_VALUE|PARAMETER_ID|PARAMETER_VALUE|SNCODE|SPCODE|SNCODE|SPCODE|PARAMETER_ID|PARAMETER_VALUE VAL column data: Add|CONTR001920905...
Categories: DBA Blogs

Check for Expiry two weeks prior

Tue, 2018-03-20 10:26
Hi Tom, i have a question regarding some expiry dates that I have in a table and need to create a function that will check for those dates and will notify before they expired in two weeks. i am very new to this and hoping that you might help me...
Categories: DBA Blogs

Moving from ADG to Golden Gate for an DB centric application

Mon, 2018-03-19 16:06
One of my application is currently running on unsupported HW & Oracle version (Solaris 9 & Oracle 10g) with ADG between primary & DR and as part of platform uplift plan is to move to private enterprise cloud (OEL 6/7 with Oracle 12c). Management ask ...
Categories: DBA Blogs

Unable to find EMP & Dept in Object Browser (APEX oracle)

Mon, 2018-03-19 16:06
i have recently made an account on APEX oracle, my trainer illustrated how to make the account on Apex and use it for training purpose PL/SQL, therefore i tried by myself when i make the account and go to object browser there i am unable to find any ...
Categories: DBA Blogs

output customization

Mon, 2018-03-19 16:06
i have a table create table demo(ord_no number(4),division varchar2(100)); value for the table is insert into demo values(1,123456789012); i just want to create a procedure to do the following cut the division into 4 in length(me...
Categories: DBA Blogs

using max function

Mon, 2018-03-19 16:06
Hi, Look at here..... Find highest salary in each department without using MAX function. Note:- Use a single SELECT statement only. For an added complexity (optional): try not using ANY functions a...
Categories: DBA Blogs

sqlloader versus external table.

Mon, 2018-03-19 16:06
hi Tom, Is there any advantage of sql* Loader over the new 9i feature external table. Which of them is faster. sql* loader direct path load or direct path load from the external table. thanx in advance Nikhil India
Categories: DBA Blogs

i face prob with the returned cursor from function

Mon, 2018-03-19 16:06
<code>i face prob with the returned cursor from function ,i created function to return cursor ,and in the script ,i use the function to give me the cursor after that in loop i try to print all data that is in the cursor . but it didnt work , and ...
Categories: DBA Blogs

sqlloader decimal fields

Mon, 2018-03-19 16:06
Hi Sir, hope your doing good.... How to load decimal numbers from file to table using sql loader?..Please give an practically one example...Thank You.
Categories: DBA Blogs

Generate PDF documents from PL/SQL

Mon, 2018-03-19 16:06
Hi Tom, How would you recommend generating PDF documents from within "plain" PL/SQL? Thank you, Dieter
Categories: DBA Blogs

Week start and end date for a given date

Thu, 2018-03-15 02:06
Hi Tom, I want the sql to get the start of the week date and end of the week date for a given date and my week starts from saturday and ends with friday. Example, if the given date is 03/mar/2018 then start date is - 03/mar/2018 and end date...
Categories: DBA Blogs

Converting Long Data Type to CLOB got ORA-06502

Thu, 2018-03-15 02:06
Hi, I am running following pl/sql code to extract image data from the table. I am able to extract data which has length less than 32768 how ever I get <b>ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind</b> when the length is...
Categories: DBA Blogs

PL SQL Type as bind variable in Dynamic FORALL

Wed, 2018-03-14 07:46
SQL Version: <code>Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0 PL/SQL Release 12.1.0.2.0 - Production 0 CORE 12.1.0.2.0 Production 0 TNS for Linux: Version 12.1.0.2.0 - Production 0 NLSRTL Version 12.1.0.2.0 - ...
Categories: DBA Blogs

Invalid number

Tue, 2018-03-13 13:26
I have a table that has two VARCHAR2 columns. create table A ( char_col varchar2(200), char_col2 varchar2(200), num1 number(30)); insert into A values ('1,23,234','1,23,234','550'); insert into A values ('44,23,234','5,130','1280');...
Categories: DBA Blogs

dump table selective rows to csv

Tue, 2018-03-13 13:26
Greetings I'm trying to include search criteria in below script's query ('select * from ' || p_tname ;) to fetch only those rows/employees which were hired during last 24 hours. I have tried below -- SQL> create or replace procedure dump_tab...
Categories: DBA Blogs

Pages