This discussion is archived
14 Replies Latest reply: Nov 22, 2012 5:49 AM by Mac_Freak_Rahul RSS

create table dynamically

Mac_Freak_Rahul Newbie
Currently Being Moderated
Hi All,

I have a variable and I am assigning the following string to it.

Lv_create_table := 'CREATE TABLE
BIAPPS.W_CUSTOMER_LOC_USE_D_P
AS SELECT * FROM
BIAPPS.W_CUSTOMER_LOC_USE_D
WHERE W_INSERT_DT >= TO_DATE(''01/JAN/2012'',''DD/MON/YYYY'') OR W_UPDATE_DT >= TO_DATE('01/JAN/2012','DD/MON/YYYY')
';

execute immediate Lv_create_table;

now this is a part of the procedure and its getting excuted successfully, yet the table is not getting created, I have logged on using 'biapps' user and I am creating this table in biapps schema, further this user has the privilege 'create table', yet the table is not getting created.

please help.

Thanks
Rahul
  • 1. Re: create table dynamically
    Manik Expert
    Currently Being Moderated
    Hi,


    TO_DATE(''01/JAN/2012'',''DD/MON/YYYY'') OR W_UPDATE_DT >= TO_DATE('01/JAN/2012','DD/MON/YYYY')



    should it be :

    TO_DATE(''01/JAN/2012'',''DD/MON/YYYY'') OR W_UPDATE_DT >= TO_DATE(''01/JAN/2012'',''DD/MON/YYYY'')

    ???

    We need details about what you are actually trying to do in the whole code pspctive....
    Do u handle exceptions in your procedure..

    Dynamic SQL to create tables on the fly is not a good DB design..

    Cheers,
    Manik.

    Edited by: Manik on Nov 22, 2012 3:49 PM
  • 2. Re: create table dynamically
    BluShadow Guru Moderator
    Currently Being Moderated
    Why are you trying to create a table dynamically at run time anyway? that is just wrong.

    Create the table once as part of your design, and then use it in your code.
    If it's required for temporary data, create a Global Temporary Table once instead.

    Creating objects at runtime is not good design.
  • 3. Re: create table dynamically
    908002 Expert
    Currently Being Moderated
    IS the procedure really executing successfully...

    You may have improper exception handling like "when others then null'

    paste complete procedure ifthis is not the case
  • 4. Re: create table dynamically
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi,

    The statement as you posted is not valid:
    Lv_create_table := 'CREATE TABLE
    BIAPPS.W_CUSTOMER_LOC_USE_D_P
    AS SELECT * FROM
    BIAPPS.W_CUSTOMER_LOC_USE_D
    WHERE W_INSERT_DT >= TO_DATE(''01/JAN/2012'',''DD/MON/YYYY'') OR W_UPDATE_DT >= TO_DATE('01/JAN/2012','DD/MON/YYYY')
    ';
    You are just using one single quote in the second TO_DATE.

    Regards.
    Al
  • 5. Re: create table dynamically
    971895 Journeyer
    Currently Being Moderated
    Now you can check... it's corrected code.


    declare
    Lv_create_table varchar2(1000);
    begin
    Lv_create_table := ' CREATE TABLE W_CUSTOMER_LOC_USE_D_P AS '||
    ' SELECT * FROM emp WHERE created_on >= TO_DATE(''01/JAN/2012'',''DD/MON/YYYY'') OR created_on >= TO_DATE(''01/JAN/2012'',''DD/MON/YYYY'')';

    execute immediate Lv_create_table;
    end;
    /
  • 6. Re: create table dynamically
    Mac_Freak_Rahul Newbie
    Currently Being Moderated
    Alright heres my complete procedure :

    create or replace PROCEDURE sp_insert_record_count_temp (
    p_db_link VARCHAR2 default null,
    suffix_col VARCHAR2)
    IS
    CURSOR c1
    IS
    SELECT table_name, table_id, restriction_clause, owner
    FROM table_record_count
    WHERE UPPER (test_yn) = 'Y';

    lv_update_count VARCHAR2 (2000);
    lv_latest_update_count VARCHAR2 (2000);
    lv_create_script VARCHAR2 (2000);

    lv_rec_upd NUMBER;

    p_table_name VARCHAR2 (50);
    p_table_id NUMBER (15);
    p_code VARCHAR2 (255);
    p_error VARCHAR2 (255);
    lv_primary_key_cols VARCHAR2 (255) := NULL;
    BEGIN

    FOR i IN c1
    LOOP
    lv_update_count := NULL;

    lv_create_script :=
    'CREATE TABLE '
    || CHR (10)
    || i.owner
    ||'.'
    || i.table_name
    || suffix_col --KK
    || ' '
    || CHR (10)
    || ' AS SELECT * FROM '
    || CHR (10)
    || i.table_name
    --|| p_db_link
    || CHR (10)
    || ' '
    || i.restriction_clause;

    p_table_name := i.table_name;
    p_table_id := i.table_id;

    BEGIN

    dbms_output.put_line(lv_create_script);
    EXECUTE IMMEDIATE lv_create_script;


    EXCEPTION
    WHEN OTHERS
    THEN

    dbms_output.Put_line('rahul'||sqlerrm);
    END;
    END LOOP;
    EXCEPTION
    WHEN OTHERS
    THEN
    ROLLBACK;
    dbms_output.Put_line('rahul1'||sqlerrm);
    END;


    --------------------------------------------------
    I am calling this procedure and passing these parameters : begin sp_insert_record_count_temp(NULL,'_P'); end;
    after execution I am getting the following on my dbms_output window:

    CREATE TABLE
    BIAPPS.W_CUSTOMER_LOC_USE_D_P
    AS SELECT * FROM
    W_CUSTOMER_LOC_USE_D
    WHERE W_INSERT_DT >= TO_DATE('01/JAN/2012','DD/MON/YYYY') OR W_UPDATE_DT >= TO_DATE('01/JAN/2012','DD/MON/YYYY')
    rahulORA-01031: insufficient privileges
  • 7. Re: create table dynamically
    Mac_Freak_Rahul Newbie
    Currently Being Moderated
    Well this is an existing code so I cant help much .. need to create backups for as many as 60 tables using this code, so need to create these backups dynamically.

    Thanks
    Rahul
  • 8. Re: create table dynamically
    Mac_Freak_Rahul Newbie
    Currently Being Moderated
    it should be : TO_DATE(''01/JAN/2012'',''DD/MON/YYYY'') OR W_UPDATE_DT >= TO_DATE(''01/JAN/2012'',''DD/MON/YYYY'')

    I have pasted the procedure below.

    Thanks
    Rahul
  • 9. Re: create table dynamically
    Manik Expert
    Currently Being Moderated
    who owns this table table_record_count ...

    ask owner of this table to give
    grant select on table_record_count  to BIAPPS; 
    and then try your code.

    Cheers,
    Manik.
  • 10. Re: create table dynamically
    Mac_Freak_Rahul Newbie
    Currently Being Moderated
    table_record_count is owned by biapps user only.

    Regards
    Rahul
  • 11. Re: create table dynamically
    908002 Expert
    Currently Being Moderated
    EXCEPTION
    WHEN OTHERS
    THEN
    ROLLBACK;

    this is causing your problem as i mnetioned in my earlier response.. you are not trying to get exact error location..
  • 12. Re: create table dynamically
    AlbertoFaenza Expert
    Currently Being Moderated
    >
    rahulORA-01031: insufficient privileges
    Message is quite clear. You don't have sufficient privileges.
    Just consider when you are executing this procedure you are impersonating the procedure owner.

    Now what is the schema owner of this procedure? Is it BIAPPS?
    Does this schema have SELECT privilege on all table used in this procedure (table_record_count, W_CUSTOMER_LOC_USE_D)?
    Does this schema have CREATE TABLE privilege directly assigned and not via a role? (GRANT CREATE TABLE to BIAPPS)?

    Regards.
    Al
  • 13. Re: create table dynamically
    Mac_Freak_Rahul Newbie
    Currently Being Moderated
    all the objects referred are owned by biapps ..

    I guess you have answered my problem I would be having a create table privilege through a role and I would need a direct privilege, just update here, thanks

    Rahul
  • 14. Re: create table dynamically
    Mac_Freak_Rahul Newbie
    Currently Being Moderated
    Does this schema have CREATE TABLE privilege directly assigned and not via a role? (GRANT CREATE TABLE to BIAPPS)? <- this is what helped me and correct answer to my query, ty so much :)

    Regards
    Rahul

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points