handling errors

[jimmy80]

Hi,

I'm having a bit of dificulty with a pl/sql block - basically I want to enter a whole load of values into a table - if there is duplicates I want to check if the duplicate exists in another table - if it does I want to enter it into table a otherwise i want to enter it into an error table.
The reason I am checking after is for speed - to check each value prior to entering it into table a would slow down the whole process since this error only very rarely occurs but does need to be handled on the rare occassion it does.
When I try to do this I get the error ORA-06512... and can't figure out why - I've included the code below and was hoping someone might be able to point me in the right direction!

Thanks!
PROCEDURE SECLIST (p_orgname IN VARCHAR2, p_tradedt IN VARCHAR2)
IS

l_cusip VARCHAR2(20);
l_symbol VARCHAR2(20);
l_trade_dt DATE;
f_cusip VARCHAR2(20);
BEGIN

FOR x IN (
SELECT DISTINCT cusip, symbol, trade_dt, 'N'
FROM orders o
WHERE trunc(trade_dt) = to_date(p_tradedt, 'yyyy-mm-dd')


LOOP
BEGIN
INSERT INTO trades (cusip, symbol, trade_dt, process_flag)
VALUES (x.cusip, x.symbol, x.trade_dt, x.flag);


EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
SELECT cusip INTO f_cusip
FROM executions
WHERE dt_trade = to_date(p_tradedt, 'yyyy-mm-dd')
and cusip = x.cusip;
IF SQL%FOUND THEN
--dbms_output.put_line('cusip exists in fame data'||f_cusip);
delete from trades
where symbol = x.symbol
and trade_dt = to_date(p_tradedt, 'yyyy-mm-dd');

INSERT INTO trades (cusip, symbol, trade_dt, process_flag)
VALUES (x.cusip, x.symbol, x.trade_dt, x.flag);
ELSIF SQL%NOTFOUND THEN
dbms_output.put_line('cusip doesn''t exist);
INSERT INTO error_table (cusip, transaction_dt, error)
VALUES (x.cusip, SYSDATE, x.symbol || ' - No data founduplicate Cusip');
END IF;

WHEN NO_DATA_FOUND THEN
--dbms_output.put_line('cusip doesn''t exist in fame data'||f_cusip);
INSERT INTO error_table (cusip, transaction_dt, error)
VALUES (x.cusip, SYSDATE, x.symbol || ' - No data founduplicate Cusip');

WHEN others THEN
INSERT INTO error_table (cusip, transaction_dt, error)
VALUES (x.cusip, SYSDATE, 'Others');
END;
END LOOP;

--COMMIT;

END;

 

[sem]

I suggest you provide the line number mentioned in error message, because this error means only that some OTHER error occured.

As for the speed, you may disable constraint, insert records and then enable it with EXCEPTIONS clause and then analyze the results, delete duplicates and enable constraint again. It looks more complex, but in fact far more efficient than looping through cursor and processing each record.

Regards, Dima

 

[jimmy80]

I'm only getting the error when the cusip doesn't exist in the executions table - the pl/sql block excutes fine if the cusip does exist in the executions table - so the error occurs around line 25...
I really appreciate your help with this!
Thanks again
Jim

 

[sem]

Around libne 25 is not right answer. What is EXACT line number? Or at least real error. As I already mentioned 6512 holds NO INFORMATION itself. I also can not understand the reason of handling NO_DATA_FOUND as it can not be raised by this INSERT statement. Another problem is that 'N' literal in your cursor has no name and can not be referenced: thus it's completely redundant. Then SELECT cusip INTO f_cusip may either really select a value or raise NO_DATA_FOUND or TOO_MANY_ROWS errors, so it's useless to check SQL%FOUND as it's always TRUE (or exception is raised and you pass this check by).
I suppose that dozen of other errors may be found by more curious person then me. So my suggestion: you may think about performance when you're familiar with concepts at least.

Regards, Dima