Project: Project ( XPP 1. SOFTWAROV┴ s.r.o.)

Description: Interface Package for eXtreme Parallel Processing

Author: Petr Jezek

Version: 2.0.1

Date: 2012-09-18



The eXtreme Parallel Processing package is useful primary in tasks where is required parallel processing to use maximal CPU with minimal effort to rewrite current applications.

It can be also used like first aid in case of low performance PLSQL code.

The tool is using Advance Queue Messaging generic way.
Itĺs offering user friendly API for automatic creation/using/maintenance/delete of Advance queues.
Over the the Advance Queue Messaging is configurable parallel processing to split use fully power of more then one CPU.
There is diffeence between Transactinal Parallel Processing and eXtreme Parallel Processing in way of using transaction. Every request processed in eXtrem Parallel Processing is in single transaction.

The transactional behavior minimalize effort to rewrite current PLSQL code.


Example of using check on XPP example 1. SOFTWAROV┴ s.r.o.



Method Summary
 event_close( p_queue_name in varchar2 , p_silent in boolean default true )
          
           The procedure is responsible to close/stop processing of XPP in queue.
           The queue name is defined by parameter P_QUEUE_NAME.
           When is the value of the parameter P_SILENT set to true, then the procedure will raise while error exception on error while processing.
           Otherwise it raise exception on error while processing.
           Deafult value of P_SILENT is true.
          
          
 event_destroy( p_queue_name in varchar2 , p_silent in boolean default true )
          
           The procedure is responsible to destroy internal structures defined while EVENT_INITIATE.
           The queue name is defined by parameter P_QUEUE_NAME.
           When is the value of the parameter P_SILENT set to true, then the procedure will raise while error exception on error while processing.
           Otherwise it raise exception on error while processing.
           Deafult value of P_SILENT is true.
          
          
 event_initiate( p_queue_name in varchar2 , p_queue_type_body in varchar2 , p_storage_clause in varchar2 , p_queue_comments in varchar2 , p_user_to_grant in varchar2 : = null , p_type_name out varchar2 )
          
           The procedure is initiating XPP processing.
           It is responsible for creation required structures including input type for processing.
           The name of type is returned in ouput parameter P_TYPE_NAME (varchar2).
          
          
 event_open( p_queue_name in varchar2 , p_number_of_executors in number , p_procedure_to_run in varchar2 , p_procedure_on_success in varchar2 default null , p_procedure_on_error in varchar2 default null , p_param_on_error_input in number default 1 )
          
           The procedure is openning XPP for processing.
           It is responsible for creation configuration for processing.
           While running this procedure is set name number of required parallel level (p_number_of_executors),
           The value of parameter p_number_of_executors is set number of parallely runned tasks.
          


           The P_PROCEDURE_TO_RUN is name of procedure required for parallel processing (p_procedure_to_run).
           The procedure name should be including schema name and should be granted for execution by <xpp_owner> (The user/schema where is XPP installed.)
           This procedure will be executed in parallel.
          


           The P_PROCEDURE_ON_SUCCESS is name of procedure to be executed when procedure in P_PROCEDURE_TO_RUN successfuly finish processing.
           Default value is null. In case of null nothing executed while parallel run of procedure from variable P_PROCEDURE_TO_RUN.
           The procedure name should be including schema name and should be granted for execution by <xpp_owner> (The user/schema where is XPP installed.)
           Whenever is defined P_PROCEDURE_ON_SUCCESS (eg. '<XPP_USER>.<ON_SUCCESS>') not null, then is expected following defintion of procedure
           create or replace procedure <XPP_USER>.<ON_SUCCESS>(
           l_message <xpp_owner>.<type_name_get_while_event_init>
           )
           is
           pragma autonomous_transaction;
           begin
           ...
           -- For example
           update <my_table_of_requests>
           set <my_status> = 'done'
           where l_message.<my_identification_of_message>
           commit;
           ...
           end;
          


          


           The P_PROCEDURE_ON_ERROR is name of procedure to be executed when procedure in P_PROCEDURE_TO_RUN fail while processing.
           Default value is null. In case of null nothing executed while parallel run of procedure from variable P_PROCEDURE_TO_RUN.
           The procedure name should be including schema name and should be granted for execution by <xpp_owner> (The user/schema where is XPP installed.)
           Whenever is defined P_PROCEDURE_ON_ERROR (eg. '<XPP_USER>.<ON_ERROR>') not null, then is expected defintion of procedure <XPP_USER>.<ON_ERROR
           Paramater of procedure defined by variable P_PROCEDURE_ON_ERROR is effected by value of parameter P_PARAM_ON_ERROR_INPUT.
          


          


           The value of parameter P_PARAM_ON_ERROR_INPUT will effect the processing only when is set not null value of parameter P_PROCEDURE_ON_ERROR.
           When P_PARAM_ON_ERROR_INPUT is 1 then is expected defintion of procedure in as
           create or replace procedure <XPP_USER>.<ON_ERROR>(
           l_message <xpp_owner>.<type_name_get_while_event_init>
           )
           is
           pragma autonomous_transaction;
           begin
           ...
           -- For example
           update <my_table_of_requests>
           set <my_status> = 'Error'
           where l_message.<my_identification_of_message>
           commit;
           ...
           end;
          


           The using while processing can be following:
           declare
           p_message <xpp_owner>.<type_name_get_while_event_init>
           begin
           p_message <xpp_owner>.<type_name_get_while_event_init>(
           <type_body_defined_while_event_init>
           );
           p_procedure_on_error(p_message => <p_message>);
           ...
           end;
          


          


           When P_PARAM_ON_ERROR_INPUT is 2 then is expected defintion of procedure in as
           create or replace procedure <XPP_USER>.<ON_ERROR>(
           l_message <xpp_owner>.<type_name_get_while_event_init>
           ,p_sqlcode varchar2
           ,p_sqlterm varchar2
           )
           is
           pragma autonomous_transaction;
           begin
           ...
           -- For example
           update <my_table_of_requests>
           set <my_status> = substr('ERR: ORA-'||p_sqlcode||' p_sqlterm:'||p_sqlterm, 1, 4000)
           where l_message.<my_identification_of_message>
           commit;
           ...
           end;
          


           The using while processing can be following:
           When p_param_on_error_input is 2 then is expected defintion of procedure in as
           declare
           p_message <xpp_owner>.<type_name_get_while_event_init>
           l_sqlcode varchar2(100);
           l_sqlterm varchar2(4000);
           begin
           p_message <xpp_owner>.<type_name_get_while_event_init>(
           <type_body_defined_while_event_init>
           );
           p_procedure_on_error(p_message => <type_defined_while_event_init>,
           ,p_sqlcode => l_sqlcode
           ,p_sqlterm => l_sqlterm
           );
           ...
           end;
          


          


           When P_PARAM_ON_ERROR_INPUT is 3 then is expected defintion of procedure in as
           create or replace procedure <XPP_USER>.<ON_ERROR>(
           l_message <xpp_owner>.<type_name_get_while_event_init>
           ,p_sqlcode varchar2
           ,p_sqlterm varchar2
           ,p_backtrace varchar2
           )
           is
           pragma autonomous_transaction;
           begin
           ...
           -- For example
           update <my_table_of_requests>
           set <my_status> = substr('ERR: ORA-'||p_sqlcode||' p_sqlterm:'||p_sqlterm||' p_backtrace: '||p_backtrace, 1, 4000)
           where l_message.<my_identification_of_message>
           commit;
           ...
           end;
          


           When p_param_on_error_input is 3 then is expected defintion of procedure in as p_procedure_on_error(p_message => <type_defined_while_event_init>, varchar2, varchar2, varchar2)
           declare
           p_message <xpp_owner>.<type_name_get_while_event_init>
           l_sqlcode varchar2(100);
           l_sqlterm varchar2(4000);
           l_backtrace varchar2(4000);
           begin
           p_message <xpp_owner>.<type_name_get_while_event_init>(
           <type_body_defined_while_event_init>
           );
           p_procedure_on_error(p_message => <type_defined_while_event_init>,
           ,p_sqlcode => l_sqlcode
           ,p_sqlterm => l_sqlterm
           ,p_backtrace => l_backtrace
           );
           ...
           end;
           Otherwise p_param_on_error_input is like 1. Default vakue of p_param_on_error_input is 1.
          
          

 event_process( p_queue_name in varchar2 , p_record in long raw )
          
           The procedure is inserting request to be processed by XPP.
          
          
 event_process( p_queue_name in varchar2 , p_record in long raw , p_message_id out raw )
          
           The procedure is inserting request to be processed by XPP and return identification of the request.
          
          
 event_wait_processing_end( p_queue_name in varchar2 )
          
           The procedure is waiting till end of processing of XPP.
           It raise exception in case of error while processing.
          
          
 licence_id( p_company_name out varchar2 , p_invoice_id out varchar2 , p_paymenent_type out varchar2 , p_paymenent_date out varchar2 )
          
           The procedure is returning identification of official buyer of the XPP.
          
          

