How to write dynamic SQL to select column names based on another table?

Question

I have a use case where I want to write a dynamic sql which would select the column names dynamically based on the other table.

Query 1: select T_SERIES_VALUE from MONTHS_MAPPING where TIME_PERIOD = 'JUN-2023'; -- T66 is the output

Query 2: select aaaa, bbbb, OUTPUT OF QUERY1 as 'Current_Month' from MAIN_TABLE;

As I am unable to handle this multiple columns in a sql function/proc in this use case, I created a sql TYPE of Object/Table and then using bulk collect as below. Proc is compiling. But when I execute the proc, I am getting inconsistent data types error. But I see the datatypes of object type and db values from MAIN_TABLE are one and the same.

I tried below:

Type 1:

create or replace type cds_type_o1 as Object 
(
aaaa    VARCHAR2(26 BYTE)
,bbbb   VARCHAR2(256 BYTE)
,Tx NUMBER(38,10)
);

Type 2: `` create or replace type cds_type_t1 is table of cds_type_o1 ;

``

Proc:

create or replace procedure test_bulk_collect_table (reporting_period IN VARCHAR2)
 is
   v_cm_sql varchar2(256);
   v_cm varchar2(256);

    v_output_execute varchar2(256);
    v_output cds_type_t1;
  begin
     v_cm_sql:='select T_SERIES_VALUE from MONTHS_MAPPING where TIME_PERIOD = '||reporting_period; -- T61
      execute immediate v_cm_sql into v_cm; --T66

     v_output_execute:= 'select aaaa, bbbb, '|| v_cm ||' from MAIN_TABLE';
      dbms_output.put_line(v_output_execute); --select aaaa, bbbb, T66 from MAIN_TABLE
      *execute immediate v_output_execute bulk collect into v_output;*  --Here I am getting the error while executing it
 dbms_output.put(v_output(0).scenario);
     for i in v_output.first..v_output.last loop
     dbms_output.put(v_output(i).aaaa);
       end loop;
 end test_bulk_collect_table;

Now when I execute this proc as SET SERVEROUTPUT ON; EXEC test_bulk_collect_table('JUN-2023');

I am getting the below error: Error report - ORA-00932: inconsistent datatypes: expected UDT got CHAR ORA-06512: at "SCHEMA.TEST_BULK_COLLECT_TABLE", line 15 (execute immediate v_output_execute bulk collect into v_output) ORA-06512: at line 1 00932. 00000 - "inconsistent datatypes: expected %s got %s" *Cause:
*Action:

I am confused with this error, as it is expecting %s and it is also getting %s, any guidance on this would be really appreciated.

Answer 1

To fetch (or bulk collect) into a list of objects, you need select objects not columns. Hence you just need to add an object constructor in your query, eg

SQL> create or replace
  2  type myobj as object ( x int, y int );
  3  /

Type created.

SQL>
SQL> create or replace
  2  type mylist is table of myobj;
  3  /

Type created.

SQL>
SQL> declare
  2    v mylist;
  3  begin
  4    select empno, sal
  5    bulk collect into v
  6    from emp;
  7  end;
  8  /
  from emp;
  *
ERROR at line 6:
ORA-06550: line 6, column 3:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored


SQL>
SQL> declare
  2    v mylist;
  3  begin
  4    select myobj(empno, sal)
  5    bulk collect into v
  6    from emp;
  7  end;
  8  /

PL/SQL procedure successfully completed.