Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    2

    Unanswered: call storedproc returns output parameter not a bind variable

    Hello,

    i'm new in oracle pl/sql and sql*plus and have following problem:

    i created a stored procedure with one input and with one output parameter.

    i want to try the procedure in sql*plus, but the call returns "ORA-06577: output parameter not a bind variable".

    Code of stored procedure looks like this:

    CREATE OR REPLACE PROCEDURE GET_SEQUENCE (
    tablename IN VARCHAR2,
    sequence_no OUT NUMBER) IS
    Begin
    IF tablename = 'table1' THEN
    SELECT sequence_table1.nextval INTO sequence_no from DUAL;
    END IF;
    IF tablename = 'table2' THEN
    SELECT sequence_table2.nextval INTO sequence_no from DUAL;
    END IF;

    ....

    End;



    how do i write the call statement to get the sequence number?
    e.g.: "call GET_SEQUENCE('table1',?)"


    thanks for any help.
    ronq

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: call storedproc returns output parameter not a bind variable

    Do this:

    VARIABLE seqno NUMBER

    EXEC GET_SEQUENCE('table1',:seqno)

    By the way, your code would be more efficient if you used ELSIF:

    IF tablename = 'table1' THEN
    SELECT sequence_table1.nextval INTO sequence_no from DUAL;
    ELSIF tablename = 'table2' THEN
    SELECT sequence_table2.nextval INTO sequence_no from DUAL;
    END IF;

    (or you could use a CASE statement).

  3. #3
    Join Date
    Jan 2003
    Posts
    2

    Re: call storedproc returns output parameter not a bind variable

    Thanks, it works.

    ronq

Posting Permissions

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