Method Detail

event_close

          event_close( p_queue_name in varchar2 , p_silent in boolean default true ) 
          
           The procedure is responsible to close/stop processing of XPP in queue.
           The queue name is defined by parameter P_QUEUE_NAME.
           When is the value of the parameter P_SILENT set to true, then the procedure will raise while error exception on error while processing.
           Otherwise it raise exception on error while processing.
           Deafult value of P_SILENT is true.
          
          
Parameters:
p_queue_name - The requested name of queue.
p_silent - The switch of silent mode.

event_destroy

          event_destroy( p_queue_name in varchar2 , p_silent in boolean default true ) 
          
           The procedure is responsible to destroy internal structures defined while EVENT_INITIATE.
           The queue name is defined by parameter P_QUEUE_NAME.
           When is the value of the parameter P_SILENT set to true, then the procedure will raise while error exception on error while processing.
           Otherwise it raise exception on error while processing.
           Deafult value of P_SILENT is true.
          
          
Parameters:
p_queue_name - The requested name of queue.
p_silent - The switch of silent mode.

event_initiate

          event_initiate( p_queue_name in varchar2 , p_queue_type_body in varchar2 , p_storage_clause in varchar2 , p_queue_comments in varchar2 , p_user_to_grant in varchar2 : = null , p_type_name out varchar2 ) 
          
           The procedure is initiating XPP processing.
           It is responsible for creation required structures including input type for processing.
           The name of type is returned in ouput parameter P_TYPE_NAME (varchar2).
          
          
