TEXT
package           tpp
/**
 * Project:         Project (<a href="http://en.1stsw.com/products/transactional-parallel-processing/">1. SOFTWAROVÁ s.r.o.</a>)<br/>
 * Description:     Interface Package for Transactional Parallel Processing<br/>
 * Author:          Petr Jezek<br/>
 * Version:         2.0.1<br/>
 * Date:            2012-09-18<br/>
 * <br/>
 * The Transactional 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 most interesting benefit is transactional behavior. It allows you rewrite only part where is required to use parallel processing without rewriting rest of the code. There's detection of COMMIT operation.<br/>
 * When is after the part of parallel processing in the main session COMMIT, then is while finalization provided COMMIT in all parallel processed sessions.<br/>
 * Otherwise is proceed after processing in all parallel sessions to execute ROLLBACK operation.<br/>
 * The transactional behavior minimalize effort to rewrite current PLSQL code.<br/>
 * </br>
 * Example of using: <br>
 * Let's assume we have current plsql code.<br>
 *<br/>
 * DECLARE<br/>
 *   X NUMBER:= 4;<br/>
 *   Y VARCHAR2(10) := 'TEST';<br/>
 * BEGIN<br/>
 *   FOR I IN (1..NODE_ID)<br/>
 *   LOOP<br/>
 *     -- BEGIN PROCESSING<br/>
 *     INSERT INTO TEST_TO_PROCESS (X, Y, NODE_ID) VALUES (TO_NUMBER(X), Y, NODE_ID);<br/>
 *     DBMS_LOCK.SLEEP(20);<br/>
 *     -- END PROCESSING<br/>
 *   END LOOP;<br/>
 * <br/>
 *   IF TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')) > 22 THEN<br/>
 *     -- EVERY PROCESSING AFTER 22 HOUR SHOULD BE ROLLED BACK<br/>
 *     ROLLBACK;<br/>
 *   ELSE<br/>
 *     -- OTHERWISE IS VALID AND CAN BE COMMITED<br/>
 *     COMMIT;<br/>
 *   END IF;<br/>
 * END;<br/>
 * /<br/>
 * <br/>
 * It can be using the Transactional Parallel Processing package rewrite following way.<br/>
 * 1) The processing part in the loop should be sparated into procedure.<br/>
 * 2) Grant the new procedure to Transactional Parallel Processing package. (We recommand to keep Transactional Parallel Processing in separate schema under separate user.)<br/>
 * 3) Rewite the processing.<br/>
 *<br/>
 * Add 1) Result will be following:<br/>
 * CREATE OR REPLACE PROCEDURE TO_EXECUTE (X IN NUMBER, Y IN VARCHAR2, NODE_ID IN NUMBER DEFAULT 0)<br/>
 * AS<br/>
 *   P VARCHAR2(100) := 'TO_EXECUTE';<br/>
 * BEGIN<br/>
 *   -- BEGIN PROCESSING<br/>
 *   INSERT INTO TEST_TO_PROCESS (X, Y, NODE_ID) VALUES (TO_NUMBER(X), Y, NODE_ID);<br/>
 *   DBMS_LOCK.SLEEP(20);<br/>
 *   -- END PROCESSING<br/>
 * EXCEPTION<br/>
 *   WHEN OTHERS THEN<br/>
 *   &&TPP_OWNER..TPP.ADD_LOG(P, 'ERR: '||TO_CHAR(SQLCODE)||' '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);<br/>
 * END TO_EXECUTE;<br/>
 * /<br/>
 *<br/>
 * Add 2) Grant of the procedure.<br/>
 * GRANT EXECUTE ON TO_EXECUTE TO &&TPP_OWNER;<br/>
 *<br/>
 * Add 2) Update current processing.<br/>
 * DECLARE<br/>
 *   L_TASK VARCHAR2(30) := 'TEST_TASK';<br/>
 *   L_SQL_EXECUTE_STMT VARCHAR2(32767);<br/>
 * BEGIN<br/>
 *<br/>
 *   -- PROCEDURE TO BE EXECUTE IN PARALLEL<br/>
 *   L_SQL_EXECUTE_STMT := 'BEGIN &&TPP_USER..TO_EXECUTE (1, ''TEST'', :NODE_ID); END;';<br/>
 *<br/>
 *   &&TPP_OWNER..TPP.INITIATE(<br/>
 *     P_TASK_NAME      => L_TASK<br/>
 *     ,P_COMMENT        => 'EXAMPLE OF PROCESSING BY THE TRANSACTIONAL PARALLEL PROCESSING'<br/>
 *     ,P_SQL_STMT       => L_SQL_EXECUTE_STMT<br/>
 *     ,P_LANGUAGE_FLAG  => DBMS_SQL.NATIVE<br/>
 *     ,P_PARALLEL_LEVEL => 4 -- THERE IS REQUIRED TO USE 4 PRALLEL INSTANES<br/>
 *   );<br/>
 *<br/>
 *   &&TPP_OWNER..TPP.WAIT_FOR_END_OF_PROCESSING(L_TASK);<br/>
 *<br/>
 *   IF TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')) > 22 THEN<br/>
 *     -- EVERY PROCESSING AFTER 22 HOUR SHOULD BE ROLLED BACK<br/>
 *     ROLLBACK;<br/>
 *   ELSE<br/>
 *     -- OTHERWISE IS VALID AND CAN BE COMMITED<br/>
 *     COMMIT;<br/>
 *   END IF;<br/>
 *<br/>
 *   &&TPP_OWNER..TPP.FINALIZATION(L_TASK);<br/>
 *<br/>
 * END;<br/>
 * /<br/>
 *<br/>
 * Note: For the example to demonstrate the transactinal behavior we define following table<br/>
 *<br/>
 * CREATE TABLE TEST_TO_PROCESS (<br/>
 *   X       NUMBER,<br/>
 *   Y       VARCHAR2(100),<br/>
 *   NODE_ID NUMBER<br/>
 * )<br/>
 * TABLESPACE &&SMALL_DATA;<br/>
 *<br/>
 * COMMENT ON TABLE TEST_TO_PROCESS IS<br/>
 * 'Test table to transction on TPP processing example'<br/>
 * ;<br/>
 * COMMENT ON COLUMN TEST_TO_PROCESS.X IS 'An value known as X number type';<br/>
 * COMMENT ON COLUMN TEST_TO_PROCESS.Y IS 'An value known as Y varchar type';<br/>
 * COMMENT ON COLUMN TEST_TO_PROCESS.NODE_ID IS 'Identification of node';<br/>
 *<br/>
 * Result of processing before 22 PM are records in table test_to_process.<br/>
 * See result of following command:<br/>
 *  SELECT * FROM TEST_TO_PROCESS;<br/>
 *<br/>
 * Result of processing before 22 PM is no record in table test_to_process.<br/>
 * See result of following command:<br/>
 *  SELECT * FROM TEST_TO_PROCESS;<br/>
 *<br/>
 * @headcom
 * @throws CHUNK_NOT_FOUND            ORA-29499       Specified chunk does not exist
 * @throws DUPLICATE_TASK_NAME        ORA-29497       Same task name has been used by an existing task
 * @throws INVALID_STATE_FOR_CHUNK    ORA-29492       Attempts to chunk a table that is not in CREATED or CHUNKING_FAILED state
 * @throws INVALID_STATE_FOR_REDSUME  ORA-29495       Attempts to resume execution, but the task is not in FINISHED_WITH_ERROR or CRASHED state
 * @throws INVALID_STATE_FOR_RUN      ORA-29494       Attempts to execute the task that is not in CHUNKED state
 * @throws INVALID_STATUS             ORA-29493       Attempts to set an invalid value to the chunk status
 * @throws INVALID_TABLE              ORA-29491       Attempts to chunk a table by rowid in cases in which the table is not a physical table, or the table is an IOT
 * @throws MISSING_ROLE               ORA-29490       User does not have the necessary ADM_PARALLEL_EXECUTE role
 * @throws TASK_NOT_FOUND             ORA-29498       Specified task_name does not exist
 * @throws INCONSIST_DATA             ORA-20001       When missing definition of any chunk. Value of p_parallel_level should be at least 1
 * @throws END_OF_EVALUATION_PERIOD   ORA-20002       When missing definition of any chunk. Value of p_parallel_level should be at least 1
 * @throws ERROR_WHILE_EXECUTION      ORA-20003       Error while execution of user defined procedure.Most likely is not the procedure granted for execution of TPP.
 * @throws ERROR_WHILE_COMMIT         ORA-20004       An error raise while processing commit of transansaction in subprocess. 
*/
is
    c_err_inconsist_data  constant PLS_INTEGER := -20001;
      err_inconsist_data  EXCEPTION;
    pragma exception_init(err_inconsist_data,     -20001);

    c_err_end_of_evaluation_period  constant PLS_INTEGER := -20002;
      err_end_of_evaluation_period  EXCEPTION;
    pragma exception_init(err_end_of_evaluation_period,     -20002);

    c_err_while_execution  constant PLS_INTEGER := -20003;
      err_while_execution  EXCEPTION;
    pragma exception_init(err_while_execution,     -20003);

    c_err_while_commit  constant PLS_INTEGER := -20004;
      err_while_commit  EXCEPTION;
    pragma exception_init(err_while_commit,     -20004);

