Re: Dynamically execute sql statements in oracle

bucycle_at_hotmail.com wrote:

> 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.

Rather than writing malformed SQL, obviously based on some other product's syntax, why don't you state the business case and let someone propose a solution.

It is far from obvious what you are trying to accomplish.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)