Assigning multiple fields in a loop using execute immediate

Question

I am using PLPDF's libraries to create spreadsheets for various files - I am trying to get a procedure written to take the values of each field and one-by-one insert them into the spreadsheet. This operation can include many different tables going into many different spreadsheets, so just doing an export is not going to cut it.

This example is has two cursors created from tables - the USER_TAB_COLUMNS to select the column names, and the actual view query to pull the data. I have one loop to go through the data record by record, and second to go field-by-field within the record.

Before doing the actual writing to the spreadsheet blob, I'm simply running the dbms_output.putline to make sure that I am getting the data needed.

declare

    q_str varchar2(100);
    this_vc varchar2(3000);

    cursor diet_table is 
        select * from vcars_diet where nhp_id = 8573;

    cursor diet_stru is
        select 'begin :1 := i.' || column_name || '; end;' line_o_code from user_tab_columns where table_name = 'VCARS_DIET';

begin
    for i in diet_table loop

        DBMS_OUTPUT.PUT_LINE ('--------------------------------------------------------'); 
        for h in diet_stru loop

            DBMS_OUTPUT.PUT_LINE ('Varchar Value for i: "' || h.line_o_code || '"'); 
            EXECUTE IMMEDIATE (h.line_o_code) USING out this_vc;
            DBMS_OUTPUT.PUT_LINE ('Varchar Value for i.' || h.line_o_code || ' is: '||this_vc); 

        end loop;
    end loop;
end;

The fields in the diet table are:

NHP_ID
DATE_TIME
DIET_NO
FORM_NAME
DATA_TYPE

The results are:

ORA-06550: line 1, column 13: PLS-00201: identifier 'I.NHP_ID' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored ORA-06512: at line 33 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

Kaushik Nayak Kaushik Nayak · Accepted Answer · 2019-04-05T17:05:33

I have copied this PL/SQL code from Connor McDonald's solution from this AskTom article. It uses type dynamic SQL to parse any SQL query and converts the column names as well as values into a collection. I've used a sample query on employees table in HR schema. Replace it with your query.

set serverout on size 999999
set verify off
declare
    p_query varchar2(32767) := 
              q'{select * from employees 
                          where rownum = 1
                 }';-- Here you put your query  
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(4000);
    l_status        integer;
    l_descTbl       dbms_sql.desc_tab;
    l_colCnt        number;
    n number := 0;
  procedure p(msg varchar2) is
    l varchar2(4000) := msg;
  begin
    while length(l) > 0 loop
      dbms_output.put_line(substr(l,1,80));
      l := substr(l,81);
    end loop;
  end;
begin
    execute immediate
    'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';

    dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
    dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );

    for i in 1 .. l_colCnt loop
        dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000);
    end loop;

    l_status := dbms_sql.execute(l_theCursor);

    while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i, l_columnValue );
            p( 'Value for '|| l_descTbl(i).col_name
              || ' is: ' || 
              l_columnValue );
        end loop;
        dbms_output.put_line( '-----------------' );
        n := n + 1;
    end loop;
    if n = 0 then
      dbms_output.put_line( chr(10)||'No data found '||chr(10) );
    end if;
end;
/

This gives the output:

Value for EMPLOYEE_ID is: 198
Value for FIRST_NAME is: Donald
Value for LAST_NAME is: OConnell
Value for EMAIL is: DOCONNEL
Value for PHONE_NUMBER is: 650.507.9833
Value for HIRE_DATE is: 21-jun-2007 00:00:00
Value for JOB_ID is: SH_CLERK
Value for SALARY is: 2600
Value for COMMISSION_PCT is: 
Value for MANAGER_ID is: 124
Value for DEPARTMENT_ID is: 50
-----------------


PL/SQL procedure successfully completed.

Assigning multiple fields in a loop using execute immediate

1 Answers