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) )
cursor c1 is
FROM (GC_User NATURAL JOIN GC_Books)
NATURAL JOIN GC_BookOwnership
WHERE GC_Books.title = name_in
fetch c1 into listToReturn;
SELECT * FROM UsersForBooks('The Da Vinci Code');
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.