1 2 Previous Next 18 Replies Latest reply: Apr 25, 2013 1:53 PM by user13328581 Go to original post RSS
      • 15. Re: Query OR Stored Proc to get data from Tables from All Schemas in the d/base
        1002358
        That looks good to me, but I still don't have any sample data, so I can see if the output is correct or not.
        Here's the Sample Data:
        SCHEMA     MAX_LOAD     PROD_LINE     MEMBERSHIP
        TEST_15@DATABASE_NAME     16-Apr-13     Commercial     1111
        TEST_15@DATABASE_NAME     16-Apr-13     Medicaid     2222
        TEST_15@DATABASE_NAME     16-Apr-13     Medicare     4757
        TEST_16@DATABASE_NAME     31-Aug-11     Medicaid     214801
        TEST_16@DATABASE_NAME     31-Aug-11     Medicare     5234
        TEST_16@DATABASE_NAME     31-Aug-11     Self-insured     151
        TEST_19@DATABASE_NAME     27-Apr-12     Medicaid     56057
        TEST_17@DATABASE_NAME     1-Jun-12     Commercial     154135
        TEST_17@DATABASE_NAME     1-Jun-12     Medicaid     171117
        TEST_17@DATABASE_NAME     1-Jun-12     Medicare     39330
        TEST_18@DATABASE_NAME     7-May-12     Commercial     263
        TEST_18@DATABASE_NAME     7-May-12     Medicaid     134030
        TEST_18@DATABASE_NAME     7-May-12     Medicare     887
        TEST_18@DATABASE_NAME     7-May-12     Self-insured     111807


        What exactly is the problem? Post CREATE TABLE and INSERT statements for all the tables involved (1 schema is enough, and you don't need more than 2 rows per prod_line) and the exact results you want from that sample data.
        I am in the process of extracting the data from 3 tables in the Schema in the database, and put that data into a separate "Table". The sql script 's are fine. But in the database, i have around 400 schema's. I wrote the script which will get all the schema names. But i am in the process of consolidating these (all) scripts in a form or SP with Cursor's, so that it can fit in one.
        I want the scrip to search for all the schemas and pull the schemas which has those tables, and extract the data and put it into another separate table. (That's the problem). I am not getting a method as to how to do this.....Looks too cumbersome......

        Do you want dbms_output.put_line to print the results,or would you like the results to be inserted into another table? (INSERTing weill be simpler and more efficient.)
        I want the results to be inserted into another table........
        • 16. Re: Query OR Stored Proc to get data from Tables from All Schemas in the d/base
          Frank Kulash
          Hi,
          999355 wrote:
          That looks good to me, but I still don't have any sample data, so I can see if the output is correct or not.
          Here's the Sample Data:
          SCHEMA     MAX_LOAD     PROD_LINE     MEMBERSHIP
          TEST_15@DATABASE_NAME     16-Apr-13     Commercial     1111
          ...
          That may be the data, but it's not CREATE TABLE and INSERT statements, so I still can't test anything.
          I want the scrip to search for all the schemas and pull the schemas which has those tables, and extract the data and put it into another separate table. (That's the problem). I am not getting a method as to how to do this.....Looks too cumbersome......

          Do you want dbms_output.put_line to print the results,or would you like the results to be inserted into another table? (INSERTing weill be simpler and more efficient.)
          I want the results to be inserted into another table........
          Okay, to INSERT, change the outer loop that I suggested earlier to:
          ... LOOP
               sql_txt   := 'INSERT INTO results_table (schema_name, max_load, prod_line, membership) '
                      || 'SELECT   ' || c.owner || ', '
                      ||             '( '
                      ||                 'SELECT  MAX (load_dt) '
                      ||                'FROM  ' || c.owner || '.load_status '
                      ||           ') AS max_load, '
                      ||           'l_p_l.prod_line, '
                      ||           'COUNT (DISTINCT enrollment.mem_nbr) AS MEMBERSHIP '
                      || 'FROM ' || c.owner || '.enrollment, '
                      ||              c.owner || '.prdct, '
                      ||          c.owner || '.L_P_L '
                      || 'WHERE     enrollment.product_id = prdct.product_id '
                      || 'AND       product.prod_line_id = l_p_l.id ' 
                      || 'GROUP BY  l_p_l.prod_line';
               dbms_output.put_line (query_txt || ' = query_txt');     -- For debugging
               EXECUTE IMMEDIATE  sql_txt;                    
              END LOOP;
          You no longer need the cursor r, or the local v_ variables.
          • 17. Re: Query OR Stored Proc to get data from Tables from All Schemas in the d/base
            1002358
            Thanks Frank,

            You made my weekend. I made some changes to the script and now it works.

            Appreciate all your help and support.

            You have a good weekend.
            Thanks again!
            • 18. Re: Query OR Stored Proc to get data from Tables from All Schemas in the d/base
              user13328581
              Interesting way to obtain data from all tables and performing a display
              1 2 Previous Next