This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Apr 25, 2013 11:53 AM 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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    Interesting way to obtain data from all tables and performing a display
1 2 Previous Next

Legend

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