Wierd sequence/function failure

    Several months ago I came to this forum and got some great help regarding sequences. Specifically, I want to do an insert of a new record with a sequence value. Then I want to immediately query the same record and display it for the user. I've got a very basic function(courtesy of this list) that grabs the currval and returns it into the query.

FUNCTION MS_CURRENT_JNUM
RETURN NUMBER IS
v_job_id NUMBER;

BEGIN
 SELECT ms_jobnums.CurrVal
 INTO v_job_id
 FROM dual;
 RETURN v_job_id;

END ms_current_jnum;

So the sequence is :

1. Insert with ms_jobnums.nextval
2. perform a select with the sequence value

SELECT JOB_INFORMATION.* FROM JOB_INFORMATION WHERE dept_short = 'MS' AND JOB_ID = ms_current_jnum

This has been set up and working great for several months. I actually have four sequences set up, each with the EXACT SAME function just with different sequences. Last night the function started to fail to bring back a good result for only one department. The other three continue to work great.

I wrote a test procedure that performs the insert/lookup for two departments. Good Sequence numbers for both departments came back. But when I try to move over to the above select statement, it works for one department and not the other!!!!(Arghhhhhhhhhhh!) WHY would this just stop working like this? For the department that fails, I get an ORA-01722 indicating that the SQL statement is getting a bad value. (Which is why I wrote the test procedure that works.)

I have also recompiled the function. Which did not help either.

I do have one clue though. I'm wondering if it is a data type conversion problem. The function returns a NUMBER and the job_id is a string(varchar2) field. I tried to rewrite the function so that it puts out a string instead of a number, but ran into the same error. (or I could have the select statement wrong because I am expecting a number instead of a string.) And of course there is the fact that the other three departments are chugging along just fine.

[Quoted] In any case, it you have read this far. THANKS. This is a real stumper and I am just a few weeks from release, so any help will be GREATLY appreciated. Anyone have any clues???

Kathy Graham
kathy_graham_at_nospam.hpl.hp.com Received on Wed Feb 17 1999 - 04:54:25 CET