SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Package errors

buggleboy007Jul 15 2021

Hi all,
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;  
 																			  	
/

This post has been answered by Alex Nuijten on Jul 15 2021

Jump to Answer

Frank Kulash

Hi, buggleboy007
As the error message says, l_transfer_id_aa is not a TYPE; it's variable of type transfer_id_aa.
If you have a procedure called proc_x that takes a NUMBER argument, and you plan to call it with a NUMBER variable named amt, you don't declare the procedure as

PROCEDURE proc_x (arg1  IN  amt)  -- WRONG!

No, you declare if with amt's type

PROCEDURE proc_x (arg1  IN  NUMBER)

It's the same with user-defined types.

1 person found this helpful

Alex Nuijten

Answer

l_transfer_id_aa

is a variable that you declared in the package.

Like the error says: it's not a datatype
Change the procedure to

 procedure accept_transfer_transferdetails( p_NbrInTransfer_id  IN  transfer_id_aa,  																			                    p_aaoutiriposdetails OUT lv_iripostransferdetails_aa 																			  	 );

and it should work

Marked as Answer by buggleboy007 · Jul 16 2021

1 person found this helpful

buggleboy007

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.
But if one of you wants to attempt correcting the error, you are welcome. Something new to learn.

Frank Kulash

Hi,
But if one of you wants to attempt correcting the error, you are welcome.
Didn't Alex and I already correct that?
l_transfer_id_aa is not a type; you can't say IN l_transfer_id_aa
transfer_id_aa is a type; you can say IN transfer_id_aa

buggleboy007

@frank-kulash : It's either your solution or Alex's solution that will work. As a matter of fact I am going ahead with yours because from technical standpoint was told that the transfer id will be one at a time and not multiples of numbers at the same time. That makes it a bit easier.
Once again thanks a lot for pointing out the error.

buggleboy007

@alex-nuijten : Yes, you are right. Your prescription does work. In fact I tried that too before posting my question here.
In other words - it's either your solution or Frank's solution that works.
Once again many thanks for pointing me in the right direction.

buggleboy007

Yes, you both did but the page had not refreshed at my end. Therefore I posted the above message.

1 - 7

Added on Jul 15 2021

7 comments

241 views

3 mentions

SQL & PL/SQL

Package errors

Comments

Post Details