Feed aggregator

Create Object with Column type attributes

AskTom - Hot Questions - 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.

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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)

AskTom - Hot Questions - 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 ?

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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...

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

Pages