Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 1 day 21 hours ago

STATS_BINOMIAL_TEST does not work

Mon, 2019-04-15 06:26
Dear Tom, I tried to run this query, following example here https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions150.htm: <code></code> SELECT AVG(DECODE(cust_gender, 'M', 1, 0)) real_proportion, STATS_BINOMIAL_TEST ...
Categories: DBA Blogs

issue with exponent value with number column

Mon, 2019-04-15 06:26
Hi , i am summing up the number column based other columns. But while doing sum small value converted into exponent. for exp. to_comm Number(10); 0.0000474 converted into 4.74E-5. I can change the setting of client to see small value ...
Categories: DBA Blogs

Insert trigger that do an update if record exists

Mon, 2019-04-15 06:26
I have a table: <code>create table test_tbl (id number, text varchar2(50));</code> with this data in it: <code>insert into test_tbl values (1,'Text 1'); insert into test_tbl values (2,'Text 2');</code> Now I want to insert a record, but ...
Categories: DBA Blogs

ORA-14692: STORE AS clause is not allowed for extended character type column

Mon, 2019-04-15 06:26
I just want to exp and imp,but when I imp the dmp file to another database,the failer message occured below: ORA-14692: STORE AS clause is not allowed for extended character type column. what should I do?
Categories: DBA Blogs

setting isolation level after gather stats worked; but did not work before it. Why ?

Mon, 2019-04-15 06:26
Hi, Please see below :- <code> SQL> create table t (x int); Table created. SQL> insert into t values (1); 1 row created. SQL> alter session set isolation_level=serializable; ERROR: ORA-01453: SET TRANSACTION must be first stateme...
Categories: DBA Blogs

global index in partitioned table

Fri, 2019-04-12 05:06
I have a question about a global index on a history table that has been partitioned into weeks from 1 to 53 with subpartitions from 1 to 4 all this into a list type partitioning. The question is that local and global indexes have been created. What w...
Categories: DBA Blogs

Update an ordered list value with consecutive numbers

Fri, 2019-04-12 05:06
I need to reset an ordered list to be consecutive numbers (ints) while maintaining the original ordering. 2,4,6 needs to become 1,2,3, as does -6, 53, 5498. I tried using rownum: <code>update T1 set SIBLING_ORDER = rownum where PARENT_ID...
Categories: DBA Blogs

Configuration of redo log and standby redo log in single instance standby database for RAC primary database

Fri, 2019-04-12 05:06
Hello, I would like your support to configure correctly at the level of redo log and standby redo log in a single instance standby database, whose primary base is RAC, so that there is no inconvenience when performing the change of roles. At pres...
Categories: DBA Blogs

Getting lowest record from duplicates

Fri, 2019-04-12 05:06
Hi Tom, Following is the View definition. <code>SELECT C.VERSION, C.DOW, C.DELV_TYPE_CODE, C.CURR_DELVPT_SYS_ID, C.EMP_ID, C.ZIP5, C.ZIP4, C.ZIP2, ...
Categories: DBA Blogs

Regular expression to find rows with characters that are not letters, numbers or keyboard symbols

Thu, 2019-04-11 10:46
Hi, This is the table t2 I have, Sl.No. Junk 1. Cigarette use ? last used 4/2017 ? NS at best; 2. test]]]]]]] 3. [[[[test 4. [CDATA[]] Now I want to write query to get only the 1st row which have junk chara...
Categories: DBA Blogs

How lob columns are transferred by Oracle Net Services.

Thu, 2019-04-11 10:46
Hi Oracle manual states: 'Starting with Oracle Database 11g, Oracle Net Services optimized bulk data transfer for components, such as Oracle SecureFiles LOBs and Oracle Data Guard redo transport services. The SDU size limit, as specified in the n...
Categories: DBA Blogs

How to check the Table structure of both the data bases & merge into single database.

Thu, 2019-04-11 10:46
Two Banks are running on Oracle database for the same application software. Now those 2 Banks merged and need to merge the Oracle Databases also.. How to check the Table structure of both the data bases & merge into single database.
Categories: DBA Blogs

Order of Update statements

Wed, 2019-04-10 16:26
Hello, Thanks for taking up this question. I have noticed a strange behavior in Oracle database (11g R2). The database I am working on has 2 instances (RAC implementation). I am executing a script containing multiple update statements. They ar...
Categories: DBA Blogs

Execute procedure in anonymous block returns ORA-06550 & PLS-00222

Wed, 2019-04-10 16:26
Hi, I created these objects: ------------------------------- <code> create table mwallet.tb_test (test_id number GENERATED ALWAYS AS IDENTITY, test_name varchar2(50) ); </code> ------------------------------- <code> create or replace pr...
Categories: DBA Blogs

How would you implement this? Returning name format based on country code

Wed, 2019-04-10 16:26
Here's the situation. System is used in several countries. User logs in and can only be at one country at a time. There's id in the system, which belongs to one country. There are functions/procedure in the system, which are country specific. F...
Categories: DBA Blogs

How to avoid functions being called twice in a single sql-statement?

Wed, 2019-04-10 16:26
Hi We have merge statements which get their values from non deterministic functions. Some of the updates merge does are not 'real' in a sence that actual values do not change. We would prefer so that these updates are not made at all but do not...
Categories: DBA Blogs

Mismatch between XML IsNumber and APEX JSON IsNumber

Wed, 2019-04-10 16:26
Hello, <code> DECLARE x xmltype; y CLOB; b varchar2(6); BEGIN WITH a AS (SELECT '<adr><str>bachstr.</str><tel>- 087</tel><tel1>-87</tel1><tel2>-087</tel2></adr>' t FROM dual ) SELECT xmltype(t) INTO x FRO...
Categories: DBA Blogs

Creating dummy data for chart

Tue, 2019-04-09 22:06
Hi, I am generating a bar chart in Apex and my data has few gaps as a result there are gaps between bars.If I fill data in query with zero value then my bar chart will be correctly displayed. Below is data and required output <code>create table...
Categories: DBA Blogs

Like Operator with IN clause

Tue, 2019-04-09 22:06
I have to compare more than one Patter using LIKE operator Say I want to prepare query SELECT name FROM employee WHERE name <b>LIKE IN</b> ('sasho','shashi%','rags')
Categories: DBA Blogs

JSON path expression syntax error

Tue, 2019-04-09 03:46
Hi TOM, I am trying to use json_value function infor loop as below. then getting error as 'PL/SQL: ORA-40442: JSON path expression syntax error'. Could you please help me with solution. Thanks set serveroutput on; DECLARE p_dn_list CLO...
Categories: DBA Blogs

Pages