Table type parameter to a procedure

2,367

Expert 2GB

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:

Expand|Select|Wrap|Line Numbers

  
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!!

Mar 24 '08 #2

151

100+

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

Expand|Select|Wrap|Line Numbers

  
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

Mar 24 '08 #3

2,367

Expert 2GB

Can you post the proceudre within your package for reference?

inv_lot_api_pub.update_inv_lot?

Mar 24 '08 #4

151

100+

Expand|Select|Wrap|Line Numbers

  
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

Mar 24 '08 #5

2,367

Expert 2GB

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

Mar 24 '08 #6

151

100+

Expand|Select|Wrap|Line Numbers

  
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.

Mar 24 '08 #7

2,367

Expert 2GB

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

Expand|Select|Wrap|Line Numbers

  
x_return_status          OUT NOCOPY    VARCHAR2

  , x_msg_count              OUT NOCOPY    NUMBER

  , x_msg_data               OUT NOCOPY    VARCHAR2

Mar 24 '08 #8

151

100+

i created a table type variable as

Expand|Select|Wrap|Line Numbers

  
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

Mar 25 '08 #9

2,367

Expert 2GB

i created a table type variable as

Expand|Select|Wrap|Line Numbers

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.

Mar 25 '08 #10

151

100+

please help me out , i am unable to solve

Expand|Select|Wrap|Line Numbers

  
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_flag varchar2(1);

x_return_status_out varchar2(10);

x_msg_count_out number;

x_msg_data_out varchar2(10);

v_inventory_item_id xxbio_lotnumber_status.inventory_item_id %type;

v_organization_id xxbio_lotnumber_status.organization_id  %type;

v_lot_number xxbio_lotnumber_status.lot_number %type;

----------------------------------------to pass the value in the parameter as a record 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;

v_a v_attributes_category;

v_b v_attributes_category;

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_flag='N';
 
Begin

FOR ln_rec_counter IN c_lotnumber_cur

LOOP

begin

--------------------------------------------------------------------Assign the varibales

v_inventory_item_id:=ln_rec_counter.inventory_item_id;

v_organization_id:=ln_rec_counter.organization_id;

v_lot_number:=ln_rec_counter.lot_number;

v_a(ln_rec_counter) := ln_rec_counter.lot_status;

v_b(ln_rec_counter) := ln_rec_counter.attribute1;

v_c(ln_rec_counter) := ln_rec_counter.item_number;

v_d(ln_rec_counter) := 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_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

);

END ;

/

thank u

Mar 25 '08 #11

2,367

Expert 2GB

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.

Expand|Select|Wrap|Line Numbers

  
declare

v_a inv_lot_api_pub.char_tbl;

BEGIN

update_inv_lot(v_a);

END;

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.

Mar 25 '08 #12

151

100+

thank u very much, one final problem is

Expand|Select|Wrap|Line Numbers

  
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_item_id:=ln_rec_counter.inventory_item_id;

v_organization_id:=ln_rec_counter.organization_id;

v_lot_number:=ln_rec_counter.lot_number;

v_exp_date :=ln_rec_counter.expiration_date;

v_a(ln_rec_counter):=ln_rec_counter.lot_status; ---------line 47

v_b(ln_rec_counter):=ln_rec_counter.attribute1;------------line 48

v_c(ln_rec_counter):=ln_rec_counter.item_number;-------------line 49

v_d(ln_rec_counter):=ln_rec_counter.expiration_date;---------------line 50

inv_lot_api_pub.update_inv_lot

(

x_return_status_out,

x_msg_count_out,

x_msg_data_out,

v_inventory_item_id,

v_organization_id,

v_lot_number,

v_exp_date,--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

);

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

Mar 25 '08 #13

Generics: Type parameter with type parameter

2,367

Expert 2GB

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!!

Mar 25 '08 #14

by: wkaras | last post by:

I would like to propose the following changes to the C++ Standard, the goal of which are to provide an improved ability to specify the constraints on type parameters to templates. Let me say from...

C / C++

by: john | last post by:

Hi to All I would like to do something similar: class Class1<T1> { } class Class2<T2, T1where T2: Class1<T1>, new() {

Pointer to a generic type parameter

by: interX | last post by:

Hi I'm new in VC++ and have a question to generics. I have a generic class, which contains an array of the generic type. This array I can pin and then I would like to get an unmanaged pointer to...

.NET Framework

Execute Sql Statement Stored in Table Type Variable

by: hisham123 | last post by:

hi, I Wrote one stored procedure in which i declare one variable as Table then i stored in that table type variable select * from emp so now my table contain one sql statement by 4 rows...

Microsoft SQL Server

Why type parameter cannot used to call static method

by: Andrus | last post by:

I tried to use type parameter to call static method but got compile error 'T' is a 'type parameter', which is not valid in the given context. Why this is not allowed ? Should I really use a lot...

Executing Oracle Stored Procedures with IN Type Parameter.

by: debasisdas | last post by:

This thread contains some of the sample code showing the method of executing Oracle stored procedures and functions from VB . Hope the user finds them useful. Oracle Procedure with only IN...

Visual Basic 4 / 5 / 6

create table in a procedure

by: DottingTheNet | last post by:

help plz!! when i try to cretae a table in a procedure like create procedure blah is begin create table..... ...... end;

Oracle Database

Different generic type parameter in generic class constructorparameter

by: tadmill | last post by:

Is it possible to pass a generic parameter of the same class to to its constructor, where the "T" type passed in the constructor is different than the "T" type of the instanced class? ie, ...

C# How to check if null value exists in database table (using stored procedure)?

by: qwedster | last post by:

Folk! How to programattically check if null value exists in database table (using stored procedure)? I know it's possble in the Query Analyzer (see last SQL query batch statements)? But how...

Hyperconverged All-in-One Streaming Engine, ushering in new age for distributed database

by: antdb | last post by:

Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...

by: WisdomUfot | last post by:

It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...

FPGA Question

by: Oralloy | last post by:

Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...

One of the easiest ways to set the background color of Excel documents in Java

by: Carina712 | last post by:

Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...

Join our open community on Discord to learn programming, SEO, and marketing

by: BLUEPANDA | last post by:

At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...