This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Apr 25, 2013 11:53 AM by user13328581 RSS

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

1002358 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    MahirM.Quluzade Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

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