Tom Kyte

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

Average of 0 and Value - gives incorrect output. Is there a way to ignore the 0 during the average function.

Fri, 2020-02-14 21:11
Hi Tom, I am having a SQL output as follows. <code>A B C D E ---------------- ---------- ---------- ------------ ----------- 2020-02-12 221 68677 99.6...
Categories: DBA Blogs

Indexing strategy for dates in a query

Fri, 2020-02-14 21:11
Hello, Ask Tom team. I have the following query: <code>SELECT guid, sender_id, doc, status, arrived_date, register_date, last_updated_date FROM user1.table1 WHERE (sender_id=:SENDER OR :SENDER IS NULL ) AND (status=:STATUS OR :status IS NU...
Categories: DBA Blogs

Left padded String based on sub-string length

Fri, 2020-02-14 21:11
Hi Chris, I want to restrict the length of the input string to 8 characters by adjusting all the digits (after 'MFT' in below example) from Input string. Means, want to accommodate all the digits in string. Ex.1. Input String is 'MFT123456' ...
Categories: DBA Blogs

Why commit/rollback or any DDL command not allowed in trigger or function?

Thu, 2020-02-13 21:10
Hi Tom, Theoretically I know that commit/rollback/DDL or anything that causes transaction to end are not allowed in a trigger and function if calling function in SQL statement. To use any of those in trigger/function we can use PRAGMA AUTONOMOUS T...
Categories: DBA Blogs

Want to replace a particular string with a null value

Thu, 2020-02-13 21:10
We have 2 types of record_data format in table speedwing table 1st type --> <code>..NTP ID MT20190125 - NTP PRODUCT META BASIC FIX 2019 - TRAVEL START DATE 24/01/2019 - TRAVEL END DATE 31/12/20' ..NTP ID MT20190125 - NTP PRODUCT META BASIC FIX 2...
Categories: DBA Blogs

Roles granted to other roles

Thu, 2020-02-13 21:10
Is it true that roles can not be granted to other roles anymore? I am unable to find documentation of this, but was informed that this was taken away in 12c. If this is true, will you please post the document?
Categories: DBA Blogs

Unable to load jar using dbms_java.loadjava in Oracle

Thu, 2020-02-13 03:10
I need to upload a jar file in Oracle RDBMS using dbms_java.loadjava method. I have granted all the required permission and able to run below function successfully. <code>create or replace function get_java_property(prop in varchar2) return varch...
Categories: DBA Blogs

LINESIZE and displaying data on a screen : the biggest part of execution time?

Thu, 2020-02-13 03:10
Hello Masters, I have one big question about the SQL*Plus parameter LINESIZE and the display of datas. I read in documentation Oracle 19 SQL*Plus : https://docs.oracle.com/en/database/oracle/oracle-database/19/sqpug/sqlplus-users-guide-and-refe...
Categories: DBA Blogs

Transform IF ELSE END IF TO CASE WHEN

Thu, 2020-02-13 03:10
I need to use PL-SQL or SQL to do my query. First: I want to transform all the IF..END IF used in the body of function F_CALCUL_TAUX to CASE..WHEN..END, and use the result inside my query. Secundo: It's possible to transform that and use it inside ...
Categories: DBA Blogs

Function for alphabetical sequence like a spreadsheet

Thu, 2020-02-13 03:10
I need function which convert numeric to alphabet like when I input 1 then it will return 'A', when i input 2 then it will return 'B' please help me on this.
Categories: DBA Blogs

Index rebuild is taking long time before partition exchange

Wed, 2020-02-12 06:04
Hi Tom, I have a work table where I will do all the calculations and do partition exchange to main table. This work table is truncate & load. This process has couple of steps. 1. I will copy few records from main table to work table and ca...
Categories: DBA Blogs

Autotrace traceonly

Wed, 2020-02-12 06:04
Hi, Could you please help to understand: How can I get the execution plan with all details like A rows E rows, etc when trace only enabled. I always get basic plan details like rows bytes and cpu% only. It would be helpful if you could share an...
Categories: DBA Blogs

UPDATE scenario - facing ORA-01427

Wed, 2020-02-12 06:04
Hello Pls see the UPDATE statement below ... I am faced with ORA-01427 when running this Have also tried "... WHERE EXISTS (SELECT 1 ..." but have not got it right I need to update the TAXINV and TAXINVDATE columns in TBLTARGET where TAXINV i...
Categories: DBA Blogs

Oracle Entity Framework Core 3.x

Tue, 2020-02-11 18:04
When will the https://www.nuget.org/packages/Oracle.EntityFrameworkCore/ support dotnet core sdk 3.1 ?
Categories: DBA Blogs

Truncate Partition is very slow

Tue, 2020-02-11 18:04
Hi Tom We got a table which is partitioned on Range (date), Each partition holds approximately 5 Million records, there are 60 partitions. There are 4 Indexes on this table and they are Global Indexes. To remove old data we truncate partition. ...
Categories: DBA Blogs

Scheduling Queries

Tue, 2020-02-11 18:04
Hi Connor, Chris, Could you please have a look at below scenario related to dbms_scheduler program and job setu: <code> -------------- Start Use Case Setup -------------- -----------------------------------------------------------------------...
Categories: DBA Blogs

Labs for Multitenant

Tue, 2020-02-11 18:04
Hi All: I have a question on the Multitenant Fundamentals hands on session scheduled for Thu, Feb 13, 2020 16:00-17:00 UTC: Will these labs be making use of VMs based on https://cloudmarketplace.oracle.com/marketplace/listing/69658839? * I'm a...
Categories: DBA Blogs

Oracle Sequence expiry

Tue, 2020-02-11 18:04
I have few sequences which are about to expires and I need notification over emails when last_number increases. Can you suggest how to do it.
Categories: DBA Blogs

ORA-30926 not raised in merge statement for non-deterministic set of rows

Tue, 2020-02-11 18:04
Hi Connor, Chris et al, Could you please kindly help me better understand when ORA-30926 is to be raised for non-deterministic set of rows on input? Up to 12.1 it worked as a charm , regardless of sort order and number duplicate rows on input O...
Categories: DBA Blogs

ORA-06553: PLS-306: wrong number or types of arguments when selecting object type instance

Tue, 2020-02-11 18:04
We're experiencing this when testing upgrade from Ora 12.1 to 19. What's wrong with the code?
Categories: DBA Blogs

Pages