Hi,

Had problems with SQLLive, (500 response), therefore the examples are her.

I have a strange behaviour with execute immediate, where it behaves differently from within a PL/SQL procedure than it does when running it standalone.

Here is the example:
I create a simple table

CREATE TABLE TEST01 
(
  ID NUMBER 
, TEXT VARCHAR2(100 CHAR) 
);

Then I create a very simple procedure that will create a table based on the original table

CREATE OR REPLACE PROCEDURE test02 AS
    
BEGIN
    EXECUTE IMMEDIATE 'create table ABCD as select * from TEST01' ;
    COMMIT ;
   
END test02;

When I run this:

begin
    test02();
end;

I receive the following error:
ORA-01031: insufficient privileges
ORA-06512: at "INGIMUNDUR.TEST02", line 4
ORA-06512: at line 2
01031. 00000 - "insufficient privileges"

But if I run:

BEGIN
    EXECUTE IMMEDIATE 'create table ABCD as select * from TEST01' ;
    COMMIT ;
   
END;

everything runs fine, with no errors.

Why is this discrepancy?

Regards,
Ingimundur K. Gudmundsson.

p.s. I also tested this on an Oracle XE 11g, and the behaviour is the same.

CREATE TABLE TEST01 (
  ID NUMBER 
, TEXT VARCHAR2(100 CHAR) 
);

CREATE OR REPLACE PROCEDURE test02 AS   
BEGIN
    EXECUTE IMMEDIATE 'create table ABCD as select * from TEST01' ;
    COMMIT ;
   
END test02;
/

exec test02();

ORA-01031: insufficient privileges

grant create table to chris;

exec test02();

desc ABCD;

Name   Null?   Type                 
ID             NUMBER               
TEXT           VARCHAR2(100 CHAR)