Re: Using DDL commands in PL/SQL

From: Iosif Tanasescu <a_at_j.smith>
Date: 28 Apr 1998 23:07:31 GMT
Message-ID: <01bd72fa$7c756220$5f1b67d1@iosif>

Mikael Schonenberg <micke_at_netg.se> wrote in article <Pine.LNX.3.96.980428141316.23404A-100000_at_valdez.netg.se>...
>
> Through dbms_sql (in PL/SQL) I manage to use DDL commands, which is very
> nice. Problem is I don't manage all of them. Drop Table works fine, but
> when I try to Create Table it complaints about insufficient priviliges.
> Well, I found that somewhat hard to believe, since the user woning
> and running the PL/SQL package is granted the DBA role.
>
> Any idea?

I had the same errors, and could add : could not select from some DBA_xxx views, or ALL_xxxx views, etc.

The answer ( which is work very fine :)  I created some proceduredures, on main problems, like :

    Procedure create_table(strString in varchar2)     is
    nCursor number;
   begin

       nCursor := dbms_sql.open_cursor;
      dbms_sql.parse(nCursor,strString,dbms_sql.v7);
       dbms_sql.close_cursor;

  etc...

and ( this was the trick, used even by ORACLE, many times), I created the procedure under SYS schema, and then i created a PUBLIC SYNONYM create_table...
and granted execution on create_table to DBA (roles !!) ANd all works very fine !!

Bye !
I.K.  

>
> /Mikael
>
>
Received on Tue Apr 28 1998 - 18:07:31 CDT