7.1, dbms_sql problem

From: Nigel Campbell <nigelc_at_Cognos.COM>
Date: 1995/04/11
Message-ID: <3me1u1$gvt_at_gatekeeper.cognos.com>#1/1


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
Received on Tue Apr 11 1995 - 00:00:00 CEST

Original text of this message