Dynamic access column oracle cursor

Question

I have a situation where I need to access the attributes of a cursor differently. I have a cursor that returns me a list of records, with columns like col_1, col_nova, col_teste (illustrative names).

I loop this cursor to adjust a return string. But to access the value of this column on the cursor, I don’t want to do it as r_cursor.col_1.

I have a table that stores the value of columns that I need to work with. Following the example of the columns cited before, in this table I have recorded the col_1 and col_nova records. Now in the cursor loop, I cannot manually set that I will use the values of a column, and it may not be used.

So I was wondering if there’s any way to loop the main records. For each of these records, access the list of columns that are accessed by a loop on another cursor, which are stored as records in another table. And with the column information of the second cursor, take the value of the column of the same name of the first cursor?

A logical example of what it would be

cursor_1 = select * na tabela principal;
cursor_2 = lista de colunas utilizadas. Que vai retornar a lista em um atributo chamado COLUNA;

    loop cursor_1
      loop cursor_2
        v_armazenar := v_armazenar || ' COLUNA '||cursor_2.COLUNA || ' VALOR: '||??????;
      end cursor_2
    end cursor_1

Where in ?????? would I show the value of this column coming from the cursor_1.

Answer 1

I made a small example here that should clear the ideas ae.

DECLARE VSQL VARCHAR2(4000);
VARMAZNAR VARCHAR2(4000);
VTEMP VARCHAR2(4000);
BEGIN

--SELECT PRINCIPAL
FOR PRINCIPAL IN (SELECT * FROM PCCLIENT WHERE CODCLI < 10)
LOOP



--TABELA COM AS COLUNAS
FOR COLUNAS IN(SELECT 'CODCLI' COLUNA FROM DUAL UNION SELECT 'CLIENTE' COLUNA FROM DUAL)
LOOP

--STRING SQL PRA EXECUTAR
VSQL := 'SELECT  '||COLUNAS.COLUNA|| ' FROM PCCLIENT WHERE CODCLI = '||PRINCIPAL.CODCLI;
EXECUTE IMMEDIATE VSQL INTO VTEMP;
--GUARDA O RESULTADO EM ALGUM LUGAR
VARMAZNAR:= VARMAZNAR|| 'COLUNA: ' ||COLUNAS.COLUNA || ' VALOR: ' ||VTEMP || '| ';


END LOOP;

END LOOP;
--EXIBE O RESULTADO
dbms_output.put_line(VARMAZNAR);



END;

The result:

COLUNA: CLIENTE VALOR: CONSUMIDOR FINAL| COLUNA: CODCLI VALOR: 1|