469,343 Members | 5,478 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,343 developers. It's quick & easy.

Table type parameter to a procedure

151 100+
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
Mar 24 '08 #1
13 38608
amitpatel66
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
  1.  
  2. CREATE OR REPLACE PACKAGE test_table AS
  3. TYPE emp_data IS TABLE OF emp%ROWTYPE;
  4. END test_table;
  5. /
  6.  
  7. CREATE OR REPLACE PROCEDURE first_proc(a IN NUMBER, emp_dat IN OUT test_table.emp_data NOCOPY) AS
  8. BEGIN
  9. SELECT * BULK COLLECT INTO emp_dat FROM emp;
  10. END first_proc;
  11. /
  12.  
  13. DECLARE
  14. empd test_table.emp_data;
  15. num1 INTEGER;
  16. BEGIN
  17. first_proc(num1, empd);
  18. FOR I IN empd.FIRST..empd.LAST LOOP
  19. DBMS_OUTPUT.PUT_LINE(empd(i).empno);
  20. END LOOP;
  21. END;
  22. /
  23.  
  24.  

Note: I have not tested this code!!
Mar 24 '08 #2
vamsioracle
151 100+
Thank u ver much, i 'll try that but just check this code which i have written.
Expand|Select|Wrap|Line Numbers
  1.  
  2. CREATE OR REPLACE PROCEDURE XXBIOUK_STATUS_UPDT_PROC
  3. AS
  4. ===================table type=====================
  5. type v_attributes_category is table of varchar2(1000)
  6. index by binary_integer;
  7. type v_n_attributes_tbl is table of number
  8. index by binary_integer;
  9. type v_d_attributes_tbl is table of date
  10. index by binary_integer;
  11.  
  12. =========== table type ===
  13. v_a v_attributes_category;
  14. v_b v_attributes_category;
  15. v_c v_n_attributes_tbl;
  16. v_d v_d_attributes_tbl;
  17. =====================cursor==================
  18. CURSOR c_lotnumber_cur
  19.   IS
  20.  SELECT
  21.      organization_id,
  22.      inventory_item_id,
  23.      lot_number,
  24.      item_number,
  25.      attribute1,
  26.      expiration_date,
  27.      lot_status
  28.  FROM
  29.   xxbio_lotnumber_status
  30.  WHERE
  31.   org_group_name = 'USA'
  32.   OR org_group_name = 'UK'
  33.   AND lot_status = 'APPROVED'
  34.   OR lot_status = 'REJECT'
  35.   AND erp_extract_flag='N';
  36. ========================================
  37. BEGIN
  38. FOR ln_rec_counter IN c_lotnumber_cur
  39. LOOP
  40. =====calling a package.proc inside loop========
  41. inv_lot_api_pub.update_inv_lot
  42. (
  43. =======passing table type params to proc====
  44. v_a,
  45. v_b,
  46. v_c,
  47. v_d,
  48. 0;
  49. ==============error i get =======
  50.  
  51.  
  52.  PLS-00306: wrong number or types of arguments in call to
  53.  'UPDATE_INV_LOT'
  54.  

what might be the problem
Mar 24 '08 #3
amitpatel66
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
vamsioracle
151 100+
Expand|Select|Wrap|Line Numbers
  1.  
  2. PROCEDURE update_inv_lot(
  3.     x_return_status          OUT NOCOPY    VARCHAR2
  4.   , x_msg_count              OUT NOCOPY    NUMBER
  5.   , x_msg_data               OUT NOCOPY    VARCHAR2
  6.   , p_inventory_item_id      IN            NUMBER
  7.   , p_organization_id        IN            NUMBER
  8.   , p_lot_number             IN            VARCHAR2
  9.   , p_expiration_date        IN            DATE
  10.   , p_disable_flag           IN            NUMBER
  11.   , p_attribute_category     IN            VARCHAR2
  12.   , p_lot_attribute_category IN            VARCHAR2
  13.   , p_attributes_tbl         IN            inv_lot_api_pub.char_tbl
  14.   , p_c_attributes_tbl       IN            inv_lot_api_pub.char_tbl
  15.   , p_n_attributes_tbl       IN            inv_lot_api_pub.number_tbl
  16.   , p_d_attributes_tbl       IN            inv_lot_api_pub.date_tbl
  17.   , p_grade_code             IN            VARCHAR2
  18.   , p_origination_date       IN            DATE
  19.   , p_date_code              IN            VARCHAR2
  20.   , p_status_id              IN            NUMBER
  21.   , p_change_date            IN            DATE
  22.   , p_age                    IN            NUMBER
  23.   , p_retest_date            IN            DATE
  24.   , p_maturity_date          IN            DATE
  25.   , p_item_size              IN            NUMBER
  26.   , p_color                  IN            VARCHAR2
  27.   , p_volume                 IN            NUMBER
  28.   , p_volume_uom             IN            VARCHAR2
  29.   , p_place_of_origin        IN            VARCHAR2
  30.   , p_best_by_date           IN            DATE
  31.   , p_length                 IN            NUMBER
  32.   , p_length_uom             IN            VARCHAR2
  33.   , p_recycled_content       IN            NUMBER
  34.   , p_thickness              IN            NUMBER
  35.   , p_thickness_uom          IN            VARCHAR2
  36.   , p_width                  IN            NUMBER
  37.   , p_width_uom              IN            VARCHAR2
  38.   , p_territory_code         IN            VARCHAR2
  39.   , p_supplier_lot_number    IN            VARCHAR2
  40.   , p_vendor_name            IN            VARCHAR2
  41.   , p_source                 IN            NUMBER
  42.   ) IS
  43.  
Mar 24 '08 #5
amitpatel66
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
vamsioracle
151 100+
Expand|Select|Wrap|Line Numbers
  1.  
  2. inv_lot_api_pub.update_inv_lot
  3. (
  4. v_inventory_item_id,
  5. v_organization_id,
  6. v_lot_number,
  7. null,--p_expiration_date
  8. null,--p_disable_flag
  9. null,--p_attribute_category
  10. null,--p_lot_attribute_category
  11. v_a ,--p_attributes_tbl
  12. v_b,--p_c_attributes_tbl
  13. v_c,--p_n_attributes_tbl
  14. v_d,--p_d_attributes_tbl
  15. null,--p_grade_code
  16. null,--p_origination_date
  17. null,--p_date_code
  18. null,--p_status_id
  19. null,--p_change_date
  20. null,--p_age
  21. null,--p_retest_date
  22. null,--p_maturity_date
  23. null,--p_item_size
  24. null,--p_color
  25. null,--p_volume
  26. null,--p_volume_uom
  27. null,--p_place_of_origin
  28. null,--p_best_by_date
  29. null,--p_length
  30. null,--p_length_uom
  31. null,--p_recycled_content
  32. null,--p_thickness
  33. null,--p_thickness_uom
  34. null,--p_width
  35. null,--p_width_uom
  36. null,--p_territory_code
  37. null,--p_supplier_lot_number
  38. null,--p_vendor_name
  39. null--p_source
  40. );
  41.  
  42.  


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
amitpatel66
2,367 Expert 2GB
You need to pass a variables for the following OUT parameters as well:

Expand|Select|Wrap|Line Numbers
  1.  
  2. x_return_status          OUT NOCOPY    VARCHAR2
  3.   , x_msg_count              OUT NOCOPY    NUMBER
  4.   , x_msg_data               OUT NOCOPY    VARCHAR2
  5.  
  6.  
Mar 24 '08 #8
vamsioracle
151 100+
i created a table type variable as
Expand|Select|Wrap|Line Numbers
  1.  
  2. create or replace package my_pack as 
  3.  
  4. type v_attributes_category is table of varchar2(1000)
  5. index by binary_integer;
  6.  
  7. v_a v_attributes_category;
  8.  
  9. cusor c_cur is
  10. select  name_varchar_variable from xx_table where condition;
  11.  
  12. for i in c_cur
  13. loop
  14. begin
  15.  
  16. v_a = i.name_varchar;
  17.  
  18.  
  19. package.procedure(
  20. .....
  21. ....
  22. v_a,
  23. .....
  24. )
  25.  
  26.  

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
amitpatel66
2,367 Expert 2GB
i created a table type variable as
Expand|Select|Wrap|Line Numbers
  1.  
  2. create or replace package my_pack as 
  3.  
  4. type v_attributes_category is table of varchar2(1000)
  5. index by binary_integer;
  6.  
  7. v_a v_attributes_category;
  8.  
  9. cusor c_cur is
  10. select  name_varchar_variable from xx_table where condition;
  11.  
  12. for i in c_cur
  13. loop
  14. begin
  15.  
  16. v_a = i.name_varchar;
  17.  
  18.  
  19. package.procedure(
  20. .....
  21. ....
  22. v_a,
  23. .....
  24. )
  25.  
  26.  

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
vamsioracle
151 100+
please help me out , i am unable to solve


Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. 54/1     PL/SQL: Statement ignored
  4. 54/5     PLS-00382: expression is of wrong type
  5. 55/1     PL/SQL: Statement ignored
  6. 55/5     PLS-00382: expression is of wrong type
  7. 56/1     PL/SQL: Statement ignored
  8. 56/5     PLS-00382: expression is of wrong type
  9. 57/1     PL/SQL: Statement ignored
  10. 57/5     PLS-00382: expression is of wrong type
  11. 58/1     PLS-00306: wrong number or types of arguments in call to
  12.          'UPDATE_INV_LOT'
  13.  
  14. 58/1     PLS-00306: wrong number or types of arguments in call to
  15.          'UPDATE_INV_LOT'
  16.  
  17. 58/1     PLS-00306: wrong number or types of arguments in call to
  18.          'UPDATE_INV_LOT'
  19.  
  20. 58/1     PLS-00306: wrong number or types of arguments in call to
  21.          'UPDATE_INV_LOT'
  22.  
  23.  
  24. these are the errors i receive and my code is 
  25.  
  26.  
  27. CREATE OR REPLACE PROCEDURE XXBIOUK_STATUS_UPDT_PROC
  28. AS
  29. v_record_count number:=0;
  30. v_erp_extract_flag varchar2(1);
  31. x_return_status_out varchar2(10);
  32. x_msg_count_out number;
  33. x_msg_data_out varchar2(10);
  34. v_inventory_item_id xxbio_lotnumber_status.inventory_item_id %type;
  35. v_organization_id xxbio_lotnumber_status.organization_id  %type;
  36. v_lot_number xxbio_lotnumber_status.lot_number %type;
  37. ----------------------------------------to pass the value in the parameter as a record type
  38. type v_attributes_category is table of varchar2(1000)
  39. index by binary_integer;
  40. type v_n_attributes_tbl is table of number
  41. index by binary_integer;
  42. type v_d_attributes_tbl is table of date
  43. index by binary_integer;
  44. v_a v_attributes_category;
  45. v_b v_attributes_category;
  46. v_c v_n_attributes_tbl;
  47. v_d v_d_attributes_tbl;
  48.  CURSOR c_lotnumber_cur
  49.   IS
  50.  SELECT
  51.      organization_id,
  52.      inventory_item_id,
  53.      lot_number,
  54.      item_number,
  55.      attribute1,
  56.      expiration_date,
  57.      lot_status
  58.  FROM
  59.   xxbio_lotnumber_status
  60.  WHERE
  61.   org_group_name = 'USA'
  62.   OR org_group_name = 'UK'
  63.   AND lot_status = 'APPROVED'
  64.   OR lot_status = 'REJECT'
  65.   AND erp_extract_flag='N';
  66.  
  67. Begin
  68. FOR ln_rec_counter IN c_lotnumber_cur
  69. LOOP
  70. begin
  71. --------------------------------------------------------------------Assign the varibales
  72. v_inventory_item_id:=ln_rec_counter.inventory_item_id;
  73. v_organization_id:=ln_rec_counter.organization_id;
  74. v_lot_number:=ln_rec_counter.lot_number;
  75. v_a(ln_rec_counter) := ln_rec_counter.lot_status;
  76. v_b(ln_rec_counter) := ln_rec_counter.attribute1;
  77. v_c(ln_rec_counter) := ln_rec_counter.item_number;
  78. v_d(ln_rec_counter) := ln_rec_counter.expiration_date;
  79. inv_lot_api_pub.update_inv_lot
  80. (
  81. x_return_status_out,
  82. x_msg_count_out,
  83. x_msg_data_out,
  84. v_inventory_item_id,
  85. v_organization_id,
  86. v_lot_number,
  87. null,--p_expiration_date
  88. null,--p_disable_flag
  89. null,--p_attribute_category
  90. null,--p_lot_attribute_category
  91. v_a,--p_attributes_tbl
  92. v_b,--p_c_attributes_tbl
  93. V_c,--p_n_attributes_tbl
  94. v_d,--p_d_attributes_tbl
  95. null,--p_grade_code
  96. null,--p_origination_date
  97. null,--p_date_code
  98. null,--p_status_id
  99. null,--p_change_date
  100. null,--p_age
  101. null,--p_retest_date
  102. null,--p_maturity_date
  103. null,--p_item_size
  104. null,--p_color
  105. null,--p_volume
  106. null,--p_volume_uom
  107. null,--p_place_of_origin
  108. null,--p_best_by_date
  109. null,--p_length
  110. null,--p_length_uom
  111. null,--p_recycled_content
  112. null,--p_thickness
  113. null,--p_thickness_uom
  114. null,--p_width
  115. null,--p_width_uom
  116. null,--p_territory_code
  117. null,--p_supplier_lot_number
  118. null,--p_vendor_name
  119. null--p_source
  120. );
  121. END ;
  122. /
  123.  
  124.  

thank u
Mar 25 '08 #11
amitpatel66
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
  1.  
  2. declare
  3. v_a inv_lot_api_pub.char_tbl;
  4. BEGIN
  5. update_inv_lot(v_a);
  6. END;
  7.  
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
vamsioracle
151 100+
thank u very much, one final problem is
Expand|Select|Wrap|Line Numbers
  1.  
  2. 47/1     PL/SQL: Statement ignored
  3. 47/5     PLS-00382: expression is of wrong type
  4. 48/1     PL/SQL: Statement ignored
  5. 48/5     PLS-00382: expression is of wrong type
  6. 49/1     PL/SQL: Statement ignored
  7. 49/5     PLS-00382: expression is of wrong type
  8. 50/1     PL/SQL: Statement ignored
  9. 50/5     PLS-00382: expression is of wrong type
  10.  
  11. i get this error 
  12.  
  13. FOR ln_rec_counter IN c_lotnumber_cur
  14. LOOP
  15. begin
  16. --******Assign the varibales**********
  17. v_inventory_item_id:=ln_rec_counter.inventory_item_id;
  18. v_organization_id:=ln_rec_counter.organization_id;
  19. v_lot_number:=ln_rec_counter.lot_number;
  20. v_exp_date :=ln_rec_counter.expiration_date;
  21. v_a(ln_rec_counter):=ln_rec_counter.lot_status; ---------line 47
  22. v_b(ln_rec_counter):=ln_rec_counter.attribute1;------------line 48
  23. v_c(ln_rec_counter):=ln_rec_counter.item_number;-------------line 49
  24. v_d(ln_rec_counter):=ln_rec_counter.expiration_date;---------------line 50
  25. inv_lot_api_pub.update_inv_lot
  26. (
  27. x_return_status_out,
  28. x_msg_count_out,
  29. x_msg_data_out,
  30. v_inventory_item_id,
  31. v_organization_id,
  32. v_lot_number,
  33. v_exp_date,--p_expiration_date
  34. null,--p_disable_flag
  35. null,--p_attribute_category
  36. null,--p_lot_attribute_category
  37. v_a,--p_attributes_tbl
  38. v_b,--p_c_attributes_tbl
  39. V_c,--p_n_attributes_tbl
  40. v_d,--p_d_attributes_tbl
  41. null,--p_grade_code
  42. null,--p_origination_date
  43. null,--p_date_code
  44. null,--p_status_id
  45. null,--p_change_date
  46. null,--p_age
  47. null,--p_retest_date
  48. null,--p_maturity_date
  49. null,--p_item_size
  50. null,--p_color
  51. null,--p_volume
  52. null,--p_volume_uom
  53. null,--p_place_of_origin
  54. null,--p_best_by_date
  55. null,--p_length
  56. null,--p_length_uom
  57. null,--p_recycled_content
  58. null,--p_thickness
  59. null,--p_thickness_uom
  60. null,--p_width
  61. null,--p_width_uom
  62. null,--p_territory_code
  63. null,--p_supplier_lot_number
  64. null,--p_vendor_name
  65. null--p_source
  66. );
  67.  
  68.  
is there any problem with the way i am assigning values to the table type variables
Mar 25 '08 #13
amitpatel66
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

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by interX | last post: by
9 posts views Thread by DottingTheNet | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.