Re: 7.1, dbms_sql problem

In article <3me1u1$gvt_at_gatekeeper.cognos.com>, nigelc_at_Cognos.COM (Nigel Campbell) wrote:
>
> Can someone shed some light on the following problem. I have two
> examples of using the 7.1 dbms_sql package to create a table.
> Using sqlplus the first example works fine. If (as the same user)
> I create the procedure and (still as the same user) I attempt
> to call the procedure from sqlplus I receive an insufficient privilige
> (1031) error. What is the difference?
>
> declare
> mycursor integer;
> retstat integer;
> begin
> dbms_output.put_line('1>');
> mycursor := dbms_sql.open_cursor;
> dbms_output.put_line('2>');
> dbms_sql.parse(mycursor,'create table abc (col1 char(1))', dbms_sql.v7);
> dbms_output.put_line('3>');
> retstat:= dbms_sql.execute(mycursor);
> dbms_output.put_line('4>');
> dbms_sql.close_cursor(mycursor);
> dbms_output.put_line('5>');
> commit;
> dbms_output.put_line(sqlerrm);
> end;
> .
> /
>
>
> create or replace procedure doit as
> mycursor integer;
> retstat integer;
> begin
> dbms_output.put_line('1>');
> mycursor := dbms_sql.open_cursor;
> dbms_output.put_line('2>');
> dbms_sql.parse(mycursor,'create table abc (col1 char(1))', dbms_sql.v7);
> dbms_output.put_line('3>');
> retstat:= dbms_sql.execute(mycursor);
> dbms_output.put_line('4>');
> dbms_sql.close_cursor(mycursor);
> dbms_output.put_line('5>');
> commit;
> exception
> when others then
> dbms_output.put_line(sqlerrm);
> dbms_sql.close_cursor(mycursor);
> dbms_output.put_line('error');
> end;
>
> --
> Nigel Campbell Voice: (613) 738-1338 ext 3016 P.O. Box 9707
> Business Intelligence FAX: (613) 738-0002 3755 Riverside Dr.
> Cognos Incorporated MCI: nigel campbell || 3074729 Ottawa, Ontario
> UUnet: nigelc_at_cognos.COM CANADA K1G 3Z4

Not sure if it is bug or feature but I ran into the same thing. My problem had to do with the owner of the procedure having the CREATE TABLE privilege via a role. When I granted the user the CREATE TABLE privilege directly it ran fine.

Steve Hoffman Consulting at Apple Computer Received on Thu Apr 13 1995 - 00:00:00 CEST