Re: Newbie: Help please !

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 07 Aug 1998 15:01:39 GMT
Message-ID: <35d01660.7369366@192.86.155.100>

A copy of this was sent to andy sykes <andrew.sykes_at_holland.sun.com> (if that email address didn't require changing) On Fri, 07 Aug 1998 16:42:33 +0200, you wrote:

>What's wrong in this anonymous block ? When I run this in sqlplus it
>complains at the "drop table" line with :
>
>ERROR at line 1:
>ORA-06550: line 9, column 3:
>PLS-00103: Encountered the symbol "DROP" when expecting one of the
>following:
>begin declare exit for goto if loop mod null pragma raise
>return select update while <an identifier>
><a double-quoted delimited-identifier> etc.
>
>If I am doing this in a stupid way then please tell me.
>
>Thanks in advance.
>
>declare
> nrecs number ;
>begin
> select count(*)
> into nrecs
> from user_tables
> where table_name = 'ts_user' ;
> if nrecs > 0 then
> drop table ts_user ;
> end if ;
> create table ts_user (
> user_id char(15) ,
> user_name varchar(30) ) ;
>end ;
>/

If you want to do ddl in pl/sql you must use dynamic sql. pl/sql cannot statically execute DDL. The above example could be written as:

declare

    l_cursor integer default dbms_sql.open_cursor;     n integer;
begin

    for x in ( select table_name from user_tables where table_name = 'TESTING' )     loop

        dbms_sql.parse( l_cursor, 'drop table testing', dbms_sql.native );
        n := dbms_sql.execute(l_cursor);
        exit;

    end loop;

    dbms_sql.parse(l_cursor, 'create table testing ( y int )', dbms_sql.native);     n := dbms_sql.execute(l_cursor);

    dbms_sql.close_cursor( l_cursor );
end;
/

Note that if you turn the above block into a PROCEDURE (as opposed to the anonymous block it is currently) that roles are never enabled during the execution of a procedure.

Try this:

SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"

If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privelege from a role and hence dbms_sql won't be able to do it either.

The above anonymous block executes with roles so you want need to grant CREATE table directly to the person executing this block but if it were a procedure, you would.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

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 Fri Aug 07 1998 - 10:01:39 CDT