Re: HELP ME

Dear Jim

Can you help me without AutoCommit pls. i do not have knowledge about PL/SQL Best regards
Shailesh

Jim Kennedy wrote:
> "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:42:54 CDT