Shell Programming and Scripting

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

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

This question is best answered in an Oracle Forum such as OTN Discussion Forums : SQL and PL/SQL

You should also:.

Sam

I posted the part of code where i know the error is produced... I working Under SuSe Linux enterprise 10, with Perl 5.8 against Oracle 10gR2.

As i said, I know that in my process can be inserts of duplicated values for de primary key, but i handle the error trying an update instead... that's suppose to be the right logic. I cannot make a MERGE couse i'm inserting calculated variables in columns each LOOP.