Forum Stats

  • 3,838,561 Users
  • 2,262,383 Discussions
  • 7,900,687 Comments

Discussions

Can we use %rowtype attribute inside the plsql record?

SHAJ_SHAJIN
SHAJ_SHAJIN Member Posts: 15 Green Ribbon

Hi everyone,

Can we use %rowtype attribute inside the plsql record like the below code..

type xx is RECORD ( v_emp employees%rowtype ,

v_loc departments.LOCATION_ID%type );

v_data xx;

Answers

  • Hub Tijhuis
    Hub Tijhuis Member Posts: 184 Gold Badge

    Following gives no errors in oracle Live SQL: so I think the answer is yes

    declare

    type xx is record ( v_emp all_tab_columns%rowtype ,

    v_loc all_tables.table_name%type );


    v_data xx;

    begin

        v_data.v_emp.column_name := 'a';

        v_data.v_loc := 'b';

    end;

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,243 Red Diamond

    Hi, @SHAJ_SHAJIN

    Can we use %rowtype attribute inside the plsql record like the below code..

    Yes. Did you have trouble when you tried it? Post your code, and a specific question about it.

  • SHAJ_SHAJIN
    SHAJ_SHAJIN Member Posts: 15 Green Ribbon

    Actually i was trying to store all columns from a table and one column from another table. Is this possible ?

    Declare

    type xx is RECORD ( v_emp employees%rowtype ,

    v_loc departments.LOCATION_ID%type );

    v_data xx;

    Begin

    Select e.*, (select location_id from departments d where d.department_id = e.department_id) loc_id into v_data from employees e where e.employee_id = 102;

    End;

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,243 Red Diamond

    Hi, @SHAJ_SHAJIN

    If employees has 11 columns, then

    Select e.*, (select location_id from departments d where d.department_id = e.department_id) loc_id into v_data ...

    gets 11 + 1 = 12 values. But v_data doesn't have 12 elements; it only has 2 elements: an xx and a NUMBER.

    One way you can use the existing xx data type is:

    DECLARE
      TYPE xx is RECORD ( v_emp  employees%ROWTYPE
      	   	    , v_loc  departments.location_id%TYPE
    		    );
      v_data xx;
    BEGIN
      SELECT *
      INTO   v_data.v_emp
      FROM   employees e
      WHERE  e.employee_id = 102;
    
      SELECT location_id
      INTO   v_data.v_loc 
      FROM   departments 
      WHERE  department_id = v_data.v_emp.department_id;
    
      dbms_output.put_line (v_data.v_loc || ' = v_loc'); -- For debugging
    END;
    

    However, that doesn't seem to be a very convenient data type. I would do something like this instead:


    DECLARE
     CURSOR c (employee_id_wanted IN employees.employee_id%TYPE) IS 
      SELECT   e.*
      , 	   (
      	     SELECT location_id
    		 FROM  departments d
    		 WHERE  d.department_id = e.department_id
      	   ) AS loc_id
      FROM   employees e
      WHERE  e.employee_id = employee_id_wanted;
       
     v_data  c%ROWTYPE;
    BEGIN
      OPEN c (102);
    
      FETCH c
      INTO  v_data;
       
      dbms_output.put_line (v_data.loc_id || ' = loc_id'); -- For debugging
    
      CLOSE c;
    END;
    


  • SHAJ_SHAJIN
    SHAJ_SHAJIN Member Posts: 15 Green Ribbon

    instead of two select statement, I am referring record variable in the same into clause but got the error saying

    PLS-00494: coercion into multiple record targets not supported. But not able to get, if we provide two select statement like the above code its working fine but when we try to do like this(below) it pops out an error. Instead of writting two into clause i'm using single clause

    DECLARE
      TYPE xx is RECORD ( v_emp  employees%ROWTYPE
      	   	    , v_loc  departments.location_id%TYPE
    		    );
      v_data xx;
    BEGIN
      SELECT e.*, (select location_id
                    from departments d
                     where d.department_id = e.department_id) loc_id
      INTO   v_data.v_emp, v_data.v_loc 
      FROM   employees e
      WHERE  e.employee_id = 102;
    END;