Home » SQL & PL/SQL » SQL & PL/SQL » Issues with my Associative Array inside a Package spec (Oracle 12c, O/S: Windows 10 pro)
Issues with my Associative Array inside a Package spec [message #684631] Thu, 15 July 2021 09:56 Go to next message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
I am creating a package where in the INPUT parameter is also an associative array and so also the output parameter. When I compile I get the following error. What is the reason for this? Is it that the input parameter cannot be an array type?

My package specs is below the error.

SQL> SHOW ERRORS;
Errors for PACKAGE MERCH.POSTOMERCHDIRECTTRANSFER:
LINE/COL ERROR
-------- --------------------------------------------
70/73  PLS-00488: 'L_TRANSFER_ID_AA' must be a type
0/0   PL/SQL: Compilation unit analysis terminated
CREATE OR REPLACE PACKAGE POStoMerchDirectTransfer
IS 
--Global public variables
 TYPE transfer_id_aa IS TABLE OF NUMBER(20) INDEX BY PLS_INTEGER;
 l_transfer_id_aa transfer_id_aa;


 TYPE iripostransferdetails_rt IS RECORD
		       (Businessunitid       IRI_POS_TRANSFERS.business_unit_id%TYPE,
		       Transfer_id        IRI_POS_TRANSFERS.transfer_id%TYPE,  
		       transfer_occurence_type  IRI_POS_TRANSFERS.transfer_occurence_type%TYPE,       
		       line_no          IRI_POS_TRANSFERS.line_no%TYPE,               
		       prgid           IRI_POS_TRANSFERS.prgid%TYPE,                
		       transfer_type       IRI_POS_TRANSFERS.transfer_type%TYPE,            
		       request_id         IRI_POS_TRANSFERS.request_id%TYPE,             
		       from_site         IRI_POS_TRANSFERS.from_site%TYPE,              
		       to_site          IRI_POS_TRANSFERS.to_site%TYPE,               
		       bar_code_bus_unit_id    IRI_POS_TRANSFERS.bar_code_bus_unit_id%TYPE,        
		       bar_code_sub_type     IRI_POS_TRANSFERS.bar_code_sub_type%TYPE,          
		       bar_code_id        IRI_POS_TRANSFERS.bar_code_id%TYPE,             
		       style_id          IRI_POS_TRANSFERS.style_id%TYPE,              
		       color_id          IRI_POS_TRANSFERS.color_id%TYPE,              
		       dimension_id        IRI_POS_TRANSFERS.dimension_id%TYPE,            
		       valid_dimension_id     IRI_POS_TRANSFERS.valid_dimension_id%TYPE,         
		       size_id          IRI_POS_TRANSFERS.size_id%TYPE,               
		       valid_size_id       IRI_POS_TRANSFERS.valid_size_id%TYPE,            
		       item_qty          IRI_POS_TRANSFERS.item_qty%TYPE,              
		       valid_ind         IRI_POS_TRANSFERS.valid_ind%TYPE,              
		       status           IRI_POS_TRANSFERS.status%TYPE,               
		       site_id          IRI_POS_TRANSFERS.site_id%TYPE,               
		       valid_header        IRI_POS_TRANSFERS.valid_header%TYPE,            
		       valid_detail        IRI_POS_TRANSFERS.valid_detail%TYPE,            
		       reason_id         IRI_POS_TRANSFERS.reason_id%TYPE,              
		       transfer_origin_name    IRI_POS_TRANSFERS.transfer_origin_name%TYPE,        
		       from_site_retail_value   IRI_POS_TRANSFERS.from_site_retail_value%TYPE,       
		       to_site_retail_value    IRI_POS_TRANSFERS.to_site_retail_value%TYPE,        
		       unit_cost         IRI_POS_TRANSFERS.unit_cost%TYPE,              
		       expense_transfer_id    IRI_POS_TRANSFERS.expense_transfer_id%TYPE,         
		       rej_rpt_printed_ind    IRI_POS_TRANSFERS.rej_rpt_printed_ind%TYPE,         
		       rejected_id        IRI_POS_TRANSFERS.rejected_id%TYPE,             
		       reason_sub_type      IRI_POS_TRANSFERS.reason_sub_type%TYPE,           
		       process_status       IRI_POS_TRANSFERS.process_status%TYPE,           
		       process_date_time     IRI_POS_TRANSFERS.process_date_time%TYPE,          
		       richter_version_id     IRI_POS_TRANSFERS.richter_version_id%TYPE,         
		       pos_version_id       IRI_POS_TRANSFERS.pos_version_id%TYPE,           
		       control_no         IRI_POS_TRANSFERS.control_no%TYPE,             
		       user_trace_id       IRI_POS_TRANSFERS.user_trace_id%TYPE,            
		       user_id          IRI_POS_TRANSFERS.user_id%TYPE,               
		       ride_error_code      IRI_POS_TRANSFERS.ride_error_code%TYPE,           
		       severity_code       IRI_POS_TRANSFERS.severity_code%TYPE,            
		       inventory_adjust_id    IRI_POS_TRANSFERS.inventory_adjust_id%TYPE,         
		       from_business_unit_id   IRI_POS_TRANSFERS.from_business_unit_id%TYPE,        
		       to_business_unit_id    IRI_POS_TRANSFERS.to_business_unit_id%TYPE,         
		       cancel_ind         IRI_POS_TRANSFERS.cancel_ind%TYPE,             
		       pos_transfer_id      IRI_POS_TRANSFERS.pos_transfer_id%TYPE,           
		       date_created        IRI_POS_TRANSFERS.date_created%TYPE,            
		       process_date_created    IRI_POS_TRANSFERS.process_date_created%TYPE,        
		       ride_out_date       IRI_POS_TRANSFERS.ride_out_date%TYPE,            
		       transfer_date       IRI_POS_TRANSFERS.transfer_date%TYPE,            
		       user_in_date        IRI_POS_TRANSFERS.user_in_date%TYPE,            
		       --fkdomshipmentid     IRI_POS_TRANSFERS.fkdomshipmentid%TYPE,           
		       rma_code          IRI_POS_TRANSFERS.rma_code%TYPE,              
		       ref_dom_order_id      IRI_POS_TRANSFERS.web_order_id%TYPE  
         
		     );
		      
		     TYPE lv_iripostransferdetails_aa  IS TABLE OF iripostransferdetails_rt INDEX BY PLS_INTEGER;

	      
 procedure accept_transfer_transferdetails( p_NbrInTransfer_id  IN  l_transfer_id_aa,  																			                    p_aaoutiriposdetails OUT lv_iripostransferdetails_aa 																			  	 );    																			  	
END POStoMerchDirectTransfer;  
 																			  	
/ 							

Re: Issues with my Associative Array inside a Package spec [message #684632 is a reply to message #684631] Thu, 15 July 2021 10:13 Go to previous messageGo to next message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
Hello all,
Just ignore my question because the business requirement changed. The input parameter
p_NbrInTransfer_id  IN  l_transfer_id_aa
is no longer a type. It seems it will be passed one at a time and that has made it easier for me.

That said on OTN a couple of volunteers pointed me out in the correct direction (which is what I was suspecting).

[Updated on: Thu, 15 July 2021 10:26]

Report message to a moderator

Re: Issues with my Associative Array inside a Package spec [message #684648 is a reply to message #684632] Sat, 17 July 2021 00:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OTN link?

Re: Issues with my Associative Array inside a Package spec [message #684656 is a reply to message #684648] Sat, 17 July 2021 12:26 Go to previous message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
Michel Cadot wrote on Sat, 17 July 2021 00:12

OTN link?

https://community.oracle.com/tech/developers/discussion/4486827/package-errors#latest
Previous Topic: CASE in where Clause (4 merged)
Next Topic: Unable to get the PL/SQL block to work
Goto Forum:
  


Current Time: Thu Apr 18 05:10:15 CDT 2024