A
A
Andrey1_M2022-03-07 21:08:55
Oracle
Andrey1_M, 2022-03-07 21:08:55

How to display data from cursor?

actually in the comments indicated that the line is not executedDBMS_OUTPUT.PUT_LINE(cur.'||cols (1).col_name||');

declare
   cur1 PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
   cols DBMS_SQL.DESC_TAB;
   ncols PLS_INTEGER;
    l_output    VARCHAR2(100):= 'ok ';
begin
    -- Разбор запроса
   DBMS_SQL.PARSE (cur1, 'select 1 field_11, ''river'' field_22 from dual 
                          union all
                          select 1 field_11, ''sharf'' field_22 from dual', DBMS_SQL.NATIVE);
   -- Получение информации о столбцах
   DBMS_SQL.DESCRIBE_COLUMNS (cur1, ncols, cols);  
   FOR cur IN (select 1 field_11, 'river' field_22 from dual 
               union all
               select 1 field_11, 'sharf' field_22 from dual)
   LOOP
         --работает если прямо указывать название столбца
         --EXECUTE IMMEDIATE 'BEGIN :a := :a || ''OK''; DBMS_OUTPUT.PUT_LINE('||cur.field_11||'); END;' USING IN OUT l_output;

         --не работает если пытаться вытащить имя столбца (а мне надо именно так), что я делаю не так? 
         EXECUTE IMMEDIATE 'BEGIN :a := :a || ''OK''; DBMS_OUTPUT.PUT_LINE(cur.'||cols (1).col_name||'); END;' USING IN OUT l_output; --INTO l_output;
   END LOOP; 
   DBMS_SQL.CLOSE_CURSOR (cur1);
end;


writes an error
Error report -
ORA-06550: line 1, column 46:
PLS-00201: identifier 'CUR.FIELD_11' must be declared

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question