SQL & PL/SQL :: How Many Times Do The Inner Execute

Oct 2, 2010

I found this query in one of my stored procedures that updates a key for a value in a data table by reading the information from a master table.

The data table is: ITEM_INVENTORY
The master table is: MASTER_SOURCE_SYSTEM

UPDATE ITEM_INVENTORY I
SET I.SOURCE_SYSTEM_ID =
(SELECT NVL(M.SRC_SYS_ID,-100)
FROM MASTER_SOURCE_SYSTEM M
WHERE M.SRC_SYS_DESC(+) = I.SOURCE_SYSTEM_CODE )
WHERE ORG_CODE = 'TNXC'
AND EXISTS (SELECT 1 FROM MASTER_SOURCE_SYSTEM M
WHERE M.SRC_SYS_DESC(+) = I.SOURCE_SYSTEM_CODE )

The situation here is that:

1. There are about 15000 rows that match ORG_CODE = 'TNXC'.
2. The SOURCE_SYSTEM_CODE is same for all the 15000 rows and there is a matching entry for it in the MASTER_SOURCE_SYSTEM table.

My question is: Do both the inner select statements execute 15000 times?

The statement executes within a second and updates 15000 rows. How is this made possible?

View 10 Replies


ADVERTISEMENT

SQL & PL/SQL :: Using Dbms_sql.execute To Build / Execute Dynamic Sql?

Oct 23, 2013

I'm working with old code that uses dbms_sql.execute to build/execute dynamic sql. In our case, the user can select varying columns(I think up to 20) with different where conditions as needed.

After building the sql, here's an example

