[ACCEPTED]-Writing a select statement inside an Oracle user-defined function-plsql
Accepted answer
Use:
CREATE OR REPLACE FUNCTION GET_GROUP_BY_ID
RETURN VARCHAR2 AS
my_result FAV_GROUP.NAME%TYPE;
BEGIN
SELECT fav_group.name
INTO my_result
FROM fav_group
WHERE fav_group.id = 12345;
RETURN my_result;
END GET_GROUP_BY_ID;
The problem was my_result was being 8 used as a variable, but never declared.
I 7 used the %TYPE notation to declare the variable 6 so it used the same data type as the column 5 being used to populate it. If the column 4 data type ever changes, the variable automatically 3 changes to suit -- no concerns with data 2 type issues after changes to the table, unless 1 you remove the column entirely.
In answer to my last comment of OMG Ponies 2 answer above:
To get one more than one result 1 back from a function, use the REF CURSOR
create or replace
PACKAGE BODY REPORTING AS
FUNCTION GET_GROUP_BY_GID RETURN REF_CURSOR AS
RESULT_SET REF_CURSOR;
BEGIN
OPEN RESULT_SET FOR
SELECT favorite_group.name
FROM favorite_group
WHERE favorite_group.creator_gid = 450160;
RETURN RESULT_SET;
EXCEPTION WHEN OTHERS THEN
RAISE;
END GET_GROUP_BY_GID;
Source:
stackoverflow.com
More Related questions
Cookie Warning
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.