Re: proplem writing function in PL/SQL
Date: 1996/10/03
Message-ID: <3254017a.17312413_at_dcsun4>#1/1
On 3 Oct 96 02:16:36 GMT, mwest_at_hort.cri.nz wrote:
>I have to following PL/SQL function:
>
>FUNCTION NewVisitID (field_names IN varchar2)
>RETURN number
>IS
> next_ID number;
>
>BEGIN
> select max(field_names) into next_ID from MIS.VISIT;
> next_ID := next_ID + 1;
> return(next_ID);
>END;
>
>
>I want the function to read in a field_name, run the select statement and
>return the max +1 number.
>
>I think the problem is that the select is trying to get the max of what i
>put into field_names rather than the max of the column specified in
>field_names. Can anyone provide a solution?????????
>
If you executed:
SQL> exec :n := NewVisitId( 'field1' )
It would be the same as if you issued the query:
select max( 'field1' ) from MIS.VISIT;
I assume you are trying to pass in the name of a column and get the max of that column, add one to it and return. You can do this with dbms_sql by building the query in a string.
If the above describes what you are trying to do though, I would suggest (strongly) that you look at the Oracle Sequence type instead of the above logic. If you are trying to generate new keys based on max(x)+1, you won't be. In a multi-user environment many people will get the same value. The only way you can get around this is to attempt to lock the table before reading it to serialize access. selecting max(id)+1 is not a good/realiable method of generating keys.
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
- Check out Oracle Governments web site! -----
Follow the link to "Tech Center"
and then downloadable Utilities for some free software...
statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Thu Oct 03 1996 - 00:00:00 CEST