Creating a Function

[braindead]

Oracle 10.2 on Windoze.

I'm having trouble calling this stored procedure.

As an anon procedure it works fine, and it will accept the title_id and return the authors for the book:

Below, I get a PLS-00306 wrong number of types or arguments error

*********************************************************

Code: Select all

set serveroutput on

create or replace procedure
   list_authors
   (v_title_id in varchar2, v_au_lname out varchar2)
is
   cursor
     c1
   is
   select
      au_lname
   from
      titles        t,
      titleauthor   ta,
      authors       a
   where
      t.title_id = ta.title_id
   and
      ta.au_id = a.au_id
   and
      t.title_id = 'v_title_id';
BEGIN
open c1;
loop
   fetch c1 into v_au_lname;
   EXIT WHEN c1%NOTFOUND;
   dbms_output.put_line(v_au_lname);
end loop;
close c1;
EXCEPTION
WHEN OTHERS THEN raise_application_error(-20001,'An error - '||SQLCODE||' -ERROR- '||SQLERRM);
end;
/
exec list_authors('TC7777');

[Tim...]

Hi.

When you post code you need to highlight it and click the "Code" button. Without the CODE tags around it the formatting is lost and it is unreadable. I've corrected your post now.

As for you issue, your procedure expects two parameters (1 in and 1 out). You have only supplied one parameter. You need a variable to hold the out value. For example,

Code: Select all

DECLARE
  l_au_lname authors.au_name%TYPE; -- Not sure if this is the correct table reference. Don't have your schema.
BEGIN
  list_authors('TC7777', l_au_lname);
END;
/

Some things I notice in your code:

- You are using an explicit cursor. Unless you plan to use a bulk collect, you should use an implicit cursor for loop. It is faster and more efficient. If you are expecting only one row to be returned form the query, you should use a SELECT ... INTO.

- If multiple rows are returned, you are repeatedly setting the out parameter in the loop. This means the value you pass out will be the value from the last row you return. I'm guessing this is not what you were expecting.

- This line is incorrect:

Code: Select all

t.title_id = 'v_title_id';

It should be:

Code: Select all

t.title_id = v_title_id;

In PL/SQL, all variables are automatically strongly typed bind variables when used within SQL. You don't need to surround them in quotes. What you have done is asked for the row with the title_id that matches the literal string 'v_title_id', not the value contained in the variable named v_title_id.

Cheers

Tim...