Hi!
I got an strange trouble executing a stored procedures that goes inserting line by line on a table. I mus integrate it with perl for an specific task... the hole process is controlled by e Perl script that:
- Load a text file calling sqlldr.
- Call a stored procedure that process the uploaded data and insert into other table.
I developed the stored procedure on SQL Developer and when i run it there everything goes OK. But when i invoke it from Perl or SQL*Plus, i Got "unique constraint (ORANGE.PK_SEGMENT_RANKING_DATA) violated", but in my PL/SQL i handle that exception... my code:
...
BEGIN
INSERT INTO segment_ranking_data (id_customer, data_date, id_category, click_counter)
VALUES (v_id_customer, TO_DATE(v_batchdate, 'DD/MM/YYYY'), v_id_category, v_clickcount);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
UPDATE segment_ranking_data SET click_counter = click_counter + v_clickcount
WHERE id_category=v_id_category AND id_customer=v_id_customer AND data_date=TO_DATE(v_batchdate,'DD/MM/YYYY');
END;
...
More info: Mi primary key is made by the fields (ID_CUSTOMER, DATA_DATE, ID_CATEGORY).
I got no more ideas for now ....