Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Posts
    4

    Unanswered: Dynamic PL/SQL Error

    I am getting an error when I call this procedure
    Microsoft][ODBC driver for Oracle][Oracle]ORA-20005: BATES_RESERVE: ORA-00905: missing keyword
    ORA-06512: at "BATES.BATES_PREFIX_PKG", line 511
    ORA-06512: at line 1


    PROCEDURE BATES_RESERVE (p_USERID IN VARCHAR2
    ,p_PREFIX IN VARCHAR2
    ,p_PAGECNT IN NUMBER
    ,p_START_BATES_NUM OUT NUMBER
    ,p_BATESFORMAT OUT VARCHAR2)
    /*
    -- Purpose: To get a range of bates numbers for a particular document and prefix.
    --
    -- MODIFICATION HISTORY
    -- Person Date Comments
    -- --------- ---------- ------------------------------------------
    -- KWilbank 11/24/2003 Creation.
    -- kwilbank 12/03/2003 Modified - removed inserts into job tables per AMeriani (Handle separately).
    -- KWILBANK 02/17/2004 MODIFIED -- ADDED ADDING (1) TO CURRENT_NUMBER SO VALUE IS INCREMENTED PER AMERIANI.
    */

    IS



    EXISTING_RECORD NUMBER;
    PrefixStatus VARCHAR2(1);
    CURRENT_BATES VARCHAR2(50);
    BATESPADVAL NUMBER;
    NEWVAL NUMBER;
    Already_checked_out EXCEPTION;
    sql_stmt VARCHAR2(4000);

    BEGIN
    p_START_BATES_NUM:= NULL;


    sql_stmt := 'SELECT COUNT(*) INTO EXISTING_RECORD FROM BATES_PREFIX_MGR WHERE PREFIX = :x' ;

    EXECUTE IMMEDIATE sql_stmt USING p_PREFIX ;

    sql_stmt:= NULL;

    sql_stmt := 'SELECT BATES_FORMAT INTO BATESPADVAL FROM BATES_PREFIX_MGR WHERE PREFIX = :x' ;

    EXECUTE IMMEDIATE sql_stmt USING p_PREFIX ;

    IF EXISTING_RECORD = 0 THEN
    RAISE NO_DATA_FOUND;
    ELSE

    sql_stmt:= NULL;

    sql_stmt := 'SELECT DECODE(CHECKED_OUT_ID,NULL,''N'',DECODE(CHECKED_OU T_ID,p_USERID,''N'',''Y'')) INTO PrefixStatus FROM BATES_PREFIX_MGR WHERE PREFIX= :x';

    EXECUTE IMMEDIATE sql_stmt USING p_PREFIX ;

    IF PrefixStatus = 'Y' THEN
    RAISE Already_checked_out;
    ELSE

    sql_stmt:= NULL;

    /* INCREMENT THE LAST BATES NUMBER IN PREFIX_MGR TABLE BY ONE PAGE NUMBER */
    sql_stmt := 'SELECT TO_NUMBER(CURRENT_NUM_TXT) + 1 INTO CURRENT_BATES FROM BATES_PREFIX_MGR WHERE PREFIX = :x' ;

    EXECUTE IMMEDIATE sql_stmt USING p_PREFIX ;

    sql_stmt:= NULL;

    /* ADD TO THE NEW AVAILABLE PAGE # THE ADDITIONAL PAGE NUMBERS TO RETURN THE LAST PAGE NUMBER */
    SELECT TO_NUMBER(CURRENT_BATES) + p_PAGECNT INTO NEWVAL FROM DUAL;

    /*UPDATE BATES_PREFIX_MGR TABLE'S CHECKED_OUT FIELDS*/
    sql_stmt := 'UPDATE BATES_PREFIX_MGR SET CURRENT_NUM_TXT=LPAD(NEWVAL,BATESPADVAL,0),CHECKED _OUT_ID=p_USERID,CHECKED_OUT_DTM=SYSDATE,MODIFIED_ BY_USER_ID=p_USERID,MODIFIED_DTM=SYSDATE WHERE PREFIX= :x ';

    EXECUTE IMMEDIATE sql_stmt USING p_PREFIX ;

    p_START_BATES_NUM := TO_NUMBER(CURRENT_BATES);
    p_BATESFORMAT := LPAD(0,BATESPADVAL,0);
    COMMIT;
    END IF;
    END IF;

    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    raise_application_error (-20005,'BATES_RESERVE: Prefix not found.');
    ROLLBACK;
    WHEN Already_checked_out THEN
    raise_application_error (-20005,'BATES_RESERVE: Prefix '||p_PREFIX||' is checked out. Please try again later.');
    ROLLBACK;
    WHEN OTHERS THEN
    raise_application_error (-20005,'BATES_RESERVE: '|| sqlerrm);
    ROLLBACK;

    END BATES_RESERVE;
    Last edited by lamyak; 04-06-04 at 15:09.

  2. #2
    Join Date
    Mar 2004
    Posts
    23

    Re: Dynamic PL/SQL Error

    This looks suspicious:

    SELECT TO_NUMBER(CURRENT_BATES) + p_PAGECNT INTO NEWVAL FROM DUAL;

    I would try:

    SELECT TO_NUMBER(CURRENT_BATES) INTO NEWVAL FROM DUAL;

    NEWVAL := NEWVAL + p_PAGECNT;

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    EXECUTE IMMEDIATE expects/requires valid SQL in order for it to work.
    >sql_stmt := 'SELECT BATES_FORMAT INTO BATESPADVAL FROM BATES_PREFIX_MGR WHERE PREFIX = :x' ;

    The SQL above does not appear to be valid to me.
    I suggest you validate the character strings using SQL*Plus or the like.
    Since you need to build the SQL as a VARCHAR2 anyways I see nothing
    is really gained by passing in argument via "USING".
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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