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

The lot looks eeringly like M$ SS2K code...

Maybe it's time to realize a schema *automatically* is appended to a tablename, but your coding is just off; in Oracle you may refer to an object (e.g. table) by schema.object. So what you can do is refer to SCOTT.EMPLOYEES (SCOTT being the schema, and EMPLOYEES being a table, or a procedure, or a function, ...).

Also - you may not have the necessary privileges to update table ctest1.table_name. Looks like you want to run this as sys - which is A Bad Idea (tm).

And indeed - instead of RUN, use EXECUTE IMMEDIATE. But do realize, this is not a scalable way of programming, and often not necessary. Not in Oracle, anyway.

-- 
Regards,
Frank van Bortel