Dynamic Cursor Column Name-CodePudding

I'm trying to dynamically reference a cursor column name based off the value of another cursor. Example...

DECLARE
    CURSOR c1 IS
    SELECT
        employee,
        employee_name
    FROM
        employee;

    CURSOR c2 IS
    SELECT
        my_column,
        my_column2
    FROM
        my_table;

BEGIN
    FOR r_c1 IN c1 LOOP
        FOR r_c2 IN c2 LOOP
                INSERT INTO test_table (test_column1,test_column2) values (r_c1.||r_c2.my_column, r_c1.||r_c2.my_column2)
        END LOOP;
    END LOOP;
END;

Is there a way to concatenate my r_c2 value onto the column name for r_c1? If not, is there another way to accomplish what I'm trying to do?

CodePudding user response：

Use a CASE expression and white-list the values:

DECLARE
    CURSOR c1 IS
      SELECT employee, employee_name
      FROM   employee;

    CURSOR c2 IS
      SELECT my_column, my_column2
      FROM   my_table;
BEGIN
  FOR r_c1 IN c1 LOOP
    FOR r_c2 IN c2 LOOP
      INSERT INTO test_table (
        test_column1,
        test_column2
      ) values (
        CASE r_c2.my_column
        WHEN 'EMPLOYEE'      THEN r_c1.employee
        WHEN 'EMPLOYEE_NAME' THEN r_c1.employee_name
        END,
        CASE r_c2.my_column2
        WHEN 'EMPLOYEE'      THEN r_c1.employee
        WHEN 'EMPLOYEE_NAME' THEN r_c1.employee_name
        END
      );
    END LOOP;
  END LOOP;
END;

However, you should get rid of all the cursors and loops and do it in a single SQL statement (that will be much faster and only generate a single undo/redo log entry, compared to the loops which will be slower and will generate log entries for each loop iteration):

INSERT INTO test_table (test_column1, test_column2)
SELECT CASE m.my_column
       WHEN 'EMPLOYEE'      THEN e.employee
       WHEN 'EMPLOYEE_NAME' THEN e.employee_name
       END,
       CASE m.my_column2
       WHEN 'EMPLOYEE'      THEN e.employee
       WHEN 'EMPLOYEE_NAME' THEN e.employee_name
       END
FROM   employee e
       CROSS JOIN my_table m;

fiddle

CodePudding user response：

I dont know, what You are trying to do. Do you want something like this?

create table peba_employee (employee varchar2(255), employee_name varchar2(255));
insert into peba_employee values ('a','Petr');
insert into peba_employee values ('b','David'); 

create table peba_my_table (my_column varchar2(255), my_column2 varchar2(255));
insert into peba_my_table values ('employee','employee_name');
insert into peba_my_table values ('employee_name','employee');

create table peba_test_table (test_column1 varchar2(255), test_column2 varchar2(255));

set serveroutput on
set feedback off

spool d:\somefile.sql

DECLARE
  CURSOR c2 IS  SELECT  my_column , my_column2  FROM  peba_my_table;
BEGIN
  dbms_output.put_line('declare');
  dbms_output.put_line('  CURSOR c1 IS SELECT  employee, employee_name  FROM  peba_employee;');
  dbms_output.put_line('begin');
  dbms_output.put_line('  FOR r_c1 IN c1 LOOP');

   FOR r_c2 IN c2 LOOP

     dbms_output.put_line('     INSERT INTO peba_test_table (test_column1,test_column2) values (r_c1.'||r_c2.my_column ||', r_c1.' ||r_c2.my_column2|| ');');
   END LOOP;
   
   dbms_output.put_line('  END LOOP;');
   dbms_output.put_line('END;');
   dbms_output.put_line('/');

END;
/

spool off

set feedback on

@d:\somefile.sql