Table type parameter to a procedure

[vamsioracle]

HI All



I need help regarding this, i have a procedure that processes some data. I created one more procedure and i am calling the first procedure from the second with some parameters. Out of the available parameters, there are two which should be of table type.

How to pass a table type parameters and how to declare them

please help me out

vamsi

[amitpatel66]

The easiest way should be using a package that contains a TYPE of table, and use the aliases of this table as input parameter types. Check this:

[code=oracle]

CREATE OR REPLACE PACKAGE test_table AS
TYPE emp_data IS TABLE OF emp%ROWTYPE;
END test_table;
/

CREATE OR REPLACE PROCEDURE first_proc(a IN NUMBER, emp_dat IN OUT test_table.emp_ data NOCOPY) AS
BEGIN
SELECT * BULK COLLECT INTO emp_dat FROM emp;
END first_proc;
/

DECLARE
empd test_table.emp_ data;
num1 INTEGER;
BEGIN
first_proc(num1 , empd);
FOR I IN empd.FIRST..emp d.LAST LOOP
DBMS_OUTPUT.PUT _LINE(empd(i).e mpno);
END LOOP;
END;
/

[/code]


Note: I have not tested this code!!

[vamsioracle]

Thank u ver much, i 'll try that but just check this code which i have written.
[code=oracle]

CREATE OR REPLACE PROCEDURE XXBIOUK_STATUS_ UPDT_PROC
AS
=============== ====table type=========== ==========
type v_attributes_ca tegory is table of varchar2(1000)
index by binary_integer;
type v_n_attributes_ tbl is table of number
index by binary_integer;
type v_d_attributes_ tbl is table of date
index by binary_integer;

