connect sys/<пароль>@<tnsname>;
grant create procedure to user1 with admin option;

connect user1/<пароль>@<tnsname>;
grant create procedure to user2;

CREATE OR REPLACE PROCEDURE CREATE_PROC(p_command IN VARCHAR2)  AS
 cursor_name INTEGER;
 rows_processed INTEGER;
 v_except  EXCEPTION;
BEGIN
 cursor_name := dbms_sql.open_cursor;
 dbms_sql.parse(cursor_name,'CREATE OR REPLACE PROCEDURE '||p_command ,dbms_sql.NATIVE);

 rows_processed := dbms_sql.execute(cursor_name);
 dbms_sql.close_cursor(cursor_name);
EXCEPTION
WHEN OTHERS THEN
 dbms_sql.close_cursor(cursor_name);
 RAISE v_except;
END CREATE_PROC;

grant execute on CREATE_PROC to user2;

QLWKS> connect user1
Связь установлена.

SQLWKS> CREATE OR REPLACE PROCEDURE CREATE_PROC(p_command IN VARCHAR2)  AS
      2 >  cursor_name INTEGER;
      3 >  rows_processed INTEGER;
      4 >  v_except  EXCEPTION;
      5 > BEGIN
      6 >  cursor_name := dbms_sql.open_cursor;
      7 >  dbms_sql.parse(cursor_name,'CREATE OR REPLACE PROCEDURE '||p_command ,dbms_sql.NATIVE);
      8 > 
      9 >  rows_processed := dbms_sql.execute(cursor_name);
     10 >  dbms_sql.close_cursor(cursor_name);
     11 > EXCEPTION
     12 > WHEN OTHERS THEN
     13 >  dbms_sql.close_cursor(cursor_name);
     14 >  RAISE v_except;
     15 > END CREATE_PROC;
     16 > 
     17 > 
Предложение обработано.

SQLWKS> grant execute on CREATE_PROC to user2
      2 > 
Предложение обработано.

SQLWKS> connect user2
Связь установлена.

SQLWKS> execute user1.CREATE_PROC('test1 as begin null; end;');
Предложение обработано.