Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181

    Unanswered: PLS-00306: wrong number or types of arguments in call to 'TEST'

    Hi,
    Ain't too familiar with procedures but this should be working as far as I can make out - but it ain't.

    Have the following procedure created in the colm schema:

    PROCEDURE test(
    in_fname IN varchar2,
    fname out varchar2
    )
    IS
    BEGIN
    select EMP_FNAMe into fname
    from employee
    where lower(emp_fname)=lower(in_fname);
    END test;

    The employee table is like:

    Name Null? Type
    ----------------------------------------- --------
    EMP_ID NOT NULL NUMBER(15)
    EMP_FNAME VARCHAR2(100)
    EMP_LNAME VARCHAR2(100)
    EMP_DEPT VARCHAR2(100)
    EMP_JOBTITLE VARCHAR2(100)
    EMP_LOCATION VARCHAR2(100)
    EMP_EXT VARCHAR2(25)
    EMP_MOBILE VARCHAR2(50)
    EMP_CARREG VARCHAR2(15)

    But when I go to execute it I get:
    SQL> exec test('breen')
    BEGIN test('breen'); END;

    *
    ERROR at line 1:
    ORA-06550: line 1, column 7:
    PLS-00306: wrong number or types of arguments in call to 'TEST'
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

    I know I must be doing something stupid but can't find it.

    Could someone give this t*t a pointer?

    Cheers,
    Breen.

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

    Re: PLS-00306: wrong number or types of arguments in call to 'TEST'

    Compare this:

    PROCEDURE test(
    in_fname IN varchar2,
    fname out varchar2
    )

    and this:

    SQL> exec test('breen')

    The procedure wants two arguments, you gave it one!

    This should work:

    SQL> VARIABLE fname VARCHAR2(100)
    SQL> exec test('breen', :fname)

    PL/SQL procedure successfully completed.

    SQL> PRINT fname

    FNAME
    --------------------------
    Breen

  3. #3
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Thanks for that Tony,
    Just one further question - how do you capture this within the procedure itself?
    Thanking you in advance,
    Breen.

  4. #4
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Thanks for that Tony,
    Just one further question - how do you capture this within the procedure itself?
    Thanking you in advance,
    Breen.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by Breen
    Thanks for that Tony,
    Just one further question - how do you capture this within the procedure itself?
    Thanking you in advance,
    Breen.
    Not sure what you mean by "capture". Within the procedure TEST you can use fname like any other variable:

    PROCEDURE test(
    in_fname IN varchar2,
    fname out varchar2
    )
    IS
    BEGIN
    select EMP_FNAMe into fname
    from employee
    where lower(emp_fname)=lower(in_fname);
    -- Display the name
    DBMS_OUTPUT.PUT_LINE(fname);
    END test;

    In a PL/SQL block you would declare a variable:

    DECLARE
    v_fname VARCHAR2(100);
    BEGIN
    test( 'breen', v_fname );
    DBMS_OUTPUT.PUT_LINE(v_fname);
    END;
    /

  6. #6
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Sorry Tony,

    I just want to be able to run the procedure straight from the SQL> prompt without having to declare the variable fname.

    SQL> exec test('breen',:fname)

    Thanks for your help - it's just that there are going to be a load of variables to declare - and indeed some cursors before it's finished.

    Again - thanks for your time,
    Breen.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by Breen
    Sorry Tony,

    I just want to be able to run the procedure straight from the SQL> prompt without having to declare the variable fname.

    SQL> exec test('breen',:fname)

    Thanks for your help - it's just that there are going to be a load of variables to declare - and indeed some cursors before it's finished.

    Again - thanks for your time,
    Breen.
    You can't do that, really. Your procedure has an OUT parameter, which requires a variable to receive the value. What do you want to do with the value?

    If you made test into a function, you could SELECT it without declaring a variable:

    SQL> CREATE OR REPLACE FUNCTION test(
    in_fname IN varchar2,
    ) RETURN VARCHAR2
    IS
    fname employee.emp_fname%TYPE;
    BEGIN
    select EMP_FNAMe into fname
    from employee
    where lower(emp_fname)=lower(in_fname);
    RETURN fname;
    END test;
    /

    SQL> SELECT test('breen') FROM DUAL;

    test('breen')
    ------------
    BREEN

    Not sure if that is relevant, as I don't know what you are trying to do really.

  8. #8
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    That should do.

    Thanks for all your time and help Tony - now get back to work

    Cheers,
    Breen.

Posting Permissions

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