Creating a Function

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

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
   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');
braindead
Member
 
Posts: 3
Joined: Thu Feb 23, 2012 2:44 pm

Re: Creating a Function

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

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...
Tim...
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: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17955
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 1 guest