Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Dynamically execute sql statements in oracle
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