Feed aggregator

Design question around automatic data change notifications

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

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

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

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

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

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


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

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

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

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

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

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


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

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

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

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

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

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