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:

 
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..empd.LAST LOOP
DBMS_OUTPUT.PUT_LINE(empd(i).empno);
END LOOP;
END;
/
 
 

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.

 
CREATE OR REPLACE PROCEDURE XXBIOUK_STATUS_UPDT_PROC
AS
===================table type=====================
type v_attributes_category 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_category;
v_b v_attributes_category;
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_flag='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;
==============error i get =======
 
 
 PLS-00306: wrong number or types of arguments in call to
 'UPDATE_INV_LOT'
 

what might be the problem

[amitpatel66]

Can you post the proceudre within your package for reference?

inv_lot_api_pub .update_inv_lot ?

[vamsioracle]

 
PROCEDURE update_inv_lot(
    x_return_status          OUT NOCOPY    VARCHAR2
  , x_msg_count              OUT NOCOPY    NUMBER
  , x_msg_data               OUT NOCOPY    VARCHAR2
  , p_inventory_item_id      IN            NUMBER
  , p_organization_id        IN            NUMBER
  , p_lot_number             IN            VARCHAR2
  , p_expiration_date        IN            DATE
  , p_disable_flag           IN            NUMBER
  , p_attribute_category     IN            VARCHAR2
  , p_lot_attribute_category IN            VARCHAR2
  , p_attributes_tbl         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_date       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_origin        IN            VARCHAR2
  , p_best_by_date           IN            DATE
  , p_length                 IN            NUMBER
  , p_length_uom             IN            VARCHAR2
  , p_recycled_content       IN            NUMBER
  , p_thickness              IN            NUMBER
  , p_thickness_uom          IN            VARCHAR2
  , p_width                  IN            NUMBER
  , p_width_uom              IN            VARCHAR2
  , p_territory_code         IN            VARCHAR2
  , p_supplier_lot_number    IN            VARCHAR2
  , p_vendor_name            IN            VARCHAR2
  , p_source                 IN            NUMBER
  ) IS
 

[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]

 
inv_lot_api_pub.update_inv_lot
(
v_inventory_item_id,
v_organization_id,
v_lot_number,
null,--p_expiration_date
null,--p_disable_flag
null,--p_attribute_category
null,--p_lot_attribute_category
v_a ,--p_attributes_tbl
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_date
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_origin
null,--p_best_by_date
null,--p_length
null,--p_length_uom
null,--p_recycled_content
null,--p_thickness
null,--p_thickness_uom
null,--p_width
null,--p_width_uom
null,--p_territory_code
null,--p_supplier_lot_number
null,--p_vendor_name
null--p_source
);
 
 

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:

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

[vamsioracle]

i created a table type variable as

 
create or replace package my_pack as 
 
type v_attributes_category is table of varchar2(1000)
index by binary_integer;
 
v_a v_attributes_category;
 
cusor c_cur is
select  name_varchar_variable from xx_table where condition;
 
for i in c_cur
loop
begin
 
v_a = i.name_varchar;
 
 
package.procedure(
.....
....
v_a,
.....
)
 
 

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

 
create or replace package my_pack as 
 
type v_attributes_category is table of varchar2(1000)
index by binary_integer;
 
v_a v_attributes_category;
 
cusor c_cur is
select  name_varchar_variable from xx_table where condition;
 
for i in c_cur
loop
begin
 
v_a = i.name_varchar;
 
 
package.procedure(
.....
....
v_a,
.....
)
 
 

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.