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: 19 min 54 sec ago


Tue, 09/18/2018 - 00:00
Hi Tom, I installed Oracle Database 11G in the folder (E:\DB11)and successfully imported a user to it. But when you install the developer 10G IN FOLDER(E:\DEV10). and TRY TO DO a user import into the database ORACLE 11G, i am receiving the follo...

Parse string then flatten into columns

Thu, 09/13/2018 - 00:00
Hi, LiveSQL link not accepted by the form: I have this situation (see link): <b>ID String</b> Id1 Thing1: Sub1, <br>Thing2: Sub7 ,Sub8 , Sub9 <br>Thing3: Sub12 Id1 Thing...

Database link queries

Tue, 09/11/2018 - 00:00
create or replace procedure P_POP_ILC (P_POL_NO VARCHAR2 DEFAULT NULL) is cursor c1 is select * from rsds_locn_exposure@dmn_rsk_150 where RLE_ULM_NO = NVL(P_POL_NO , RLE_ULM_NO); begin DELETE FROM IDS_LOCN_CLM@dmn_rsk_150; comm...

How to use partial indexes for PK/UK keys

Fri, 09/07/2018 - 00:00
LiveSQL link: not working post link into LiveSQL link, but link works Hello all, we have a BI solution (Oracle 12c + ODI11). We have a lot of partitioned tables (daily and mont...

sql performance

Tue, 09/04/2018 - 00:00
Hi Tom,I have met a SQL that with the latest statistics and the execution plan is in the wrong estimate-rows ,that's to say,it's a large difference between the estimate and the actual,the sql statement and execution is below: <code>select t1.card_no...

Storage XMLs in Oracle 12cR2 database

Fri, 08/31/2018 - 00:00
Hello! I created a database in order to store XMLs. I'm going to storage a large amount of data. I was told that Oracle 12cR2 allows to storage XMLs and handle them very good. I created a table with a xmltype column. <code>CREATE TABLE test...

Want to use single select statement instead of various union all's for fetching previous quarter data

Fri, 08/31/2018 - 00:00
I have 5 select statements using union all(showing a few in here) want to use a single statement to fetch the revenue from a table . So based on the condition that AND period_id>=1009 AND Opp_Created_Date_Period_Id<=1009(for every previous quart...

TABLE function and where clause parameters retrieving

Fri, 08/31/2018 - 00:00
Hi there, My goal is to return fieldX or fieldY depending on the where clause (which I don't have any control on since it's generated by a third party software). The TABLE() function is very close to what I want to achieve : select * from T...

Design question around automatic data change notifications

Wed, 08/08/2018 - 00:00
Hello Oracle experts. Thank you in advance for going through my question. In our organisation, we are planning to implement a solution to automatically push the data that is changed in our databases to consumers. They are not a high traffic system...

How to grant v_$Session to a normal user, If we do not have access to sys user

Wed, 08/01/2018 - 00:00
How to grant v_$Session to a normal user, in a normal user we are using in a stored procedure. And we dont have access to sys user. By using select any dictionary privilege we can access but they do not want grant select any dictionary privilege to a...

returning top 10 records based on previous records counts

Sat, 07/28/2018 - 00:00
Hi, I have an ordered table of products and customers as follows: <code>rn product customer 1 859274 A 2 859267 A 3 859250 A 4 863592 B 5 862250 B 6 862700 B 7 862694 B 8 862120 B 9 863592 C 10 862250 C 11 862120 D 12 86...

Improve INSERT speed for loading volume sample data

Fri, 07/27/2018 - 00:00
Hi Team At the time of writing this, on LiveSQL I get "500 Internal Server Error", so will just paste the re-producible code here. I'm building sample application data for testing using PL/SQL. For loading 10M data in USERS_MAPPING table, cur...

Need to create dynamic columns in the report

Thu, 07/26/2018 - 00:00
Hi Tom, I am new to SQL and PLSQL, I have a requirement to create a report, which includes dynamic column for month based on number of months from Program duration. I actually tried to create scripts in LiveSQL, but right now it is not accessib...

Oracle client for MACBOOK

Wed, 07/25/2018 - 00:00
When can we have full Oracle client software for Mac iOS platform as MacBook is widely used by Oracle users.


Mon, 07/23/2018 - 00:00
I have a procedure that was previously using a "slow-by_slow" type procedure. I've converted it to BULK COLLECT and FORALL (test_cur, see livesql). The procedure is obtaining data from 2 tables then inserts this data into 2 corresponding tables. ...

Line Level Logging using controlled FND_LOG\DBMS_OUTPUT statements vs DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

Wed, 07/18/2018 - 00:00
Hi Tom, I have used DBMS_UTILITY.FORMAT_ERROR_BACKTRACE for understanding the exact line where error occurred in a package which is exposed using a service outside. However one of my client feels I should write diagnostic message after certain set ...

Partition query - limiting results

Mon, 07/09/2018 - 00:00
I have a situation where I am trying to determine the taxability of an invoiced line. If the invoiced line quantity is 6, for example, the detail lines should not exceed 6. The problem is that if one of the detailed lines causes the cumulative q...

function-based index issue

Fri, 07/06/2018 - 00:00
Hi Tom, I have table with 1 755 001 rows and functional-based index as follows: <code> create table OC_YKB.TAB_TEST3 (guid number(16), pan varchar2(19 byte)); create sequence oc_ykb.sq_test3; INSERT /*+ APPEND */ INTO OC_YKB.TAB_TEST3 sele...

Equality check and TO_NUMBER bug with trailing CHR(0) on SUBSTR function

Fri, 07/06/2018 - 00:00
Hello Tom ! It seems that Oracle has a bug in TO_NUMBER function on strings with trailing CHR(0). For example it happens when SUBSTR function is used for which there is not defined length parameter. Example: <code>select substr(session_id...

Using identity columns in Oracle 12c

Fri, 07/06/2018 - 00:00
What is the difference between using sequence.netxval as DEFAULT value in a column or check the column as identity? Please, check the following scenarios: <b>SCENARIO 1:</b> CREATE TABLE USER1.TEST_TABLE ( ID NUMBER GENERATED BY DEFAULT AS I...