=========== table type ===
v_a v_attributes_ca tegory;
v_b v_attributes_ca tegory;
v_c v_n_attributes_ tbl;
v_d v_d_attributes_ tbl;
=============== ======cursor=== ===============
CURSOR c_lotnumber_cur
IS
SELECT
organization_id ,
inventory_item_ id,
lot_number,
item_number,
attribute1,
expiration_date ,
lot_status
FROM
xxbio_lotnumber _status
WHERE
org_group_name = 'USA'
OR org_group_name = 'UK'
AND lot_status = 'APPROVED'
OR lot_status = 'REJECT'
AND erp_extract_fla g='N';
=============== =============== ==========
BEGIN
FOR ln_rec_counter IN c_lotnumber_cur
LOOP
=====calling a package.proc inside loop========
inv_lot_api_pub .update_inv_lot
(
=======passing table type params to proc====
v_a,
v_b,
v_c,
v_d,
0;
==============e rror i get =======


PLS-00306: wrong number or types of arguments in call to
'UPDATE_INV_LOT '
[/code]


what might be the problem

[amitpatel66]

Can you post the proceudre within your package for reference?

inv_lot_api_pub .update_inv_lot ?

[vamsioracle]

[code=oracle]

PROCEDURE update_inv_lot(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_inventory_ite m_id IN NUMBER
, p_organization_ id IN NUMBER
, p_lot_number IN VARCHAR2
, p_expiration_da te IN DATE
, p_disable_flag IN NUMBER
, p_attribute_cat egory IN VARCHAR2
, p_lot_attribute _category IN VARCHAR2
, p_attributes_tb l IN inv_lot_api_pub .char_tbl
, p_c_attributes_ tbl IN inv_lot_api_pub .char_tbl
, p_n_attributes_ tbl IN inv_lot_api_pub .number_tbl
, p_d_attributes_ tbl IN inv_lot_api_pub .date_tbl
, p_grade_code IN VARCHAR2
, p_origination_d ate IN DATE
, p_date_code IN VARCHAR2
, p_status_id IN NUMBER
, p_change_date IN DATE
, p_age IN NUMBER
, p_retest_date IN DATE
, p_maturity_date IN DATE
, p_item_size IN NUMBER
, p_color IN VARCHAR2
, p_volume IN NUMBER
, p_volume_uom IN VARCHAR2
, p_place_of_orig in IN VARCHAR2
, p_best_by_date IN DATE
, p_length IN NUMBER
, p_length_uom IN VARCHAR2
, p_recycled_cont ent IN NUMBER
, p_thickness IN NUMBER
, p_thickness_uom IN VARCHAR2
, p_width IN NUMBER
, p_width_uom IN VARCHAR2
, p_territory_cod e IN VARCHAR2
, p_supplier_lot_ number IN VARCHAR2
, p_vendor_name IN VARCHAR2
, p_source IN NUMBER
) IS
[/code]

[amitpatel66]

I see when you are calling this procedure, ou are passing only 4-6 vales but this procedure has lots of input parameters, that is the reason the error is coming. You need to make the parameters optional in case of avoiding an error or pass all the parameter values when calling that procedure

[vamsioracle]

[code=oracle]

inv_lot_api_pub .update_inv_lot
(
v_inventory_ite m_id,
v_organization_ id,
v_lot_number,
null,--p_expiration_da te
null,--p_disable_flag
null,--p_attribute_cat egory
null,--p_lot_attribute _category
v_a ,--p_attributes_tb l
v_b,--p_c_attributes_ tbl
v_c,--p_n_attributes_ tbl
v_d,--p_d_attributes_ tbl
null,--p_grade_code
null,--p_origination_d ate
null,--p_date_code
null,--p_status_id
null,--p_change_date
null,--p_age
null,--p_retest_date
null,--p_maturity_date
null,--p_item_size
null,--p_color
null,--p_volume
null,--p_volume_uom
null,--p_place_of_orig in
null,--p_best_by_date
null,--p_length
null,--p_length_uom
null,--p_recycled_cont ent
null,--p_thickness
null,--p_thickness_uom
null,--p_width
null,--p_width_uom
null,--p_territory_cod e
null,--p_supplier_lot_ number
null,--p_vendor_name
null--p_source
);

[/code]



now i am passing all the parameters, still the error persists. is this the correct way to send table type parameter and moreover i didnt take any value into v_a,v_b......, its just declared as table type object.

[amitpatel66]

You need to pass a variables for the following OUT parameters as well:

[code=oracle]

x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2

[/code]

[vamsioracle]

i created a table type variable as
[code=oracle]

create or replace package my_pack as

type v_attributes_ca tegory is table of varchar2(1000)
index by binary_integer;

v_a v_attributes_ca tegory;

cusor c_cur is
select name_varchar_va riable from xx_table where condition;

for i in c_cur
loop
begin

v_a = i.name_varchar;


package.procedu re(
.....
....
v_a,
.....
)

[/code]


in the above example, i have few doubts
1. is that the right way to assign values to v_a inside a loop of cursor
2. is that the right way to pass v_a, which is table type, to procedure
3. can we use v_a(i) := i. name_varchar to assign value


please help me out,

i used the above sample structure and i get

PLS-00382: expression is of wrong type and

PLS-00306: wrong number or types of arguments in call to procedure

thanks in advance

[amitpatel66]

i created a table type variable as
[code=oracle]

create or replace package my_pack as

type v_attributes_ca tegory is table of varchar2(1000)
index by binary_integer;

v_a v_attributes_ca tegory;

cusor c_cur is
select name_varchar_va riable from xx_table where condition;

for i in c_cur
loop
begin

v_a = i.name_varchar;


package.procedu re(
.....
....
v_a,
.....
)

[/code]


in the above example, i have few doubts
1. is that the right way to assign values to v_a inside a loop of cursor
2. is that the right way to pass v_a, which is table type, to procedure
3. can we use v_a(i) := i. name_varchar to assign value


please help me out,

i used the above sample structure and i get

PLS-00382: expression is of wrong type and

PLS-00306: wrong number or types of arguments in call to procedure

thanks in advance

2 and 3 are correct. First is incorrect. You cannot assign a value to an array that way. Instead use your point 3 for assiging a value to an array of table.

[vamsioracle]

please help me out , i am unable to solve


[code=oracle]


54/1 PL/SQL: Statement ignored
54/5 PLS-00382: expression is of wrong type
55/1 PL/SQL: Statement ignored
55/5 PLS-00382: expression is of wrong type
56/1 PL/SQL: Statement ignored
56/5 PLS-00382: expression is of wrong type
57/1 PL/SQL: Statement ignored
57/5 PLS-00382: expression is of wrong type
58/1 PLS-00306: wrong number or types of arguments in call to
'UPDATE_INV_LOT '

58/1 PLS-00306: wrong number or types of arguments in call to
'UPDATE_INV_LOT '

58/1 PLS-00306: wrong number or types of arguments in call to
'UPDATE_INV_LOT '

58/1 PLS-00306: wrong number or types of arguments in call to
'UPDATE_INV_LOT '


these are the errors i receive and my code is


CREATE OR REPLACE PROCEDURE XXBIOUK_STATUS_ UPDT_PROC
AS
v_record_count number:=0;
v_erp_extract_f lag varchar2(1);
x_return_status _out varchar2(10);
x_msg_count_out number;
x_msg_data_out varchar2(10);
v_inventory_ite m_id xxbio_lotnumber _status.invento ry_item_id %type;
v_organization_ id xxbio_lotnumber _status.organiz ation_id %type;
v_lot_number xxbio_lotnumber _status.lot_num ber %type;
----------------------------------------to pass the value in the parameter as a record type
type v_attributes_ca tegory is table of varchar2(1000)
index by binary_integer;
type v_n_attributes_ tbl is table of number
index by binary_integer;
type v_d_attributes_ tbl is table of date
index by binary_integer;
v_a v_attributes_ca tegory;
v_b v_attributes_ca tegory;
v_c v_n_attributes_ tbl;
v_d v_d_attributes_ tbl;
CURSOR c_lotnumber_cur
IS
SELECT
organization_id ,
inventory_item_ id,
lot_number,
item_number,
attribute1,
expiration_date ,
lot_status
FROM
xxbio_lotnumber _status
WHERE
org_group_name = 'USA'
OR org_group_name = 'UK'
AND lot_status = 'APPROVED'
OR lot_status = 'REJECT'
AND erp_extract_fla g='N';

Begin
FOR ln_rec_counter IN c_lotnumber_cur
LOOP
begin
--------------------------------------------------------------------Assign the varibales
v_inventory_ite m_id:=ln_rec_co unter.inventory _item_id;
v_organization_ id:=ln_rec_coun ter.organizatio n_id;
v_lot_number:=l n_rec_counter.l ot_number;
v_a(ln_rec_coun ter) := ln_rec_counter. lot_status;
v_b(ln_rec_coun ter) := ln_rec_counter. attribute1;
v_c(ln_rec_coun ter) := ln_rec_counter. item_number;
v_d(ln_rec_coun ter) := ln_rec_counter. expiration_date ;
inv_lot_api_pub .update_inv_lot
(
x_return_status _out,
x_msg_count_out ,
x_msg_data_out,
v_inventory_ite m_id,
v_organization_ id,
v_lot_number,
null,--p_expiration_da te
null,--p_disable_flag
null,--p_attribute_cat egory
null,--p_lot_attribute _category
v_a,--p_attributes_tb l
v_b,--p_c_attributes_ tbl
V_c,--p_n_attributes_ tbl
v_d,--p_d_attributes_ tbl
null,--p_grade_code
null,--p_origination_d ate
null,--p_date_code
null,--p_status_id
null,--p_change_date
null,--p_age
null,--p_retest_date
null,--p_maturity_date
null,--p_item_size
null,--p_color
null,--p_volume
null,--p_volume_uom
null,--p_place_of_orig in
null,--p_best_by_date
null,--p_length
null,--p_length_uom
null,--p_recycled_cont ent
null,--p_thickness
null,--p_thickness_uom
null,--p_width
null,--p_width_uom
null,--p_territory_cod e
null,--p_supplier_lot_ number
null,--p_vendor_name
null--p_source
);
END ;
/

[/code]


thank u

[amitpatel66]

Why are you creating a TYPE again within your procedure??
You can use the one that you have created within your package inv_lot_pub. Something like inv_lot_api_pub .char_tbl for one of the input parameter.

[code=oracle]

declare
v_a inv_lot_api_pub .char_tbl;
BEGIN
update_inv_lot( v_a);
END;
[/code]

The above code gives you a hint of how you can use the type that is already created with in a package. Do the similar way for other array type input parameters.

[vamsioracle]

thank u very much, one final problem is
[code=oracle]

47/1 PL/SQL: Statement ignored
47/5 PLS-00382: expression is of wrong type
48/1 PL/SQL: Statement ignored
48/5 PLS-00382: expression is of wrong type
49/1 PL/SQL: Statement ignored
49/5 PLS-00382: expression is of wrong type
50/1 PL/SQL: Statement ignored
50/5 PLS-00382: expression is of wrong type

i get this error

FOR ln_rec_counter IN c_lotnumber_cur
LOOP
begin
--******Assign the varibales****** ****
v_inventory_ite m_id:=ln_rec_co unter.inventory _item_id;
v_organization_ id:=ln_rec_coun ter.organizatio n_id;
v_lot_number:=l n_rec_counter.l ot_number;
v_exp_date :=ln_rec_counte r.expiration_da te;
v_a(ln_rec_coun ter):=ln_rec_co unter.lot_statu s; ---------line 47
v_b(ln_rec_coun ter):=ln_rec_co unter.attribute 1;------------line 48
v_c(ln_rec_coun ter):=ln_rec_co unter.item_numb er;-------------line 49
v_d(ln_rec_coun ter):=ln_rec_co unter.expiratio n_date;---------------line 50
inv_lot_api_pub .update_inv_lot
(
x_return_status _out,
x_msg_count_out ,
x_msg_data_out,
v_inventory_ite m_id,
v_organization_ id,
v_lot_number,
v_exp_date,--p_expiration_da te
null,--p_disable_flag
null,--p_attribute_cat egory
null,--p_lot_attribute _category
v_a,--p_attributes_tb l
v_b,--p_c_attributes_ tbl
V_c,--p_n_attributes_ tbl
v_d,--p_d_attributes_ tbl
null,--p_grade_code
null,--p_origination_d ate
null,--p_date_code
null,--p_status_id
null,--p_change_date
null,--p_age
null,--p_retest_date
null,--p_maturity_date
null,--p_item_size
null,--p_color
null,--p_volume
null,--p_volume_uom
null,--p_place_of_orig in
null,--p_best_by_date
null,--p_length
null,--p_length_uom
null,--p_recycled_cont ent
null,--p_thickness
null,--p_thickness_uom
null,--p_width
null,--p_width_uom
null,--p_territory_cod e
null,--p_supplier_lot_ number
null,--p_vendor_name
null--p_source
);

[/code]

is there any problem with the way i am assigning values to the table type variables

[amitpatel66]

Yes, the way you are assigning a value to the table type variables is a problem here. You cannot assign individual values to table type in a LOOP and call a procedure, Instead you need to assign all the values of the cursor together to the type and then call a procedure.

you need to change either of the following:

1. change upd_inv_lot to accept individual values instead of table type variables
2. change upd_inv_lot to accept inventory_id and other two varialbes as table type instead of a individual values.

Only then you will be able to work out on your requirement!!