Parameters:
p_queue_name - The requested name of queue.
p_queue_type_body - the body of type for encapsulation input for parallel procedure.
p_storage_clause - The storage clausule for creation of queue table. (It is usually for name of tablespace. Eg. 'tablespace my_small_tablespace')
p_queue_comments - The comments of name of queue.
p_user_to_grant - The list of users in coma separated format. The default value is null. In case of null is laoded current executor name and granted.
p_type_name - The generated name of input type parallel processing.

event_open

          event_open( p_queue_name in varchar2 , p_number_of_executors in number , p_procedure_to_run in varchar2 , p_procedure_on_success in varchar2 default null , p_procedure_on_error in varchar2 default null , p_param_on_error_input in number default 1 ) 
          
           The procedure is openning XPP for processing.
           It is responsible for creation configuration for processing.
           While running this procedure is set name number of required parallel level (p_number_of_executors),
           The value of parameter p_number_of_executors is set number of parallely runned tasks.
          


           The P_PROCEDURE_TO_RUN is name of procedure required for parallel processing (p_procedure_to_run).
           The procedure name should be including schema name and should be granted for execution by <xpp_owner> (The user/schema where is XPP installed.)
           This procedure will be executed in parallel.
          


           The P_PROCEDURE_ON_SUCCESS is name of procedure to be executed when procedure in P_PROCEDURE_TO_RUN successfuly finish processing.
           Default value is null. In case of null nothing executed while parallel run of procedure from variable P_PROCEDURE_TO_RUN.
           The procedure name should be including schema name and should be granted for execution by <xpp_owner> (The user/schema where is XPP installed.)
           Whenever is defined P_PROCEDURE_ON_SUCCESS (eg. '<XPP_USER>.<ON_SUCCESS>') not null, then is expected following defintion of procedure
           create or replace procedure <XPP_USER>.<ON_SUCCESS>(
           l_message <xpp_owner>.<type_name_get_while_event_init>
           )
           is
           pragma autonomous_transaction;
           begin
           ...
           -- For example
           update <my_table_of_requests>
           set <my_status> = 'done'
           where l_message.<my_identification_of_message>
           commit;
           ...
           end;
          


          


           The P_PROCEDURE_ON_ERROR is name of procedure to be executed when procedure in P_PROCEDURE_TO_RUN fail while processing.
           Default value is null. In case of null nothing executed while parallel run of procedure from variable P_PROCEDURE_TO_RUN.
           The procedure name should be including schema name and should be granted for execution by <xpp_owner> (The user/schema where is XPP installed.)
           Whenever is defined P_PROCEDURE_ON_ERROR (eg. '<XPP_USER>.<ON_ERROR>') not null, then is expected defintion of procedure <XPP_USER>.<ON_ERROR
           Paramater of procedure defined by variable P_PROCEDURE_ON_ERROR is effected by value of parameter P_PARAM_ON_ERROR_INPUT.
          


          


           The value of parameter P_PARAM_ON_ERROR_INPUT will effect the processing only when is set not null value of parameter P_PROCEDURE_ON_ERROR.
           When P_PARAM_ON_ERROR_INPUT is 1 then is expected defintion of procedure in as
           create or replace procedure <XPP_USER>.<ON_ERROR>(
           l_message <xpp_owner>.<type_name_get_while_event_init>
           )
           is
           pragma autonomous_transaction;
           begin
           ...
           -- For example
           update <my_table_of_requests>
           set <my_status> = 'Error'
           where l_message.<my_identification_of_message>
           commit;
           ...
           end;
          


           The using while processing can be following:
           declare
           p_message <xpp_owner>.<type_name_get_while_event_init>
           begin
           p_message <xpp_owner>.<type_name_get_while_event_init>(
           <type_body_defined_while_event_init>
           );
           p_procedure_on_error(p_message => <p_message>);
           ...
           end;
          


          


           When P_PARAM_ON_ERROR_INPUT is 2 then is expected defintion of procedure in as
           create or replace procedure <XPP_USER>.<ON_ERROR>(
           l_message <xpp_owner>.<type_name_get_while_event_init>
           ,p_sqlcode varchar2
           ,p_sqlterm varchar2
           )
           is
           pragma autonomous_transaction;
           begin
           ...
           -- For example
           update <my_table_of_requests>
           set <my_status> = substr('ERR: ORA-'||p_sqlcode||' p_sqlterm:'||p_sqlterm, 1, 4000)
           where l_message.<my_identification_of_message>
           commit;
           ...
           end;
          


           The using while processing can be following:
           When p_param_on_error_input is 2 then is expected defintion of procedure in as
           declare
           p_message <xpp_owner>.<type_name_get_while_event_init>
           l_sqlcode varchar2(100);
           l_sqlterm varchar2(4000);
           begin
           p_message <xpp_owner>.<type_name_get_while_event_init>(
           <type_body_defined_while_event_init>
           );
           p_procedure_on_error(p_message => <type_defined_while_event_init>,
           ,p_sqlcode => l_sqlcode
           ,p_sqlterm => l_sqlterm
           );
           ...
           end;
          


          


           When P_PARAM_ON_ERROR_INPUT is 3 then is expected defintion of procedure in as
           create or replace procedure <XPP_USER>.<ON_ERROR>(
           l_message <xpp_owner>.<type_name_get_while_event_init>
           ,p_sqlcode varchar2
           ,p_sqlterm varchar2
           ,p_backtrace varchar2
           )
           is
           pragma autonomous_transaction;
           begin
           ...
           -- For example
           update <my_table_of_requests>
           set <my_status> = substr('ERR: ORA-'||p_sqlcode||' p_sqlterm:'||p_sqlterm||' p_backtrace: '||p_backtrace, 1, 4000)
           where l_message.<my_identification_of_message>
           commit;
           ...
           end;
          


           When p_param_on_error_input is 3 then is expected defintion of procedure in as p_procedure_on_error(p_message => <type_defined_while_event_init>, varchar2, varchar2, varchar2)
           declare
           p_message <xpp_owner>.<type_name_get_while_event_init>
           l_sqlcode varchar2(100);
           l_sqlterm varchar2(4000);
           l_backtrace varchar2(4000);
           begin
           p_message <xpp_owner>.<type_name_get_while_event_init>(
           <type_body_defined_while_event_init>
           );
           p_procedure_on_error(p_message => <type_defined_while_event_init>,
           ,p_sqlcode => l_sqlcode
           ,p_sqlterm => l_sqlterm
           ,p_backtrace => l_backtrace
           );
           ...
           end;
           Otherwise p_param_on_error_input is like 1. Default vakue of p_param_on_error_input is 1.
          
          

