Home » SQL & PL/SQL » SQL & PL/SQL » To Call Procedure Name Based on the CURSOR VALUE..
icon9.gif  To Call Procedure Name Based on the CURSOR VALUE.. [message #382575] Fri, 23 January 2009 01:17 Go to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
Hi all..

I want to create a procedure in such a way, it retrieve the procedure name from the current value of cursor like this:


CREATE OR REPLACE PROCEDURE NIS_DWH.P_NIS_REXECUTE AS
/*----------------------------------------------------------------------*/

CURSOR A IS
select 
'P_NIS_'||max(a.TABLE_NAME) "PROCEDURE_NAME"
from nis_loading_log a, 
(select TABLE_NAME, max(loading_date) loading_date from nis_loading_log group by TABLE_NAME) b
where a.table_name = b.table_name and a.loading_date = b.loading_date
and a.rows_inserted = 0 and a.rows_updated = 0;

arec A%rowtype;
proc_name varchar2(30);

begin

     OPEN  A;
 
	 loop
	 FETCH A INTO arec;
	 exit when A%notfound;

	BEGIN

	proc_name := arec.PROCEDURE_NAME;   
    
    proc_name;
      
	END;

	end loop;

	close A;

end P_NIS_REXECUTE;
/

In the above example, the cursor select statement would return 'P_NIS_PTT' as the value.

So, how would I write down the code to execute that procedure based on that value?

If I substitute this stament
    proc_name := arec.PROCEDURE_NAME;   
    
    proc_name;

with
P_NIS_PTT;

it would be working fine.

Thank you very much.


[EDITED by LF: fixed [code] tags]

[Updated on: Fri, 23 January 2009 01:25]

Report message to a moderator

Re: To Call Procedure Name Based on the CURSOR VALUE.. [message #382577 is a reply to message #382575] Fri, 23 January 2009 01:32 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
you need to use execute immediate.

execute immediate 'Begin '||arec.PROCEDURE_NAME||'; end;'


or
execute immediate 'Begin '||proc_name||'; end;'

[Updated on: Fri, 23 January 2009 01:38]

Report message to a moderator

Re: To Call Procedure Name Based on the CURSOR VALUE.. [message #382583 is a reply to message #382577] Fri, 23 January 2009 01:54 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
bonker wrote on Fri, 23 January 2009 15:32
you need to use execute immediate.

execute immediate 'Begin '||arec.PROCEDURE_NAME||'; end;'


or
execute immediate 'Begin '||proc_name||'; end;'



Wow!!!

Thanks a lot.. http://img01.picoodle.com/img/img01/3/11/1/aimy/f_tqm_bcc85fd.gif http://img01.picoodle.com/img/img01/3/11/1/aimy/f_tqm_bcc85fd.gif http://img01.picoodle.com/img/img01/3/11/1/aimy/f_tqm_bcc85fd.gif

I really appreciate, http://planet.time.net.my/KLCC/freekey/smilies/nodding.gif
Re: To Call Procedure Name Based on the CURSOR VALUE.. [message #382589 is a reply to message #382575] Fri, 23 January 2009 02:35 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
will it not call procedure on simple if
yours
dr.s.raghunathan
Re: To Call Procedure Name Based on the CURSOR VALUE.. [message #382595 is a reply to message #382589] Fri, 23 January 2009 03:02 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
dr.s.raghunathan wrote on Fri, 23 January 2009 16:35
will it not call procedure on simple if
yours
dr.s.raghunathan

How it should be done then? Sad
Re: To Call Procedure Name Based on the CURSOR VALUE.. [message #382597 is a reply to message #382595] Fri, 23 January 2009 03:10 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
how many procedures you are expecting to call for execution
icon9.gif  Re: To Call Procedure Name Based on the CURSOR VALUE.. [message #382599 is a reply to message #382575] Fri, 23 January 2009 03:13 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
One more question,

How do I rearrange this procedure so that it will continue to find the max value?

Thank you.
Re: To Call Procedure Name Based on the CURSOR VALUE.. [message #382600 is a reply to message #382597] Fri, 23 January 2009 03:30 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
dr.s.raghunathan wrote on Fri, 23 January 2009 17:10
how many procedures you are expecting to call for execution

My objective here is to reexecute those procedures which are still not loading anything.

I've found the answer to my question..
CREATE OR REPLACE PROCEDURE NIS_DWH.P_NIS_REXECUTE AS
/*----------------------------------------------------------------------*/

CURSOR A IS
select 
'P_NIS_'||max(a.TABLE_NAME) "PROCEDURE_NAME"
from nis_loading_log a, 
(select TABLE_NAME, max(loading_date) loading_date from nis_loading_log group by TABLE_NAME) b
where a.table_name = b.table_name and a.loading_date = b.loading_date
and a.rows_inserted = 0 and a.rows_updated = 0 and a.table_name <> 'PTT';

arec A%rowtype;

begin

     OPEN  A;
 
	 loop
	 FETCH A INTO arec;
	 exit when A%notfound;

	BEGIN

    execute immediate 'Begin '||arec.PROCEDURE_NAME||'; end;';
	
	commit;
		    
	END;

	end loop;
	
	close A;
	
	P_NIS_REXECUTE;

end P_NIS_REXECUTE;
/

But now how do I cater this..
BEGIN P_NIS_REXECUTE; END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'P_NIS_' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
ORA-06512: at "NIS_DWH.P_NIS_REXECUTE", line 24
ORA-06512: at "NIS_DWH.P_NIS_REXECUTE", line 34
ORA-06512: at "NIS_DWH.P_NIS_REXECUTE", line 34
ORA-06512: at "NIS_DWH.P_NIS_REXECUTE", line 34
ORA-06512: at "NIS_DWH.P_NIS_REXECUTE", line 34
ORA-06512: at "NIS_DWH.P_NIS_REXECUTE", line 34
ORA-06512: at line 1

Means that no more procedures to be executed bu 'P_NIS_' itself.

Thank you.
Re: To Call Procedure Name Based on the CURSOR VALUE.. [message #382605 is a reply to message #382600] Fri, 23 January 2009 03:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Apparently you fetched a NULL value for max(a.table_name)

Since you don't seem too familiar with dynamic sql, I strongly recommend you to review your requirements and look for a static sql based solution.
This will give you numerous problems that will be difficult to trace if you are not into dynamic sql.
Re: To Call Procedure Name Based on the CURSOR VALUE.. [message #382611 is a reply to message #382605] Fri, 23 January 2009 04:03 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
you can change the code like this

CURSOR A IS
select 
max(a.TABLE_NAME) "PROCEDURE_NAME"
from nis_loading_log a, 
(select TABLE_NAME, max(loading_date) loading_date from nis_loading_log group by TABLE_NAME) b
where a.table_name = b.table_name and a.loading_date = b.loading_date
and a.rows_inserted = 0 and a.rows_updated = 0;

arec A%rowtype;
proc_name varchar2(30);

begin

     OPEN  A;
 
	 loop
	 FETCH A INTO arec;
	 exit when A%notfound;

	

        if arec.procedure_name is not null then
	         
             proc_name := 'P_NIS_'||arec.PROCEDURE_NAME;   
    
             execute immediate 'Begin ' || proc_name||'; end;';
      
	End if;

	end loop;

	close A;

end P_NIS_REXECUTE;




But as Frank said you need to understand how to use Dynamic SQL and try to write good Static SQL before looking at Dynamic Sql


Re: To Call Procedure Name Based on the CURSOR VALUE.. [message #382617 is a reply to message #382611] Fri, 23 January 2009 04:14 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
From a performance point of view, if you will be using this a lot, then you will get noticably better performance if you ditch the whole dynamic Sql thing, and replace it with something like :
IF procname = 'A' THEN
  proc_A;
ELSIF procname = 'B' THEN
  proc_B;
...


Previous Topic: Reading "Or" Operator (merged)
Next Topic: How to Implement IsNumeris Function using Oracle
Goto Forum:
  


Current Time: Sun Dec 04 18:39:52 CST 2016

Total time taken to generate the page: 0.23751 seconds