Re: SEQUENCE Question...

From: Jason Jay Weiland <archduke_at_uclink4.berkeley.edu>
Date: Tue, 06 Oct 1998 10:18:23 -0700
Message-ID: <361A50DF.78DA2627@uclink4.berkeley.edu>

Kathy,

     You may consider creating a PL/SQL function that will return the CurrVal of the sequence that you are attempting to put in the WHERE clause of the SELECT statement.

In a text file: c:\temp\new_job.sql

CREATE OR REPLACE FUNCTION f_job_num_currval   RETURN NUMBER
IS
  v_job_id NUMBER;
BEGIN
  SELECT job_numbers.CurrVal
    INTO v_job_id
    FROM dual;
  RETURN v_job_id;
END; Then in SQLPlus:

SQL> @c:\temp\new_job
Function created.

SQL> select * from job_info
  2 where job_id = f_job_num_currval;

   JOB_ID JOB_NAME

--------- --------------------------------
        3 Troubleshooting for Kathy

     You could also create a procedure that would insert the row and print

the values it just inserted to the screen, or along those lines, a function that would insert the row and return any value you specified.

Jay!!!

P.S> You may want to consider putting "nomail" in the email-address of your news-postings' header as well if you want to cut down on spam.

Kathy Graham wrote:

> Hi Folks;
> Doing my first full-blown project and have a quick question
> regarding sequences. I have established a sequence to create primary
> keys for a job-tracking application. I have successfully inserted a
> record using the sequence. So here is the question.
>
> How do I get THAT record back for reporting purposes. I tried to do
> something along the lines of:
>
> select * from job_info where job_id = job_numbers.currval
>
> but received an error that the sequence did not belong there. I know
> that I could possibly do this in two statements maybe by pulling in the
> currval into a variable and then executing with the value of the
> variable, but I am trying to do this in a single SQL statment if it is
> possible.
>
> Also, I am concerned about some strange behaviour I am seeing. Maybe
> due to caching? For example the last record used sequence number 10.
> NEXTVAL reports as 11 but CURRVAL is 7?????
>
> In any case, any help I can get on getting my record back that I just
> inserted would be a big help. Thanks in advance....
>
> Kathy Graham
> kathy_graham_at_nomail.hpl.hp.com <----remove nomail for replies
Received on Tue Oct 06 1998 - 12:18:23 CDT