/**
* Add user trace into table trace_log<br/>
* @param p_procedure  Name of procedure to better searching in the log
* @param p_message    Content of the message
*/
    procedure add_log (
      p_procedure in varchar2
     ,p_message   in varchar2
    );

/**
 * Initiate and run the Transactional Paralel Processsing
 * <br/>
 * @param p_task_name                   Name of the task to create
 * @param p_comment                     Comment of the task to easier search
 * @param p_sql_stmt                    Statement to parallel run for example begin '&&TPP_USER..to_execute (1, ''test'', :node_id); end;'
 * @param p_parallel_level              Number of parallel jobs; Should be number >= 1.
 *
 * @throws CHUNK_NOT_FOUND            ORA-29499       Specified chunk does not exist
 * @throws DUPLICATE_TASK_NAME        ORA-29497       Same task name has been used by an existing task
 * @throws INVALID_STATE_FOR_CHUNK    ORA-29492       Attempts to chunk a table that is not in CREATED or CHUNKING_FAILED state
 * @throws INVALID_STATE_FOR_REDSUME  ORA-29495       Attempts to resume execution, but the task is not in FINISHED_WITH_ERROR or CRASHED state
 * @throws INVALID_STATE_FOR_RUN      ORA-29494       Attempts to execute the task that is not in CHUNKED state
 * @throws INVALID_STATUS             ORA-29493       Attempts to set an invalid value to the chunk status
 * @throws INVALID_TABLE              ORA-29491       Attempts to chunk a table by rowid in cases in which the table is not a physical table, or the table is an IOT
 * @throws MISSING_ROLE               ORA-29490       User does not have the necessary ADM_PARALLEL_EXECUTE role
 * @throws TASK_NOT_FOUND             ORA-29498       Specified task_name does not exist
 * @throws INCONSIST_DATA             ORA-20001       When missing definition of any chunk. Value of p_parallel_level should be at least 1
 * @throws END_OF_EVALUATION_PERIOD   ORA-20002       When missing definition of any chunk. Value of p_parallel_level should be at least 1
 */

    procedure initiate (
      p_task_name      in varchar2
     ,p_comment        in varchar2
     ,p_sql_stmt       in clob
     ,p_parallel_level in number
    );

