Tom Kyte

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

JSON query returning ORA-01460

Fri, 2016-12-09 06:26
We have a problem with a JSON query retruning ORA-01460. I thin it's because it's referring to an actual parameter formally defined as varchar2. This string is > 4000 (about 4400 chars). I see this error can be replicated by referring to a large ...
Categories: DBA Blogs

condition after join vs where

Fri, 2016-12-09 06:26
Hi Tom, Good day. Which of this queries is better in performance specially when joining with multiple tables and huge data ? example. 1.option 1 SELECT h.name, h.je_source, l.entered_dr, l.accounted_dr FROM gl_je_headers h INNER JOIN...
Categories: DBA Blogs

Need alternative for PIVOT for dynamic date values

Fri, 2016-12-09 06:26
Hi Tom, I need a alternative for PIVOT, i need to pass the date values dynamically. Date should fetch from current month first day to last day. Result should be count of employees assigned to a shift on each day of the month. Please help me on t...
Categories: DBA Blogs

Use pragma

Fri, 2016-12-09 06:26
Hi Tom, I have a stored procedure where I have to check values of several parameters and if they are empty I have to return -1. Oracle cannot use return statement. I cannot use function because it return control to stored procedure. If I use pragma,...
Categories: DBA Blogs

Oracle Utilities ( SQL Loader) for Ubuntu

Thu, 2016-12-08 12:06
I need to install SQL Loader in ubuntu to access the Oracle 12c r1 on different box. My understanding is that Ubuntu is not certified for Oracle . Want to confirm my understanding . If yes , please provide the link to install instructions.
Categories: DBA Blogs

Double quotes found on the cloud which are not in the create script

Thu, 2016-12-08 12:06
Mr. Kyte... We have purchased a few instances of Oracle 12c in the cloud and most recently have encountered a curious double quoting in our tables. create table t_is_it_quoted ( is_this_text_quoted varchar2(20) ); when we look at...
Categories: DBA Blogs

Insert/update table based on condition

Thu, 2016-12-08 12:06
We have 2 tables one having 4 columns and second has 2 columns. Create table A(Currency_old varchar(20),Currency_indicator_old varchar(20),Currency_New varchar(20),Currency_indicator_new varchar(20)); Create table B (Currency_old varchar(20),C...
Categories: DBA Blogs

DML operations in before/after trigger

Thu, 2016-12-08 12:06
SQL> CREATE OR REPLACE package DB_ADMIN.TRI_pkg is 2 type t_dblogin is table of t1%rowtype index by pls_integer; 3 type t_audit is table of audit_tab%rowtype index by pls_integer; 4 5 v_dblogon tri_pkg.T_DBLOGIN; 6 v_audit tri_pkg.t_audit; ...
Categories: DBA Blogs

SQL Loader to load multiple tables from CSV input file

Thu, 2016-12-08 12:06
I have data file which has comma separated values : col1,col2,col3,col4,Key1,Value1,Key2,Value2,Key3,Value3............ Requirement is to load col1-col4 inTab1(col1,col2,col3,col4) AND second part of data is key/Value pair, number of key/value...
Categories: DBA Blogs

Non-pipelined table function with dynamic sql statement

Thu, 2016-12-08 12:06
Hello, Ask Tom Team. Would you mind telling me how I can create table function without pipelined option (I think that it unnecessary, because I have not a big data in it) in a package level using dynamic sql and table of records collection. I know ...
Categories: DBA Blogs

Can we update a locked row column in other session ?

Thu, 2016-12-08 12:06
Hi Tom, I have created one table for example like CREATE TABLE reservation ( Reservation_Id NUMBER, Reservation_number NUMBER ); INSERT INTO reservation (Reservation_Id, Reservation_number) VALUES(1, 100001); INSERT INTO reservat...
Categories: DBA Blogs

Change Decimal pointer

Thu, 2016-12-08 12:06
Hi, Please inform how can replace decimal operator dot(.) with comma(,) in oracle database Permanently I want to change it in database. Also no separator is required for numaric values.All decimal fields of table will accept comma(,) i...
Categories: DBA Blogs

How to use Clob parameter inside dynamic sql statement to execute immediate

Thu, 2016-12-08 12:06
Hello, Ask Tom team! There is my problem. i have a package procedure with some parameters: procedure updt(p_col varchar2,p_value clob) is v_update_clob clob; begin v_update_clob:= 'update table set :p_col=:p_value where cond = ''some_conditi...
Categories: DBA Blogs

Retrieving DBMS_OUTPUT.put_line from JDBC?

Wed, 2016-12-07 17:46
I'm using a Java application to process an incoming feed. The app inserts data into some Oracle tables, then runs an Oracle stored procedure. The Java application is called from a UNIX script, connects to Oracle using Oracle's thin client driver an...
Categories: DBA Blogs

index issue with our partitioned table ora14196 and question if plan OK?

Wed, 2016-12-07 17:46
Hello Tom I have an index issue with our partitioned table this table cw_tb_zvlist11 has about 500 mio rows inserted and the insert process is still active I see that unfortunality we created the primary index as a nonunique one. Now I tr...
Categories: DBA Blogs

adding column with default value 0

Wed, 2016-12-07 17:46
Hi Tom, I have read lot of posts which you have discussed on the subject of adding column with some default value. Thanks a lot for such a great service you render to oracle community. Unfortunately one of my friend informed me some bugs are as...
Categories: DBA Blogs

Is there another option for Next Value For

Wed, 2016-12-07 17:46
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for 64-bit Windows: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production How would I go abo...
Categories: DBA Blogs

Flashback Archive Internal History table is not compressed -- Kindly suggest

Tue, 2016-12-06 05:06
Hi Tom, Once flashback archive is enabled for a table, a corresponding history table will be created by oracle internally. It is automatically partitioned and compressed as well. But when I have enabled FBA for a table, the history table is partit...
Categories: DBA Blogs

Column with default value

Tue, 2016-12-06 05:06
I have a table in which one of the column is as below create_date TIMESTAMP(6) default SYSDATE, When the record is inserted from the GUI(URL) , it doesn't insert any value for create_Date .. But when inserted from other than URL i...
Categories: DBA Blogs

Redo Log 4K Blocksize

Tue, 2016-12-06 05:06
Good Evening, In 11g, I've read about the possibility of setting redo logs to have a blocksize of 4k. Supposedly, the blocksize is automatically set based on the block sector of the disk. Supposedly, high capacity disks have block sectors of 4k....
Categories: DBA Blogs

Pages