Home » SQL & PL/SQL » SQL & PL/SQL » Question on EXEC procedures stored in variables (Oracle 10g 10.2.0.1.0)
Question on EXEC procedures stored in variables [message #357643] Thu, 06 November 2008 02:21 Go to next message
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
Re: Question on EXEC procedures stored in variables [message #357646 is a reply to message #357643] Thu, 06 November 2008 02:31 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
have a look into this thread, especially the end: http://www.orafaq.com/forum/?t=msg&th=127182&96705/
Re: Question on EXEC procedures stored in variables [message #357648 is a reply to message #357643] Thu, 06 November 2008 02:37 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

EXECUTE IMMEDIATE

Smile
Rajuvan
Re: Question on EXEC procedures stored in variables [message #357651 is a reply to message #357648] Thu, 06 November 2008 02:54 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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:
  


Current Time: Sat Dec 03 09:37:27 CST 2016

Total time taken to generate the page: 0.19538 seconds