/**
 * Goal of the procedure is to cancel processing of the task and rollback all already processed parts.
 * <br/>
 * @param p_task_name                   Name of the task to create
 * @throws CHUNK_NOT_FOUND            29499       Specified chunk does not exist
 * @throws DUPLICATE_TASK_NAME        29497       Same task name has been used by an existing task
 * @throws INVALID_STATE_FOR_CHUNK    29492       Attempts to chunk a table that is not in CREATED or CHUNKING_FAILED state
 * @throws INVALID_STATE_FOR_REDSUME  29495       Attempts to resume execution, but the task is not in FINISHED_WITH_ERROR or CRASHED state
 * @throws INVALID_STATE_FOR_RUN      29494       Attempts to execute the task that is not in CHUNKED state
 * @throws INVALID_STATUS             29493       Attempts to set an invalid value to the chunk status
 * @throws INVALID_TABLE              29491       Attempts to chunk a table by rowid in cases in which the table is not a physical table, or the table is an IOT
 * @throws MISSING_ROLE               29490       User does not have the necessary ADM_PARALLEL_EXECUTE role
 * @throws TASK_NOT_FOUND             29498       Specified task_name does not exist
 * @throws INCONSIST_DATA             20001       When missing definition of any chunk. Value of p_parallel_level should be at least 1
*/
    procedure cancel_processing (
      p_task_name in varchar2
    );