WITH ph AS
(SELECT ph.* FROM po_header ph WHERE 1 = 2),
pf AS
(SELECT DISTINCT pf.order_id, pf.fund
FROM po_fau pf, ph
WHERE 1 = 1
AND ph.order_id = pf.order_id

[code]....

Where table records for

po_header = ~567746
po_fau = ~2153570

and PK "order_id" is a NUMBER(10) not null and a snippet of the code looks like

nDDL_Cursor := dbms_sql.open_cursor;
dbms_sql.parse(nDDL_Cursor, sSQLStr, 2);
FOR x IN 1 .. nCols LOOP
sCols(x) := '';
dbms_sql.define_column(nDDL_Cursor, x, sCols(x), 100);
END LOOP;
nError := dbms_sql.execute(nDDL_cursor);

why when the "execute" statement is fired off the elapsed time takes ~4.5 seconds but If I change "1 = 1" above to "1 = 2" it takes ~.2 seconds. If I run the above query interactively it takes ~.2 seconds. Shouldn't the above query when joining

ph.order_id = pf.order_id

return zero rows back instantly or does the "dbms_sql_execute" do some other type of parsing internally that takes cpu time.

View 14 Replies View Related

PL/SQL :: How To Sum Times Value

Mar 18, 2013

I'm using Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - ProdMy problem is, There is data for IN time and OUT time data type is DATE. I need to calculate OT hour from this two field and store into a third one column. But i'm confuse what should be the data type of the third column. Is it number or else ?

I know difference of two date column is number. So, first i need to know that data type i should use ?

Secondly, how i get the sum of the all stored time ? for example data are

1.30 -- One hour and thirty min.
2.45
3.50

View 20 Replies View Related

SQL & PL/SQL :: How To Get Minutes Between Two Times

Oct 19, 2012

i want one query which return minute between two times which is in this format: 12:00:00 and 06:00:00

so in this it should return 360 minutes.

View 5 Replies View Related

Execute Dynamic SQL Using Both Execute Immediate And Ref Cursor

Jan 8, 2009

We can execute dynamic sql using both execute immediate and ref cursor..But what is the difference between the two and performance-wise which is better?

View 5 Replies View Related

SQL & PL/SQL :: How To Get Rows N Times In A Result Set

Mar 9, 2010

How to get rows N times in a result set?

For obscure test purposes I need to modify an existing SQL query to emit the rows N times instead once. I'm aware of the possibility to "UNION ALL" the query with itself to get the all rows twice.

But as I require the resulting rows to be emitted around ten to hundred times this approach doesn't seem sensible to me. Not to speak of the missing possibility to parametrize the number of "repetitions".

View 2 Replies View Related

SQL & PL/SQL :: Dealing With Times Recorded In UTC

Oct 8, 2011

I am dealing with a bunch of tables containing sales information for an New Zealand organisation. The sale datetime has been recorded as UTC.

New Zealand operates Daylight Savings, so twice a year it changes its clocks.

When New Zealand is on standard time it is UTC+12.

When New Zealand is on daylight savings time it is UTC+13.

Thus an event which actually occurred when New Zealand was on standard time at 2011-08-31 15:20:52 local time, is recorded in the database as having occurred at 2011-08-31 03:20:52. However, an event that actually occurred when New Zealand was on daylight savings time at 2011-10-06 15:20:52 local time, is recorded in the database as having occurred at 2011-10-06 02:20:52.

I want to be able to read the sales dates from my table and convert them to the actual time in New Zealand when the event occurred. The table will contain data for sales that occurred in both standard and daylight savings times.

I do not think that the data has been stored with time zone information, simply that the application writing the data to the Oracle database, calculated the event time as UTC when it occurred and wrote that time to the table.

Does Oracle only know about what UTC-offset is in force right now or is it capable of determining what offset from UTC is required for any given historical date ?

View 6 Replies View Related

How To Make A Command Loop Three Times

Jun 28, 2013

I have code that creates a sequence and a select statement that generates the next number in a sequence. It starts the sequence at 5 and every subsequent number is the previous number minus 3. The minimum value it can go to is 0. All I want is for my select statement to execute three times in a row. Is there any way I can do that?

create sequence MY_FIRST_SEQ
increment by -3
maxvalue 5
start with 5
minvalue 0
nocycle;

select MY_FIRST_SEQ.NEXTVAL from dual;

View 5 Replies View Related

Generate Last Seven Days Batch Run Times

Jan 8, 2013

I would the query to do the following:

1. Make the jobname distinct, because it keeps giving me multiple entries for each jobname
2. Add the the start_time of SOD_start_data9_UAT1 to end_time fodba_MUAT1 to get the combined duration
3. CONCAT jobnames SOD_start_data9_UAT1 and end_time fodba_MUAT1
4. Generate the last seven days batch run times
5. Generate a report into .csv format and email out
6. I have access to sqlplus and plsql developer

select distinct JOBNAME, schedtab
, to_char(to_timestamp(trim(timestmp)
, 'YYYYMMDDHH24MISS') - numtodsinterval(elaptime / 100
, 'SECOND'), 'YYYY-MM-DD HH24:MI:SS') as start_time
[code]...

View 3 Replies View Related

SQL & PL/SQL :: Determine Number Of Times A Value Appears

Nov 11, 2012

I am trying to determine the number of times a value appears and display the count. However the value can only be counted once per 'trip' even though it may appear several times per trip.

for e.g.

Quote:trip table
-------
trip_id start_date end_date duration
445 01-jan-12 03-jan-12 3

Quote:pickup table
--------
pickup_id trip_id company
1 445 randomname
2 445 randomname
3 445 google.inc
4 878 randomname

with the above data the expected value would be two because the trip id appears twice so it was just the one trip - given a count of one. I am not sure how create a query to check this.

View 2 Replies View Related

SQL & PL/SQL :: Populate Column Two Times With Different Criteria?

Mar 22, 2011

I am trying to query O/P from two tables.Hence, populate same column(Level_Name) two time on report based on its Level

Table =Level_Mst

Codeno Level_Name Parent_level
1 IT Dir 0
2 HR Dir 0
3 Assets Section 1
4 Payroll Section 2

Table=Users

User_id Name Top_level Bottom_Level
1 John 1 3
2 Smith 2 4

Desired O/P

Name Top_lvl Bottom_lvl Top_lvl_Name Bottom_Lvl_Name
John 1 3 IT Dir Assets_Section

Right now im getting name from level_mst either for top_level or for bottom_level .I want to show both names in one row.

View 6 Replies View Related

SQL & PL/SQL :: How To Fetch Two Times Same Explicit Cursor

Nov 25, 2012

I need to open an explicit cursor for making a total: after I have to use the same information of that explicit cursor for dividing a column of the cursor by that total. It is not enough to open close, reopen and reclose because I just obtain one register at the same time and it is the same register two times consecutively.

I don't want to use auxiliary structures cause there are 18000 columns for 10200 rows.

FOR i IN 300..300 --18000
LOOP
y:=ymax-ysize*(i+0.5);

[Code]......

View 6 Replies View Related

SQL & PL/SQL :: Run Query Multiple Times With Time Gap

May 25, 2013

I need to run the below query and display the result every one hour.

Below code giving result only once.

I think my method is wrong.

SQL> declare
2 counter number :=0;
3 item varchar(300);
4 BEGIN
5 FOR item IN (

[Code]...

PL/SQL procedure successfully completed.

View 12 Replies View Related

Replication :: Job Failing Several Times In TOAD

Jan 25, 2011

In TOAD,I see one of my jobs is failing several times. However, I could refresh it manually.I found the following error. What is the cause of error and the way to rectify this.

ORA-23404: refresh group "ORA-23404: refresh group "schema_name"."job_name" does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_REFRESH", line 23
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
"."" does not exist.

View 4 Replies View Related

PL/SQL :: Dates And Times In A Subquery With A Group By

Dec 20, 2012

I have some data that I need to group to the Month, Day, Year, Hour and minute in a subquery. Then I need to summarize it to the Month and year in the parent query.If I try to group by the field itself, it is not taking it down to hour and minutes - just the day, so I am losing records.if I do a TO_char (visitdate, 'DD-MON-YY HH:MI AM') in the subquery, then the main query no longer sees it as a date, so cannot do my TO_CHAR(VISITDATE,'MON-YYYY') in the parent. I could parse out the pieces using string manipulation, but that seems rather silly.Is there a way to keep as a date in my sub query and then convert to a string?

it looks a little like this, with some other fields that I have to max, sum ...

visit     provider     person     visitdate
1     2     1     12/20/2012 10:30
2     2     2     12/20/2012 10:30
3     2     5     12/20/2012 11:30
4     3     3     12/21/2012 11:30
5     3     4     12/21/2012 11:30

I need to boil this down to

provider     visitdate
2     12/20/2012 10:30
3     12/21/2012 11:30
2     12/20/2012 11:30

Then I use that in a subquery where I use just the month and year TO_CHAR(VISITDATE,'MON-YYYY') AS APPT_MO_YR right now if I do a group by visitdate on the subquery it returns

provider     visitdate
2     12/20/2012
3     12/21/2012

even if I do a group by to_date(visitdate, 'DD-MON-YY HH:MI AM') it is still returning :

provider     visitdate
2     12/20/2012
3     12/21/2012

View 4 Replies View Related

How To Copy Set Of Rows N Times And Append Them With Different Value For A Column

May 27, 2011

Im trying to replicate a set of rows multiple times to create large volume. I am trying by For Loop, but got confused how to pass the parameters in any cursor i declare.

View 8 Replies View Related

Performance Tuning :: Number Of Times Sql Had Been Executed / Run?

Oct 25, 2011

how many number of times the sql's had been executed/run in an oracle session. Executions in V$sql, is it the right place to look at? Will it give the number of times the sql has been executed in a particular session?

View 3 Replies View Related

PL/SQL :: Sample Function Returns Many Times Of Expected Value

Nov 30, 2012

The SAMPLE clause in the select statement works well in most cases, but we found in some instances the result is way off - between 200% to 700% discrepancy has been observed.

For example, we have thee tables with the following results:

Table1: 495,365,317 rows (20 cols, unique primary key present), SAMPLE ( 0.002018712182064212 ) returns 41,499 (about four times off - we expected about 10,000)
Table2: 3,350,864,539 rows ( 5 cols, unique primary key present), SAMPLE ( 0.00029843044634040336 ) returns 9,835 (this is good as it is close to 10,000)
Table3: 6,974,724,543 rows ( 5 cols, no unique primary key present), SAMPLE ( 0.00014337483779250091 ) returns 58,789 (about six times off - we expected about 10,000)

The tables got billions of rows, and that is why we want to do sampling. The sample percentage rate is computed to return about 10,000 rows in all three tables.On Table3, we ran the sampling three times in one occasion, and we got "58,570", "24,575" and "24,561"

I expected +/- 20% of variance, but 200% to 700% seems to be way too much.Once again, I stress that it does work well in most cases (another 3.4 billion table and numerous smaller tables we tested were well within +/- 5 percent of the target).I noted the presence of a primary key above because I read an article saying that the SAMPLE function relies on the existence of a primary key (which does not quite explain the examples above).Is this kind of spread something we should expect or is it a bug? Is the sampling rate too small for such large tables?

View 2 Replies View Related

Forms :: FRM-40209 - Field Must Be Of Form XX-XX-XXX Coming Several Times

Oct 3, 2012

I have a form which has a Number field. I set it's Format Mask property to 9,99,99,99,99,999 so that I can format it comma separated and to restrict the user to enter decimal values. If the user enter any format other than this for example any decimal values, it is showing the message FRM-40209:Field must be of the form 9,99,99,99,99,999 which is fine. But when the user enters a decimal value and try to save the form it pop up this message several times.

I tried to catch this error in ON-ERROR trigger and display a message. But this is also pop up several times. I tried to raise form_trigger_failure also. This is also not working. I want this error message to come only once.

View 2 Replies View Related

Performance Tuning :: ASH Report - SQL Has Been Executed For 1567 Times?

Sep 12, 2012

In ASH report

there is a section that goes like this

SQL ID Planhashed Sampled # of Executions % ActivityEvent% Event Top Row Source
fdy93qpr1227 1567 7.58direct path read 3.65TABLE ACCESS - FULL

does it suggest that this SQL has been executed for 1567 times is this correct .

View 7 Replies View Related

Backup & Recovery :: How Many Times To Recover (TSPITR) A Tablespace

Feb 7, 2011

How many times i can do recover (TSPITR) a tablespace ? While trying to recover tspitr tablespace on 2nd time i got error message ORA-01178 file 13 created before last create controlfile,but first time it was success.

View 1 Replies View Related

An Iteration Count Of Zero Specified For Statement Action - Specify Number Of Times?

Aug 2, 2013

Get the following error from Oracle  ORA-24333: zero iteration count Cause: An iteration count of zero was specified for the statementAction: Specify the number of times this statement must be executed on whether is this a Oracle bug or if not what should be set to avoid this failure.

View 2 Replies View Related

SQL & PL/SQL :: Carriage Return Line Feed Multiple Times In Same CLOB

Jan 31, 2012

I'm looking for a way to make CRLFs show in a CLOB.I'm feeding the insert statement with a concatinated string like this:

insert into table(Data) values (MyConc)

(MyConc is a string put togheter by another application)

Because of this I can't use the "|| chr(13) || chr(10) ||" because I only have that one concatinated string.Is there a character I can set in my string that automatically translates to a CRLF?

My CLOB-data should look something like this:

1;blue;Woodstock;;
34;giant;squid;attack;
5;blue;squid;;

And in this case the "MyConc" would look like this "1;blue;Woodstock;;[X]34;giant;squid;attack;[X]5;blue;squid;;[X]

where [X]=the character I need for CRLF =)

View 11 Replies View Related

Performance Tuning :: Fragmentation Can Reduce Performance In Query Times

Jun 16, 2010

I have a question about database fragmentation.I know that fragmentation can reduce performance in query times. The blocks are distributed in many extents and scans process takes a long time. Oracle engine have to locate the address of the next extent..

I want to know if there is any system view in which you can check if your table or index has high fragmentation. If it's needed I will have to re-create, move or rebulid the table or index, but before I want to know if the degree of fragmentation is high.

Any useful script or query to do this, any interesting oracle system view?

View 2 Replies View Related

Reports & Discoverer :: How To Make Name Of Column Appear As Many Times As Column On Page

Nov 21, 2010

This is what i want to do:

I have a basic report looking like this:

<Column name>
value 1
value 2
value 3
.
.
.
value n

Since the column is small it can fit in a page more then one time. I know how to make it print more then once: i switch the repeating frame to print down and across and i modify the frame that contains it so the horizontal elasticity is variable.

After these changes my report looks like this:
<Column name>
value 1-----------------------value n+1-----------------------value m+1
value 2-----------------------value n+2-----------------------value m+2
value 3-----------------------value n+3-----------------------...
.------------------------------- .
.------------------------------- .
.------------------------------- .
value n-----------------------value m

What i want is my report to look like this:

<Column name>-------<Column name>------------<Column name>
value 1-----------------------value n+1-----------------------value m+1
value 2-----------------------value n+2-----------------------value m+2
value 3-----------------------value n+3-----------------------...
.------------------------------- .
.------------------------------- .
.------------------------------- .
value n-----------------------value m

View 4 Replies View Related

Displaying Records Between Specific Times On Specific Dates?

Apr 11, 2011

I have a database which consists of various orders and various field.

I have a variable called createddatetime . I want that whenever i should run the database it should display records from

Yesterday 06:00:00 am to Current Date 05:59:59 am

Now to implement this i tried to put this syntax

and to_char(Createddatetime,'dd/mm/yyyy HH24:mi:ss') between 'sysdate-1 06:00:00' and 'sysdate 05:59:59'

But nothing comes up

where as definitely there are records between times because when i do and Createddatetime between sysdate-1 and sysdate I see valid records coming up.

View 3 Replies View Related

Backup & Recovery :: How Many Times To Backup Control-file

Jan 5, 2012

When i issue this command ,how many times to backup controlfile?

RMAN> BACKUP CURRENT CONTROLFILE INCLUDE CURRENT CONTROLFILE;

Is there difference between 'BACKUP CURRENT CONTROLFILE INCLUDE CURRENT CONTROLFILE' and 'BACKUP CURRENT CONTROLFILE'?

View 2 Replies View Related

Execute Immediate In Trigger

Apr 17, 2013

I am trying to use execute immediate in a trigger. See the below example

create table test_tbl(col1 number,col2 varchar2(10), col3 date);
create table test_tbl_log as select * from test_tbl where 1 = 2;

create or replace trigger test_trig
after insert or update or delete on test_tbl
for each row
declare
v_str varchar2(4000);
[code].......

insert into test_tbl values(3,'test',sysdate);

I got the Error : -1008->ORA-01008: not all variables bound

In the execute immediate statement if I change the v_using_str variable to :new.col1,:new.col2,:new.col3 trigger is running fine. But here I have to create trigger on all the tables in my schema and I dont want to list all the column names for each table manually and I am using a for loop to build the v_using_str string in my original code.

View 2 Replies View Related

Execute Sql File From Pl / Sql?

Aug 18, 2010

Need to execute @/sql file after checking an output of below sql.

SELECT COUNT(*) FROM all_directories WHERE DIRECTORY_NAME='RMANDUMP';

If directory exist then oonly we require to run sql file.

If there a way we can execute sql files depending on sql output.

View 4 Replies View Related

SQL & PL/SQL :: Execute Immediate With DBLink

Feb 23, 2011

I need to write several variations of this statement below to query a remote database, but am having trouble with the syntax.

execute immediate 'select count(*) from ' || v_tablename || '@dblink ' || into v_rowcount;
PLS-00103: Encountered the symbol "INTO"....

What's the correct way to format this kind of dynamic query?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved