Re: SQL Help

Paul wrote:

> I'm running Oracle 8.
>
> I wish to execute an SQL command that is stored in a table in PL/SQL.
>
> So, for example, take a table called COMMANDS
>
> COMMANDS
>
> Command Other Data....
> select * from TABLE ....
>
> The SQL command "select * from TABLE" is stored in the column Command. I
> want to retrieve this command and execute it.
>
> Can I do the whole thing in PL/SQL?

Try this:

create or replace procedure run_cmd as

comm_cursor integer;
comm_return integer;
cursor r_cur is select command from COMMANDS;

begin

comm_cursor := dbms_sql.open_cursor;

for r_cur_rec in r_cur
loop

   dbms_sql.parse(comm_cursor, r_cur_rec.command, DBMS_SQL.NATIVE);    comm_return := dbms_sql.execute(comm_cursor);    END;
end loop;
end;
/

I haven't cheched, but it should work, I hope, anyway you have to use the DBMS_SQL package.

Hope this helps
Ciao Gennaro Received on Tue Jul 06 1999 - 03:26:34 CDT