HELP ME

From: shailesh <ShaileshGothal_at_gmail.com>
Date: 11 Jun 2006 07:14:34 -0700
Message-ID: <1150035274.109770.280500@g10g2000cwb.googlegroups.com>

Dear Friends,

I need to write a Stored Procedure in Oracle 9i which should insert/update data from temp table to staging tables. Table structure will be exactly same. It has to be performance tunned. Destination table will have records upto 9 million. So performace will be a key factor. Following are the design level highlights which I could think of with very little PL/SQL knowledge.

1. Insert or Update -

We need to insert/update data from temp table to staging tables. Following is the general logic for the same. We need to execute update first and if SQL%ROWCOUNT < 1 then need to execute Insert. We can not execute Insert first since we have large number records and select to match primary key on entire table will affect the performance.

CREATE OR REPLACE procedure_name
DECLARE
cursor c1 select empno,deptno from tem_emp; BEGIN
For c1_rec is c1 loop
UPDATE emp
SET deptno = c1_rec.deptno
WHERE empno = c1_rec.empno;

If SQL%ROWCOUNT < 1 then
INSERT INTO emp VALUES (c1_rec.empno,c1_rec.deptno) End if;
End loop;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_PUTPUT.PUT_LINE('Employee Number Does Not Exist'); WHEN INVALID_CURSOR THEN

DBMS_PUTPUT.PUT_LINE('Employee Number Does Not Exist');
WHEN DUP_VAL_ON_INDEX THEN
DBMS_PUTPUT.PUT_LINE('Primary Key Column can not be duplicated');

WHEN OTHERS THEN
DBMS_PUTPUT.PUT_LINE('No valid error message'); End;
END procedure_name;

2) Exception Handling -

I have specified only predefine Exceptions above, we need to identify any User Define Exception to be handled and then use Raise Exception statement.

3) Delete -

For Delete condition, we assume that temp table will have some flag field XX_flag which indicates record to be deleted. We will just update similar flag in Staging table.

4) Commit -

Above code template does not specify Commit statement. We can not have commit on every insert or update as this will affect the performace with large number records.
We need to set AUTO COMMIT option at specific number of records. Infact we can have no of records as a input parameter to SP for AUTO COMMIT.

5) Error handling -

We can have a Error table which will be updated when exception is raised. So instead of DBMS_PUTPUT.PUT_LINE above we will have update to Error Table which will help to understand which record is failed to Insert/Update. Error table should have column like Table Name, Column Name, Table primary key etc.

6) Recorvery -

Do we have provide recovery feature, incase the SP fails to execute at specific time?
This will be an additional fancy feature we will be providing. This can be implemented using AUDIT TRAIL table which will have information like Transaction ID, Transaction Date, Transaction Status.

PLEASE HELP ME. Regards / Shailesh Received on Sun Jun 11 2006 - 09:14:34 CDT