We usually use cursor for loops to process data.(i.e declare a cursor, open it, fetch from it row by row in a loop and process the row they fetch) statements in plsql programs causes a context switch between the plsql engine and the sql engine.Too many context switches may degrade performance dramatically.
In order to reduce the number of these context switches we can use bulk collecting feature
Bulk collecting lets us to transfer rows between the sql engine and the plsql engine as collections.
Bulk collecting is available for select, insert, delete and update statements.
Below are some examples:
create table BULK_COLLECT_TEST as select * from PER_ALL_PEOPLE_F;
Table created.
insert into BULK_COLLECT_TEST
select * from BULK_COLLECT_TEST;
20000 rows created.
--BLOCK1:Using Loops
declare
cursor c1
is select object_name from BULK_COLLECT_TEST;
rec1 c1%rowtype;
begin
open c1;
loop
fetch c1 into rec1;
exit when c1%notfound;
null;
end loop;
end;
total Elapsed Time is : 45 Secs
--BLOCK2: Using Bulk Collecting
declare
cursor c1 is select object_name from BULK_COLLECT_TEST;
type c1_type is table of c1%rowtype;
rec1 c1_type;
begin
open c1;
fetch c1 bulk collect into rec1;
end;
total Elapsed Time is : 5 Sec
So bulk collecting the rows shows a huge performance improvement over fetching row by row.
Some cases there are many rows to process, we can limit the number of rows to bulk collect, process those rows and fetch again.
Otherwise process memory gets bigger and bigger as you fetch the rows.
--Bulk Collect Example using LIMIT :
declare
cursor c1 is select object_name from BULK_COLLECT_TEST;
type c1_type is table of c1%rowtype;
rec1 c1_type;
begin
open c1;
loop
fetch c1 bulk collect into rec1 limit 200;
for i in 1..rec1.count loop
null;
end loop;
exit when c1%notfound;
end loop;
end;
For each Concurrent program we must use mandatory parameters : ERRBUF and RETCODE.
ERRBUF:It return the error message. For you program if you get any error in exception block you can assign the error message to this parameter. This error message you can see after concurrent program run go to details button it will open details in that Completion Text filed will show your errbuf. RETCODE: This parameter returns the status of the concurrent program. 0- Success --Completed 1- Warning -- Yellow color 2- Error -- Red
These parameters we call as a first parameters for the program.
Ex: Create procedure CProgram(ERRBUF out varchar2, RETCODE out varchar2, person_id in NUmber) as begin <Declaration Goes Here> begin <Code Block1 Here>
exception when no_data_found then retcode := 1; errbuf:= 'RetCode : Warning'; end; ... <Code Block2 Here> ... retcode:= 0; commit; exception when others then
Order Management Tables. Entered oe_order_headers_all 1 record created in header table oe_order_lines_all Lines for particular records oe_price_adjustments When discount gets applied oe_order_price_attribs If line has price attributes then populated oe_order_holds_all If any hold applied for order like credit check etc. Booked oe_order_headers_all Booked_flag=Y Order booked. wsh_delivery_details Released_status Ready to release Pick Released wsh_delivery_details Released_status=Y Released to Warehouse (Line has been released to Inventory for processing) wsh_picking_batches After batch is created for pick release. mtl_reservations This is only soft reservations. No physical movement of stock Full Transaction mtl_material_transactions No records in mtl_material_transactions mtl_txn_request_headers mtl_txn_request_lines wsh_delivery_details Released to warehouse. wsh_new_deliveries if Auto-Create is Yes then data populated. wsh_delivery_assignments deliveries get assigned Pick Confirmed wsh_delivery_details Released_status=Y Hard Reservations. Picked the stock. Physical movement of stock Ship Confirmed wsh_delivery_details Released_status=C Y To C:Shipped ;Delivery Note get printed Delivery assigned to trip stopquantity will be decreased from staged mtl_material_transactions On the ship confirm form, check Ship all box wsh_new_deliveries If Defer Interface is checked I.e its deferred then OM & inventory not updated. If Defer Interface is not checked.: Shipped oe_order_lines_all Shipped_quantity get populated. wsh_delivery_legs 1 leg is called as 1 trip.1 Pickup & drop up stop for each trip. oe_order_headers_all If all the lines get shipped then only flag N Autoinvoice wsh_delivery_details Released_status=I Need to run workflow background process. ra_interface_lines_all Data will be populated after wkfw process. ra_customer_trx_all After running Autoinvoice Master Program for ra_customer_trx_lines_all specific batch transaction tables get populated Price Details qp_list_headers_b To Get Item Price Details. qp_list_lines Items On Hand Qty mtl_onhand_quantities TO check On Hand Qty Items. Payment Terms ra_terms Payment terms AutoMatic Numbering System ar_system_parametes_all you can chk Automactic Numbering is enabled/disabled. Customer Information hz_parties Get Customer information include name,contacts,Address and Phone hz_party_sites hz_locations hz_cust_accounts hz_cust_account_sites_all hz_cust_site_uses_all ra_customers Document Sequence fnd_document_sequences Document Sequence Numbers fnd_doc_sequence_categories fnd_doc_sequence_assignments Default rules for Price List oe_def_attr_def_rules Price List Default Rules oe_def_attr_condns ak_object_attributes End User Details csi_t_party_details To capture End user Details Sales Credit Sales Credit Information(How much credit can get) oe_sales_credits Attaching Documents fnd_attached_documents Attched Documents and Text information fnd_documents_tl fnd_documents_short_text Blanket Sales Order oe_blanket_headers_all Blanket Sales Order Information. oe_blanket_lines_all Processing Constraints oe_pc_assignments Sales order Shipment schedule Processing Constratins oe_pc_exclusions Sales Order Holds oe_hold_definitions Order Hold and Managing Details. oe_hold_authorizations oe_hold_sources_all oe_order_holds_all Hold Relaese oe_hold_releases_all Hold released Sales Order. Credit Chk Details oe_credit_check_rules To get the Credit Check Againt Customer. Cancel Orders oe_order_lines_all Cancel Order Details
Summary: in this tutorial, you will learn how to create a simple PL/SQL package that is a group of related functions, procedures, types, etc.
Introducing to PL/SQL Package
PL/SQL package is a group of related functions, procedures, types, cursors, etc. PL/SQL package is like a library once written stored in the Oracle database and can be used by many applications.
A PL/SQL package has two parts: package specification and package body.
A package specification is the public interface of your applications. The public means the stored function, procedures, types, etc., are accessible from other applications.
A package body contains the code that implements the package specification.
PL/SQL Package
Creating PL/SQL Package Specification
The package specification is required when you create a new package. The package specification lists all the objects which are publicly accessible from other applications. The package specification also provides the information that developers need to know in order to use the interface. In short, package specification is the package’s API.
If the package specification does not contain any stored functions, procedures and no private code is needed, you don’t need to have a package body. These packages may contain only type definition and variables declaration. Those variables are known as package data. The scope of package data is global to applications. It is recommended that you should hide as much as package data as possible and use get and set functions to read and write that data. By doing this, you can prevent your package data changed unintentionally.
It is important to note that you must compile the package specification before package body.
Here is the syntax of creating PL/SQL package specification:
1
2
3
4
5
6
7
8
9
10
11
CREATE[ORREPLACE]PACKAGEpackage_name
[AUTHID{CURRENT_USER|DEFINER}]
{IS|AS}
[definitionsofpublicTYPES
,declarationsofpublicvariables,types,andobjects
,declarationsofexceptions
,pragmas
,declarationsofcursors,procedures,andfunctions
,headersofproceduresandfunctions]
END[package_name];
The CREATE PACKAGE statement is used to define a new package specification. If you want to rebuild existing package you can use the REPLACE keyword instead of the CREATE keyword. In the package specification, you can define new types, declare global variables, types, objects, exceptions, cursors, procedures, and functions.
Below is an example of creating a new package specification called personnel. The personnelpackage contains two functions: get_fullname() and get_salary() based on employee’s ID.
1
2
3
4
5
6
7
8
CREATEORREPLACEPACKAGEpersonnelAS
-- get employee's fullname
FUNCTIONget_fullname(n_emp_idNUMBER)
RETURNVARCHAR2;
-- get employee's salary
FUNCTIONget_salary(n_emp_idNUMBER)
RETURNNUMBER;
ENDpersonnel;
Creating PL/SQL Package Body
PL/SQL package body contains all the code that implements stored functions, procedures, and cursors listed in the package specification.
The following illustrates the syntax of creating package body:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE[ORREPLACE]PACKAGEBODYpackage_name
{IS|AS}
[definitionsofprivateTYPEs
,declarationsofprivatevariables,types,andobjects
,fulldefinitionsofcursors
,fulldefinitionsofproceduresandfunctions]
[BEGIN
sequence_of_statements
[EXCEPTION
exception_handlers]]
END[package_name];
The syntax is similar to the package specification except the keyword BODY and the implemented code of package specification.
The package body can contain an optional package initialization section that appears at the end of the package body. The package initialization sections start with the BEGIN keyword and end with the EXCEPTION section or END keyword of the package. The package initialization section is executed when the application references to the package element at the first time.
The following illustrates package body of the personnel package:
