Dynamic access oracle cursor column

Navigation
1

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 fit a return string. But to access the value of this column in the cursor, I do not want to do as r_cursor.col_1.

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

So I was wondering if there is any way to loop the main logs. For each of these records, access the list of columns that are accessed by a loop in another cursor, which are stored as records in another table. And with the second column's column information, get the value of the column of the same name from 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 ?????? I would show the value of this column from cursor_1.

    
asked by anonymous 06.07.2016 / 21:22

1 answer

2

I have a small example here that should clear up 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|
    
06.07.2016 / 22:50
Model Relational database with SKU NullPointerException on a servlet when using DataVoting method