Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    67

    Unanswered: insert and return the sequence.currval

    All I want to do is create an SP that inserts a record with an assigned sequence number. Then after the insert I want to return the sequence number that was used for the insert. I get errors where I am trying to return the value.

    This is what I have:
    Code:
    CREATE OR REPLACE PACKAGE WEBCZCS.USER_PKG
    AS 
    -- the lone var being returned...
    TYPE USERID IS TABLE OF NUMBER(8) INDEX BY BINARY_INTEGER;
    
    -- the sp head
    PROCEDURE WEBCZCS.S_USER_AUTHENTICATE (
    iUSER_NAME IN CZCS_USERS.USER_NAME%TYPE, 
    oUSERID OUT USERID, iCUSTID IN CZCS_USERS.CUSTID%TYPE, 
    iFIRST_NAME IN  CZCS_USERS.FIRST_NAME%TYPE, 
    iLAST_NAME IN  CZCS_USERS.LAST_NAME%TYPE, 
    iUPASSWORD IN CZCS_USERS.UPASSWORD%TYPE,  
    iUPDATE_USERID CZCS_USERS.UPDATE_USERID%TYPE, 
    iUSER_EMAIL IN CZCS_USERS.USER_EMAIL%TYPE, 
    iPASSWORDHASH IN CZCS_USERS.PASSWORDHASH%TYPE, 
    iSALT IN CZCS_USERS.SALT%TYPE 
    );
    
    -- package
    CREATE OR REPLACE PACKAGE BODY WEBCZCS.USER_PKG
    AS
     
    -- proc head again (got to love that)    
    PROCEDURE WEBCZCS.S_USER_AUTHENTICATE (
    iUSER_NAME IN CZCS_USERS.USER_NAME%TYPE, 
    oUSERID OUT USERID, 
    iCUSTID IN CZCS_USERS.CUSTID%TYPE, 
    iFIRST_NAME IN  CZCS_USERS.FIRST_NAME%TYPE, 
    iLAST_NAME IN  CZCS_USERS.LAST_NAME%TYPE, 
    iUPASSWORD IN CZCS_USERS.UPASSWORD%TYPE,  
    iUPDATE_USERID CZCS_USERS.UPDATE_USERID%TYPE, 
    iUSER_EMAIL IN CZCS_USERS.USER_EMAIL%TYPE, 
    iPASSWORDHASH IN CZCS_USERS.PASSWORDHASH%TYPE, 
    iSALT IN CZCS_USERS.SALT%TYPE 
    )
    
    IS 
    BEGIN
    
    -- the insert
    INSERT INTO WEBCZCS.CZCS_USERS 
    (USER_NAME, USERID, CUSTID, FIRST_NAME, LAST_NAME, 
    UPASSWORD, UPDATE_DATE,CREATE_DATE, UPDATE_USERID, 
    USER_EMAIL, PASSWORDHASH, SALT)
    VALUES (iUSER_NAME, WEBCZCS.USERID.NEXTVAL, iCUSTID, iFIRST_NAME, iLAST_NAME, iUPASSWORD, SYSDATE, SYSDATE, iUPDATE_USERID, iUSER_EMAIL, iPASSWORDHASH, iSALT) ;
    
    -- return the val of the rec just entered
    SELECT WEBCZCS.USERID.CURRVAL INTO oUSERID FROM DUAL;
    
    END S_USER_AUTHENTICATE;
    
    END USER_PKG;
    I am a Sybase/SQL2000 dev, my Oracle pl/sql is weak.

    Please let me know what you think.

    Thanks

  2. #2
    Join Date
    Mar 2004
    Posts
    23
    I wish I had a nickel for everybody who says they have an error but don't tell you what the error is.

    An Oracle sequence is typically a big NUMBER and used as a primary key for a table but is not associated with any table. The sequence just has a name you define, like maybe order_id_seq for an orders table and don't use it for another table.

    TYPE USERID IS TABLE OF NUMBER(8) INDEX BY BINARY_INTEGER;

    This won't work. I assume USERID is a column in the CZCS_USERS table so just declare a var like:

    n_next_userid CZCS_USERS.USERID%TYPE;

    I would get the nextval right away:

    SELECT seq_name.nextval INTO n_next_userid FROM dual;

    Do the INSERT using n_next_userid and assign oUSERID := n_next_userid at the end

    Hope this helps . . .

  3. #3
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    You can also use the returning clause of the insert statement.

    PHP Code:
    INSERT INTO WEBCZCS.CZCS_USERS 
    (USER_NAME
    USERID
    CUSTID
    FIRST_NAME
    LAST_NAME
    UPASSWORD
    UPDATE_DATE,
    CREATE_DATE
    UPDATE_USERID
    USER_EMAIL
    PASSWORDHASH
    SALT)
    VALUES 
    (iUSER_NAME
    WEBCZCS.USERID.NEXTVAL
    iCUSTID
    iFIRST_NAME
    iLAST_NAME
    iUPASSWORD
    SYSDATE
    SYSDATE
    iUPDATE_USERID
    iUSER_EMAIL
    iPASSWORDHASH
    iSALT
    RETURNING USERID INTO oUserId
    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  4. #4
    Join Date
    Jan 2003
    Posts
    67
    Wow, Thanks to bothe of you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •