Home » SQL & PL/SQL » SQL & PL/SQL » execute order (10g r2 , Linux)
icon5.gif  execute order [message #332202] Mon, 07 July 2008 20:45 Go to next message
lzfhope
Messages: 67
Registered: July 2006
Member
hi,
I created a procedure like the code as following :
  Create or replace procedure SP_test
  is
  begin
    SP_A;
    SP_B;
    SP_C;
  end;


----
I hope that sp_a,sp_b,sp_c would run one by one.In fact,by viewing the associated data ,i found that sp_b and sp_c would begin to run while sp_a is still running .
So ,this would make problem.
How to resolve this?
Thank you in advance!
Re: execute order [message #332203 is a reply to message #332202] Mon, 07 July 2008 20:59 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

>In fact,by viewing the associated data ,i found that sp_b and sp_c would begin to run while sp_a is still running .

Your statement is not supported by Oracle fundamentals.
Post proof to support the statement above.

[Updated on: Mon, 07 July 2008 21:00] by Moderator

Report message to a moderator

Re: execute order [message #332215 is a reply to message #332203] Mon, 07 July 2008 22:55 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
That depends on what you have writiten in your SP's.

Are you using PRAGMA AUTONOMOUS TRANSACTION.In that case it may be the case.

Post your procedure code.

Regards,
Rajat Ratewal
Re: execute order [message #332240 is a reply to message #332215] Tue, 08 July 2008 00:24 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Have you ever seen an autonomous procedure being executed together (as in "at the same time") as another procedure? I have not.

Procedures in Oracle are executed one after another; no multi-threading.
Re: execute order [message #332246 is a reply to message #332240] Tue, 08 July 2008 00:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Nothing more clarifying than a little testscenario:
clear screen
spool faq
set echo on
set linesize 80
set pagesize 10000

drop table tst_tab
/
create table tst_tab
( procedure_name varchar2(20)
, id             number
)
/
create sequence tst_seq;
create or replace trigger tst_tab_bir
before insert on tst_tab
for each row
begin
  select tst_seq.nextval
  into   :new.id
  from   dual;
end;
/
create or replace procedure auton_test
( p_run_id in varchar2
) as
  pragma autonomous_transaction;
begin
  for r in 1 .. 100000
  loop
    insert into tst_tab (procedure_name) values ('AUTON_TEST'||p_run_id);
    commit;
  end loop;
end;
/
create or replace procedure non_auton
as
begin
  for r in 1 .. 100000
  loop
    insert into tst_tab (procedure_name) values ('NON_AUTON');
    commit;
  end loop;
end;
/
begin
  auton_test('1');
  auton_test('2');
  non_auton;
  auton_test('3');
  auton_test('4');
end;
/
select procedure_name
,      lead_proc_name
,      id
from   (select procedure_name
        ,      lead(procedure_name) over (order by id) lead_proc_name
        ,      id
        from   tst_tab
       )
where  procedure_name != lead_proc_name
/
spool off


The outcome:

SQL> set echo on
SQL> set linesize 80
SQL> set pagesize 10000
SQL> 
SQL> drop table tst_tab
  2  /

Table dropped.

SQL> create table tst_tab
  2  ( procedure_name varchar2(20)
  3  , id	      number
  4  )
  5  /

Table created.

SQL> create sequence tst_seq;
create sequence tst_seq
                *
ERROR at line 1:
ORA-00955: name is already used by an existing object 


SQL> create or replace trigger tst_tab_bir
  2  before insert on tst_tab
  3  for each row
  4  begin
  5    select tst_seq.nextval
  6    into   :new.id
  7    from   dual;
  8  end;
  9  /

Trigger created.

SQL> create or replace procedure auton_test
  2  ( p_run_id in varchar2
  3  ) as
  4    pragma autonomous_transaction;
  5  begin
  6    for r in 1 .. 100000
  7    loop
  8  	 insert into tst_tab (procedure_name) values ('AUTON_TEST'||p_run_id);
  9  	 commit;
 10    end loop;
 11  end;
 12  /

Procedure created.

SQL> create or replace procedure non_auton
  2  as
  3  begin
  4    for r in 1 .. 100000
  5    loop
  6  	 insert into tst_tab (procedure_name) values ('NON_AUTON');
  7  	 commit;
  8    end loop;
  9  end;
 10  /

Procedure created.

SQL> begin
  2    auton_test('1');
  3    auton_test('2');
  4    non_auton;
  5    auton_test('3');
  6    auton_test('4');
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> select procedure_name
  2  ,	    lead_proc_name
  3  ,	    id
  4  from   (select procedure_name
  5  	     ,	    lead(procedure_name) over (order by id) lead_proc_name
  6  	     ,	    id
  7  	     from   tst_tab
  8  	    )
  9  where  procedure_name != lead_proc_name
 10  /

PROCEDURE_NAME       LEAD_PROC_NAME               ID                            
-------------------- -------------------- ----------                            
AUTON_TEST1          AUTON_TEST2              100000                            
AUTON_TEST2          NON_AUTON                200000                            
NON_AUTON            AUTON_TEST3              300000                            
AUTON_TEST3          AUTON_TEST4              400000                            

SQL> spool off

The results are exactly ordered, according to the execution order.
Re: execute order [message #332264 is a reply to message #332240] Tue, 08 July 2008 01:07 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Frank i was not aware of this.

Thanks for explaining.

Regards,
Rajat
Re: execute order [message #332296 is a reply to message #332202] Tue, 08 July 2008 02:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm guessing (and based on the vague details provided by the Op it can be nothing more) that your SP_A, SP_B and SP_C procedures insert some records that have a timestamp on them.
The Op is then querying the table that the records are inserted into, withou using an Order by clause, and the records are coming back in an order that, if it were the order that they had been inserted, would imply that SP_A had not finished before SP_B started.

Could @lzfhope confirm this?
Re: execute order [message #332403 is a reply to message #332296] Tue, 08 July 2008 08:08 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Oh crapenstein. JRow beat me to it. That is what I was going to say.
Re: execute order [message #332439 is a reply to message #332403] Tue, 08 July 2008 09:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Great minds think alike.....
Re: execute order [message #332539 is a reply to message #332215] Tue, 08 July 2008 21:54 Go to previous messageGo to next message
lzfhope
Messages: 67
Registered: July 2006
Member
Thank you!
The codes is too Long ,I can not paste them here .
I am sure that each subprogram has a "commit" or "rollback" keyword at the end .
No PRAGMA AUTONOMOUS TRANSACTION!
I'm trying my codes again!
I heard this before ,Maybe i also come across it !



Re: execute order [message #332542 is a reply to message #332539] Tue, 08 July 2008 23:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What did you hear before?
What does it matter if there is a pragma autonomous_transaction or not?
Re: execute order [message #332575 is a reply to message #332539] Wed, 09 July 2008 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am sure that each subprogram has a "commit" or "rollback" keyword at the end .

This is a bad thing.
Oracle is not Sybase or SQL Server, you don't need to commit/rollback each time you make something.
Only application knows when to end a transaction not a subprogram.

Regards
Michel
Re: execute order [message #332600 is a reply to message #332539] Wed, 09 July 2008 02:29 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
I'm guessing (and based on the vague details provided by the Op it can be nothing more) that your SP_A, SP_B and SP_C procedures insert some records that have a timestamp on them.
The Op is then querying the table that the records are inserted into, withou using an Order by clause, and the records are coming back in an order that, if it were the order that they had been inserted, would imply that SP_A had not finished before SP_B started.

Could @lzfhope confirm this?



I wonder if I'll get a reply this time

[paste message inside QUOTE tags, rather than outside]

[Updated on: Wed, 09 July 2008 02:30]

Report message to a moderator

Previous Topic: how to write the local procedure
Next Topic: Controlling user's environment
Goto Forum:
  


Current Time: Sat Dec 03 04:03:46 CST 2016

Total time taken to generate the page: 0.22972 seconds