/**
 * Goal of the procedure is to wait in the main session since the Transactional Parallel Processing will finish processing
 * <br/>
 * @param p_task_name                   Name of the task to create
 * @throws CHUNK_NOT_FOUND            29499       Specified chunk does not exist
 * @throws DUPLICATE_TASK_NAME        29497       Same task name has been used by an existing task
 * @throws INVALID_STATE_FOR_CHUNK    29492       Attempts to chunk a table that is not in CREATED or CHUNKING_FAILED state
 * @throws INVALID_STATE_FOR_REDSUME  29495       Attempts to resume execution, but the task is not in FINISHED_WITH_ERROR or CRASHED state
 * @throws INVALID_STATE_FOR_RUN      29494       Attempts to execute the task that is not in CHUNKED state
 * @throws INVALID_STATUS             29493       Attempts to set an invalid value to the chunk status
 * @throws INVALID_TABLE              29491       Attempts to chunk a table by rowid in cases in which the table is not a physical table, or the table is an IOT
 * @throws MISSING_ROLE               29490       User does not have the necessary ADM_PARALLEL_EXECUTE role
 * @throws TASK_NOT_FOUND             29498       Specified task_name does not exist
 * @throws INCONSIST_DATA             20001       When missing definition of any chunk. Value of p_parallel_level should be at least 1
*/

    procedure clean_task (
      p_task_name in varchar2
    );

/**
 * Goal of the procedure is remove task from queue and stop with rollback if it is required 
 * <br/>
 * @param p_task_name                   Name of the task to create
*/

    procedure wait_for_end_of_processing (
      p_task_name in varchar2
    );

/**
 * The procedure is detecting transactional bahvior in the main session.<br/>
 * When there is between initiate and finalization commit, then it commits work in each parallel session.<br/>
 * Otherwise is the detect rollback trancastion in parallel sesssions is rollbacking<br/>
 *<br/>
 * @param p_task_name                   Name of the task to create
 * @throws CHUNK_NOT_FOUND            29499       Specified chunk does not exist
 * @throws DUPLICATE_TASK_NAME        29497       Same task name has been used by an existing task
 * @throws INVALID_STATE_FOR_CHUNK    29492       Attempts to chunk a table that is not in CREATED or CHUNKING_FAILED state
 * @throws INVALID_STATE_FOR_REDSUME  29495       Attempts to resume execution, but the task is not in FINISHED_WITH_ERROR or CRASHED state
 * @throws INVALID_STATE_FOR_RUN      29494       Attempts to execute the task that is not in CHUNKED state
 * @throws INVALID_STATUS             29493       Attempts to set an invalid value to the chunk status
 * @throws INVALID_TABLE              29491       Attempts to chunk a table by rowid in cases in which the table is not a physical table, or the table is an IOT
 * @throws MISSING_ROLE               29490       User does not have the necessary ADM_PARALLEL_EXECUTE role
 * @throws TASK_NOT_FOUND             29498       Specified task_name does not exist
 * @throws ERROR_WHILE_EXECUTION      20003       Error while execution of user defined procedure.Most likely is not the procedure granted for execution of TPP.
*/
    procedure finalization (
      p_task_name in varchar2
    );

/**
 * The procedure is returning details of licence TPP<br/>
 *<br/>
 * @param p_company_name      Name of company licenced to use TPP
 * @param p_invoice_id        The ID of invoice
 * @param p_paymenent_type    Type of payment
 * @param p_paymenent_date    Date of payment
 * @throws ERROR_WHILE_COMMIT         20004       An error raise while processing commit of transansaction in subprocess. 
*/    
    procedure licence_id (
      p_company_name   out varchar2
     ,p_invoice_id     out varchar2
     ,p_paymenent_type out varchar2
     ,p_paymenent_date out varchar2
    );


end tpp;