Tom Kyte

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

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

changing default tablespace of LOB in a partitioned table

Tue, 2018-03-13 13:26
Hi, I want to move a Partitioned table having LOB to a different tablespace, I have moved all data,indexes and partitions, and set the default tablespace for table and indexes every thing is moved/changed except for the default tablespace of ...
Categories: DBA Blogs

Merge statement is throwing ORA-08006 error

Tue, 2018-03-13 13:26
We have a ETL job with merge statement. It was working fine earlier. There was a node eviction on last week and due to that the DB was bounced. After bouncing the DB the procedure started failing with ORA-08006 error. Same error is appearing when we ...
Categories: DBA Blogs

Rollback to save-point rollbacks everything

Tue, 2018-03-13 13:26
Hi, In the below example, "rollback to b;" rollbacks everything instead of clearing only emp3 entries. ---------- <code>create table emp1(empno number,ename char(4),sal number); create table emp2(empno number,ename char(4),sal number); create ...
Categories: DBA Blogs

character conversion for numbers

Mon, 2018-03-12 19:06
Dear Sir, Is there any way to convert number from english to other language like Bangla. e.g = 1 will be show when convert ? is possible.............
Categories: DBA Blogs

DB Cloning

Mon, 2018-03-12 00:46
Hi Team, Would you please share the steps for cloning 11.2.0.4 DB and making it as DR server2, we already have one DR server. now they are planning to make one more DR server. I want to understand what are the ways to clone DB with their in d...
Categories: DBA Blogs

Pages