package xpp is
/**
* Project: Project (<a href="http://en.1stsw.com/products/extreme-parallel-processing/"> XPP 1. SOFTWAROVÁ s.r.o.</a>)<br/>
* Description: Interface Package for eXtreme Parallel Processing<br/>
* Author: Petr Jezek<br/>
* Version: 2.0.1<br/>
* Date: 2012-09-18<br/>
* <br/>
* 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.<br/>
* It can be also used like first aid in case of low performance PLSQL code.<br/>
* 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.<br/>
* The transactional behavior minimalize effort to rewrite current PLSQL code.<br/>
* <p>
* Example of using check on <a href="http://en.1stsw.com/TPP-example/">XPP example 1. SOFTWAROVÁ s.r.o.</a>
* <p>
*/
c_err_get_curr_schema_name constant PLS_INTEGER := -20001;
c_err_get_user_schema_name constant PLS_INTEGER := -20002;
c_err_type_not_found constant PLS_INTEGER := -20003;
c_err_queue_name constant PLS_INTEGER := -20004;
c_err_creation_queue_table constant PLS_INTEGER := -20005;
c_err_deletion_queue_table constant PLS_INTEGER := -20006;
c_err_creation_queue constant PLS_INTEGER := -20007;
c_err_deletion_queue constant PLS_INTEGER := -20008;
c_err_creation_subscriber constant PLS_INTEGER := -20009;
c_err_deletion_subscriber constant PLS_INTEGER := -20010;
c_err_creation_notif_proc constant PLS_INTEGER := -20011;
c_err_deletion_notif_proc constant PLS_INTEGER := -20012;
c_err_task_processing constant PLS_INTEGER := -20013;
c_err_queue_is_not_defined constant PLS_INTEGER := -20014;
c_err_processing_end constant PLS_INTEGER := -20015;
c_err_number_of_executors constant PLS_INTEGER := -20016;
c_err_subscr_conf_exists constant PLS_INTEGER := -20017;
c_err_while_add_subscr_conf constant PLS_INTEGER := -20018;
c_err_error_while_run_parallel constant PLS_INTEGER := -20019;
c_err_task_not_exists constant PLS_INTEGER := -20020;
c_err_end_of_evaluation_period constant PLS_INTEGER := -20021;
/**
* 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).
*
* @param p_queue_name The requested name of queue.
* @param p_queue_type_body the body of type for encapsulation input for parallel procedure.
* @param p_storage_clause The storage clausule for creation of queue table. (It is usually for name of tablespace. Eg. 'tablespace my_small_tablespace')
* @param p_queue_comments The comments of name of queue.
* @param 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.
* @param p_type_name The generated name of input type parallel processing.
*
* @throw xpp.c_err_queue_name The queue name is forbidden.
* @throw xpp.c_err_creation_queue_table The table is not created or is not created in expected time.
* @throw xpp.c_err_creation_queue The queue is not created or is not created in expected time.
* @throw xpp.c_err_creation_subscriber The subscriber is not created or is not created in expected time.
*/
procedure 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 -- list of users in coma separated format.When null then is selected actual executor UID.
,p_type_name out varchar2
);
/**
* 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.
* <p>
* 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.
* <p>
* 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;
* <p>
* <p>
* 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.
* <p>
* <p>
* 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;
* <p>
* 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;
* <p>
* <p>
* 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;
* <p>
* 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;
* <p>
* <p>
* 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;
* <p>
* 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.
*
* @param p_queue_name The requested name of queue.
* @param p_number_of_executors Number of required parallel processes to run.
* @param p_procedure_to_run The name of procedure to run in parallel
* @param p_procedure_on_success The name of procedure to be executed after sucessfull processing.
* @param p_procedure_on_error The name of procedure to be executed when exception while parallel processing raise.
* @param 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
*
* @throw xpp.c_err_queue_name The queue name is forbidden.
* @throw xpp.c_err_creation_queue_table The table is not created or is not created in expected time.
* @throw xpp.c_err_creation_queue The queue is not created or is not created in expected time.
* @throw xpp.c_err_creation_subscriber The subscriber is not created or is not created in expected time.
*/
procedure 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 inserting request to be processed by XPP.
*
* @param p_queue_name The requested name of queue.
* @param p_record The request to be proccessed by XPP.
* @param p_message_id The ouput identification of the request.
*
* @throw xpp.c_err_queue_name The queue name is forbidden.
*/
procedure event_process(
p_queue_name in varchar2
,p_record in long raw
);
/**
* The procedure is inserting request to be processed by XPP and return identification of the request.
*
* @param p_queue_name The requested name of queue.
* @param p_record The request to be proccessed by XPP.
* @param p_message_id The ouput identification of the request.
*
* @throw xpp.c_err_queue_name The queue name is forbidden.
*/
procedure event_process(
p_queue_name in varchar2
,p_record in long raw
,p_message_id out raw
);
/**
* The procedure is waiting till end of processing of XPP.
* It raise exception in case of error while processing.
*
* @param p_queue_name The requested name of queue.
* @param p_record The request to be proccessed by XPP.
* @param p_message_id The ouput identification of the request.
*
* @throw xpp.c_err_queue_name The queue name is forbidden.
* @throw xpp.c_err_task_processing The Error created while running parallel task.
*/
procedure event_wait_processing_end(
p_queue_name in varchar2
);
/**
* 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.
*
* @param p_queue_name The requested name of queue.
* @param p_silent The switch of silent mode.
*
* @throw xpp.c_err_queue_name The queue name is forbidden.
*/
procedure event_close(
p_queue_name in varchar2
,p_silent in boolean default true -- when is true then it doesn''t raise exception when object is not exists
);
/**
* 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.
*
* @param p_queue_name The requested name of queue.
* @param p_silent The switch of silent mode.
*
* @throw ORA-04043 The object (queue table or type or package) not exists.
* @throw ORA-02289 The sequence not exists.
* @throw xpp.c_err_queue_name The queue name is forbidden.
* @throw xpp.c_err_subscriber_not_exists The subscriber not exists.
* @throw xpp.c_err_deletion_queue_table Error while deleting of queue table.
*
*/
procedure event_destroy(
p_queue_name in varchar2
,p_silent in boolean default true -- when is true then it doesn''t raise exception when object is not exists
);
/**
* The procedure is returning identification of official buyer of the XPP.
*
* @param p_company_name The name of company.
* @param p_invoice_id The identification of invoice.
* @param p_paymenent_type The payment information.
* @param p_paymenent_date The date of payment.
*/
procedure licence_id (
p_company_name out varchar2
,p_invoice_id out varchar2
,p_paymenent_type out varchar2
,p_paymenent_date out varchar2
);
end xpp; |