DBA Blogs

SQL problem [ORA-01722: invalid number]

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Presenting At ODTUG Kscope18 Conference in Orlando, Florida 10-14 June 2018

Richard Foote - Mon, 2018-03-19 22:47
I’m very excited to have a couple of papers accepted at this year’s ODTUG Kscope18 Conference in sunny and likely very hot Orlando, Florida between 10-14 June 2018. I’m excited because I’ve been to a few of these conferences before and they have always been excellent events. As a mainly Oracle Database kinda guy, it’s […]
Categories: DBA Blogs

Moving from ADG to Golden Gate for an DB centric application

Tom Kyte - 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)

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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.

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Subscribe to Oracle FAQ aggregator - DBA Blogs