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.
That's because you need to grant "create table" to your user. Not inherit it via a role:
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) You don't need this when running anonymous blocks. Only compiled PL/SQL.