CODEWITHSUNDEEP
oracle-databaseuser-defined-types
user2151312
user2151312

Reputation: 21

Passing values from Oracle Object type parameter to PLSQL Table type parameter

How can we pass a parameter that is declared as an Oracle Object type to a Procedure having a parameter as PLSQL Table type?

Eg:

Procedure A(p_obj_emp t_obj_emp)
Procedure B(p_emp_tab t_emp_tab)

Where t_obj_emp = Oracle Object and t_emp_tab is a PLSQL Table of binary_integer

How can we pass a parameter that is declared as an Oracle Object type to a Procedure having a parameter as PLSQL Record type?

Eg:

Procedure C(p_obj_dept t_obj_dept)
Procedure D(p_dept_rec t_dept_rec)

Where t_obj_dept = Oracle Object having 2 fields (deptid, deptname) and t_dept_rec is declared in package specification as t_dept_rec with 2 fields (deptid, deptname).

Kindly help with some example. Thanks in advance

Upvotes: 2

Views: 9428

Answers (1)

Luke Woodward
Luke Woodward

Reputation: 64949

The following compiles for me and appears to do what you want:

CREATE OR REPLACE TYPE t_obj_emp AS OBJECT (
  emp_id      INTEGER,
  emp_name    VARCHAR2(100)
);
/

CREATE OR REPLACE TYPE t_obj_dept AS OBJECT (
  dept_id     INTEGER,
  dept_name   VARCHAR2(100)
);
/

CREATE OR REPLACE PACKAGE my_pkg AS

  TYPE t_emp_tab IS TABLE OF t_obj_emp INDEX BY BINARY_INTEGER;

  TYPE t_dept_rec IS RECORD (
    dept_id     INTEGER,
    dept_name   VARCHAR2(100)
  );

  PROCEDURE A(p_obj_emp t_obj_emp);
  PROCEDURE B(p_emp_tab t_emp_tab);
  PROCEDURE C(p_obj_dept t_obj_dept);
  PROCEDURE D(p_dept_rec t_dept_rec);
END;
/

CREATE OR REPLACE PACKAGE BODY my_pkg AS

  PROCEDURE A(p_obj_emp t_obj_emp)
  IS
    v_emp_tab     t_emp_tab;
  BEGIN 
    v_emp_tab(1) := p_obj_emp;
    B(v_emp_tab);
  END;

  PROCEDURE B(p_emp_tab t_emp_tab) IS BEGIN NULL; END;

  PROCEDURE C(p_obj_dept t_obj_dept)
  IS
    v_dept_rec    t_dept_rec;
  BEGIN
    v_dept_rec.dept_id := p_obj_dept.dept_id;
    v_dept_rec.dept_name := p_obj_dept.dept_name;
    D(v_dept_rec);
  END;

  PROCEDURE D(p_dept_rec t_dept_rec) IS BEGIN NULL; END;
END;
/

Note that there isn't any 'convenient' way to create one object/record from another, even if they have identical members (such a t_obj_dept and t_dept_rec in the example above). You must copy across the values of all the fields individually.

You say that t_emp_tab is a "PLSQL Table of binary_integer". I'm guessing you mean that it's a PL/SQL Table of some type indexed by binary_integer, as it's far more common to index these tables by binary_integer than it is to store binary_integers in them. For the example above I've assumed that it's a table of t_obj_emps. If it's not, you'll have to map the t_obj_emp object to whatever type of object or record t_emp_tab is a table of.

Upvotes: 2

Related Questions