Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Dynamically execute sql statements in oracle

Dynamically execute sql statements in oracle

From: <bucycle_at_hotmail.com>
Date: 29 Apr 2005 07:39:03 -0700
Message-ID: <1114785543.147894.53030@g14g2000cwa.googlegroups.com>


Hello:

I am trying to create dynamic update statements, but then have them execute as well, so I do not have to take another step of running my results. I cannot figure out the proper syntax to run my update statement in the cursor.
I am trying to append the db schema name in front of the revisionname for all the tables.
My script looks like this, but it is not working properly. Any suggestions would be greatly appreciated, if another approach would be easier, I would also consider those.
Thanks
David

DECLARE CURSOR test IS
SELECT table_name,vs.schemaname FROM cols, v$session vs WHERE column_name='REVISIONNAME' --AND table_name ='acctcode' AND table_name NOT LIKE 'BIN%'
AND audsid=USERENV('sessionid');
ctest1 test%ROWTYPE;
BEGIN
OPEN test;
FETCH test INTO ctest1;
LOOP test%NOTFOUND

RUN ('UPDATE '||ctest1.table_name||' SET revisionname = '''||ctest1.schemaname||''' ||SUBSTR(revisionname,1,16)');

END LOOP;
END; I also read that instead of run, I would use: EXEC SQL IMMEDIATE EXECUTE Thanks again ahead of time. Your input is greatly appreciated. Received on Fri Apr 29 2005 - 09:39:03 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US