10 Replies Latest reply: Jan 14, 2014 4:33 PM by Sudhakar_B RSS

    How to Populate a table with DBMS_OUTPUT.put_line

    Xev Bellringer

      Hey Guys, it's Xev.

       

      Please only pleasant people reply to this.

       

      I have a PL/SQL Program that searches for strings and then at the end of it it prints out to DBMS_OUTPUT.put_line.

       

      I have the owner, the table_name, the column name and the count, then it goes to DBMS_OUTPUT.put_line

       

      What i want to do, is take the results of DBMS_OUTPUT.put_line and insert it into a table.

       

      Here is the script I am talking about, as you can see it's simple, yet for me it works.  I want to take the results of this and insert it into a table. How can i do  that??

       

       

      set serveroutput on size unlimited

      execute DBMS_OUTPUT.ENABLE (buffer_size => NULL);

       

       

      ------------------------------------------------------------------------------------------------------------------------

      DECLARE

         FND_GOVIDS       INTEGER;

      ------------------------------------------------------------------------------------------------------------------------  

         BEGIN

      FOR t  IN (SELECT owner, table_name, column_name

                  FROM all_tab_columns

                  WHERE owner = upper('&SCHEMA_NAME'))

                           

      ------------------------------------------------------------------------------------------------------------------------                            

      LOOP

        BEGIN

          EXECUTE IMMEDIATE 'with a as ( select  case when REGEXP_LIKE(' || t.column_name ||

         

         

      --This searches for 8 Alpha Digits

      ',''^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{8}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?$'')

       

       

       

       

      then ''Match Found''

      else ''No Match Found'' end as output from ' || t.owner || '.' || t.table_name || ')

      select count(*) from a where a.output=''Match Found'' '

       

       

            INTO FND_GOVIDS ;

           

      IF FND_GOVIDS > 0         THEN

      DBMS_OUTPUT.put_line (

      t.owner

      || '.'

      || t.table_name

      || ' '

      || t.column_name

      || ' '

      || FND_GOVIDS);

        END IF;

        EXCEPTION

          WHEN OTHERS

            THEN

            DBMS_OUTPUT.put_line (

            'Generic Error '

            || t.column_name

            || ' from '

            || t.owner

            || '.'

            || t.table_name);      

            END;

         END LOOP;

      END;

      /

        • 1. Re: How to Populate a table with DBMS_OUTPUT.put_line
          Brian Bontrager

          Instead of concatenating your string directly in the call to put_line(), build it in a variable.  Perform an INSERT to the table you want to save it to using the variable.  This also gives you the option of re-using that variable and still output the DBMS_OUTPUT.

          • 2. Re: How to Populate a table with DBMS_OUTPUT.put_line
            Sudhakar_B

            If I understand you correctly...

            In addition to (or in place of) DBMS_OUTPUT... can you not do the following?

             

            INSERT INTO <your target table name> values(

            t.owner

            || '.'

            || t.table_name

            || ' '

            || t.column_name

            || ' '

            || FND_GOVIDS

            );

             

            Commit after the loop. Your target table must be created upfront.

            • 3. Re: How to Populate a table with DBMS_OUTPUT.put_line
              Xev Bellringer

              Sudhakar_B Wrote:

               

              If I understand you correctly...

              In addition to (or in place of) DBMS_OUTPUT... can you not do the following?

               

              INSERT INTO <your target table name> values(

              t.owner

              || '.'

              || t.table_name

              || ' '

              || t.column_name

              || ' '

              || FND_GOVIDS

              );

               

              Ok, this is wonderful. I will try that right now!

               

              Thanks Sudhakar_B

              • 4. Re: How to Populate a table with DBMS_OUTPUT.put_line
                Xev Bellringer

                Guys, am I missing something that is obvious? I created a table that hold each field, this is the code now....but it tells me "not enough values" when I try to run it?

                 

                 

                set serveroutput on size unlimited

                execute DBMS_OUTPUT.ENABLE (buffer_size => NULL);

                 

                 

                ------------------------------------------------------------------------------------------------------------------------

                DECLARE

                   FND_LOOK       INTEGER;

                ------------------------------------------------------------------------------------------------------------------------ 

                   BEGIN

                FOR t  IN (SELECT owner, table_name, column_name

                            FROM all_tab_columns

                            WHERE owner = upper('&SCHEMA_NAME'))

                                    

                ------------------------------------------------------------------------------------------------------------------------                           

                LOOP

                  BEGIN

                    EXECUTE IMMEDIATE 'with a as ( select  case when REGEXP_LIKE(' || t.column_name ||

                  

                  

                --This searches for 6 Alpha Digits

                ',''^([[:alpha:]]|[[:alpha:]]{3})[0-9]{6}$'')

                 

                 

                 

                 

                then ''Match Found''

                else ''No Match Found'' end as output from ' || t.owner || '.' || t.table_name || ')

                select count(*) from a where a.output=''Match Found'' '

                 

                 

                      INTO FND_LOOK ;

                    

                 

                 

                /*Table insert for resulting */

                    

                IF FND_LOOK > 0 THEN

                 

                 

                INSERT INTO DYNAMIC_COUNTS (T_OWNER, T_TABLE_NAME, T_COLUMN_NAME, FND_LOOK) VALUES (

                t.owner

                || '.'

                || t.table_name

                || ' '

                || t.column_name

                || ' '

                || FND_LOOK

                );

                 

                 

                  END IF;

                 

                 

                /* dbms_output */

                 

                 

                IF FND_LOOK > 0 THEN

                DBMS_OUTPUT.put_line (

                t.owner

                || '.'

                || t.table_name

                || ' '

                || t.column_name

                || ' '

                || FND_LOOK);

                 

                 

                  END IF;

                 

                ------------------------------------------------------------------------------------------------------------------------

                /* Exception Handling */

                 

                 

                 

                  EXCEPTION

                    WHEN OTHERS

                      THEN

                      DBMS_OUTPUT.put_line (

                      'Generic Error '

                      || t.column_name

                      || ' from '

                      || t.owner

                      || '.'

                      || t.table_name);     

                      END;

                   END LOOP;

                END;

                /

                • 5. Re: How to Populate a table with DBMS_OUTPUT.put_line
                  TPD-Opitz

                  Xev Bellringer wrote:

                   

                  Guys, am I missing something that is obvious? [...]

                  INSERT INTO DYNAMIC_COUNTS (T_OWNER, T_TABLE_NAME, T_COLUMN_NAME, FND_GOVIDS) VALUES (

                  t.owner

                  || '.'

                  || t.table_name

                  || ' '

                  || t.column_name

                  || ' '

                  || FND_GOVIDS

                  );

                  Yes: you specify 4 attributes in your insert statement but give only one String in the values part.

                   

                  bye

                  TPD

                  • 6. Re: How to Populate a table with DBMS_OUTPUT.put_line
                    Hoek

                    Xev,

                     

                    Can you you please stop with turning errors into success?

                    Seriously, QUIT USING A WHEN OTHERS.

                    • 7. Re: How to Populate a table with DBMS_OUTPUT.put_line
                      Sudhakar_B

                      Please use..

                       

                      This

                      INSERT INTO DYNAMIC_COUNTS (T_OWNER, T_TABLE_NAME, T_COLUMN_NAME, FND_GOVIDS)

                      VALUES (t.owner, t.table_name, t.column_name, FND_GOVIDS);

                       

                      Instead of

                      INSERT INTO DYNAMIC_COUNTS (T_OWNER, T_TABLE_NAME, T_COLUMN_NAME, FND_GOVIDS) VALUES (

                      t.owner

                      || '.'

                      || t.table_name

                      || ' '

                      || t.column_name

                      || ' '

                      || FND_GOVIDS

                      );

                       

                      AND

                       

                      Please remove EXCEPTION WHEN OTHER block completely.

                      • 8. Re: How to Populate a table with DBMS_OUTPUT.put_line
                        Xev Bellringer

                        Nope, the table is empty....

                         

                        But it ran without error??!?! Wait a minute, I think i have to commit right? Since it's not straight sql, ok, that's what I am going to try..

                         

                        set serveroutput on size unlimited

                        execute DBMS_OUTPUT.ENABLE (buffer_size => NULL);

                         

                         

                        ------------------------------------------------------------------------------------------------------------------------

                        DECLARE

                           FND_GOVIDS       INTEGER;

                        ------------------------------------------------------------------------------------------------------------------------ 

                           BEGIN

                        FOR t  IN (SELECT owner, table_name, column_name

                                    FROM all_tab_columns

                                    WHERE owner = upper('&SCHEMA_NAME'))

                                            

                        ------------------------------------------------------------------------------------------------------------------------                           

                        LOOP

                          BEGIN

                            EXECUTE IMMEDIATE 'with a as ( select  case when REGEXP_LIKE(' || t.column_name ||

                          

                          

                        --This searches for 6 Alpha Digits

                        ',''^([[:alpha:]]|[[:alpha:]]{3})[0-9]{6}$'')

                         

                         

                         

                         

                        then ''Match Found''

                        else ''No Match Found'' end as output from ' || t.owner || '.' || t.table_name || ')

                        select count(*) from a where a.output=''Match Found'' '

                         

                         

                              INTO FND_GOVIDS ;

                            

                         

                         

                        /*Table insert for resulting */

                            

                        IF FND_GOVIDS > 0 THEN

                         

                         

                        INSERT INTO "SMEG"."DYNAMIC_COUNTS" (T_OWNER, T_TABLE_NAME, T_COLUMN_NAME, FND_GOVIDS) VALUES

                         

                         

                        ('t.owner','t.table_name','t.column_name','FND_GOVIDS');

                         

                         

                         

                         

                          END IF;

                         

                         

                        /* dbms_output */

                         

                         

                        IF FND_GOVIDS > 0 THEN

                         

                         

                        DBMS_OUTPUT.put_line (t.owner || '.' || t.table_name || ' ' || t.column_name || ' ' || FND_GOVIDS);

                         

                         

                         

                         

                          END IF;

                         

                        ------------------------------------------------------------------------------------------------------------------------

                        /* Exception Handeling */

                         

                         

                         

                          EXCEPTION

                            WHEN OTHERS

                              THEN

                              DBMS_OUTPUT.put_line (

                              'Generic Error '

                              || t.column_name

                              || ' from '

                              || t.owner

                              || '.'

                              || t.table_name);     

                              END;

                           END LOOP;

                        END;

                        /

                        • 9. Re: How to Populate a table with DBMS_OUTPUT.put_line
                          Xev Bellringer

                          I got it to work with the count part of it!!!

                           

                          T_OWNER                        T_TABLE_NAME                   T_COLUMN_NAME                  FND_LOOK

                          ------------------------------ ------------------------------ ------------------------------ ----------

                                                                                                                                                              2

                                                                                                                                                              2

                          • 10. Re: How to Populate a table with DBMS_OUTPUT.put_line
                            Sudhakar_B

                            There should not be any quotes in the following...

                             

                            INSERT INTO "SMEG"."DYNAMIC_COUNTS" (T_OWNER, T_TABLE_NAME, T_COLUMN_NAME, FND_GOVIDS) VALUES

                            ('t.owner','t.table_name','t.column_name','FND_GOVIDS');

                             

                            It should be like...

                             

                            INSERT INTO "SMEG"."DYNAMIC_COUNTS" (T_OWNER, T_TABLE_NAME, T_COLUMN_NAME, FND_GOVIDS) VALUES

                            (t.owner,t.table_name,t.column_name,FND_GOVIDS);

                             

                            Since this insert statement is within the implicit cursor loop t.ower has the value of the owner, putting them in quotes is not helping.

                            Even the double quotes around the SMEG a DYNAMIC_COUNTS are not needed, but that is not causing any issue.