Cursors from multiple tables plsql

Question

need some help with the following task.

I have 3 tables: active_t, company_t, element_t

active_t has 2 columns: instancename and active. It basically contains a list of schemas and active will either be 1(meaning active) or 0 (not active). I need to query this table to fetch list of active schemas. Then based on each active schema I need to find list of active company codes (from column comp_code) from company_t. Then for each active company I need to insert data from the table element_t into a 4th table (target_t);

Point to note here is that the tables company_t and element_t exist in all schemas.

I tried to first create a cursor c1 like : select instancename from active_t where active=1;

but now not sure how to use this cursor for the next steps. Help will be greatly appreciated.

One thing that I missed to add is that we have created a separate schema "TESTUSER" which has the table active_t. While the tables company_t and element_t are existing in each country specific schema. This TESTUSER has been given select grant on company_t and element_t.

The table active_t looks like this:

INSTANCENAME    ACTIVE
IN              1
SI              0

company_t looks like this (for active country IN)

CODE    DELDATE
1111    null    
2222    null
3333    null    
4444    null

The code will first have to check this table and go for the schema IN( since active=1). For this schema the code will have to then get a list of active companies from the table company_t from that country schema (IN in this case). Company_t and element_t does not have a column which identifies the schema.

Thanks.

d r · Answer

If we take as sample data the folowing CTEs:

WITH
    active_t AS
        (
            Select 'A'  "INSTANCE_NAME", 0 "IS_ACTIVE" From Dual Union All
            Select 'B'  "INSTANCE_NAME", 1 "IS_ACTIVE" From Dual Union All
            Select 'C'  "INSTANCE_NAME", 1 "IS_ACTIVE" From Dual Union All
            Select 'D'  "INSTANCE_NAME", 0 "IS_ACTIVE" From Dual Union All
            Select 'E'  "INSTANCE_NAME", 1 "IS_ACTIVE" From Dual 
        ),
    company_t AS
        (
            Select 'A'  "INSTANCE_NAME", 101 "COMPANY_CODE" From Dual Union All
            Select 'A'  "INSTANCE_NAME", 102 "COMPANY_CODE" From Dual Union All
            Select 'B'  "INSTANCE_NAME", 201 "COMPANY_CODE" From Dual Union All
            Select 'B'  "INSTANCE_NAME", 202 "COMPANY_CODE" From Dual Union All
            Select 'B'  "INSTANCE_NAME", 203 "COMPANY_CODE" From Dual Union All
            Select 'C'  "INSTANCE_NAME", 301 "COMPANY_CODE" From Dual Union All
            Select 'D'  "INSTANCE_NAME", 401 "COMPANY_CODE" From Dual Union All
            Select 'D'  "INSTANCE_NAME", 402 "COMPANY_CODE" From Dual Union All
            Select 'E'  "INSTANCE_NAME", 501 "COMPANY_CODE" From Dual 
        ),
    element_t AS
        (
            Select 101 "COMPANY_CODE", 'EL_11_A' "ELEMENT_ID" From Dual Union All
            Select 101 "COMPANY_CODE", 'EL_12_A' "ELEMENT_ID" From Dual Union All
            Select 101 "COMPANY_CODE", 'EL_13_A' "ELEMENT_ID" From Dual Union All
            Select 202 "COMPANY_CODE", 'EL_21_B' "ELEMENT_ID" From Dual Union All
            Select 203 "COMPANY_CODE", 'EL_22_B' "ELEMENT_ID" From Dual Union All
            Select 301 "COMPANY_CODE", 'EL_31_C' "ELEMENT_ID" From Dual Union All
            Select 401 "COMPANY_CODE", 'EL_41_D' "ELEMENT_ID" From Dual Union All
            Select 401 "COMPANY_CODE", 'EL_42_D' "ELEMENT_ID" From Dual Union All
            Select 401 "COMPANY_CODE", 'EL_44_D' "ELEMENT_ID" From Dual Union All
            Select 401 "COMPANY_CODE", 'EL_45_D' "ELEMENT_ID" From Dual Union All
            Select 401 "COMPANY_CODE", 'EL_46_D' "ELEMENT_ID" From Dual Union All
            Select 501 "COMPANY_CODE", 'EL_51_E' "ELEMENT_ID" From Dual Union All
            Select 501 "COMPANY_CODE", 'EL_52_E' "ELEMENT_ID" From Dual Union All
            Select 505 "COMPANY_CODE", 'EL_53_E' "ELEMENT_ID" From Dual Union All
            Select 501 "COMPANY_CODE", 'EL_54_E' "ELEMENT_ID" From Dual 
        )

... then (if I got it right) the main SQL (your Cursor) could be like this:

SELECT
  a.INSTANCE_NAME "INSTANCE_NAME",
  c.COMPANY_CODE "COMPANY_CODE",
  e.ELEMENT_ID "ELEMENT_ID"
FROM
  active_t a
INNER JOIN
  company_t c ON(c.INSTANCE_NAME = a.INSTANCE_NAME)
INNER JOIN
  element_t e ON(e.COMPANY_CODE = c.COMPANY_CODE)
WHERE
  a.IS_ACTIVE = 1

resulting as

--  INSTANCE_NAME COMPANY_CODE ELEMENT_ID
--  ------------- ------------ ----------
--  B                      202 EL_21_B    
--  B                      203 EL_22_B    
--  C                      301 EL_31_C    
--  E                      501 EL_51_E    
--  E                      501 EL_52_E    
--  E                      501 EL_54_E

All of this is just for generating some sample data to work with. Now, if you want to use the data in a PL/SQL block then you can do it like below:

SET SERVEROUTPUT ON
Declare
    CURSOR cur IS 
        SELECT
          a.INSTANCE_NAME "INSTANCE_NAME",
          c.COMPANY_CODE "COMPANY_CODE",
          e.ELEMENT_ID "ELEMENT_ID"
        FROM
          active_t a
        INNER JOIN
          company_t c ON(c.INSTANCE_NAME = a.INSTANCE_NAME)
        INNER JOIN
          element_t e ON(e.COMPANY_CODE = c.COMPANY_CODE)
        WHERE
          a.IS_ACTIVE = 1;
    curSet    cur%ROWTYPE;
Begin
    OPEN cur;     -- Open cursor
    LOOP    
        FETCH cur INTO curSet;    -- fetch cursor row by row into curSet variable
        EXIT WHEN cur%NOTFOUND;   -- exit Loop when there are no rows left in the cursor
        -- Here you can do whatever you want with fetched cursor row
        -- I will just print the data out so you can see how to refference values from curSet variable
        DBMS_OUTPUT.PUT_LINE('Instance: ' || curSet.INSTANCE_NAME || ' Company Code: ' || curSet.COMPANY_CODE || ' Element: ' || curSet.ELEMENT_ID);
    END LOOP;  
    CLOSE cur;   -- close cursor
End;

DBMS_OUTPUT is:

--  anonymous block completed
--  Instance: B Company Code: 202 Element: EL_21_B
--  Instance: B Company Code: 203 Element: EL_22_B
--  Instance: C Company Code: 301 Element: EL_31_C
--  Instance: E Company Code: 501 Element: EL_51_E
--  Instance: E Company Code: 501 Element: EL_52_E
--  Instance: E Company Code: 501 Element: EL_54_E

Regards...

Cursors from multiple tables plsql

1 Answers