Re: DBMS_SQL (NO PRIVILIGES TO CREATE TABLES)

yuan wrote:
>
> It seems i miss some privileges to create tables with the dbms_sql
> package:
>
> id1 varchar2(30);
>
> Any answers/comments welcome
> c1 integer;
>
> nom varchar2(30);
>
> nb_snp number;
>
> serial2 number;
>
> cursor c2 is select name from dba_snapshots
>
> where owner='SCOTT'
>
> AND NAME <> 'TEST';
>
> return1 integer;
>
> begin
>
> open c2;
>
> loop
>
> fetch c2 into nom;
>
> exit when c2%notfound;
>
> dbms_output.put_line('CREATE TABLE SCOTT.' || nom || ' ('||NOM|| '
> NUMBER)');
>
> c1:=dbms_sql.open_cursor;
>
> dbms_sql.parse(c1,'CREATE TABLE SCOTT.' || nom || ' ('||NOM|| '
> NUMBER)',dbms_sql.v7);
>
> return1 := dbms_sql.execute(c1);
>
> dbms_output.put_line('RESULTAT: '||RETURN1);
>
> dbms_sql.close_cursor(c1);
>
> end loop;
>
> dbms_output.put_line(SYSDATE);
>
> dbms_output.NEW_line;
>
> end;
>
> ------------------
>
> execute scott.tgr_select
>
> execute scott.tgr_select
>
> ORA-01031: privilèges insuffisants
>
> ORA-06512: à "SYS.DBMS_SYS_SQL", ligne 239
>
> ORA-06512: à "SYS.DBMS_SQL", ligne 25
>
> ORA-06512: à "SCOTT.TGR_SELECT", ligne 24
>
> ORA-06512: à ligne 2
>
> CREATE TABLE SCOTT.TEST1XX (TEST1 NUMBER)
Hi yuan,

the user who executes your package must have been granted the right to create tables directly not via a role.

-- 
Regards

Matthias Gresz    :-)