how to get the field name and value from a record dynamically

Question

I have a procedure which receive as input parameter a record with 170 columns (it is based on the structure of a table).

In the procedure I want to call a debugging procedure one of whose parameters is a text string containing all the field names and values of this record.

For example:

CREATE OR REPLACE PROCEDURE xxx (pi_record IN table_name%ROWTYPE) as
    text VARCHAR2(10000) := NULL;
BEGIN
    ...
    text := 'pi_record.column1 = ' || pi_record.column1 || CHR(13) ||
            'pi_record.column2 = ' || pi_record.column2 || CHR(13) ||
            ...
            'pi_record.column170 = ' || pi_record.column170;
    logging_procedure (text);
    ...
END;

Is there any simple way to achieve this in a dynamic way (looping through record fields names and values) without enumerating all of them?

Maybe something like this:

CREATE OR REPLACE PROCEDURE xxx (pi_record IN table_name%ROWTYPE) as
    text VARCHAR2(10000) := NULL;
BEGIN
    ...      
    LOOP in pi_record.columns
        text := text || CHR(13) || pi_record.column.name || ' : ' || pi_record.column.value
    END LOOP

    logging_procedure (text);
    ...
END; 

Many thanks,

Surf

Is there a way to retrieve a RefCursor instead of a Record? – Inbreed

@WernfriedDomscheit: Unfortunately, there is no possibility to use a RefCursor. – Surf

... but any other solution would be very appreciated, not only by me but maybe for other users where your example may be applied. – Surf

Answer 1

Here's one way to do that. A package spec contains a variable whose type matches the one we'll use in a procedure.

SQL> set serveroutput on
SQL> create or replace package pkg_xxx
  2  as
  3     dept_rec   dept%rowtype;
  4  end;
  5  /

Package created.

SQL> create or replace procedure xxx (pi_record in dept%rowtype)
  2  as
  3     text    varchar2 (10000) := null;
  4     l_str   varchar2 (200);
  5     l_var   varchar2 (200);
  6  begin
  7     pkg_xxx.dept_rec := pi_record;
  8
  9     for cur_r in (  select column_name
 10                       from user_tab_columns
 11                      where table_name = 'DEPT'
 12                   order by column_id)
 13     loop
 14        l_str :=
 15              'begin '
 16           || ':x := to_char(pkg_xxx.dept_rec.'
 17           || cur_r.column_name
 18           || '); '
 19           || 'end; ';
 20
 21        execute immediate l_str using out l_var;
 22
 23        text := text || chr (10) || cur_r.column_name || ' = ' || l_var;
 24     end loop;
 25
 26     dbms_output.put_line (text);
 27  end;
 28  /

Procedure created.

Now, let's pass something to the procedure and see what happens:

SQL> declare
  2     cursor c1
  3     is
  4        select *
  5          from dept
  6         where deptno = 10;
  7
  8     c1r   c1%rowtype;
  9  begin
 10     open c1;
 11     fetch c1 into c1r;
 12     close c1;
 13
 14     xxx (c1r);
 15  end;
 16  /

DEPTNO = 10
DNAME = ACCOUNTING
LOC = NEW YORK

PL/SQL procedure successfully completed.

SQL>

Huh, kind of works (if that's what you asked). Of course, it is just an example, you'll have to modify it if you want to get something really smart (hint: DATE columns).

Tehuantepec

Using a package spec variable is the trick! I was struggling with PLS-00457: expressions have to be of SQL types – Inbreed

Answer 2

The only idea I have is to insert the record into a TEMP table:

CREATE OR REPLACE PROCEDURE xxx (pi_record IN TABLE_NAME%ROWTYPE) AS

   TEXT VARCHAR2(10000) := NULL;
   item VARCHAR2(1000);

    TABLE_DOES_NOT_EXIST EXCEPTION;
    PRAGMA EXCEPTION_INIT(TABLE_DOES_NOT_EXIST, -942);

BEGIN

    BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE TABLE_NAME_TMP';
        EXCEPTION
            WHEN TABLE_DOES_NOT_EXIST then null;
    END;
    EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE TABLE_NAME_TMP AS SELECT * FROM TABLE_NAME WHERE ROWNUM = 0';

    DELETE FROM TABLE_NAME_TMP;
    INSERT INTO TABLE_NAME_TMP VALUES pi_record;

    FOR aCol IN (SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE table_name = 'TABLE_NAME' ORDER BY COLUMN_ID) LOOP       
        EXECUTE IMMEDIATE 'SELECT '||aCol.COLUMN_NAME||' FROM TABLE_NAME_TMP' INTO item;
        TEXT := TEXT || CHR(13) || aCol.COLUMN_NAME || ' : ' || item;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE ( TEXT );
END;

In case table TABLE_NAME has static attributes then you should skip dynamic DROP TABLE ... and CREATE GLOBAL TEMPORARY TABLE ... and create the TEMP table only once.

Inbreed

Answer 3

everyone!

I got a different approach to get the difference between records dynamically:

You just have to create the global variables on the package header as bellow:

v_NAME_OF_TABLE_new NAME_OF_TABLE%rowtype; v_NAME_OF_TABLE_old NAME_OF_TABLE%rowtype;

then create the function on your pkg body that return a boolean even if a field is different:

function is_different(p_old NAME_OF_TABLE%rowtype, p_new NAME_OF_TABLE%rowtype) 
return boolean     
is
     
   cursor cols is
           select tb.COLUMN_NAME
           from   all_tab_columns tb
           where  tb.OWNER = 'DW'
           and    tb.TABLE_NAME = 'NAME_OF_TABLE'
           order by tb.COLUMN_ID;
              
   l_sql  varchar2(4000);
   l_new  varchar2(4000);
   l_old  varchar2(4000);
           
  begin
    
   pkg_NAME.v_NAME_OF_TABLE_new := p_new;
   pkg_NAME.v_NAME_OF_TABLE_old := p_old;
           
   for reg in cols loop
                   
       l_sql := '
                 begin
                    :x := pkg_NAME.v_NAME_OF_TABLE_new.'||reg.COLUMN_NAME||';'||'
                 end;';   
           
       execute immediate l_sql using out l_new;          

       l_sql := '
                 begin
                    :x := pkg_NAME.v_NAME_OF_TABLE_old.'||reg.COLUMN_NAME||';'||'
                 end;';   
           
       execute immediate l_sql using out l_old;          
               
      --- dbms_output.put_line(l_new||' - '||l_old);
               
       if nvl(l_new,'NULO') <> nvl(l_old,'NULO') then
          return true;
       end if;
                  
   end loop;   
    
   return false;
           
  end;  

Atention: This can turn your process heavier and slower.

That's all! Hope this can be helpful!

Outcry