SQL & PL/SQL

Hi All,

Database Version: 11.2.0.3.0

I have the package below:

Package Spec:

I am trying to invoke the get warranty procedure of this package(seagrs_ext_ref_call_pkg) as below:

declare 
  l_parent_tbl     apps.seagrs_ext_ref_call_pkg.drive_tbl_type;
  -- l_child_sn_tbl   apps.seagrs_ext_ref_call_pkg.child_sn_tbl_type;
  l_tbl_type       apps.seagrs_ext_ref_call_pkg.pn_sn_tbl_type:=apps.seagrs_ext_ref_call_pkg.pn_sn_tbl_type();
  -- p_part_number varchar2(30):= '9HH131-188'; 
  -- p_serial_number varchar2(30):='5KH006N5';
  l_return_code number:=0;
  l_return_message varchar2(500):=null;
  l_1 number:=0;
BEGIN 
 apps.seagrs_ext_ref_call_pkg.get_warranty(l_tbl_type(NULL,NULL,'5KH006N5',NULL,NULL,NULL),NULL,l_parent_rec,l_return_code,l_return_message);    
  /*for i in l_parent_tbl.first ..l_parent_tbl.last  
  loop   
    dbms_output.put_line(l_parent_tbl(i).product_part_number);  
  end loop;
  for i in 1 .. l_parent_tbl.count 
  loop
    dbms_output.put_line(l_parent_tbl(i).serial_number);  
  end loop;*/
  dbms_output.put_line('The return code and error message are : '||l_return_code||'   '||l_return_message);
END;

I am getting a error as below, please advise

Error report:
ORA-06550: line 12, column 15:
PLS-00316: PL/SQL TABLEs must use a single index
ORA-06550: line 12, column 3:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.


CREATE OR REPLACE PACKAGE seagrs_ext_ref_call_pkg
AS

----------------------------
-- Define public constant --
----------------------------

   -- constant for maximum input for product and serial pair allowed in
   -- get_warranty call
   c_max_part_serial   CONSTANT PLS_INTEGER := 50;

------------------------
-- Define record type --
------------------------

   -- declare record type for input parameters use in get_warranty call
   TYPE pn_sn_rec_type IS RECORD
   ( model_part_number   VARCHAR2(32)
   , customer_number     VARCHAR2(32)
   , serial_number       VARCHAR2(32)
   , doa_date            DATE
   , return_country_code VARCHAR2(3)
   , enforce_part_number VARCHAR2(1)
   );

   -- declare table type for input parameters use in get_warranty call
   TYPE pn_sn_tbl_type IS TABLE OF pn_sn_rec_type;   

   -- declare table type for output result - drive's parent/child
   -- use in get_warranty call
   TYPE child_sn_tbl_type IS TABLE OF child_sn_rec_type;
   
------------------------------
-- Define public procedures --
------------------------------

   -- declare get_warranty procedure
   PROCEDURE get_warranty
   ( p_part_serial_tbl IN  seagrs_ext_ref_call_pkg.pn_sn_tbl_type
   , p_serial_type     IN OUT VARCHAR2
   , p_drive_tbl       OUT seagrs_ext_ref_call_pkg.drive_tbl_type
   , p_return_code     OUT PLS_INTEGER
   , p_return_message  OUT VARCHAR2
   );

   -- declare get_price procedure
   PROCEDURE get_price
   ( p_get_sma_price_rec IN OUT seagrs_ext_ref_call_pkg.get_sma_price_rec_type
   , p_return_code       OUT    PLS_INTEGER
   , p_return_message    OUT    VARCHAR2
   );

END seagrs_ext_ref_call_pkg;

Edited by: user2626293 on Apr 8, 2013 1:05 AM

Edited by: user2626293 on Apr 8, 2013 1:08 AM