14 Replies Latest reply: Nov 22, 2012 7:49 AM by Mac_Freak_Rahul RSS

    create table dynamically

    Mac_Freak_Rahul
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            table_record_count is owned by biapps user only.

                            Regards
                            Rahul
                            • 11. Re: create table dynamically
                              908002
                              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
                                >
                                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
                                  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
                                    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