+11
Hi all,
When using an API such as Purchase_Order_API.User_Requisition_Line_To_Order(), the first 3 attributes are the newly created Order_No, Line_No and Release_No - but how do I capture them for subsequent use in the same code block?
Thanks in advance,
Ged.
+7
The spec for the procedure you are calling looks like,
PROCEDURE User_Requisition_Line_To_Order (
req_order_no_ IN OUT NOCOPY VARCHAR2,
assg_line_no_ IN OUT NOCOPY VARCHAR2,
assg_release_no_ IN OUT NOCOPY VARCHAR2,
req_info_ IN OUT NOCOPY VARCHAR2,
requisition_no_ IN VARCHAR2,
line_no_ IN VARCHAR2,
release_no_ IN VARCHAR2,
authorize_code_ IN VARCHAR2,
buyer_code_ IN VARCHAR2,
new_order_ IN VARCHAR2 DEFAULT NULL,
use_default_buyer_ IN VARCHAR2 DEFAULT NULL,
purchase_site_ IN VARCHAR2 DEFAULT NULL,
central_order_flag_ IN VARCHAR2 DEFAULT 'NOT CENTRAL ORDER',
check_approval_amount_ IN VARCHAR2 DEFAULT 'TRUE',
inquiry_no_ IN NUMBER DEFAULT NULL,
inquiry_line_ IN NUMBER DEFAULT NULL,
init_po_no_ IN NUMBER DEFAULT NULL )
1) IN type parameter: These types of parameters are used to send values to stored procedures.
2) OUT type parameter: These types of parameters are used to get values from stored procedures. This is similar to a return type in functions.
3) IN OUT parameter: These types of parameters are used to send values and get values from stored procedures.
NOTE: If a parameter is not explicitly defined a parameter type, then by default it is an IN type parameter.
Once the procedure call returns to your procedure code you should see that the parameters may have new values (depends on what you passed in). There’s nothing else you need to do to assign new values. If you called the procedure using a parameter named order_no_, then you can use that parameter after the call returns.
+20
The spec for the procedure you are calling looks like,
PROCEDURE User_Requisition_Line_To_Order (
req_order_no_ IN OUT NOCOPY VARCHAR2,
assg_line_no_ IN OUT NOCOPY VARCHAR2,
assg_release_no_ IN OUT NOCOPY VARCHAR2,
req_info_ IN OUT NOCOPY VARCHAR2,
requisition_no_ IN VARCHAR2,
line_no_ IN VARCHAR2,
release_no_ IN VARCHAR2,
authorize_code_ IN VARCHAR2,
buyer_code_ IN VARCHAR2,
new_order_ IN VARCHAR2 DEFAULT NULL,
use_default_buyer_ IN VARCHAR2 DEFAULT NULL,
purchase_site_ IN VARCHAR2 DEFAULT NULL,
central_order_flag_ IN VARCHAR2 DEFAULT 'NOT CENTRAL ORDER',
check_approval_amount_ IN VARCHAR2 DEFAULT 'TRUE',
inquiry_no_ IN NUMBER DEFAULT NULL,
inquiry_line_ IN NUMBER DEFAULT NULL,
init_po_no_ IN NUMBER DEFAULT NULL )
1) IN type parameter: These types of parameters are used to send values to stored procedures.
2) OUT type parameter: These types of parameters are used to get values from stored procedures. This is similar to a return type in functions.
3) IN OUT parameter: These types of parameters are used to send values and get values from stored procedures.
NOTE: If a parameter is not explicitly defined a parameter type, then by default it is an IN type parameter.
Once the procedure call returns to your procedure code you should see that the parameters may have new values (depends on what you passed in). There’s nothing else you need to do to assign new values. If you called the procedure using a parameter named order_no_, then you can use that parameter after the call returns.
Just adding some finishing touches to
NOCOPY hint tells that this IN OUT parameter is passed by reference.
Default setting for IN OUT parameter is ‘pass by value’ and if you are dealing with large objects and data structures, it will have a huge performance overhead to pass parameter by value.
Instead, when using NOCOPY, parameter is ‘pass by reference’ and compiler sends the pointer to the memory location of the parameter which cuts the overhead of memory and processing to copy value.
Simple code sample on how to use this in code:
DECLARE
req_order_no_ VARCHAR2(100);
assg_line_no_ VARCHAR2(100);
assg_release_no_ VARCHAR2(100);
req_info_ VARCHAR2(100);
--other IN parameters
BEGIN
dbms_output.put_line('Previous req_order_no_ ' || req_order_no_);
dbms_output.put_line('Previous ssg_line_no_ ' || assg_line_no_);
dbms_output.put_line('Previous assg_release_no_ ' || assg_release_no_);
dbms_output.put_line('Previous req_info_ ' || req_info_);
Purchase_Order_API.User_Requisition_Line_To_Order(req_order_no_ IN OUT NOCOPY VARCHAR2,
assg_line_no_ IN OUT NOCOPY VARCHAR2,
assg_release_no_ IN OUT NOCOPY VARCHAR2,
req_info_ IN OUT NOCOPY VARCHAR2,
other IN params);
dbms_output.put_line('New req_order_no_ ' || req_order_no_);
dbms_output.put_line('New assg_line_no_ ' || assg_line_no_);
dbms_output.put_line('New assg_release_no_ ' || assg_release_no_);
dbms_output.put_line('New req_info_ ' || req_info_);
END;
Cheers!
Damith
+11
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.