SQL & PL/SQL

hi

I need a simple piece of code to be included in this procedure.

in the BEGIN block insert should be performed after validating the count ABAINTERMEDIATE table count is zero. DELETE should be executed after insert. I hope this is alreay there in the code otherwise. Consider INTERMEDIATE_PROCESS is the source table. ABAINTERMEDIATE is the target table.

Please help me on that too.

Thanks
Raj


CREATE OR REPLACE PROCEDURE ABA.inter_process
AS
-- Declare variables to hold information about the first 350 instances.
v_INSTANCEID INTERMEDIATE_PROCESS.INSTANCEID%TYPE;
v_PROCESSKEY INTERMEDIATE_PROCESS.PROCESSKEY%TYPE;
v_AUDITTIMESTAMP INTERMEDIATE_PROCESS.AUDITTIMESTAMP%TYPE;
v_STATUS INTERMEDIATE_PROCESS.STATUS%TYPE;
v_current VARCHAR2 (600);
v_sql_error VARCHAR2 (600);

-- Cursor to retrieve the information about first 350 records
CURSOR c_first500
IS
SELECT INSTANCEID, PROCESSKEY, AUDITTIMESTAMP, STATUS
FROM ( SELECT INSTANCEID, PROCESSKEY, AUDITTIMESTAMP, STATUS from INTERMEDIATE_PROCESS order by audittimestamp) where rownum<351;
BEGIN
v_current := 'BEFORE CURSOR OPENING';

OPEN c_first500; -- Open the cursor and initialize the active set

LOOP
-- Retrieve information for first 350 records.

FETCH c_first500
INTO v_INSTANCEID, v_PROCESSKEY, v_AUDITTIMESTAMP, v_STATUS;

EXIT WHEN c_first500%NOTFOUND;

-- Exit loop when there are no more rows to fetch
-- Process the fetched rows, retwaintermediate just copy of twaintermediate
BEGIN
v_current := 'INSERT INTO RETWAINTERMEDIATE';

INSERT INTO ABAINTERMEDIATE (INSTANCEID,
PROCESSKEY,
AUDITTIMESTAMP,
STATUS)
VALUES (v_INSTANCEID,
v_PROCESSKEY,
v_AUDITTIMESTAMP,
v_STATUS);

v_current := 'DELETE FROM INTERMEDIATE_PROCESS';

DELETE FROM INTERMEDIATE_PROCESS
WHERE INSTANCEID = v_INSTANCEID
AND PROCESSKEY = v_PROCESSKEY
AND AUDITTIMESTAMP = v_AUDITTIMESTAMP
AND STATUS = v_STATUS;
EXCEPTION
WHEN OTHERS
THEN
v_sql_error := sqlerrm || ' - ' || v_current;
ROLLBACK;
END;
--To be discussed
/*INSERT INTO temp_table (INSTANCEID, PROCESSKEY, AUDITTIMESTAMP, STATUS)
VALUES (v_INSTANCEID , v_PROCESSKEY, v_AUDITTIMESTAMP,v_STATUS);*/
END LOOP;

CLOSE c_first500; -- Free resources used by the cursor

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
v_sql_error := sqlerrm || ' - ' || v_current;
ROLLBACK;
END;
/