Re: 7.1, dbms_sql problem

In article <3me1u1$gvt_at_gatekeeper.cognos.com>, nigelc_at_Cognos.COM (Nigel Campbell) writes:
|> 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

This is yet another occurrence of the 'roles are disabled inside of stored procedures' issue. That is the basic problem here. The priviledge to create tables was granted via the 'resource' role. This role is disabled inside stored procedures and triggers, hence the error. You need to grant 'create table' directly to the user owning the procedure. Received on Wed Apr 12 1995 - 00:00:00 CEST