PL/SQL DDL stmts & DBMS_SQL package

From: Suresh Easwar <suresh.easwar_at_citicorp.com>
Date: 1996/02/02
Message-ID: <3111FDEF.3607_at_citicorp.com>#1/1

I am seeing the following inexplicable behavior in PL/SQL (oracle 7.1 / Windows NT 3.51):

I am trying to change user passwords via a stored procedure called execute_ddl_statement (code is shown below) using the DBMS_SQL package.

When I execute the stored procedure from sqlplus (connected as creator of the stored procedure, 'fcc'), I get the following error:

SQL> begin execute_ddl_sql('alter user xyz identified by abc'); end; SQL> /
ERROR at line 1:

ORA-01031: insufficient privileges
ORA-06512: at "FCC.EXECUTE_DDL_SQL", line 18
ORA-06512: at line 1

SQL> However, if I execute the code in the stored procedure manually everything works fine:

SQL> declare
SQL>	i_cursor integer;
SQL>	i_rows_affected integer;
SQL> begin
SQL>	i_cursor := dbms_sql.open_cursor;
SQL>	dbms_sql.parse(i_cursor, 'alter user xyz identified by abc', dbms_sql.v7);
SQL>	i_rows_affected := dbms_sql.execute(i_cursor);
SQL>	commit;
SQL>	if dbms_sql.is_open(i_cursor) then
SQL>		dbms_sql.close_cursor(i_cursor);
SQL>	end if;
SQL> exception
SQL> when others then
SQL>	if dbms_sql.is_open(i_cursor) then
SQL>		dbms_sql.close_cursor(i_cursor);
SQL>	end if;
SQL>	raise;
SQL> end;
SQL> /

procedure completed successfully.
SQL> The code for the stored procedure follows:

create or replace procedure execute_ddl_sql (v_sql_statement varchar2) declare

	i_cursor integer;
	i_rows_affected integer;
begin
	i_cursor := dbms_sql.open_cursor;
	dbms_sql.parse(i_cursor, v_sql_statement, dbms_sql.v7);
	i_rows_affected := dbms_sql.execute(i_cursor);
	commit;
	if dbms_sql.is_open(i_cursor) then
		dbms_sql.close_cursor(i_cursor);
	end if;

exception
when others then

	if dbms_sql.is_open(i_cursor) then
		dbms_sql.close_cursor(i_cursor);
	end if;
	raise;

end;
/

grant execute on execute_ddl_statement to public
/

Curiously, when i am connected as 'internal' both methods work fine. Also, if i create the procedure as 'sys' and run it as 'sys' all is fine again.
But, if i create the procedure as 'fcc' and try to execute as 'sys', calling the procedure does'nt work.

Please help.
Suresh Received on Fri Feb 02 1996 - 00:00:00 CET