Re: Filling in the blanks

You will need to write a PL/SQL function like:

GetNextValue(varchar2 KeyPrefix) returns varchar2

This would need to start from the lowest value and continue until it finds an available key.
If multiple instances of you function can be run then remember that by the time you insert the row the key value may have been used so you will have to expect an error and try again.

To do this you should have an index on column_a and it's performance may be a problem depending on the number of keys for a given prefix.

Liz Huckins wrote:

> Hi,
> I am trying to fill in the blanks in my clients customer numbers.
> Let me explain.
> I need to do something like this
> IF the user selects ABQ from the list of values THEN
> SELECT the min(substr(column_a,5,5 ) +1 my_number
> FROM table_a
> WHERE substr(column_a,1,3) = 'ABQ'
> AND my_number does not exist in table_a
> in other words the client may have customer numbers like ABQ.10000,
> ABQ.10001, ABQ.10003 and ABQ.10005 how do I fill in the blanks for them so
> the next time it selects a number for them to use it selects ABQ.10002 and
> the next time it would select ABQ.10004.
> I need to place this code in the custom library so that I can populate a
> column on an Oracle standard form.
> Thanks in advance
> Liz
Received on Tue Apr 20 1999 - 03:38:10 CDT