1 2 Previous Next 18 Replies Latest reply: Apr 25, 2013 1:53 PM by user13328581 RSS

    Query OR Stored Proc to get data from Tables from All Schemas in the d/base

    1002358
      Hello Experts, (I appologize if i am not using the right way to ask questions)

      I have a database, and it has around 400 schemas in it. I have designed a query which will fetch the data from three different table's from Schema1.
      But it will be a tedious process of entering the 400 schemas names and pulling the information.

      I would like to know as to what would be the best possible way to;
      1) Look for all the schemas in the database
      2) Look for those specific tables in the schema, which has the data in the tables.
      3) If the tables are not present, than Ignore that schema and proceed further.
      4) Load the data into a table

      Any help, would appreciate it.

      Thanks!


      The query that i am using is as follows;

      -- Query to select all the Schemas from the database
      select username from all_users
      order by username;

      -- Sample Query to see if Tables exsist in the schema
      SELECT DISTINCT OWNER, OBJECT_NAME
      FROM ALL_OBJECTS
      WHERE OBJECT_TYPE = 'TABLE'
      AND OBJECT_NAME IN ('ENROLLMENT', 'PRDCT', 'L_P_L')
      AND OWNER in ('Schema_1', 'Schema_2', Schema_3', Schema_4',Schema_5', Schema_6')
      ORDER BY OWNER;


      --Query to get the data from the tables in a Schema
      select 'Schema_1@DATABASE_NAME' AS SCHEMA,
      (SELECT MAX(LOAD_DT) FROM Schema_1.LOAD_STATUS) AS MAX_LOAD,
      L_PROD_LINE.PROD_LINE,
      COUNT(DISTINCT ENROLLMENT.MEM_NBR) AS MEMBERSHIP
      FROM
      Schema_1.ENROLLMENT,
      Schema_1.PRDCT,
      Schema_1.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;
        • 1. Re: Query OR Stored Proc to get data from Tables from All Schemas in the d/base
          Frank Kulash
          Hi,
          999355 wrote:
          Hello Experts, (I appologize if i am not using the right way to ask questions)
          See the froum FAQ {message:id=9360002}
          I have a database, and it has around 400 schemas in it. I have designed a query which will fetch the data from three different table's from Schema1.
          But it will be a tedious process of entering the 400 schemas names and pulling the information.

          I would like to know as to what would be the best possible way to;
          1) Look for all the schemas in the database
          2) Look for those specific tables in the schema, which has the data in the tables.
          3) If the tables are not present, than Ignore that schema and proceed further.
          4) Load the data into a table

          Any help, would appreciate it.

          Thanks!


          The query that i am using is as follows;

          -- Query to select all the Schemas from the database
          select username from all_users
          order by username;

          -- Sample Query to see if Tables exsist in the schema
          SELECT DISTINCT OWNER, OBJECT_NAME
          FROM ALL_OBJECTS
          WHERE OBJECT_TYPE = 'TABLE'
          AND OBJECT_NAME IN ('ENROLLMENT', 'PRDCT', 'L_P_L')
          AND OWNER in ('Schema_1', 'Schema_2', Schema_3', Schema_4',Schema_5', Schema_6')
          ORDER BY OWNER;
          Do you want to give a list of possible schemas (like the 6 above), or do you want to consider all schemas, however many and whatever they are called?

          You can get the right information for ALL_OBJECTS, but, since you known all the objects of interest are tables, ALL_TABLES will be faster and simpler.
          --Query to get the data from the tables in a Schema
          select 'Schema_1@DATABASE_NAME' AS SCHEMA,
          (SELECT MAX(LOAD_DT) FROM Schema_1.LOAD_STATUS) AS MAX_LOAD,
          L_PROD_LINE.PROD_LINE,
          COUNT(DISTINCT ENROLLMENT.MEM_NBR) AS MEMBERSHIP
          FROM
          Schema_1.ENROLLMENT,
          Schema_1.PRDCT,
          Schema_1.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;
          I take it that the tables in question are ENROLLMENT, PRDCT and L_P_L; they won't have different names in different schemas.

          You can start this way:
          BEGIN
              FOR  c  IN  (
                                SELECT    owner
                        FROM      all_tables
                        WHERE     table_name  IN ( 'ENROLLMENT'
                                                  , 'PRDCT'
                                        , 'L_P_L'
                                        )
                        GROUP BY  owner
                        HAVING    COUNT (*) = 3
                            )
              LOOP
                  ...  -- Now get the results for tables in the c.owner schema
              END LOOP;
          END;
          /
          This will find the schemas that have all 3 of those tables.
          Inside the loop, write another dynamic query. All that will change is the value of c.owner

          Sorry, I'm running out of time now. I hope this helps.
          • 2. Re: Query OR Stored Proc to get data from Tables from All Schemas in the d/base
            1002358
            Do you want to give a list of possible schemas (like the 6 above), or do you want to consider all schemas, however many and whatever they are called?

            I am looking for all the schemas in the database.

            The query does makes sense, but i am quite confused as to how to integrate all the queries into one. OR As per your query, we have figured out all the schemas which hve 3 tables. Now you mentioned that;
            +... -- Now get the results for tables in the c.owner schema+
            END LOOP;
            END;
            +/+

            This will find the schemas that have all 3 of those tables.
            Inside the loop, write another dynamic query. All that will change is the value of c.owner

            Which i am a bit confused.

            Appreciate if you can explain me the process.

            Thanks!
            • 3. Re: Query OR Stored Proc to get data from Tables from All Schemas in the d/base
              sb92075
              4) Load the data into a table
              a) which schema owns the source data?
              b) from which table owned by above is the data source?
              c) who owns target table?
              d) which table owned by target user is destination for the data?
              • 4. Re: Query OR Stored Proc to get data from Tables from All Schemas in the d/base
                1002358
                a) which schema owns the source data?
                Like i mentioned, we are extrating the data from 3 different tables. These 3 tables can be in any schema. (We have a total of around 400 schema objects in the database)
                b) from which table owned by above is the data source?
                The tables that i am looking for are "'ENROLLMENT', 'PRDCT', 'L_P_L'"
                c) who owns target table?
                There's nothing here as Target table..... Can you pls elaborate more on this one?
                d) which table owned by target user is destination for the data?
                Once i have figured out that so many n no. of schemas have these 3 tables, we will extract (pull) the data from those tables, and load it into a table(by creating it), in the current schema

                "CREATE TABLE MEMBERS_BY_PRODUCT AS
                ........................."
                • 5. Re: Query OR Stored Proc to get data from Tables from All Schemas in the d/base
                  sb92075
                  999355 wrote:
                  a) which schema owns the source data?
                  Like i mentioned, we are extrating the data from 3 different tables. These 3 tables can be in any schema. (We have a total of around 400 schema objects in the database)
                  b) from which table owned by above is the data source?
                  The tables that i am looking for are "'ENROLLMENT', 'PRDCT', 'L_P_L'"
                  c) who owns target table?
                  There's nothing here as Target table..... Can you pls elaborate more on this one?
                  d) which table owned by target user is destination for the data?
                  Once i have figured out that so many n no. of schemas have these 3 tables, we will extract (pull) the data from those tables, and load it into a table(by creating it), in the current schema

                  "CREATE TABLE MEMBERS_BY_PRODUCT AS
                  ........................."
                  If 375 schemas have data in table ENROLLMENT, do you load data from 375 tables into 1 table?
                  • 6. Re: Query OR Stored Proc to get data from Tables from All Schemas in the d/base
                    1002358
                    This is the script that i wrote to pull the data from those 3 tables from schema_1

                    --Query to get the data from the tables in a Schema+
                    select 'Schema_1@DATABASE_NAME' AS SCHEMA,
                    +(SELECT MAX(LOAD_DT) FROM Schema_1.LOAD_STATUS) AS MAX_LOAD,+
                    L_PROD_LINE.PROD_LINE,
                    COUNT(DISTINCT ENROLLMENT.MEM_NBR) AS MEMBERSHIP
                    FROM
                    Schema_1.ENROLLMENT,
                    Schema_1.PRDCT,
                    Schema_1.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;

                    The scenario is, if all 3 tables are not found in the schema, then ignore that schema (Even if one table is present and the rest are not, Ignore it).
                    • 7. Re: Query OR Stored Proc to get data from Tables from All Schemas in the d/base
                      sb92075
                      999355 wrote:
                      This is the script that i wrote to pull the data from those 3 tables from schema_1

                      --Query to get the data from the tables in a Schema+
                      select 'Schema_1@DATABASE_NAME' AS SCHEMA,
                      +(SELECT MAX(LOAD_DT) FROM Schema_1.LOAD_STATUS) AS MAX_LOAD,+
                      L_PROD_LINE.PROD_LINE,
                      COUNT(DISTINCT ENROLLMENT.MEM_NBR) AS MEMBERSHIP
                      FROM
                      Schema_1.ENROLLMENT,
                      Schema_1.PRDCT,
                      Schema_1.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;

                      The scenario is, if all 3 tables are not found in the schema, then ignore that schema (Even if one table is present and the rest are not, Ignore it).
                      OK. Good for you!
                      I am glad to see that you know how to solve the problem
                      • 8. Re: Query OR Stored Proc to get data from Tables from All Schemas in the d/base
                        1002358
                        Thanks,
                        But i am looking to consolidate these script using Stored Proc (using cursor's), which can loop through the database and find only those tables that have these 3 tables, and extract the data and load it into the table.

                        Not getting the scenario as to how to proceed.
                        • 9. Re: Query OR Stored Proc to get data from Tables from All Schemas in the d/base
                          sb92075
                          999355 wrote:
                          Thanks,
                          But i am looking to consolidate these script using Stored Proc (using cursor's), which can loop through the database and find only those tables that have these 3 tables, and extract the data and load it into the table.

                          Not getting the scenario as to how to proceed.
                          replace specific schema name with variable & build dynamic SQL from the results.
                          It is only a Simple Matter Of Programming (SMOP)!
                          • 10. Re: Query OR Stored Proc to get data from Tables from All Schemas in the d/base
                            Mahir M. Quluzade
                            Hi,

                            Please check following script I think this shall help you.

                            You can change table name to your table, and change INSERT statement.
                            CREATE TABLE T1 (N NUMBER, V VARCHAR2(10)); 
                            CREATE TABLE T2 (N NUMBER, V VARCHAR2(10)); 
                            CREATE TABLE T3 (N NUMBER, V VARCHAR2(10)); 
                            
                            CREATE TABLE T (N NUMBER, V VARCHAR2(10)); 
                            
                            DELETE FROM T1; 
                            DELETE FROM T2; 
                            DELETE FROM T3; 
                            
                            INSERT INTO T1 VALUES(1,'Mahir'); 
                            INSERT INTO T2 VALUES(2,'Quluzade'); 
                            INSERT INTO T3 VALUES(3,'Meherrem');
                            
                            COMMIT;
                            
                            
                            SELECT *  FROM T1;
                            SELECT *  FROM T2;
                            SELECT *  FROM T3;
                            
                            
                            
                            create or replace 
                            PROCEDURE GETDATA(P_T1 VARCHAR2,P_T2 VARCHAR2,P_T3 VARCHAR2)
                            IS 
                            BEGIN
                              
                               FOR U IN (SELECT USERNAME FROM ALL_USERS) 
                               LOOP
                                  FOR T IN (SELECT COUNT(1) AS CNT FROM ALL_TABLES 
                                             WHERE OWNER=U.USERNAME AND TABLE_NAME IN (P_T1,P_T2,P_T3))
                                   LOOP
                                     IF T.CNT = 3 THEN 
                                       EXECUTE IMMEDIATE  'INSERT INTO T '||          
                                                          'SELECT * FROM '||U.USERNAME||'.'||P_T1||' UNION ALL '||
                                                          'SELECT * FROM '||U.USERNAME||'.'||P_T2||' UNION ALL '||
                                                          'SELECT * FROM '||U.USERNAME||'.'||P_T3;
                                                           
                                     END IF;      
                                   END LOOP;
                               END LOOP; 
                            END; 
                            /
                            
                            DELETE FROM T;
                            
                            COMMIT;
                            
                            BEGIN
                             getdata('T1','T2','T3');
                            END; 
                            /
                            select *  from  t;
                            • 11. Re: Query OR Stored Proc to get data from Tables from All Schemas in the d/base
                              1002358
                              I tried this query, but it's not doing as it should do.

                              --Change table name to your table, and change INSERT statement.

                              CREATE TABLE ENROLLMENT (N NUMBER, V VARCHAR2(10));
                              CREATE TABLE PRODUCT (N NUMBER, V VARCHAR2(10));
                              CREATE TABLE L_PROD_LINE (N NUMBER, V VARCHAR2(10));

                              CREATE TABLE SAMPLE_TEST (N NUMBER, V VARCHAR2(10));

                              select * from sample_test;
                              DELETE FROM ENROLLMENT;
                              DELETE FROM PRODUCT;
                              DELETE FROM L_PROD_LINE;

                              INSERT INTO ENROLLMENT VALUES(1,'Mahir');
                              INSERT INTO PRODUCT VALUES(2,'Quluzade');
                              INSERT INTO L_PROD_LINE VALUES(3,'Meherrem');

                              COMMIT;

                              SELECT * FROM ENROLLMENT;
                              SELECT * FROM PRODUCT;
                              SELECT * FROM L_PROD_LINE;

                              create or replace PROCEDURE GETDATA(P_ENROLLMENT VARCHAR2,P_PRODUCT VARCHAR2,P_L_PROD_LINE VARCHAR2)
                              IS
                              BEGIN

                              FOR U IN (SELECT USERNAME FROM ALL_USERS)
                              LOOP
                              FOR SAMPLE_TEST IN (SELECT COUNT(1) AS CNT FROM ALL_TABLES
                              WHERE OWNER=U.USERNAME AND TABLE_NAME IN (ENROLLMENT,PRODUCT,L_PROD_LINE))
                              LOOP
                              IF T.CNT = 3 THEN
                              EXECUTE IMMEDIATE 'INSERT INTO SAMPLE_TEST '||
                              'SELECT * FROM '||U.USERNAME||'.'||ENROLLMENT||' UNION ALL '||
                              'SELECT * FROM '||U.USERNAME||'.'||PRODUCT||' UNION ALL '||
                              'SELECT * FROM '||U.USERNAME||'.'||L_PROD_LINE;

                              END IF;
                              END LOOP;
                              END LOOP;
                              END;
                              /

                              DELETE FROM SAMPLE_TEST;

                              COMMIT;

                              BEGIN
                              getdata('ENROLLMENT','PRODUCT','L_PROD_LINE');
                              END;
                              /
                              select * from SAMPLE_TEST;
                              • 12. Re: Query OR Stored Proc to get data from Tables from All Schemas in the d/base
                                Frank Kulash
                                Hi,

                                You may want somehing like this:
                                DECLARE
                                    query_txt       VARCHAR2 (1000);
                                    r            SYS_REFCURSOR;
                                    v_max_load       load_status.load_dt%TYPE;     -- or hard-code
                                    v_membership  PLS_INTEGER;
                                    v_prod_line       l_p_l.prod_line%TYPE          -- or hard-code
                                BEGIN
                                    FOR  c  IN  (
                                                      SELECT    owner
                                              FROM      all_tables
                                              WHERE     table_name  IN ( 'ENROLLMENT'
                                                                        , 'PRDCT'
                                                              , 'L_P_L'
                                                              , 'LOAD_STATUS'
                                                              )
                                              GROUP BY  owner
                                              HAVING    COUNT (*) = 4
                                                  )
                                    LOOP
                                        ...  -- Now get the results for tables in the c.owner schema
                                     query_txt := 'SELECT   ( '
                                            ||                 '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
                                     OPEN  r  FOR  query_txt
                                     LOOP
                                         FETCH  r  INTO  v_max_load, v_prod_line, v_membership;
                                         EXIT  WHEN  r%NOTFOUND;
                                
                                         -- The following is one way to display the results
                                         -- You may want to do something else, e.g. INSERT this into a table
                                         dbms_output.put_line (  RPAD (c.owner,     20)     -- Adjust padding sizes as needed
                                                         || RPAD (v_max_load,  12)
                                                     || RPAD (v_prod_line, 15)
                                                     || v_membership
                                                     );
                                     END LOOP;
                                     CLOSE  r;
                                                    
                                    END LOOP;
                                END;
                                / 
                                Of course, without any sample data, and not knowing the results you want, even for 1 schema, I can't test it.

                                As you can see, a lot of the complexity involves fetching individual lines from the dynamic query. Depending on your requirements, you might not need anything quite this complicated.
                                • 13. Re: Query OR Stored Proc to get data from Tables from All Schemas in the d/base
                                  1002358
                                  Hi Frank,

                                  Here's the sample query that i executed and got the result.
                                  Here's what i got when i execute for one Schema;

                                  <SELECT 'TEST_15@DATABASE_NAME' AS SCHEMA,(SELECT Max(LOAD_DT) FROM LOAD_STATUS) AS MAX_LOAD,
                                  L_PROD_LINE.PROD_LINE,
                                  Count(DISTINCT ENROLLMENT.MEM_NBR) AS MEMBERSHIP
                                  FROM
                                  TEST_15.ENROLLMENT,
                                  TEST_15.PRODUCT,
                                  TEST_15.L_PROD_LINE
                                  WHERE
                                  ENROLLMENT.PRODUCT_ID = PRODUCT.PRODUCT_ID AND
                                  PRODUCT.PROD_LINE_ID = L_PROD_LINE.ID
                                  GROUP BY
                                  L_PROD_LINE.PROD_LINE;>

                                  Output: <
                                  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
                                  >
                                  • 14. Re: Query OR Stored Proc to get data from Tables from All Schemas in the d/base
                                    Frank Kulash
                                    Hi,
                                    999355 wrote:
                                    Hi Frank,

                                    Here's the sample query that i executed and got the result.
                                    Here's what i got when i execute for one Schema;

                                    <SELECT 'TEST_15@DATABASE_NAME' AS SCHEMA,(SELECT Max(LOAD_DT) FROM LOAD_STATUS) AS MAX_LOAD,
                                    L_PROD_LINE.PROD_LINE,
                                    Count(DISTINCT ENROLLMENT.MEM_NBR) AS MEMBERSHIP
                                    FROM
                                    TEST_15.ENROLLMENT,
                                    TEST_15.PRODUCT,
                                    TEST_15.L_PROD_LINE
                                    WHERE
                                    ENROLLMENT.PRODUCT_ID = PRODUCT.PRODUCT_ID AND
                                    PRODUCT.PROD_LINE_ID = L_PROD_LINE.ID
                                    GROUP BY
                                    L_PROD_LINE.PROD_LINE;>

                                    Output: <
                                    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
                                    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.
                                    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.
                                    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.)
                                    1 2 Previous Next