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: 31 min 44 sec ago

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

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

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 ?

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

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

Using Dynamic Table Name in Select Statement To Prepare Extract File

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

dbms_metadata - getting inconsistent SXML (ordering)

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

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?

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

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

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

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

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

Same query from multipule sessions, but dont see Read By Multipule Session

Tue, 08/01/2017 - 00:00
Hi, I was going through the below link, and doing some testing of my own. http://oracleinaction.com/simulate-buff-busy/ Focusing only on the below. PROCEDURE RUNSELECT as cursor dummy is select * from t; c1 t_refcur; rec dummy...

ORA-01001: invalid cursor while using function returning ref cursor.

Wed, 07/26/2017 - 00:00
<code>Hi Team, I am getting these errors (1 - ORA-00932: inconsistent datatypes: expected - got - 2 - <b>ORA-01001: invalid cursor</b>) when I am using function returning ref cursor. Can anyone help me in this. These are the efforts...

GETTING ORA-29270: too many open HTTP requests error

Wed, 07/26/2017 - 00:00
Hi, Please find below procedure from which we are keep on getting "ORA-29270: too many open HTTP requests" error, Procedure: <code>CREATE OR REPLACE PROCEDURE test_task ( p_quote_number IN cct_quote.quote_number%TYPE, p_quote...

Load CSV into table and split column into different rows

Sat, 07/22/2017 - 00:00
I have to load a csv file data into a table in oracle. One of the columns of csv file has data more than 4000 characters which has many values. These values can be distinguished from one another by keyword "http" or "https". I need to split them int...

Not able to recover Space even after Shrink space

Thu, 07/20/2017 - 00:00
Hi Tom, I have DB with one tablespace and many datafiles whereas tables are spread over many datafiles. I used below query to check fragmented tables list. select owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2) "TOTAL_SIZE...

Break the results in select and put them in an XML

Wed, 07/19/2017 - 00:00
Hi Team, I have a table Employee as below <code>Employee_ID Employee_Name 1 Usha 2 Soma 3 Uma</code> . . . and so on I have 5000 records in the table I have a defined count only 1000 reco...

"Fast" MERGE PARTITION

Wed, 07/19/2017 - 00:00
Hi Tom ! As of Oracle 12c there exists FAST SPLIT PARTITION. Is there something similar for MERGE PMO? The exact problem is this. As currently there is no way to increase HIGH VALUE of RANGE PARTITION (at least I'm aware of), the only way t...

How to Create Missing Records with Analytical Functions

Fri, 07/14/2017 - 00:00
Hi AskTom Team, I am having some trouble figuring out a query to do the following: I have a staging table populated by an external system. This table stores information about how much an item sold during a day. If an item hasn't sold anything d...

Pages