CODEWITHSUNDEEP
oracle-databaseplsqloracle10g
REDO IT
REDO IT

Reputation: 11

PL SQL How can I address record field using variable

Is it possible to get value from record field using variable to adress it? If yes how can i do it?

When i do it like this it works.

DECLARE
   dev_day JX_DATA_20160301%ROWTYPE;
   ichar varchar2(3);
BEGIN
  SELECT * into dev_day FROM JX_DATA_20160301 WHERE did=100;
  ichar:='H01';
  dbms_output.put_line(dev_day.H01);
END;

But when i change it this way:

DECLARE
   dev_day JX_DATA_20160301%ROWTYPE;
   ichar varchar2(3);
BEGIN
  SELECT * into dev_day FROM JX_DATA_20160301 WHERE did=100;
  ichar:='H01';
  dbms_output.put_line(dev_day.ichar);
END;

I get error: 

 ORA-06550: linia 7, kolumna 32: PLS-00302: component
'ICHAR' must be declared ORA-06550: linia 7, kolumna 3: PL/SQL:
Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"

Upvotes: 1

Views: 113

Answers (3)

Stepan Kasyanenko
Stepan Kasyanenko

Reputation: 3186

You can translate your request in XMLtype. And then work with XmlType.

For example:

DECLARE
 -- Local variables here
 xml_t           xmltype;
 ichar CHAR(3) := 'H01';
 l_refcursor SYS_REFCURSOR;
BEGIN
 OPEN l_refcursor FOR
        SELECT * FROM JX_DATA_20160301 WHERE did=100;
 xml_t := XMLTYPE(l_refcursor);
 dbms_output.put_line(xml_t.extract('//'||ichar||'/text()').getStringVal);
END;

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191520

You can't directly refer to the record field using a variable name . You could do this using the dbms_sql package, but it seems like overkill:

DECLARE
  l_column_name user_tab_columns.column_name%type;
  l_cursor integer;
  l_cols integer;
  l_rows integer;
  l_desc dbms_sql.desc_tab;
  l_value varchar2(4000);
BEGIN
  l_column_name := 'H01';

  l_cursor := dbms_sql.open_cursor;
  dbms_sql.parse(l_cursor, 'SELECT * FROM JX_DATA_20160301 WHERE did=:did',
    dbms_sql.native);
  dbms_sql.bind_variable(l_cursor, 'did', 100);
  l_rows := dbms_sql.execute(l_cursor);

  dbms_sql.describe_columns(c => l_cursor, col_cnt => l_cols,
    desc_t => l_desc);

  for i in 1..l_cols loop
    dbms_sql.define_column(l_cursor, i, l_value, 4000);
  end loop;

  while dbms_sql.fetch_rows(l_cursor) > 0 loop
    for i in 1..l_cols loop
      if l_desc(i).col_name = l_column_name then
        dbms_sql.column_value(l_cursor, i, l_value);
        dbms_output.put_line(l_value);
      end if;
    end loop;
  end loop;

  dbms_sql.close_cursor(l_cursor);
END;
/

You can use a more specific data type for l_value if you know the column will always be a number, say. Or you can have a variable of each possible type and choose which to use based on the data type, which you can get from l_desc(i).

But assuming you know the table structure, you could switch on the variable value instead:

DECLARE
   dev_day JX_DATA_20160301%ROWTYPE;
   ichar varchar2(3);
BEGIN
  SELECT * into dev_day FROM JX_DATA_20160301 WHERE did=100;
  ichar:='H01';
  case ichar
    when 'H01' then
      dbms_output.put_line(dev_day.h01);
    when 'H02' then
      dbms_output.put_line(dev_day.h02);
    -- ... etc, for each column you might want
  end case;    
END;
/

Upvotes: 0

MT0
MT0

Reputation: 168520

According to AskTom, you need to store the record in a package:

CREATE PACKAGE temp_storage_pkg
AS
  JX_DATA_REC JX_DATA_20160301%ROWTYPE;
END;
/

DECLARE
  ichar CHAR(3) := 'H01';
  value VARCHAR2(4000);
BEGIN
  SELECT * INTO temp_storage_pkg.JX_DATA_REC FROM JX_DATA_20160301 WHERE did=100;
  EXECUTE IMMEDIATE 'BEGIN :x := temp_storage_pkg.JX_DATA_REC.' || ichar || '; END;'
    USING OUT value;
  DBMS_OUTPUT.PUT_LINE( value );
END;
/

Upvotes: 1

Related Questions