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: 59 min 56 sec ago

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

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

How to generate some big test tables and rapidly export their SQL data

Tue, 05/29/2018 - 00:00
Hello teams, Our Developer manager asks me to export Oracle production database's three big tables with <i><b>CSV</b></i> or this format <i><b>"insert ... into ..."</b></i> for giving another App teams (their database is MySQL 5.7) once again, I r...

Regular Expression is not working if the search criteria with LIKE and NOT LIKE in single input field

Fri, 05/25/2018 - 00:00
Hi Tom, Need your help ! Please find the LiveSQL link Thanks in Advance ! I have a table st_exp with s_desc column only, user has option to search by s_desc criteria. Scenario :The user may enter text critiria LIKE and NOT LIKE in the ...

Oracle Database - Grant/Revoke High Concurrency

Tue, 05/22/2018 - 00:00
We have an Oracle 10g release 2 database running on a production environment. It's experiencing a lot of concurrency, as Sql Developer 17.4 "Waits for past 1 hour" graph shows. When the database is behaving slow, we take a look at that graph, and ...

Update all database tables with current timestamp

Mon, 05/21/2018 - 00:00
Hi, I have a Production database ( version shown below) with about 2000 tables, having over 10 million rows each. <code>BANNER ----------------------------------------...

performance tunning for Sql query

Sat, 05/12/2018 - 00:00
hi Team, I am struggling in one of query tunning , below are details assoicated with , please analyse and recommend on it . <code> SELECT * FROM (SELECT /*+ INDEX(A INDX14_TABLE1) INDEX(B IDX51_TABLE2) */ * F...

insert into local table with select from multiple database links in a loop

Mon, 05/07/2018 - 00:00
Hi Tom, i would like to apply the Orignial SQL Statement from Oracle MOS DOC ID 1317265.1 and 1309070.1 for license and healthcheck for all of my database instances. My Goal is to create a centralized repository with informations of my databases. Un...

User Password Masking

Wed, 05/02/2018 - 00:00
Hello, Application user dont want to put the clear text password in the script which in turn connecting to database using this authentication. Is there any way to avoid clear text password and to use encrypted password for login . Regards,...

Why are all table subpartitions going in the same tablespace?

Wed, 05/02/2018 - 00:00
Dears, I have a problem regarding automatic list partitioning with hash sub-partitioning. the problem is the automatic created hash sub-partitions are not well distributed on the correct table spaces although the first hash-partitions are well dis...