Hi,
I have a question regarding the use of execute immediate on CREATE TABLE statement.
CREATE USER SAM IDENTIFIED BY <PASSWORD>;
CREATE ROLE SAM_ROLE;
GRANT CREATE TABLE TO SAM_ROLE;
GRANT SAM_ROLE TO SAM;
CASE 1
DECLARE
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE SAM_TEST(COLUMN1 VARCHAR2(100))';
END;
CASE 2
CREATE OR REPLACE PROCEDURE PR_SAM
AS
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE SAM_TEST(COLUMN1 VARCHAR2(100))';
END;
EXEC PR_SAM;
CASE 3
CREATE OR REPLACE PROCEDURE PR_SAM
AUTHID CURRENT_USER
AS
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE SAM_TEST(COLUMN1 VARCHAR2(100))';
END;
CASE 1 and CASE 3 execute successfully. However CASE 2 gives me an insufficient privilege error. Could someone explain me the reason for the error. And why does it not show up during the ither two cases.
Thanks in advance