Re: HELP ME

"shailesh" <ShaileshGothal_at_gmail.com> wrote in message news:1150035274.109770.280500_at_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
>

Have you looked at merge? There is no autocommit in plsql.(it would slow it down)
Jim Received on Sun Jun 11 2006 - 10:11:08 CDT