Feed aggregator

Optimize Query

AskTom - Hot Questions - Tue, 10/17/2017 - 00:00
I want to optimize the following query SELECT t.merchant_id, t.brand_id, t.transaction_type, t.trns_currency_code, <b>COUNT (*) AS total_no_tx, SUM (t.mv_is_trnx_approved) AS approved_no_tx, ...

Using Analytical Functions to get Group Total

AskTom - Hot Questions - Wed, 10/11/2017 - 00:00
I need a GROUP TOTAL for EACH MONTH that SUMS up all the values in APPL column even when the query is for a particular APPL value. <code>create table test_grp_total (dttm_month date, appl varchar2(5), mins number) insert into test_grp_total va...

TNS error about 'TNS-12535 and TNS-00505' in alert log file of my Oracle 11.2.0.4.0 ?

AskTom - Hot Questions - Thu, 09/21/2017 - 00:00
Hi,teams I found that there has a plenty of TNS error about 'TNS-12535 and TNS-00505' in alert log file of my Oracle 11.2.0.4.0. part content as follows, <code> ...... *********************************************************************** Fata...

extract numbers from varchar2 upto first occurrence of a non-numeric value

AskTom - Hot Questions - Thu, 09/21/2017 - 00:00
On Oracle12 database, we have this table tbl_house_number that has one column "house_number" which is a varchar2 and has data entries of all different combinations. And all I need is the numbers from left to the first occurrence of a non-numeric li...

need to extract numbers from a varchar upto a non-numeric character

AskTom - Hot Questions - Tue, 09/19/2017 - 00:00
Hi, I have a column called house number which is a varchar2, and it has all kinds of combinations of data entered (incorrect format) as shown below: House_Number ------------- 416-A 416-A 1573A 131# A23 133 A-21 133 A22 13320A 133A-21 1...

Need to generate the output in incremental loops

AskTom - Hot Questions - Fri, 09/08/2017 - 00:00
Hi Tom, I have a query which returns the below output. ONE TWO THREE I need to get the out as ONE ONE TWO ONE TWO THREE ONE TWO THREE FOUR i.e like incremental sets (by step 1). ONE ONE TWO ONE TWO THREE ONE ...

Standby Redo Logs Required For All Protection Modes ?

AskTom - Hot Questions - Fri, 09/01/2017 - 00:00
Hi TOM, Why do we need standby redo logs on standby database when the protection mode is MAX PERFORMANCE? Oracle documentation (http://docs.oracle.com/database/121/DGBKR/dbresource.htm#DGBKR270) says : "You must add standby redo log files on a...

Tuning Question

AskTom - Hot Questions - Wed, 08/30/2017 - 00:00
Hi I have the following query: <code> SELECT cust_id, cosmos_cust_id, prot_id, customer_name, protocol_name, dist_order_no, cust_ivr_ref_no, ship...

JSON object access using JSON_TABLE

AskTom - Hot Questions - Sun, 08/27/2017 - 00:00
Hi I have a JSON object containing complex MVA, and would like to have relational access to my JSON object using JSON_TABLE + View. The way I am doing it right now, gives me 4 rows when I query the View, and I would like the SQL to return two r...

Using Dynamic Table Name in Select Statement To Prepare Extract File

AskTom - Hot Questions - Fri, 08/25/2017 - 00:00
Hi. We use to prepare extract file from oracle using extract script. There are few tables whose name gets change every month. Need to handle these table names dynamically. <code>SELECT TICKET_ID ,SOURCE_ID ,SERV_ID ,...

Visualize Processes Vertical

AskTom - Hot Questions - Wed, 08/23/2017 - 00:00
Hello, I've got a tricky one and can't solve it. Hopefully you can point me in the right direction. we have a table in which processes with their start and end time are listed. Here is a simplified example:<code>PROCESS | START_DATE | END_DATE ...

dbms_metadata - getting inconsistent SXML (ordering)

AskTom - Hot Questions - Tue, 08/22/2017 - 00:00
Hi Tom I am trying to use dbms_metadata to retrieve a SXML document per object in my production database, and compare it to the same document from my test database, in order to compare the two environments. Initially, I calculate a HASH value f...

Check if a file exists on the server for processing with UTL_FILE

AskTom - Hot Questions - Mon, 08/14/2017 - 00:00
I am looking for a way to check if a file exists on the server and pass the file name on to UTL file in Oracle EBS. File name: DDMMYYY_Data.dat. We will receive one DDMMYYY_Data.dat file every month and it got placed in a directory. once the ...

Using dbca -silent -createTemplateFromDB xxxxxx command to create template file?

AskTom - Hot Questions - Sun, 08/13/2017 - 00:00
hello, i use ' dbca -silent -createTemplateFromDB -sourceDB host:port:sid -templateName sidbak -sysDBAUserName xxxxxx -sysDBAPassword xxxxxx' to generate template file, for example, [oracle@orcl13 ~]$ dbca -silent -createTemplateFromDB -sourceDB o...

What is the directory in an impdp operation

AskTom - Hot Questions - Wed, 08/09/2017 - 00:00
I have new server and i installed oracle 10g same like production. and i Have databump full backup using sys user, i need to restore on the new server. I used the following command while database is not mounted but asks for directory p...

listagg gives ORA-01427: single-row subquery returns more than one row

AskTom - Hot Questions - Thu, 08/03/2017 - 00:00
I need to concatenate row field into one field and I'm trying to use LISTAGG, but I need values to be distinct in the list. I was able to do almost everything with regexp_replace as alternative, but when I have too many orders for a customer I would...

update bulk row based on case statement

AskTom - Hot Questions - Wed, 08/02/2017 - 00:00
Hello Experts, We have a requirement as per below example, For Example, Need to create a procedure which fulfill below condition, create or replace procedure as <declaration part> Begin select emp_id,emp_name,mgr_id, (case when dep...

SQL Execution Speed depending on Line Breaks in Query

AskTom - Hot Questions - Wed, 08/02/2017 - 00:00
Dear Tom, if I execute in Toad a query like: select * from SOMESCHEMA.TABLE_A A join SOMESCHEMA.TABLE_B B on B.A_ID = A.A_ID join SOMESCHEMA2.TABLE_C C on A.C_ID = C.C_ID join SOMESCHEMA.TABLE_D D on B.D_ID = D.D_ID where C.C_ID = 1234; ...

Pages