Parameters:
p_queue_name - The requested name of queue.
p_number_of_executors - Number of required parallel processes to run.
p_procedure_to_run - The name of procedure to run in parallel
p_procedure_on_success - The name of procedure to be executed after sucessfull processing.
p_procedure_on_error - The name of procedure to be executed when exception while parallel processing raise.
p_param_on_error_input - The type of parameters for procedure configured in p_procedure_on_error. Possible values are 1 - only type is send, 2 - type, SQLCODE, SQLTERM is send, 3 - type, SQLCODE, SQLTERM, dbms_utility.format_error_backtrace is send

event_process

          event_process( p_queue_name in varchar2 , p_record in long raw ) 
          
           The procedure is inserting request to be processed by XPP.
          
          
Parameters:
p_queue_name - The requested name of queue.
p_record - The request to be proccessed by XPP.
p_message_id - The ouput identification of the request.

event_process

          event_process( p_queue_name in varchar2 , p_record in long raw , p_message_id out raw ) 
          
           The procedure is inserting request to be processed by XPP and return identification of the request.
          
          
Parameters:
p_queue_name - The requested name of queue.
p_record - The request to be proccessed by XPP.
p_message_id - The ouput identification of the request.

event_wait_processing_end

          event_wait_processing_end( p_queue_name in varchar2 ) 
          
           The procedure is waiting till end of processing of XPP.
           It raise exception in case of error while processing.
          
          
Parameters:
p_queue_name - The requested name of queue.
p_record - The request to be proccessed by XPP.
p_message_id - The ouput identification of the request.

licence_id

          licence_id( p_company_name out varchar2 , p_invoice_id out varchar2 , p_paymenent_type out varchar2 , p_paymenent_date out varchar2 ) 
          
           The procedure is returning identification of official buyer of the XPP.
          
          
Parameters:
p_company_name - The name of company.
p_invoice_id - The identification of invoice.
p_paymenent_type - The payment information.
p_paymenent_date - The date of payment.