Re: 7.1, dbms_sql problem

Hi Nigel,

I have been trying invain to solve this problem. Can you please tell me the solution when you find it?

Thanks
Vivek
vivek_at_eng2.uconn.edu

--
Nigel Campbell (nigelc_at_Cognos.COM) 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