Running EXECUTE IMMEDIATE for CREATE PROCEDURE statement, from an anonymous PL/SQL block is working as expected. But if I have the same query inside a standalone procedure and call that procedure from the PL/SQL block it gives me the following error:
ORA-01031: insufficient privilege
Example:
connect sys/<sys_pass>@<tns_alias> as sysdba;
alter session set "_oracle_script"=true;
-- not relevant, just for dropping the user
-- https://stackoverflow.com/a/50711859/10721332
CREATE OR REPLACE PROCEDURE drop_user
( user_name IN varchar2 )
IS
v_user_exists NUMBER;
BEGIN
LOOP
FOR c IN (SELECT s.sid, s.serial# FROM v$session s WHERE upper(s.username) = user_name)
LOOP
EXECUTE IMMEDIATE
'alter system kill session ''' || c.sid || ',' || c.serial# || ''' IMMEDIATE';
END LOOP;
BEGIN
EXECUTE IMMEDIATE 'drop user ' || user_name || ' cascade';
EXCEPTION WHEN OTHERS THEN
IF (SQLCODE = -1940) THEN
NULL;
ELSE
RAISE;
END IF;
END;
BEGIN
SELECT COUNT(*) INTO v_user_exists FROM dba_users WHERE username = user_name;
EXIT WHEN v_user_exists = 0;
END;
END LOOP;
END;
/
call drop_user('CREED');
create user creed identified by bratton;
grant connect, resource to creed;
grant unlimited tablespace to creed;
connect creed/bratton@<tns_alias>;
set serveroutput on;
---------------------------------------------------
CREATE OR REPLACE PROCEDURE executor(
query VARCHAR2
) IS
BEGIN
EXECUTE IMMEDIATE query;
EXCEPTION
WHEN OTHERS THEN dbms_output.put_line(sqlerrm || ' : ' || query);
END;
/
DECLARE
query1 varchar2(200);
query2 varchar2(200);
BEGIN
query1 := 'create or replace procedure PROC1 is begin dbms_output.put_line(''hello world''); end;';
executor(query1); -- doesn't work
query2 := 'create or replace procedure PROC2 is begin dbms_output.put_line(''hello world''); end;';
EXECUTE IMMEDIATE query2; -- works
END;
/
Why does query2 work correctly while query1 fails to execute?
I tried the following, which gave me the same error:
- Altering the CURRENT_SCHEMA inside the procedure
- Adding the SCHEMA prefix in the procedure name
