Skip to Main Content
  • Questions
  • Object Type Variables in Bulk Collect and FORALL

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Pradeep.

Asked: September 04, 2024 - 4:48 am UTC

Last updated: September 04, 2024 - 5:16 pm UTC

Version: 19c

Viewed 100+ times

You Asked

I was reading the following discussion about using Object Types

https://asktom.oracle.com/ords/f?p=100:11:607033327783906:::::

https://docs.oracle.com/cd/B10501_01/appdev.920/a96624/10_objs.htm

But, I couldnt understand the object types usage in packages (mainly in the bulk processing). Is it possible to use objects in bulk processing? if yes, can you please help with an example?

with LiveSQL Test Case:

and Chris said...

I'm not sure which discussion you're referring to - the link leads back to the homepage. Also those docs are from 9i - over 20 years old now!

The syntax:

insert into ... values <rec>


Is only for adding PL/SQL records. To insert an object type, list its attributes specifically. Or - as you can query an object table like a regular table - use insert select to add the rows.

For example:

CREATE OR REPLACE PACKAGE BODY xxylp_emp_pkg 
AS 
  PROCEDURE insert_rec (p_emp_typ IN  emp_type) 
  AS 
  BEGIN 
    for rws in ( select * from table ( p_emp_typ ) ) loop
      dbms_output.put_line ( rws.empno );
    end loop;

    FORALL idx IN p_emp_typ.FIRST .. p_emp_typ.LAST 
      INSERT INTO emp ( empno, ename ) 
           VALUES ( p_emp_typ(idx).empno, p_emp_typ(idx).ename ); 
       
    dbms_output.put_line('Insert Count: '||SQL%ROWCOUNT);   

    INSERT INTO emp ( empno, ename ) 
      select empno, ename from table ( p_emp_typ ); 

    dbms_output.put_line('Insert Count: '||SQL%ROWCOUNT);   

  END insert_rec;     
END xxylp_emp_pkg; 
/

begin 
  XXYLP_EMP_PKG.INSERT_REC ( 
    emp_type ( 
      emp_obj ( 1, 'test', 'JOB', null, sysdate, 1000, 0, 10 ),
      emp_obj ( 2, 'test', 'JOB', null, sysdate, 2000, 0, 10 )
    )
  );
end;
/
1
2
Insert Count: 2
Insert Count: 2

select empno, ename from emp;

     EMPNO ENAME               
---------- --------------------
         1 test                
         2 test                
         1 test                
         2 test    

Rating

  (2 ratings)

Comments

Can we use hints with object types for faster DML?

Pradeep, September 04, 2024 - 3:58 pm UTC

Thanks Chris.

Are there any restrictions or downside using hints with objects?

    FORALL idx IN p_emp_typ.FIRST .. p_emp_typ.LAST 
      INSERT /*+ APPEND_VALUES */ INTO emp ( empno, ename ) 
           VALUES ( p_emp_typ(idx).empno, p_emp_typ(idx).ename ); 
       


    INSERT /*+ APPEND */ INTO emp ( empno, ename ) 
      select empno, ename from table ( p_emp_typ ); 

Chris Saxon
September 04, 2024 - 5:16 pm UTC

The hints will work whether you're using object types or not.

The bigger question is why you want to use hints at all - in most cases you should avoid them.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library