Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!g14g2000cwa.googlegroups.com!not-for-mail
From: bucycle@hotmail.com
Newsgroups: comp.databases.oracle.server
Subject: Dynamically execute sql statements in oracle
Date: 29 Apr 2005 07:39:03 -0700
Organization: http://groups.google.com
Lines: 36
Message-ID: <1114785543.147894.53030@g14g2000cwa.googlegroups.com>
NNTP-Posting-Host: 66.250.6.102
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1114785547 22652 127.0.0.1 (29 Apr 2005 14:39:07 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 29 Apr 2005 14:39:07 +0000 (UTC)
User-Agent: G2/0.2
Complaints-To: groups-abuse@google.com
Injection-Info: g14g2000cwa.googlegroups.com; posting-host=66.250.6.102;
   posting-account=voG4yw0AAABqAJ-DUsxleBjlpStQLDkc
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:241639

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.