Re: why wont this work ? PL\sql question

There are a number of things wrong here, so I'll just point you in the general direction to get going, starting with the minor problems first:-

Syntax:- You have "WHERE OTHERS" instead of "WHEN OTHERS".

Exceptions:- You would find it helpful if you add the following to your WHEN OTHERS:-

   WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,255));
      RAISE;

It will break the program and tell you what made it fail (i.e. the oracle error message) - make sure you have set serveroutput on if using SQL*Plus...

Function:- Looks like you are trying to use a function to UPDATE the database which has all sorts of implications for purity. There are restrictions to doing this. Without having tested it, I'm sure you can't do it - Oracle will tell you something like it can't guarantee to do your update. Use a PROCEDURE with an OUT parameter instead if you really must do an UPDATE and return a value. This can do the UPDATE without setting any purity levels and also pass back a value. Or an alternative is to have a function to return you the new value (data + 1) only and then have either a procedure calling the function and executing the update or even a simple UPDATE statement in SQL using the function e.g (UPDATE table SET column = my_function(column);

So lookup how to write procedures using out parameters and also how to execute them...good luck!!!

Regards

Adrian Received on Mon Dec 17 2001 - 10:08:03 CET