Tom Kyte

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

Using Factored Subqueries within SQLPLUS

Mon, 2018-07-09 03:26
I have a Perl script that invokes SQLPlus to execute an Oracle query. the SQL uses the WITH clause to create several named queries with the purpose of reusing them later within the query. the general structure of the SQL is as follows: <code> S...
Categories: DBA Blogs

Multi threading in PLSQL

Mon, 2018-07-09 03:26
Team, In current project, one of the batch process procedure is called in Java layer for Multi threading purpose. As current requirement, we need to call same stored procedure in PLSQL layer instead of Java layer. we tried to implement using DBMS...
Categories: DBA Blogs

insert primary key value by trigger

Mon, 2018-07-09 03:26
create or replace trigger t_emp_no before insert on emp_info begin insert into emp_info(emp_no) values(s_emp_no.nextval) end; / i tried to entry primary key emp_no values without showing it on oracle 10g form with S_emp_no sequence .Problem is...
Categories: DBA Blogs

Updating salary rows

Mon, 2018-07-09 03:26
hello Tom, i wrote that mentioned plsql update salary procedure.It compiled also successfully.But it didn't change any of my salary row.Actually,I am new in programming.I want to know .how does is work. how can i confirm that it is working & how ca...
Categories: DBA Blogs

Equality check and TO_NUMBER bug with trailing CHR(0) on SUBSTR function

Fri, 2018-07-06 20:46
Hello Tom ! It seems that Oracle has a bug in TO_NUMBER function on strings with trailing CHR(0). For example it happens when SUBSTR function is used for which there is not defined length parameter. Example: <code>select substr(session_id...
Categories: DBA Blogs

Inherit Privileges privilege to PUBLIC

Fri, 2018-07-06 02:26
I was doing a query on my database against dba_tab_privs where the grantee was PUBLIC. I noticed that for every user I have in my database, PUBLIC has "INHERIT PRIVILEGES" on that user. Should this be something that is allowed? How exactly is this...
Categories: DBA Blogs

LOB Storage parameters

Fri, 2018-07-06 02:26
TOM, I wrote a script to create a table with LOB columns. I specified the LOB storage parameters as follows: <code>CREATE TABLE CONFIGURATION (CFG_ID NUMBER(19,0) NOT NULL ENABLE, CFG_CONTEXT_ID VARCHAR2(255) NOT NULL ENABLE, CFG_...
Categories: DBA Blogs

maximum number of expressions

Fri, 2018-07-06 02:26
Hi, I have a this... Select * from MYDB where MYDB.ID IN (1,2,3...5000000) Any way to export without 1000 output lines ?? Thanks
Categories: DBA Blogs

How to manage disable/enable Maximize/Resize in oracle forms?

Fri, 2018-07-06 02:26
i need to manage disable/enable Maximize,Resize button by programming in oracle mdi parent forms in 10g. Please give me solution of this problems.
Categories: DBA Blogs

Where is alter session coming from? Every time I log in, I see "Session altered."

Fri, 2018-07-06 02:26
Every time I log in to our DB via sqlplus, I see this: $ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 5 11:48:29 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c E...
Categories: DBA Blogs

Does Oracle 9i Data Guard have real time sync?

Fri, 2018-07-06 02:26
Hi We have a Oracle 9i database ,When I set up the data guard I want to use real time sync I got a error .So I want to confirm whether Oracle 9i have this future
Categories: DBA Blogs

update cascade

Fri, 2018-07-06 02:26
How do I cascade update on the key fields of linked tables thereby ensuring referencial integrity.
Categories: DBA Blogs

I want to understand this decode function which applied in this query

Fri, 2018-07-06 02:26
<code> SELECT a.syrmn, a.orgcode, SUBSTR (a.accode, 1, 3) || '0000000' caccode, SUBSTR (a.accode, 1, 6) || '0000' saccode, accode, DECODE (SIGN (SUM (DECODE (a.sntb, 'D', a.samnt, -a.samnt))), 1, SUM (...
Categories: DBA Blogs

ORA-01652 unable to extend temp

Thu, 2018-07-05 08:06
Hi Team, I went through some weird situation where I am not able to figure out the reason behind error ORA-01652 unable to extend temp. While executing a PL/SQL block at one point I am facing this issue. ORA-01652 unable to extend temp. Whi...
Categories: DBA Blogs

CentOS as the Operating System for Oracle 12.2 database server

Thu, 2018-07-05 08:06
Hello Experts, We are planning to install Oracle <b>12.2</b> database server on a Linux <b>CentOS 7.4</b>. <b>My first question</b> May I know whether this setting is supported by Oracle? I've already noted that this OS is not listed under t...
Categories: DBA Blogs

Exadata and direct path read

Thu, 2018-07-05 08:06
Hi Tom, In order for smart scan to be achieved , mainly two thing should happen : 1 - The optimizer should choose to full scan the table. 2 - Scanning the data should be done using direct path read. The first requirement is quit simple , bu...
Categories: DBA Blogs

regexp question

Wed, 2018-07-04 13:46
Hi, I am trying to highlight a search term in a result set feeding a web page. So far I can highlight the entire result, but not just the term match. The problem is in white space mismatches between the column data and the search term that is ente...
Categories: DBA Blogs

IMPLICIT CURSOR ATTRIBUTE SQL%NOTFOUND NOT WORKING

Wed, 2018-07-04 13:46
Hi Tom, I am using implicit cursor attributes SQL%NOTFOUND for the below PL/SQL block. But it raises NO_DATA_FOUND exception. It works fine for update and delete statement as they are not going to raise the NO_DATA_FOUND exception. ...
Categories: DBA Blogs

Email Domain Extraction using sql query

Tue, 2018-07-03 01:06
if the part of email before domain matches for eg in : xyz@gmail.com xyzef@gmail.com if xyz and xyzef does not match ,then do not consider this records and if the scenario is like , if non domain part are equal like xyz@gmail.com xyz@g...
Categories: DBA Blogs

Make Index Invisible for a session

Tue, 2018-07-03 01:06
Hi With the advent of In-Memory capabilities in 12C is there a way to make the optimizer ignore indexes for particular table for a session. The reason being if we want to use Oracle 12C as HTAP (Hybrid Transactional Analytical Processing) we wou...
Categories: DBA Blogs

Pages