Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Passing parameters to PL/SQL table types

Keen2LearnDec 13 2012 — edited Dec 15 2012
Hi Everybody,
I have one question about passing PL/SQL tables types and tabs as IN parameter in procedure.I am working in 11.2.0.2.0 environment. I am stuck on how to pass those values to procedure.Please find below more details:

Table 1:
CREATE TABLE ITEMS
(
ITEM_ID VARCHAR2(40 BYTE) NOT NULL,
ITEM_NAME VARCHAR2(40 BYTE),
SERIAL NUMBER(2),
ADDED_ON DATE);

Table 2:
CREATE TABLE ITEM_ACTIVITY_INFO
(
ITEM_ID VARCHAR2(40 BYTE) NOT NULL,
ACCOUNT_TYPE VARCHAR2(1 BYTE),
ID_NUMBER NUMBER(3),
ACTIVATION_DATE DATE);

Table 3:
CREATE TABLE ITEM_GROUP
(
GROUP_ID NUMBER(2) NOT NULL,
ITEM_ID VARCHAR2(40 BYTE),
GROUP_TYPE VARCHAR2(20 BYTE),
GROUP_DATE DATE);

Table 4:
CREATE TABLE ITEM_ADDRESS
(
GROUP_ID NUMBER(2) NOT NULL,
NAME VARCHAR2(60 BYTE),
ADDRESS VARCHAR2(100));

Following types are created:

CREATE OR REPLACE TYPE ITEMS_TYPE AS OBJECT
(
ITEM_ID VARCHAR2(40 BYTE),
ITEM_NAME VARCHAR2(40 BYTE),
SERIAL NUMBER(2),
ADDED_ON DATE);

CREATE OR REPLACE TYPE ITEM_ACTIVITY_TYPE AS OBJECT
(
ITEM_ID VARCHAR2(40 BYTE),
ACCOUNT_TYPE VARCHAR2(1 BYTE),
ID_NUMBER NUMBER(3),
ACTIVATION_DATE DATE);

CREATE OR REPLACE TYPE ITEM_GROUP_COMP_TYPE AS OBJECT
(
GROUP_ID NUMBER(2) NOT NULL,
ITEM_ID VARCHAR2(40 BYTE),
GROUP_TYPE VARCHAR2(20 BYTE),
GROUP_DATE DATE
ITEM_ADDRESS_IN ITEM_ADDRESS_TYPE);

CREATE OR REPLACE TYPE ITEM_ADDRESS_TYPE AS OBJECT
(
GROUP_ID NUMBER(2),
NAME VARCHAR2(60 BYTE),
ADDRESS VARCHAR2(100));

CREATE OR REPLACE TYPE ITEM_GROUP_COMP_TAB AS TABLE OF ITEM_GROUP_COMP_TYPE;

Create or replace procedure ITEM_ADD_CHANGE(
ITEM_IN IN ITEMS_TYPE,
ITEM_ACTIVITY_IN IN ITEM_ACTIVITY_TYPE,
ITEM_GROUP_IN IN ITEM_GROUP_COMP_TAB,
ITEM_OUT IN OUT ITEMS.ITEM_ID%TYPE);

Above are the paramteres we are passing to procedure.
I need help in how to pass parameters to above procedure. All comments and responses will be highly appreciated. Thanks everyone for going through the post. Please let me know if more more information is required on this problem.

Regards
Dev
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 12 2013
Added on Dec 13 2012
12 comments
10,040 views