Feed aggregator

Weighted Average Inventory calculation.

AskTom - Hot Questions - Wed, 03/06/2019 - 00:00
Hello, Hope you are at the best of you health. I am working in Microsoft Windows environment and installed database is Oracle 11gR2. I have following two table with sample data. <code>CREATE TABLE stock_master ( vno INTEGER, vdate DATE, vtype...

Index skip scan with high NDV leading column

AskTom - Hot Questions - Wed, 03/06/2019 - 00:00
Hello Tom, I have the situation: A table tbl (220 Mio recs) the following query <code>select col_a, col_b, col_c from tbl t where Mod(Dbms_Rowid.Rowid_Row_Number(t.rowid, 2) = 0 --pred 1 and col_d =<lit>; --pred 2</code> col_d is in...

Caching for PLSQL packages over ORDS

AskTom - Hot Questions - Tue, 03/05/2019 - 00:00
I need to cache few values when the plsql procedure is called through a rest service multiple times i.e. when it is executed from a same user multiple times for optimization. I am calling below package procedure through ORDS rest service. Belo...

How to group by for a data set

AskTom - Hot Questions - Wed, 02/27/2019 - 00:00
Hi Tom, I have a table with many records for example as below. <code>create table test (a varcahr2(20)); insert into test values ('1'); insert into test values ('2'); insert into test values ('3'); insert into test values ('abc-oo cde')...

Oracle 12.2: Delete Privilege needs an additional Select Privilege

AskTom - Hot Questions - Tue, 02/26/2019 - 00:00
Hi, Why do we need an extra select privilege additional to the delete privilege? From my point of view, this does'nt make sense: <code>--execute as user a: create table b.t (col varchar2 (10)); insert into b.t values ('a'); commit; gr...

getting error ( ORA-06553: PLS-306: wrong number or types of arguments in call to) after migrating code from oracle 11.2.0.3.0 to 12 c

AskTom - Hot Questions - Fri, 02/22/2019 - 00:00
we are in the process of upgrading our oracle database from 11.2 to 12.2 ran into an error: ORA-06553: PLS-306: wrong number or types of arguments in call to I have created a simplified script that recreates the issue. the error occurs at com...

Performance issue with data densification process

AskTom - Hot Questions - Mon, 02/18/2019 - 00:00
Hi Tom, I am facing an issue while making sparse data dense. Here is the problem statement: We are getting price information for securities from upstream in a file and prices will come only when either there will be new security on boarded or t...

With clause in distributed transactions

AskTom - Hot Questions - Thu, 02/14/2019 - 00:00
Hi Tom ! As there is put a restriction on GTTs: Distributed transactions are not supported for temporary tables does that mean that inline views in a query, i.e. using WITH clause, but those with MATERIALIZED hint will not work properly...

Efficient way to fetch data using rownumber from a billion record table??

AskTom - Hot Questions - Thu, 02/14/2019 - 00:00
I'm trying to fetch required data from a billion record table using the row number approch (i.e, select "ingest_ts_utc" from (SELECT to_char(sys_extract_utc(systimestamp), 'YYYY-MM-DD HH24:MI:SS.FF') as "ingest_ts_utc" ,ROWNUM as rno from XYZ.ABC ) ...

Access Table Without Synonym and Without Schema Name

AskTom - Hot Questions - Wed, 02/13/2019 - 00:00
I have a schema called HCR_SCHEMA, in which I have one table called TEST_EMP, and I have a user say USER_A and granted him select on the TEST_EMP table. I connected as USER_A as tried to execute SELECT * FROM TEST_EMP. Obviosuly it threw me error ...

Update production database from ETL Process

AskTom - Hot Questions - Wed, 02/13/2019 - 00:00
Hello, Ask Tom Team. I am using a Microsoft SSIS ETL Package to insert data to Oracle (source and destination). I want to do an incremental load every day but I have to flag the rows that are already loaded in the destination. It is not just s...

DB Link between Oracle 12c (Linux) and Oracle 11.2 R2 (Windows)

AskTom - Hot Questions - Tue, 02/12/2019 - 00:00
Hi everyone, I have a problem with DB Link. I'm trying to create a dblink from and Oracle 12c (installed on Linux Server) and Oracle 11.2 R2 (installed on Windows Server). I've correctly created the dblink, but when i try to query the result is: "<b...

Log Recovery in Dataguard alert log

AskTom - Hot Questions - Thu, 02/07/2019 - 00:00
Hi all, I am using Oracle 11g Active Dataguard in maximum performance mode. I notice 2 general pattern of log recovery in the database alert log. ===================== Pattern1 <code>RFS[7]: Selected log 11 for thread 2 sequence xxxxx Arc...

Parsing through a Long Character with 255 characters and Stripping out Words

AskTom - Hot Questions - Wed, 02/06/2019 - 00:00
Hi, I have a long character field for every one of my 10,000 rows and I need to write a PL/SQL that goes through every row a parses this long field for any WORD (Mix of Alpha and Numeric) up to 6 characters and spits it out and Save it to a differ...

Pages