Dan Smith Dan Smith - 1 month ago 8
SQL Question

Creating a function that can return a list items in using Oracle sql

I am looking to create a function in Oracle where I can pass a value as a string and return a list. For example I would like to have a function where I give it the title of a book and it returns a list of users who own that book.

I have created the function below which contains warnings which I'm not sure how to fix and whether it is the right way to go about it.

CREATE OR REPLACE Function UsersForBooks
( name_in IN varchar(250) )
RETURN varchar(250)
IS
listToReturn varchar(250);
cursor c1 is
SELECT username
FROM (GC_User NATURAL JOIN GC_Books)
NATURAL JOIN GC_BookOwnership
WHERE GC_Books.title = name_in
;
BEGIN
open c1;
fetch c1 into listToReturn;
close c1;
RETURN listToReturn;
END;
/


I am calling my function in the following way and not sure if it is correct.

SELECT * FROM UsersForBooks('The Da Vinci Code');

Answer

You want to do something like this:

CREATE OR REPLACE FUNCTION UsersForBooks (name_in IN varchar2)
  RETURN SYS_REFCURSOR
IS

  c SYS_REFCURSOR;

BEGIN

  OPEN c FOR
  SELECT...;

  RETURN  c;

END;
/

In Oracle always use VARCHAR2 rather than VARCHAR.

You don't specify the size of variables in a parameter list, only in the actual variable declaration.

Comments