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 52 min ago

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


Thu, 07/05/2018 - 00:00
Hi Team, Could you please have a look at below use case and help to form SQL/PLSQL using which I can get the below report.. Table: order_country : holds order id and country its belong. There can be 100 and more countries in that but for sampl...

How to manage disable/enable Maximize/Resize in oracle forms?

Thu, 07/05/2018 - 00:00
i need to manage disable/enable Maximize,Resize button by programming in oracle mdi parent forms in 10g. Please give me solution of this problems.

I want to understand this decode function which applied in this query

Tue, 07/03/2018 - 00:00
<code> SELECT a.syrmn, a.orgcode, SUBSTR (a.accode, 1, 3) || '0000000' caccode, SUBSTR (a.accode, 1, 6) || '0000' saccode, accode, DECODE (SIGN (SUM (DECODE (a.sntb, 'D', a.samnt, -a.samnt))), 1, SUM (...

converting TIMESTAMP(6) to TIMESTAMP(0)

Wed, 06/20/2018 - 00:00
Currently I have a column with datatype TIMESTAMP(6) but now i have a requirement to change it to TIMESTAMP(0). Because we cannot decrease the precision, ORA-30082: datetime/interval column to be modified must be empty to decrease fractional sec...

How to detect if insert transactions in oracle db are really slow?

Mon, 06/18/2018 - 00:00
At work, I have an Oracle DB (11g) in which I want to detect if there's slow performance while inserting data. Here's the situation: Some production devices send data results from tests to Server A, this server is a important server and it replica...

Get a JSON from a SQL query

Thu, 06/14/2018 - 00:00
Hello! Just a question. Is it possible to write a query that returns a JSON code? If yes, could you give me a brief example? Thanks!

Display master child data as a set - from 2 different tables

Fri, 06/01/2018 - 00:00
Hi , I will be glad if you could help me in this. I have a Parent Table ( ORDER_HEADER ) and a Child table ( ORDER_LINE ). They are linked by order_id. ORDER_HEADER holds order details for customers and ORDER_LINE holds the child lines for ea...

Parsing a CLOB field with CSV data and put the contents into it proper fields

Fri, 06/01/2018 - 00:00
My Question is a variation of the one originally posted on 11/9/2015. parsing a CLOB field which contains CSV data. My delimited data that is loaded into a clob field contains output in the form attribute=data~ The clob field can contain up to 6...