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 -> Re: Dynamically execute sql statements in oracle

Re: Dynamically execute sql statements in oracle

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Fri, 29 Apr 2005 08:46:29 -0700
Message-ID: <1114789357.472296@yasure>


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.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Fri Apr 29 2005 - 10:46:29 CDT

Original text of this message

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