O2OFramework/sql/master_payload_o2o.sql

0001 /*
0002  * master_payload_o2o.sql
0003  *
0004  * PL/SQL procedure to execute O2O procedure for object_name and do all general bookkeeping and logging.
0005  * Parameters:  object_name:  Name of the object/prefix of the procedure to execute O2O on
0006  *
0007  */
0008
0009 CREATE OR REPLACE PROCEDURE master_payload_o2o (
0010   i_object_name IN VARCHAR2
0011 )
0012 AS
0013
0014 schema VARCHAR2(32) := NULL;
0015 top_level_table VARCHAR2(32) := NULL;
0016 schema_table VARCHAR(65) := NULL;
0017 start_time TIMESTAMP := NULL;
0018 plsql_block VARCHAR2(256) := NULL;
0019 last_id NUMBER(10) := NULL;
0020 cnt1 NUMBER(10) := 0;
0021 cnt2 NUMBER(10) := 0;
0022
0023 BEGIN
0024   -- Check that <object_name>_payload_o2o() exists
0025
0026   -- Get the top level table
0027   BEGIN
0028     SELECT schema, top_level_table INTO schema, top_level_table
0029       FROM O2O_SETUP WHERE object_name = i_object_name;
0030   EXCEPTION
0031     WHEN NO_DATA_FOUND THEN
0032       raise_application_error(-20101, 'Object ' || i_object_name || ' not found in O2O_SETUP table');
0033   END;
0034
0035   schema_table := schema || '.' || top_level_table;
0036
0037   -- Get the count and ID variables
0038   BEGIN
0039     EXECUTE IMMEDIATE 'SELECT count(*) FROM ' || schema_table INTO cnt1;
0040     EXECUTE IMMEDIATE 'SELECT max(IOV_VALUE_ID) FROM ' || schema_table INTO last_id;
0041   EXCEPTION
0042     WHEN OTHERS THEN /* table not found */
0043       raise_application_error(-20102, 'Cannot read top-level-table ' || schema_table, TRUE);
0044   END;
0045
0046
0047   -- It is the first transfer, ensure ALL ids are transferred
0048   -- Use -1 because all IOV_VALUE_ID are > 0
0049   IF last_id IS NULL THEN
0050     last_id := -1;
0051   END IF;
0052
0053   -- Get the start time
0054   SELECT systimestamp INTO start_time FROM dual;
0055
0056   -- Execute transfer
0057   BEGIN
0058     plsql_block := 'BEGIN ' || schema || '.' || i_object_name || '_payload_o2o(:last_id); END;';
0059     EXECUTE IMMEDIATE plsql_block USING last_id;
0060   EXCEPTION
0061     WHEN OTHERS THEN /* procedure not found */
0062       raise_application_error(-20103, 'Cannot read ' || i_object_name || '_payload_o2o procedure', TRUE);
0063   END;
0064
0065   -- Get the count and ID variables again
0066   EXECUTE IMMEDIATE 'SELECT count(*) FROM ' || schema_table INTO cnt2;
0067   EXECUTE IMMEDIATE 'SELECT max(IOV_VALUE_ID) FROM ' || schema_table INTO last_id;
0068
0069   -- Write into O2O_LOG if there was a change
0070   IF cnt2 != cnt1 THEN
0071     INSERT INTO o2o_log (object_name, last_id, num_transferred, transfer_start, transfer_duration)
0072          VALUES (i_object_name, last_id, cnt2 - cnt1, start_time, start_time - systimestamp);
0073   END IF;
0074   COMMIT;
0075
0076 END;
0077 /
0078 show errors;