View Poll Results: can i create table by using dynamic sql

Voters
0. You may not vote on this poll
  • can i create table by using dynamic sql

    0 0%
  • can i create table by using dynamic sql

    0 0%
Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Posts
    6

    Talking Unanswered: insufficient priviliges for table creation

    CREATE OR REPLACE PROCEDURE pvg
    AS
    l_table_name USER_TABLES.table_name%TYPE;
    l_num_rows USER_TABLES.num_rows%TYPE;
    ext varchar2(3) :='TMP';
    stmt varchar2(1000);
    cursor c_stage1_tables is select TABLE_NAME,NUM_ROWS from USER_TABLES
    where TABLE_NAME like 'STAGE1_A521_S%';
    BEGIN
    open c_stage1_tables;
    loop
    fetch c_stage1_tables into l_table_name,l_num_rows;
    exit when c_stage1_tables%notfound;
    --stmt:='create table trypvg tablespace BIESAPTS01 as select * from '|| l_table_name;
    stmt:='create table ' ||l_table_name||'_'||ext|| ' tablespace BIESAPTS01 as select * from '|| l_table_name;
    EXECUTE IMMEDIATE stmt;


    end loop;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    Null;
    END pvg;



    I am getting insufficient priviliges error while executing above procedure. I am able to create table from sql plus in same schema.

    Any suggestions appreciated.

    Regards,
    Prashant

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: insufficient priviliges for table creation

    Yes you can. But you must have the CREATE TABLE privilege granted directly to your USERNAME, not via a role, for this to work in a stored procedure. For example, I can do this:

    tandrews@IDEV> begin
    2 execute immediate 'create table junk ( x varchar2(1) )';
    3 end;
    4 /

    PL/SQL procedure successfully completed.

    But I can't do this:

    tandrews@IDEV> create or replace procedure p as
    2 begin
    3 execute immediate 'create table junk ( x varchar2(1) )';
    4 end;
    5 /

    Procedure created.

    tandrews@IDEV> exec p;
    begin p; end;

    *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    ORA-06512: at "TANDREWS.P", line 3
    ORA-06512: at line 1

    - I would have to ask the DBA to grant the CREATE TABLE privilege to user TANDREWS for this to work.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •