Re: Create SQL satatment in stored procedure and execute it immediately

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/12/09
Message-ID: <3492a4b5.21534384@inet16>#1/1

On 9 Dec 1997 18:09:53 GMT, "N. Marc" <nester_marchenko_at_epam.com> wrote:

>Hi all.
>What function can execute SQL satatment immediatly, if this statment was
>stored in variable ?
>
>Please mail to: nester_marchenko_at_epam.com
>
>Thanks for any ideas.
>Nester.
>
>

dbms_sql does this, I use a procedure like:

create or replace procedure exec( stmt in varchar2 ) as

    exec_cursor integer default dbms_sql.open_cursor;     rows_processed number default 0;
begin

    dbms_sql.parse(exec_cursor, stmt, dbms_sql.native );
    rows_processed := dbms_sql.execute(exec_cursor);
    dbms_sql.close_cursor( exec_cursor );

exception

    when others then

      if dbms_sql.is_open(exec_cursor) then
        dbms_sql.close_cursor(exec_cursor);
      end if;
      raise;

end;
/

to make it easier. bear in mind that the owner of the procedure must have the privelege to do what the statement wants to do directly, NOT via a role. That is, if you want to do it dynamically in pl/sql, you better be able to:

SQL> set role none;
SQL> -- do that statement ---

If it fails in sqlplus with no roles, it will fail in pl/sql as well. You need to grant things like "create table", "create view" etc to the owner directly.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Dec 09 1997 - 00:00:00 CST