CODEWITHSUNDEEP
oracle-databaseplsqlnestedcursor
Gab1991
Gab1991

Reputation: 11

PL/SQL Nested Loops with cursors

I am working with Oracle PL/SQL. There are two cursors, namely c1 and c2.

v_temp VARCHAR(50);
For s1 IN c1
LOOP
    --do something
    FOR s2 IN c2 
    LOOP
        --do something
        v_temp := s1.s2.xxx; --PLS-00302: component 's2' must be declared
    END LOOP;
END LOOP;

s2.xxx gives a column name, and with that column name I hope to assign the value of that column from s1 to v_temp. For example: In the first iteration, s2.xxx is 'column1', I would like to assign s1.column1 to v_temp. In the second iteration, s2.xxx is 'column2', I would then like to assign s1.column2 to v_temp.

I got the error: Error(191,48): PLS-00302: component 's2' must be declared while trying to compile. I know that s1.s2.xxx is not valid, but is there another way of writing it that can make it work?

Upvotes: 1

Views: 6353

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

You need to fetch from a REF CURSOR and dynamically append the column_name to the select statement while opening the cursor. Here I am fetching all the column names from USER_TAB_COLUMNS for table EMPLOYEES and assigning their corresponding values to v_temp. 

SET SERVEROUTPUT ON;
DECLARE
  v_temp VARCHAR(50);
  query1 VARCHAR2(1000);
  c1 SYS_REFCURSOR;
  CURSOR c2
  IS
    SELECT COLUMN_NAME xxx FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'EMPLOYEES';
BEGIN
  FOR s2 IN c2
  LOOP
    --do something
    query1 := 'SELECT ' ||s2.xxx||' FROM EMPLOYEES';
    OPEN c1 FOR query1 ;
    LOOP
      FETCH c1 INTO v_temp;
      DBMS_OUTPUT.PUT_LINE('COLUMN:'||s2.xxx||', VALUE:'|| v_temp);
      EXIT
    WHEN c1%NOTFOUND;
    END LOOP;
    CLOSE c1;
  END LOOP;
END;
/

Since lengths of all the columns of Employees are < 50 , it is working Fine.The conversion happens implicitly for NUMBER and DATE data types.

Here is a sample Output.

COLUMN:EMPLOYEE_ID, VALUE:100
COLUMN:EMPLOYEE_ID, VALUE:101
COLUMN:EMPLOYEE_ID, VALUE:102
COLUMN:FIRST_NAME, VALUE:Eleni
COLUMN:FIRST_NAME, VALUE:Eleni
COLUMN:LAST_NAME, VALUE:Whalen
COLUMN:LAST_NAME, VALUE:Fay
COLUMN:HIRE_DATE, VALUE:17-06-03
COLUMN:HIRE_DATE, VALUE:21-09-05

Upvotes: 1

I3rutt
I3rutt

Reputation: 584

I think you need smth like that:

declare
    v_temp VARCHAR(50);  
    v_temp_1 VARCHAR(50);
    cursor c2(p VARCHAR) is
        SELECT *
        FROM tbl
        WHERE tbl.column = p;
begin
    For s1 IN c1
    LOOP
            --do something   
            v_temp_1 := s1.xxx;
            FOR s2 IN c2(v_temp_1) 
            LOOP
                    --do something
                    v_temp := s1.xxx; 
            END LOOP;
    END LOOP;
end;

Upvotes: 1

Related Questions