1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

how to pass variables for table names into a cursor

Discussion in 'SQL PL/SQL' started by jay_pink_elephant, Dec 29, 2008.

  1. jay_pink_elephant

    jay_pink_elephant Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    hi all... I am trying to pass a table name from one cursor to another but the code doesn't compile. I cannot hardcode this as I'll have several tables in cursor A1 (this is just an example). Cursor B1 gets distinct dates for each table.
    Code (Text):

    SET SERVEROUT ON SIZE 200000 TRIMS ON
    DECLARE

    DT1 NUMBER := 0;

    CURSOR A1 IS SELECT DISTINCT TABLE_NAME FROM ALL_TAB_COLS
    WHERE OWNER = 'EDW_STAR_SIT'
    AND TABLE_NAME = 'CU_WS_DOCSTATUS_FACT_SNAP'
    AND COLUMN_NAME = 'DT_SK';

    CURSOR B1 (A VARCHAR2) IS SELECT DISTINCT DT_SK FROM A ORDER BY DT_SK;

    BEGIN
    FOR G1 IN A1
    LOOP
    DBMS_OUTPUT.PUT_LINE('TABLE_NAME..........'||G1.TA BLE_NAME);

    FOR H1 IN B1(G1.TABLE_NAME)
    LOOP
    DBMS_OUTPUT.PUT_LINE('DATE..........'||H1.DT_SK);

    END LOOP;
    END LOOP;
    END;
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    If you are trying to pass table name as parameter (cursor A1)and to use the same table name as the source table for Cursor B1 , Execute immediate is the better option for you.

    You script wont compile because , there wont be either the table A in your schema or column DT_SK in the table A as mentioned in Cursor B1.

    Code (Text):

    CURSOR B1 (A VARCHAR2) IS SELECT DISTINCT DT_SK FROM A ORDER BY DT_SK;
     
    Cursor B wont treat A as the source table ; it just treats it as variable. If you want to treat variable as table , Execute immediate is the good option.

    Find an example here
    If output is multiple records , store them in record type and display.


    Meanwhile Parameterized Cursor treats its as a variable instead as in the following example.

    Code (Text):

    SQL> SET SERVEROUT ON SIZE 200000 TRIMS ON
    SQL> DECLARE
      2      CURSOR AA IS SELECT DISTINCT TABLE_NAME  FROM USER_TABLES WHERE TABLE_NAME LIKE 'E%';
      3
      4      CURSOR BB (tname VARCHAR2) IS SELECT COLUMN_NAME  FROM USER_TAB_COLUMNS where TABLE_NAME=tname;
      5
      6  BEGIN
      7  FOR G1 IN AA
      8  LOOP
      9  DBMS_OUTPUT.PUT_LINE('TABLE_NAME..'||G1.TABLE_NAME);
     10
     11  FOR H1 IN BB(G1.TABLE_NAME)
     12  LOOP
     13  DBMS_OUTPUT.PUT_LINE('Column .................'||H1.COLUMN_NAME);
     14
     15  END LOOP;
     16  END LOOP;
     17  END;
     18  /
    TABLE_NAME..EMP_XX
    Column .................EMPNO
    Column .................ENAME
    Column .................JOB
    Column .................MGR
    Column .................SAL
    TABLE_NAME..EMP_20
    Column .................EMPNO
    Column .................ENAME
    Column .................JOB
    Column .................MGR
    Column .................SAL
    TABLE_NAME..EMP
    Column .................EMPNO
    Column .................ENAME
    Column .................JOB
    Column .................MGR
    Column .................HIREDATE
    Column .................SAL
    Column .................COMM
    Column .................DEPTNO
    TABLE_NAME..EMP2
    Column .................EMPNO
    Column .................ENAME
    Column .................JOB
    Column .................MGR
    Column .................HIREDATE
    Column .................SAL
    Column .................COMM
    Column .................DEPTNO
    TABLE_NAME..EMP_MGR
    Column .................EMPNO
    Column .................ENAME
    Column .................JOB
    Column .................MGR
    Column .................HIREDATE
    Column .................SAL
    Column .................COMM
    Column .................DEPTNO
    TABLE_NAME..EMP_EXC_DEMO
    Column .................EMPNO
    Column .................DEPTNO
    Column .................SAL
    TABLE_NAME..EMP_10
    Column .................EMPNO
    Column .................ENAME
    Column .................JOB
    Column .................MGR
    Column .................SAL
    TABLE_NAME..EMP_30
    Column .................EMPNO
    Column .................ENAME
    Column .................JOB
    Column .................MGR
    Column .................SAL

    PL/SQL procedure successfully completed.

    SQL>
     
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Anyway , there is another method to achieve the same without EXECUTE IMMEDIATE . It is by using Ref cursor and OPEN cusor loop as follows.

    Code (Text):

    SQL> DECLARE
      2      CURSOR AA IS SELECT DISTINCT TABLE_NAME  FROM USER_TABLES WHERE TABLE_NAME LIKE 'E%';
      3      TYPE BBType IS REF CURSOR;
      4      BB BBType;
      5      emp_no NUMBER ;
      6  BEGIN
      7  FOR G1 IN AA
      8  LOOP
      9      DBMS_OUTPUT.PUT_LINE('TABLE_NAME..'||G1.TABLE_NAME);
     10          OPEN BB FOR 'SELECT EMPNO FROM '||G1.TABLE_NAME;
     11              LOOP
     12               FETCH BB into emp_no ;
     13               EXIT WHEN BB%NOTFOUND;
     14               DBMS_OUTPUT.PUT_LINE('empno .................'||emp_no);
     15              END LOOP;
     16          CLOSE  BB;
     17  END LOOP;
     18  END;
     19  /
    TABLE_NAME..EMP_XX
    TABLE_NAME..EMP_20
    empno .................7369
    empno .................7566
    empno .................7788
    empno .................7876
    empno .................7902
    TABLE_NAME..EMP
    empno .................7369
    empno .................7499
    empno .................7521
    empno .................7566
    empno .................7654
    empno .................7698
    empno .................7782
    empno .................7788
    empno .................7839
    empno .................7844
    empno .................7876
    empno .................7900
    empno .................7902
    empno .................7934
    TABLE_NAME..EMP2
    empno .................7369
    empno .................7499
    empno .................7521
    empno .................7566
    empno .................7654
    empno .................7698
    empno .................7782
    empno .................7788
    empno .................7839
    empno .................7844
    empno .................7876
    empno .................7900
    empno .................7902
    empno .................7934
    TABLE_NAME..EMP_MGR
    empno .................7369
    empno .................7499
    empno .................7521
    empno .................7566
    empno .................7654
    empno .................7698
    empno .................7782
    empno .................7788
    empno .................7839
    empno .................7844
    empno .................7876
    empno .................7900
    empno .................7902
    empno .................7934
    empno .................2000
    TABLE_NAME..EMP_EXC_DEMO
    empno .................1000
    empno .................2000
    empno .................3000
    TABLE_NAME..EMP_10
    empno .................7782
    empno .................7839
    empno .................7934
    TABLE_NAME..EMP_30
    empno .................7499
    empno .................7521
    empno .................7654
    empno .................7698
    empno .................7844
    empno .................7900

    PL/SQL procedure successfully completed.

    SQL>
     
    Try to change your script accordingly .