OraFAQ Forum: SQL & PL/SQL » Question on EXEC procedures stored in variables

Home » SQL & PL/SQL » SQL & PL/SQL » Question on EXEC procedures stored in variables (Oracle 10g 10.2.0.1.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Question on EXEC procedures stored in variables [message #357643]

Thu, 06 November 2008 02:21

wmgonzalbo
Messages: 98
Registered: November 2008

Member

* If my table TABLE1 has a field named PROC_NAME which stores a name (VARCHAR2) of a working stored procedure..

Table: TABLE1
Field: PROC_NAME -- VARCHAR2 (30 BYTE)
Data: PROC_1 --> name of a working procedure

* I need to run the procedures named in PROC_NAME in TABLE1.

* In an another procedure named MAIN_PROC i have this code..

CREATE OR REPLACE PROCEDURE MAIN_PROC IS

TYPE cv_rconfig IS REF CURSOR;
cv_prconfig cv_rconfig;

v_row REPORT_CONFIG%ROWTYPE;

BEGIN

OPEN cv_prconfig FOR 'SELECT process_order, proc_name from TABLE1 order by process_order';
loop
fetch cv_prconfig into v_row.process_order, v_row.proc_name;
exit when cv_prconfig%NOTFOUND;

IF <some condition> then
-- if conditions are met, run PROC1

PROC1;

ELSIF ....
-- cant i change this to 'EXEC v_row.proc_name;' (which raises errors) or some other way so that i can execute procedures 'dynamically'?

-- to make it more clear, what im trying to do is IF <some condition> THEN run the name of the procedure stored in the variable v_row.procname.

-- Using a JOB is not an requirement/objective...

-- Any Suggestions or Comments will do..

Thanks,
Wilbert

Report message to a moderator

Re: Question on EXEC procedures stored in variables [message #357646 is a reply to message #357643]

Thu, 06 November 2008 02:31

flyboy
Messages: 1903
Registered: November 2006

Senior Member

have a look into this thread, especially the end: http://www.orafaq.com/forum/?t=msg&th=127182&96705/

Report message to a moderator

Re: Question on EXEC procedures stored in variables [message #357648 is a reply to message #357643]

Thu, 06 November 2008 02:37

rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India

Senior Member

EXECUTE IMMEDIATE

Smile

Rajuvan

Report message to a moderator

Re: Question on EXEC procedures stored in variables [message #357651 is a reply to message #357648]

Thu, 06 November 2008 02:54

wmgonzalbo
Messages: 98
Registered: November 2008

Member

* flyboy, rajavu1 thanks a million! you guys Rock!

-- Hope this mistake will help other newbies like me...

* Did that a while ago but kept receiving errors..

ORA-06550: line 1, column 17:
PLS-00103: Encountered the symbol "END" when expecting one of the following:

:= . ( @ % ;
ORA-06512: at "ARGON_RS.PROC_REPORT_CONFIG", line 20
ORA-06512: at line 2

* Its the semi-colon (;)!!! Shocked

IF <condition> THEN
EXECUTE IMMEDIATE 'BEGIN '||v_row.proc_name||'; END;';

PL/SQL procedure successfully completed.

Again, thanks a million! Laughing

[Updated on: Thu, 06 November 2008 02:54]

Report message to a moderator

Re: Question on EXEC procedures stored in variables [message #357658 is a reply to message #357651]

Thu, 06 November 2008 02:59

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Use SQL*Plus and copy and paste your session, it is then easier to see the errors.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel

[Updated on: Thu, 06 November 2008 03:00]

Report message to a moderator

Previous Topic:	recover tables (urgent)
Next Topic:	DYNAMIC Record?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 09:11:10 CST 2024