AskTom - Hot Questions

Subscribe to AskTom - Hot Questions feed AskTom - Hot Questions
These are the questions on Ask Tom that have the most comments
Updated: 1 hour 1 min ago

Create Object with Column type attributes

Fri, 04/12/2019 - 00:00
Hi Tom, Please help me on one of our prod issue. We have an object and all the attributes in the object(EMP_OBJ) are columns of a table(Lets say EMP) in diff schema. So when we defined the object we gave the datatype and size of the object attr...

How lob columns are transferred by Oracle Net Services.

Wed, 04/10/2019 - 00:00
Hi Oracle manual states: 'Starting with Oracle Database 11g, Oracle Net Services optimized bulk data transfer for components, such as Oracle SecureFiles LOBs and Oracle Data Guard redo transport services. The SDU size limit, as specified in the n...

Mismatch between XML IsNumber and APEX JSON IsNumber

Tue, 04/09/2019 - 00:00
Hello, <code> DECLARE x xmltype; y CLOB; b varchar2(6); BEGIN WITH a AS (SELECT '<adr><str>bachstr.</str><tel>- 087</tel><tel1>-87</tel1><tel2>-087</tel2></adr>' t FROM dual ) SELECT xmltype(t) INTO x FRO...

Subtotal, Grand Total, ordering and breaking on different fields

Thu, 03/28/2019 - 00:00
Hi Tom I was looking through the already asked questions about Subtotals and Grand totals , but couldn't really find anything that suits my problem. I have a table with the following fields <code>DATE REGION REG_NUM AMOUNT 0...

Infidelity when storing XMLType data elements (spaces)

Wed, 03/27/2019 - 00:00
When saving XMLType data into table. The data fidelity is is not maintained for fields containing only spaces (see LiveSQL). Before save, spaces are present in <COL> </COL> After reading the saved data, spaces are gone and we see empty tag <COL...

How to move to a new tablespace a column LOB of the dataype ANYDATA ?

Wed, 03/20/2019 - 00:00
Hello Masters, I have a table with a PUBLIC.ANYDATA column. <code>SQL> desc XL_EURO_AXAIA.EXT_TCV_TCH_VALEUR Nom NULL ? Type ----------------------------------------------- TCH_ID NOT NULL NUMBER ID NO...

cannot access objects in different schema

Tue, 03/19/2019 - 00:00
I am the admin user and can create tables and procedures in any schema. I have few tables in Schema B which I am referencing in a package i am creating in Schema A however upon compiling it does not see the tables in Schema B. Schema B does not ha...

Number of Chunks in DBMS_PARALLEL_EXECUTE

Wed, 03/13/2019 - 00:00
Hi TOM, I am having a data movement activity from one table to another table. Due to Huge data size in both tables, i have planned to use DBMS_PARALLEL_EXECUTE functionality, so that i can finish it quickly. I am creating chunks based on ROWID. ...

Need rank based on person_no column

Tue, 03/12/2019 - 00:00
Hi All, I have column ord which is common for all the records , then person_no. This can be repeated. I have a column called flag which is a sequence. I was trying populate a new column(NEW_FLAG) which will basically rank the person_no according...

Using connect by level to generate dates and times

Mon, 03/11/2019 - 00:00
I have two SQL Queries: SQL Query 1: <code>select to_date(:sDate,'dd-mm-rrrr')+(level-1)DateRange from dual connect by level <= ((to_date(:endDate,'dd-mm-rrrr')-to_date(:sDate,'dd-mm-rrrr')) + 1);</code> SQL Query 2: <code>select level,to...

How to decide to create index on column

Mon, 03/11/2019 - 00:00
Dear, I talk about single table contain approx 20 to 25 columns. And in production database this table used with different column combination or single column also used in different where clause at different query. When some time query run slow s...

Weighted Average Inventory calculation.

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

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

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

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

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

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

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

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??

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 ) ...

Pages