Table type parameter to a procedure

2,367

Recognized Expert Top Contributor

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

New Member

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

Recognized Expert Top Contributor

Can you post the proceudre within your package for reference?

inv_lot_api_pub.update_inv_lot?

Mar 24 '08 #4

151

New Member

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

Recognized Expert Top Contributor

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

New Member

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

Recognized Expert Top Contributor

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

New Member

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

Recognized Expert Top Contributor

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

New Member

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

Recognized Expert Top Contributor

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

New Member

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

proposal for template type parameter constraint

2,367

Recognized Expert Top Contributor

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

Similar topics

2716

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

5446

Generics: Type parameter with type parameter

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

3415

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

3724

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

7635

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.

5848

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

802

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

3131

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)?

14641

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

Problem With Comparison Operator <=> in G++

7278

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

6991

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

Windows Server

7458

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

General

5578

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

Career Advice

4672

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...

Trying to create a lan-to-lan vpn between two differents networks

3167

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The...

Networking - Hardware / Configuration

1512

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...