Creating a Function

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Posts: 3
Joined: Thu Feb 23, 2012 2:44 pm

Creating a Function

Postby braindead » Thu Feb 23, 2012 3:24 pm

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
   (v_title_id in varchar2, v_au_lname out varchar2)
      titles        t,
      titleauthor   ta,
      authors       a
      t.title_id = ta.title_id
      ta.au_id = a.au_id
      t.title_id = 'v_title_id';
open c1;
   fetch c1 into v_au_lname;
end loop;
close c1;
WHEN OTHERS THEN raise_application_error(-20001,'An error - '||SQLCODE||' -ERROR- '||SQLERRM);
exec list_authors('TC7777');

Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Creating a Function

Postby Tim... » Thu Feb 23, 2012 3:47 pm


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

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

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.


Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website:
My blog:

Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 3 guests