Work with DBMS_SQL package

run_plsql.sql

set serveroutput on;
DECLARE    
    v_cursor_id number;
    v_last_scale_division_idx number;
    v_column_int  number;
    v_column_str  varchar2(4000);
    v_fetched_cnt number;
BEGIN
    select max(column_id)
      into v_last_scale_division_idx 
    from user_tab_columns
    where table_name = :p_table_name and
          column_name like 'TOTAL%' ;
    
    v_cursor_id := dbms_sql.open_cursor;
    DBMS_SQL.PARSE(v_cursor_id, '
select * 
  from '||:p_table_name||'
 where lid = :p_lid 
', DBMS_SQL.NATIVE);
    DBMS_SQL.BIND_VARIABLE(v_cursor_id, 'p_lid', :p_lid);

    DBMS_SQL.DEFINE_COLUMN (v_cursor_id, 1, v_column_int);
    DBMS_SQL.DEFINE_COLUMN (v_cursor_id, 2, v_column_str, 4000 );

--    v_column_int := 3;
--    WHILE v_column_int <= v_last_scale_division_idx LOOP
--      DBMS_SQL.DEFINE_COLUMN (v_cursor_id, v_column_int, v_column_int);
--      v_column_int := v_column_int + 1;
--    END LOOP;

    for i in 3..v_last_scale_division_idx loop
        DBMS_SQL.DEFINE_COLUMN (v_cursor_id, v_column_int, v_column_int);    
    end loop;

    DBMS_SQL.DEFINE_COLUMN (v_cursor_id, v_column_int, v_column_int);
    v_column_int := v_column_int + 1;
    DBMS_SQL.DEFINE_COLUMN (v_cursor_id, v_column_int, v_column_str, 4000);
    
    v_fetched_cnt := DBMS_SQL.EXECUTE_AND_FETCH(v_cursor_id);

    for i in 1..v_last_scale_division_idx loop
      
      if i = 2 then 
                  DBMS_SQL.COLUMN_VALUE (v_cursor_id, i, v_column_str);
                  dbms_output.put_line(v_column_str);    
               else 
                DBMS_SQL.COLUMN_VALUE (v_cursor_id, i, v_column_int);
                dbms_output.put_line(v_column_int);    
      end if;
    end loop;
    DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
EXCEPTION
  WHEN OTHERS THEN
      DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
      raise;
END;