Feed aggregator

how to sum pivot query results

AskTom - Hot Questions - Fri, 03/13/2020 - 00:00
<code>with t1 as (select 'dfu1' dfu, 1 type, '01-Jan-2020' startdt, 10 period1, 20 period2, 30 period3, null period4, 50 period5 from dual union all select 'dfu1' dfu, 4 type, '01-Jan-2020' startdt, 1 period1, 2 period2, 3 period3, 4 period4, nul...

Stored procedure to truncate multiple tables not doing anything

AskTom - Hot Questions - Fri, 03/13/2020 - 00:00
Hello, I have written a stored procedure to truncate the multiple tables. When running the procedure no error but when checking by select statement still I can see that in the table. This means the procedure is not doing anything. Any help much ap...

Binding variables to dynamic SQL for a cursor

AskTom - Hot Questions - Thu, 03/12/2020 - 00:00
Looking at examples on https://docs.oracle.com/cd/A97630_01/appdev.920/a96624/11_dynam.htm I am trying to create a simple example of a procedure that returns a ref cursor and safely handles optional parameters. The two things I'm not clear on is how...

Partition strategy implementation

AskTom - Hot Questions - Thu, 03/05/2020 - 00:00
Hi Team, We have non partitioned Audit tables with billion of rows and now we are trying to partition those tables and archive them periodically . From your blog we understood that partition strategy should be purely based on data access and ma...

JPPD not occurring with nested UNION and/or row generation

AskTom - Hot Questions - Tue, 03/03/2020 - 00:00
Hello, I am faced with a performance issue with a sql in our application and am struggling to come up with ways to influence the optimizer to use JPPD. I have tried to simulate the setup as much as possible and can reproduce the issue on Livesql ...

Best way to check for existence based on last event date

AskTom - Hot Questions - Tue, 02/25/2020 - 00:00
Hello, I am trying to find out the best way to include a criteria in a sql to check is the latest event for a user has occurred before a specific date. Below is my table setup and brief description USER_NP => Parent table listing a...

Unable to load jar using dbms_java.loadjava in Oracle

AskTom - Hot Questions - Wed, 02/12/2020 - 00:00
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...

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

AskTom - Hot Questions - Wed, 02/12/2020 - 00:00
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...

Function for alphabetical sequence like a spreadsheet

AskTom - Hot Questions - Wed, 02/12/2020 - 00:00
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.

Scheduling Queries

AskTom - Hot Questions - Mon, 02/10/2020 - 00:00
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 -------------- -----------------------------------------------------------------------...

Getting error -12842 : ORA-12842: CURSOR INVALIDATED DURING PARALLEL EXECUTION

AskTom - Hot Questions - Mon, 02/03/2020 - 00:00
Below is my scenario. We are getting error randomly in production. when we restart the process error disappears. we are not able to reproduce even in lower environment. Can you please help in letting us know the potential issue with insert statement ...

Unpivoting billion rows

AskTom - Hot Questions - Thu, 01/23/2020 - 00:00
Hi ask Tom team, Hope you people are doing great in 2020. I am working in migration team ,our goal is to take csv file from client and ultimately transform data as per our production table structure . Requirement :- Recenty we have rec...

User Requiring Access to SQL Tuning Advisor Getting ORA-13616

AskTom - Hot Questions - Thu, 01/23/2020 - 00:00
Hi, we have created a User to manage the SQL Tuning Advisor. Due to internal policy /Guideline we created a Role and assigned the Role the following Oracle Privs : <code>Role Privs ------------------------------ PERF_OWN CREATE...

Calling executable from Scheduler

AskTom - Hot Questions - Wed, 01/22/2020 - 00:00
<b></b><code></code><u></u>Hi Tom I have been trying to call an executable shell script placed on AIX 7.2 from Oracle 12.2 via following code: <code>BEGIN DBMS_SCHEDULER.create_job ( job_name => 'GEN_DAILY_CNT_FILE', job_typ...

99% wait time on SQL*Net more data to client

AskTom - Hot Questions - Tue, 01/21/2020 - 00:00
Hello Team, We are transferring 4 million records over the network and SQL*Net more data to client is 99%. We tried multiple drivers like ODBC, OLEDB , Oracle Drivers, QlikSense application Oracle drivers but the % didn't get reduced. We have ...

how to load XML file into oracle table and using XML query to get result

AskTom - Hot Questions - Sun, 01/19/2020 - 00:00
Hi Tom, I have XML file from Web Report. there are some error records in the file, it is difficult to find the error in XML file, I want to load this file into oracle table. then use XML query. below is sample file and expected result. This is fir...

Benchmark summary processing technique

AskTom - Hot Questions - Tue, 01/14/2020 - 00:00
I have a package that is responsible for processing data from Detail tables. <b>All query logic and other parameters that is used to process data is stored inside a table</b>, which acts as a configuration table (sample example of configuration table...

changing where clause using case statement

AskTom - Hot Questions - Tue, 01/14/2020 - 00:00
Hello Tom Is it possible to change the where condition (using case statement) based on certain variable? For example <code> var T varchar2(1) exec :T := 'E'; var E number; exec :E := 7788; var N varchar2(20) exec :N := 'MILLER'; sele...

Oracle Flashback Data Archive

AskTom - Hot Questions - Sun, 01/12/2020 - 00:00
Hi All, We are using Oracle Flashback Data Archive in our database to track Audit in most of the critical tables. We initially planned to store data for 6months but there are few errors / issues we face making it unstable. So we have it as retentio...

Is “With clause” not allowed within a cursor?

AskTom - Hot Questions - Thu, 01/09/2020 - 00:00
Hi Tom, We need to drop old partitions in regular basis for some tables partitioned by interval. To keep 60 days data for the TEST table partitioned by day interval, for instance, I could get partitions name older than two months by using the f...

Pages