Tom Kyte

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

Database Design for Invoices Table with Many Null Columns

Tue, 2018-12-18 16:26
Hello, Ask Tom Team. I have a 70 columns table storing valid invoices. There are cases where the invoices are rejected because business rules. When the invoices are rejected just a few columns are filled (up to 5), then all 65 remaining columns ar...
Categories: DBA Blogs

Parallel execution of Select .. for update with Insert

Tue, 2018-12-18 16:26
Hi Tom We have a stored proc something like below: <code>Proc P1 (p_id in varchar2) is cursor c1 is select a,b,c from t1, t2 where t1.pk = t2.fk and t1.id = p_id ; v_id varchar2(10); Begin Open c1 (p_id); fetch c1 into v_...
Categories: DBA Blogs

how to equate substring of one table column to a value in a list from another table column as part of an exists?

Tue, 2018-12-18 16:26
Hello Oracle Masters, I was looking at 'connect by level' (https://asktom.oracle.com/pls/apex/asktom.search?tag=clever-sql) hoping I could make use of it, but don't seem to be able to. Perhaps there is another way? We have a table of products ...
Categories: DBA Blogs

How to extract distinct group of associated records (not exactly hierarchical in nature)

Tue, 2018-12-18 16:26
Hello All, It would be greatly appreciated if someone can provide a way out on how to extract distinct group/array of associated (they are not of parent-child relationship or hierarchical) records as shown below. The same table has been created wi...
Categories: DBA Blogs

ORA-01779: cannot modify a column which maps to a non key-preserved table

Tue, 2018-12-18 16:26
This is on checking below link - https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:273215737113#followup-3016346200346884929 Below answer given by Tom - "Consider, if T had: <code>OBJECT_NAME OBJECT_ID -------------- ----...
Categories: DBA Blogs

How do I Declare/Define a date field in a query

Tue, 2018-12-18 16:26
When i was working with MSSQL I could DECLARE a start & End date (or other parameters) to avoid having to change a variable in the query itself. This is what the table looks like -------------------- ------ ------ DateTime Action User --------...
Categories: DBA Blogs

Business case problem

Mon, 2018-12-17 22:06
I have a problem with that i have table emp_table(name, salary, department_name) I want to move data from this table to two tables employees(name,salary,department_id), department(department_name) And i putted a trigger on departments to make ...
Categories: DBA Blogs

Cannot append more than 32kb data to a clob variable

Mon, 2018-12-17 22:06
Hello, I am trying to write a pl/sql procedure(oracle) in which I am storing the final output in a clob variable, but it gives me the error when I test the procedure by providing the input: [Error] Execution (1: 1): ORA-06502: PL/SQL: numeric or ...
Categories: DBA Blogs

Perf : Insert into Select Vs For All

Mon, 2018-12-17 22:06
I need to perform db operations where I need to inserts .5 million reocrds from another complex query involving joins,exists etc: As of now I am using INSERT INTO SELECT inside a pl/sq package. but some time I am observing for same data set t...
Categories: DBA Blogs

INSERT IF NOT EXISTS

Mon, 2018-12-17 22:06
I HAVE 1 table Create table TB_COBA1 (ID NUMBER , NIS NUMBER , NILAI_A NUMBER , semester number); Create table TB_COBA2 (ID NUMBER , NIS NUMBER , NILAI_b NUMBER , semester number); SQL> Insert into TB_COBA1 values (1,1,1,1); SQL> Inse...
Categories: DBA Blogs

Error with cursor syntax insert where not exists

Mon, 2018-12-17 22:06
i have a new table called jobs_new(job_id , job_title , min_salary , max_salary) and it have only one row with the job_id 'ST_MAN' i want to make anonymous block to copy the other rows from jobs table except when the job_id is 'ST_MAN' because it...
Categories: DBA Blogs

ORA-22835 Buffer too small for CLOB to CHAR or BLOB to RAW conversion when reading AUD$

Mon, 2018-12-17 03:46
Hello Tom, I am trying to read sqltext from AUD$. However, I am skipping the 32K and sql is giving me an ORA Error. ORA-22835 Buffer too small for CLOB to CHAR or BLOB to RAW conversion(actual 4664, maximum 4000). Sql is : SELECT TO_CHAR(NTIME...
Categories: DBA Blogs

I want to use case in a SQL statement but getting error: too many values.

Mon, 2018-12-17 03:46
My Query is this: <code> Select cm.CASE_NUM , lrc.AGENCY_NAME , lg.GENDER , (Select PAT_STAT_PREG, CASE PAT_STAT_PREG when 0 then 'No' when 1 then 'Yes' when 2 then 'Unknown' else 'Not Applicable' END from case_pat_info ) from safetyreport sr...
Categories: DBA Blogs

Objects with Stat locked

Mon, 2018-12-17 03:46
Hi, We have noticed that for few tables and indexes in our DB stats are locked and its causing some queries to use bad plan. We are trying to unlock them and do gather stats to try and help them run faster. We can find the tables and indexes fo...
Categories: DBA Blogs

service issue with pluggable database

Mon, 2018-12-17 03:46
I used windows server 2012 with 64 Bit Operating system and I install Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production. When Create pluggable database as per following link. https://www.oracle.com/technetwork/articles/da...
Categories: DBA Blogs

ACL created but accessing gives ORA-29273 ORA-12541

Mon, 2018-12-17 03:46
I have created a ACL and assigned it to a host. When accessing I get the above erros. I did the following steps SQL> exec dbms_network_acl_admin.create_acl(acl=>'testlitle.xml', description=> 'all hctra.net connections',principal=>'TAG_OWNER't=...
Categories: DBA Blogs

LOAD JAVA Erorrs

Mon, 2018-12-17 03:46
Hi, We have default ORACLE8I(8.1.6) server installation. Following is the JAVA Program. import java.sql.*; import java.io.*; public class DropAny{ public static void object(String object_type,String object_name) ...
Categories: DBA Blogs

Max value to be used in a parallel hint

Mon, 2018-12-17 03:46
Hi, I am working on Oracle Database 11gR2 Enterprise Edition, Single Instance and i want to use parallel hint in one of the query. Below are the values taken from v$parameter : parallel_min_time_threshold AUTO parallel_max_servers...
Categories: DBA Blogs

Packages are invalid -- ORA-04061

Fri, 2018-12-14 02:46
Hi, In our DB, few packages became invalid. And when we verified it we saw that there are no errors related to them. I was expecting sessions to run this package without any error, however when it was executed for first time, we got ORA-04061 erro...
Categories: DBA Blogs

Full-Text Index (Table, Index and LOB files) Size Creep

Fri, 2018-12-14 02:46
Q: How do we "manage" the size of a table's associated SYS_LOB files? Background: I have a table [SRCH_CACHE] that was setup as a lookup table, because the two base tables could not be joined/filtered with sufficient speed. The table is pretty ...
Categories: DBA Blogs

Pages