Tom Kyte

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

OSB - Use os stored procedures

Wed, 2016-10-26 18:46
Hello, I'm working on a SOA project and we're using stored procedures to work with Oracle RAC in OSB. This keep the project clean, organized, avoiding XA and others benefits. The only bad thing is a little extra time to work with. We decided t...
Categories: DBA Blogs

alter session force parallel QUERY parallel

Wed, 2016-10-26 18:46
I have a job running with the error ORA-12842: Cursor Invalidated During Parallel Execution,and I re-run and it's complete. I check the MOS NOTE:1322894.1 to look up the cause,I see that it give the cause is : ORA-12842 can be reported to a user ...
Categories: DBA Blogs

timestamp of a inserted row

Wed, 2016-10-26 00:26
In a classic case of replication (forget the tool of replication here ) Oracle - 2 - Oracle create table abc (anum number, aname varchar2(30) , adate date default sysdate ) ; insert into abc values(1,'1test',sysdate); insert into abc val...
Categories: DBA Blogs

PL/SQL Programming

Wed, 2016-10-26 00:26
Write a PL/SQL block that uses looping to display the value of the number two (2) raised to a power. The power should be given as a local variable called lv_power. For example, if the variable is initialized to 5, the output would be 2 raised to th...
Categories: DBA Blogs

How to avoid external table's data source file be replaced when another query is using the old data source file

Wed, 2016-10-26 00:26
The external table's data source file is updated with some interval, if another query is running, replace the old data source file will cause 1)ORA-29913 and ORA-30653 if not setting reject limit UNLIMITED 2) data inconsistent, part data is old sou...
Categories: DBA Blogs

Query on Stored Procedure - MINUS clause

Wed, 2016-10-26 00:26
Dear All, Ques 1. Please advise if the use of minus clause is best when I am looking to exclude certain specific conditions or shall I be using Not IN? I am in situation where I know what to avoid, not sure what all to select and hence I needed...
Categories: DBA Blogs

When shutting down a container database

Wed, 2016-10-26 00:26
Hello, Some background info: We are working in a 12c multitenant environment on windows server 2012 R2. Container database contains about 20-25 pluggable databases. Typically we have to shutdown the container database to perform maintenan...
Categories: DBA Blogs

ORA-29532: Java call terminated by uncaught Java exception: java.lang.OutOfMemoryError

Wed, 2016-10-26 00:26
When Passing clob size more than 3 MB to java stored procedure I get java.lang.OutOfMemoryError. The data is in the JSON format which we try to deserialize into an object using a java json library in the java stored procedure. We have tried the ...
Categories: DBA Blogs

SP2-0743 and SP2-0042

Wed, 2016-10-26 00:26
Hi Sir, From the doc: SP2-0042 unknown command command_name - rest of line ignored Cause: The command entered was not valid. Action: Check the syntax of the command you used for the correct options. SP2-0734 Unknown command beginning com...
Categories: DBA Blogs

Case construct with WHERE clause

Tue, 2016-10-25 06:06
Hi Tom, I have a question and I don't know if this is possible or if i'm jsut doing something wrong because i get multiple errors like missing right paren, or missing keyword. I want to use the CASE construct after a WHERE clause to build an expre...
Categories: DBA Blogs

Missing Physical Reads

Tue, 2016-10-25 06:06
Hi Tom, Please find below the experimented done, sequentially. Scripts ---------- create table cust (cust_id number, last_name varchar2(20),first_name varchar2(20)); create index cust_idx1 on cust(last_name); SQL> set autotrace on; SQL> ...
Categories: DBA Blogs

Function with multi-dimensional array as parameter?

Tue, 2016-10-25 06:06
How would I define a function that takes a multi-dimensional array as an input parameter and returns json_tbl PIPELINED that has been defined as <code>CREATE OR REPLACE TYPE CIC3.json_t as OBJECT (JSON_TEXT varchar2(30000)); CREATE OR REPLACE TYPE ...
Categories: DBA Blogs

Depth of attributes,

Tue, 2016-10-25 06:06
Hello, I have a situation where my data (for a given sys_id) has values for multiple depths (level1 attribute, level2 attribute and so on). For a given sys_id, I have to select the rows that has the maximum depth. However, as an example, if a va...
Categories: DBA Blogs

Oracle Auditing - Syslog

Tue, 2016-10-25 06:06
Hi Guys, I have two questions with regard to Oracle database auditing via syslog. 1. When auditing via OS syslog, what is the ideal value for the AUDIT_SYSLOG_LEVEL parameter, where AUDIT_SYSLOG_LEVEL = facility.priority It is the priortity...
Categories: DBA Blogs

how to optimize a query that is concatenating fields routinely

Tue, 2016-10-25 06:06
Hi. I'm trying to find a way to optimize this situation below. Example table definition: create table rw_test (A varchar2(10), B varchar2(10), C varchar2(10), D varchar2(10), E varchar2(10), F number(10), entry_date date); ...
Categories: DBA Blogs

Number to Hours and minutes.

Tue, 2016-10-25 06:06
Hello- I have a field which totals the number of hours worked; and for example returns a figure of 41.75 What is the best way to represent this number as 42 hours and 15 minutes? Thanks Venkat
Categories: DBA Blogs

temp table and third party

Mon, 2016-10-24 11:46
Hi, i was use sybase and connect to it with third party name (SAP business object - Desktop intelligent) but now we decide to use oracle instead of sybase but i found problem , most script which i was run in sybase has Temp table like that --------...
Categories: DBA Blogs

Dynamically Logging parameters of procedure or function

Mon, 2016-10-24 11:46
Hi Tom. Is there any way to log the parameters of functions and procedures inside package or as standalone objects without having to write every parameter name and its value. I think of something generic where I can put into procedure/function ...
Categories: DBA Blogs

Generating rownumbers without rownum,row_number(),sum() over and rowid in oracle

Mon, 2016-10-24 11:46
Recently one of my friend faced a question in one of the interview to generate rownumbers without using rownum or Row_number() function. He had suggested to do to running sum of 1's but that was not the right solution as the table contained duplicat...
Categories: DBA Blogs

Automatic Selection of Quality Limited Data (row) for Export

Mon, 2016-10-24 11:46
I would like to use an existing functionality or function or by what other means for automatic selection of quality data for all tables in the tablespace/schema that follows through the foreign keys relation until the end of the chain (the most compl...
Categories: DBA Blogs

Pages