Error in PL/SQL block

[dilippanda]

Hi,

While executing the following block i am getting error as Insuffiecient privilege.

CREATE OR REPLACE PROCEDURE ddl_create_proc (p_table_name IN VARCHAR2)
AS
l_stmt VARCHAR2(200);
BEGIN
DBMS_OUTPUT.put_line('STARTING ');
l_stmt := 'create table '|| p_table_name || ' as (select * from emp )';
EXECUTE IMMEDIATE l_stmt;
DBMS_OUTPUT.PUT_LINE('end ');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('exception '||SQLERRM || 'message'||sqlcode);
END;

exec ddl_create_proc('TEMP');

STARTING
exception ORA-01031: insufficient privilegesmessage-1031.

Please help me regarding this error.

Thanks,
Dilip
09337877895

[debasisdas]

There could be 2 possible reasons.

1. The user does not have create table previliges.
2. The user does not have select previleges on the emp table.

[dilippanda]

Hi,

EMP table has been created in SCOTT user.
I am able to create and select tables in that user.So that user has create table privileges and select privileges.

Is there any other problem?

Thanks,
Dilip

There could be 2 possible reasons.

1. The user does not have create table previliges.
2. The user does not have select previleges on the emp table.

[debasisdas]

Log in through the sys user and grant CREATE TABLE previleges to scott user or ask your DBA to do the same for you.

It should work for you.

I can't figure out any other problem

Please try the above and do post back.