Re: HELP ME

Comments embedded

On 11 Jun 2006 07:14:34 -0700, "shailesh" <ShaileshGothal_at_gmail.com> wrote:

>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.

Regrettably as the below procedure doesn't do any bulk processing, it will be dead slow.

 Following are the design level highlights which I could think
>of with very little PL/SQL knowledge.

You don't need PL/SQL. One Merge command in a sql script and you are there.

>
>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.

Incorrect. If the primary key is indexed insert ... where not exists (correlated subquery) will work as a charm
>
>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;

I'm not sur whether this update wouldn't already raise no data found. And exactly how many of those updates are going to fail because you have a new record? This approach stinks and will be slow.

>
>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');
dbms_output.put_line
>WHEN INVALID_CURSOR THEN
>DBMS_PUTPUT.PUT_LINE('Employee Number Does Not Exist');
>WHEN DUP_VAL_ON_INDEX THEN
will not occur
>DBMS_PUTPUT.PUT_LINE('Primary Key Column can not be duplicated');
>WHEN OTHERS THEN

horrible. Unexpected error messages shouldn't be suppressed at all.
>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.
>

You don't need to set autocommit, you need to commit *AFTER* the loop, and *NOWHERE ELSE*.

>
>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

You don't need 'fancy features', you need to learn PL/SQL. What you have so far right now, should be thrown away. And as Jim already stated: replace this mess by a simple MERGE statement, which will be fully equivalent.

--
Sybrand Bakker, Senior Oracle DBA