EXECUTE IMMEDIATE Feature allows you to execute DYNAMIC SQL Statements inside the PL/SQL Procedure, but here i show you interesting thing about this feature.
Here is Video of these procedures
let’s demonstrate small demo
SQL> create user qobesa identified by qobesa; User created. SQL> grant connect,resource to qobesa; Grant succeeded.
Our user called qobesa has privileges to connect database, create table and others because resource role contains privileges such as ‘create table’, test it
sqlplus qobesa/qobesa SQL> create table test (id number); Table created.
let’s create simple procedure which creates table with execute immediate
SQL> create or replace procedure test_proc 2 is 3 begin 4 EXECUTE IMMEDIATE 'CREATE TABLE TEST1(ID NUMBER)'; 5 end; 6 / Procedure created.
and execute it
SQL> exec test_proc BEGIN test_proc; END; * ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "QOBESA.TEST_PROC", line 4 ORA-06512: at line 1
got an error “insufficient privileges” – Do we have this privilege?, YES. we have. It has been grouped under RESOURCE role and the RESOURCE role has been granted to this user. Then, What is stopping us?
In STORED PROCEDURE, roles are DISABLED. Hence any PRIVILEGES granted by a ROLE to USER, will NOT be in effect. The PRIVILEGES MUST be directly granted to the user.
Therefore we can grant create table privilege to our user and will work, test it
SQL> conn sys / as sysdba Enter password: Connected. SQL> grant create table to qobesa; Grant succeeded. SQL> conn qobesa/qobesa Connected. SQL> exec test_proc PL/SQL procedure successfully completed.
It’s all, good luck.