Re: Wierd sequence/function failure

A copy of this was sent to Kathy Graham <kathy_graham_at_hpl.hp.com> (if that email address didn't require changing) On Tue, 16 Feb 1999 19:54:25 -0800, you wrote:

>Hi Folks;
> 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] I think that if you look at job_id, some of the values will not be numbers and thats the issue. Consider:

SQL> create table test( x varchar2(5) ); Table created.

SQL> insert into test values ( 1 );
1 row created.

SQL> select * from test where x = 1;

X

SQL> insert into test values ( 'nan' );
1 row created.

SQL>
SQL> select * from test where x = 1;
ERROR:
ORA-01722: invalid number
no rows selected

SQL> select * from test where x = to_char(1);

X

can you try wrapping a to_char() around you function and see if that fixes it?

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

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.