11 Replies Latest reply on May 1, 2012 3:34 PM by rp0428

    CTAS using dbms_metadata.get_ddl for Partitioned table

    chakra
      Hi,
      I would like to create a temporary table from a partitioned table using CTAS. I plan to use the following steps in a PL/SQL procedure:
      1. Use dbms_metadata.get_ddl to get the script
      2. Use raplace function to change the tablename to temptable
      3. execute the script to get the temp table created.

      SQL> create or replace procedure p1 as
      2 l_clob clob;
      3 str long;
      4 begin
      5 SELECT dbms_metadata.get_ddl('TABLE', 'FACT_TABLE','USER1') into l_clob FROM DUAL;
      6 dbms_output.put_line('CLOB Length:'||dbms_lob.getlength(l_clob));
      7 str:=dbms_lob.substr(l_clob,dbms_lob.getlength(l_clob),1);
      8 dbms_output.put_line('DDL:'||str);
      9 end;
      12 /

      Procedure created.

      SQL> exec p1;
      CLOB Length:73376
      DDL:

      PL/SQL procedure successfully completed.


      I cannot see the DDL at all. Please help.
        • 1. Re: CTAS using dbms_metadata.get_ddl for Partitioned table
          sb92075
          chakra wrote:
          Hi,
          I would like to create a temporary table from a partitioned table using CTAS. I plan to use the following steps in a PL/SQL procedure:
          1. Use dbms_metadata.get_ddl to get the script
          2. Use raplace function to change the tablename to temptable
          3. execute the script to get the temp table created.

          SQL> create or replace procedure p1 as
          2 l_clob clob;
          3 str long;
          4 begin
          5 SELECT dbms_metadata.get_ddl('TABLE', 'FACT_TABLE','USER1') into l_clob FROM DUAL;
          6 dbms_output.put_line('CLOB Length:'||dbms_lob.getlength(l_clob));
          7 str:=dbms_lob.substr(l_clob,dbms_lob.getlength(l_clob),1);
          what does line above do?
          • 2. Re: CTAS using dbms_metadata.get_ddl for Partitioned table
            rp0428
            >
            str long;
            >
            Don't use the deprecated long datatype. Use a VARCHAR; it can be up to 32k.

            And why not just use sql developer to get the DDL and create the temp table?
            • 3. Re: CTAS using dbms_metadata.get_ddl for Partitioned table
              chakra
              For automation, a PL/SQL procude would be used which will create the temporary table.
              • 4. Re: CTAS using dbms_metadata.get_ddl for Partitioned table
                AdamMartin
                You are going to need to print that DDL in chunks if you want to see it with dbms_output. Try this:
                declare
                  l_clob clob;  
                  l_str varchar2(4000);
                  l_length number;
                  l_loops number;
                begin
                  SELECT dbms_metadata.get_ddl('TABLE', 'FACT_TABLE','USER1') into l_clob FROM DUAL;
                  l_length := dbms_lob.getlength(l_clob);
                  dbms_output.put_line('CLOB Length:'||l_length);
                  l_loops := ceil(l_length/4000);
                  dbms_output.put_line('DDL:');
                  for i in 0..l_loops loop
                    l_str:=dbms_lob.substr(l_clob,4000,4000*i +1);
                    dbms_output.put_line(l_str);
                  end loop;
                end;
                You can also just execute the clob without printing it:
                declare
                  l_clob clob;  
                begin
                  SELECT dbms_metadata.get_ddl('TABLE', 'FACT_TABLE','USER1') into l_clob FROM DUAL;
                  l_clob := replace(l_clob,'CREATE TABLE "USER1"."FACT_TABLE"','CREATE TABLE "USER1"."TEMP_FACT_TABLE"');
                  l_clob := replace(l_clob,'"FACT_TABLE_PK"','"TEMP_FACT_TABLE_PK"');  
                  execute immediate l_clob;
                end;
                Notice that I also replaced the primary key name, as you may have to do with your constraints as well.
                • 5. Re: CTAS using dbms_metadata.get_ddl for Partitioned table
                  Sven W.
                  Why not simply SQL?

                  as in
                  SELECT dbms_metadata.get_ddl('TABLE', 'FACT_TABLE','USER1')  FROM DUAL;
                  Maybe use
                  set head off
                  set feedback off
                  and other sqlplus settings to format the output.
                  • 6. Re: CTAS using dbms_metadata.get_ddl for Partitioned table
                    chakra
                    We have 10.2.0.5. EXECUTE IMMEDIATE does not support CLOB as far as I know.
                    • 7. Re: CTAS using dbms_metadata.get_ddl for Partitioned table
                      AdamMartin
                      We have 10.2.0.5. EXECUTE IMMEDIATE does not support CLOB as far as I know.
                      Good point, but I had no way of knowing which database version you were on since it was not listed anywhere in this thread. The first release of Oracle 11g is almost 5 years old, so I listed information that might be useful for a current release.

                      So just convert your DDL to a Varchar2 data type, or if the string is too long for that, then use DBMS_SQL instead of execute immediate.

                      We have shown you how:
                      1. You can use various tools to get the DDL without even using PL/SQL
                      2. How various client tools will give you the DDL without even typing a query
                      3. That the LONG data type is deprecated
                      4. How to print large strings with dbms_output by chunking up long strings into smaller ones
                      5. One way to not only retrieve the DDL but also replace it with the text you want and execute it, all at once
                      6. That you may need to replace more than just the table name for your plan to succeed

                      Think you can take it from here?
                      • 8. Re: CTAS using dbms_metadata.get_ddl for Partitioned table
                        chakra
                        Thanks Adam. The following piece of code is supposed to do that. But, its failing because the dbms_lob.substr(l_clob,4000,4000*v_intIdx +1); is putting newline and therefore dbms_sql.parse
                        is failing.

                        Please advice.

                        create table my_metadata(stmt_no number, ddl_stmt clob);
                        /

                        CREATE OR REPLACE package USER1.genTempTable is
                        procedure getDDL;
                        procedure createTempTab;
                        end;
                        /

                        CREATE OR REPLACE package body USER1.genTempTable is

                        procedure getDDL as
                        /**
                        Description: get a DDL from a partitioned table and change the table name
                        Reference: Q: How Could I Format The Output From Dbms_metadata.Get_ddl Utility? [ID 394143.1]
                        **/

                        l_clob clob := empty_clob();
                        str long;
                        l_dummy varchar2(25);

                        /**
                        dbms_lob does not have any replace function; the following function is a trick to do that
                        **/

                        procedure lob_replace( p_lob in out clob, p_what in varchar2, p_with in varchar2 )as
                        n number;
                        begin
                        n := dbms_lob.instr( p_lob, p_what );
                        if ( nvl(n,0) > 0 )
                        then
                        dbms_lob.copy( p_lob,
                        p_lob,
                        dbms_lob.getlength(p_lob),
                        n+length(p_with),
                        n+length(p_what) );

                        dbms_lob.write( p_lob, length(p_with), n, p_with );
                        if ( length(p_what) > length(p_with) )
                        then
                        dbms_lob.trim( p_lob,
                        dbms_lob.getlength(p_lob)-(length(p_what)-length(p_with)) );
                        end if;
                        end if;
                        end lob_replace;

                        begin
                        DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
                        DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',false);
                        DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
                        DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',false);

                        execute immediate 'truncate table my_metadata';

                        -- Get DDL
                        SELECT dbms_metadata.get_ddl('TABLE', 'FACT','USER1') into l_clob FROM DUAL;

                        -- Insert the DDL into the metadata table
                        insert into my_metadata values(1,l_clob);
                        commit;

                        -- Change the table name into a temporary table
                        select ddl_stmt into l_clob from my_metadata where stmt_no =1 for update;
                        lob_replace(l_clob,'"FACT"','"FACT_T"');
                        insert into my_metadata values(2,l_clob);
                        commit;

                        -- execute immediate l_clob; <---- Cannot be executed in 10.2.0.5; supported in 11gR2

                        DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT');
                        end getDDL;

                        /**
                        Procedure to create temporary table
                        **/

                        procedure createTempTab as
                        v_intCur pls_integer;
                        v_intIdx pls_integer;
                        v_intNumRows pls_integer;
                        v_vcStmt dbms_sql.varchar2a;
                        l_clob clob := empty_clob();

                        l_str varchar2(4000);
                        l_length number;
                        l_loops number;

                        begin

                        select ddl_stmt into l_clob from my_metadata where stmt_no=2;

                        l_length := dbms_lob.getlength(l_clob);
                        l_loops := ceil(l_length/4000);
                        for v_intIdx in 0..l_loops loop
                        l_str:=dbms_lob.substr(l_clob,4000,4000*v_intIdx +1);
                        l_str := replace(l_str,chr(10),'');
                        l_str := replace(l_str,chr(13),'');
                        l_str := replace(l_str,chr(9),'');
                        v_vcStmt(v_intIdx) := l_str;
                        end loop;


                        for v_intIdx in 0..l_loops loop
                        dbms_output.put_line(v_vcStmt(v_intIdx));
                        end loop;

                        v_intCur := dbms_sql.open_cursor;
                        dbms_sql.parse(
                        c => v_intCur,
                        statement => v_vcStmt,
                        lb => 0,
                        --ub => v_intIdx,
                        ub => l_loops,
                        lfflg => true,
                        language_flag => dbms_sql.native);

                        v_intNumRows := dbms_sql.execute(v_intCur);
                        dbms_sql.close_cursor(v_intCur);
                        end createTempTab;

                        end;
                        /
                        • 9. Re: CTAS using dbms_metadata.get_ddl for Partitioned table
                          AdamMartin
                          For DBMS_SQL why not use up to 32767 characters for your strings instead of 4000? You really should not need to use dbms_output at all.
                          • 10. Re: CTAS using dbms_metadata.get_ddl for Partitioned table
                            chakra
                            It works :-) after changing l_str to varchar2(32760).

                            procedure createTempTab as
                            v_intCur pls_integer;
                            v_intIdx pls_integer;
                            v_intNumRows pls_integer;
                            v_vcStmt dbms_sql.varchar2a;
                            l_clob clob := empty_clob();

                            l_str varchar2(32760);
                            l_length number;
                            l_loops number;

                            begin

                            select ddl_stmt into l_clob from my_metadata where stmt_no=2;

                            l_length := dbms_lob.getlength(l_clob);
                            dbms_output.put_line('CLOB Len: '|| l_length);

                            l_loops := ceil(l_length/32760);
                            for v_intIdx in 0..l_loops loop
                            l_str:=dbms_lob.substr(l_clob,32760,32760*v_intIdx +1);
                            v_vcStmt(v_intIdx) := l_str;
                            end loop;



                            v_intCur := dbms_sql.open_cursor;
                            dbms_sql.parse(
                            c => v_intCur,
                            statement => v_vcStmt,
                            lb => v_vcStmt.first,
                            ub => v_vcStmt.last,
                            lfflg => true,
                            language_flag => dbms_sql.native);

                            v_intNumRows := dbms_sql.execute(v_intCur);
                            dbms_sql.close_cursor(v_intCur);
                            end createTempTab;
                            • 11. Re: CTAS using dbms_metadata.get_ddl for Partitioned table
                              rp0428
                              You could also use DBMS_METADATA.GET_SXML (and apply transforms) to get the XML format, modify the table name (which is in a NODE right up front) and then use DBMS_METADATA.PUT to create the temp table.

                              See 'PUT' in the DBMS_METADATA doc in the section 'Subprograms for Submitting XML to the Database'
                              http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_metada.htm#BABBIDBB

                              See also SET_PARSE_ITEM for how to access individual attributes of the XML for getting or putting.

                              Here is an example from another user.
                              http://www.orafaq.com/forum/t/157190/0/