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@xxxxxxxxx> wrote in message
news:1150035274.109770.280500@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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

.

Relevant Pages

  • Re: System.Security.SecurityException was unhandled
    ... The exception gave you the CLSID. ... the first thing to check might be whether the COM server ... assembly actually has the permission in question. ... When I execute the application I received and error message. ...
    (microsoft.public.dotnet.security)
  • Re: HELP ME
    ... We need to insert/update data from temp table to staging tables. ... any User Define Exception to be handled and then use Raise Exception ... Above code template does not specify Commit statement. ...
    (comp.databases.oracle.misc)
  • Re: collecting results in threading app
    ... but execute one at the time'. ... Could a Queue help me there? ... You can take a look at papyros, a small package I wrote for hiding ... # some exception was raised when executing this job ...
    (comp.lang.python)
  • HELP ME
    ... We need to insert/update data from temp table to staging tables. ... any User Define Exception to be handled and then use Raise Exception ... Above code template does not specify Commit statement. ...
    (comp.databases.oracle.misc)
  • Re: HELP ME
    ... We need to insert/update data from temp table to staging tables. ... any User Define Exception to be handled and then use Raise Exception ... Above code template does not specify Commit statement. ...
    (comp.databases.oracle.misc)