Dynamic column name to record type variable

Question

 DECLARE
 TYPE t IS RECORD (
 col_name   VARCHAR2 (100)
 );

  t_row   t;
  cname   VARCHAR (100) := 'col_name';
BEGIN
  t_row.col_name             := 'col';
 DBMS_OUTPUT.put_line ('out');

  IF t_row.cname IS NULL THEN
    DBMS_OUTPUT.put_line ('in');
  END IF;
 END;

Error at line 1
ORA-06550: line 12, column 12:
PLS-00302: component 'CNAME' must be declared
ORA-06550: line 12, column 3:
PL/SQL: Statement ignored

How can I assign dynamic column name to type variable of record?

You know at compile time what the record fields are named, why can't you just use them in your code? — Jeffrey Kemp

A.B.Cade A.B.Cade · Accepted Answer · 2012-03-01T14:27:30

You can do that with dynamic sql:
To make the example simpler I'll make your type t a schema object (but basically you don't have to - you can put it in the dynamic part as well)

create or replace type t is object(col_name varchar2(100));
/

Then you can look at this script:

declare
  t_row t;
  cname varchar2(100) := 'col_name';
begin

  t_row := new t('col');

  execute immediate 'declare t_in t := :0; begin if t_in.' || cname ||
                    ' is null then dbms_output.put_line(''in''); end if; end;'
    using t_row;
end;

Though, I must say, that this is a strange requirement ...

Dynamic column name to record type variable

2 Answers