I have the following package spec:
<code>create or replace PACKAGE queries_pkg AUTHID DEFINER
AS
TYPE categories_rec IS RECORD (
ID categories.ID%TYPE,
NAME categories.NAME%TYPE,
STATUS_ID categories.STATUS_ID%TYPE,
TYPE_ID categories.TYPE_ID%TYPE
);
TYPE categories_tab IS TABLE OF categories_rec;
FUNCTION query_categories
RETURN categories_tab;
END queries_pkg;
</code>
<code>create or replace PACKAGE queries_pkg AUTHID DEFINER
AS
TYPE categories_rec IS RECORD (
ID categories.ID%TYPE,
NAME categories.NAME%TYPE,
STATUS_ID categories.STATUS_ID%TYPE,
TYPE_ID categories.TYPE_ID%TYPE
);
TYPE categories_tab IS TABLE OF categories_rec;
FUNCTION query_categories
RETURN categories_tab;
END queries_pkg;
</code>
create or replace PACKAGE queries_pkg AUTHID DEFINER
AS
TYPE categories_rec IS RECORD (
ID categories.ID%TYPE,
NAME categories.NAME%TYPE,
STATUS_ID categories.STATUS_ID%TYPE,
TYPE_ID categories.TYPE_ID%TYPE
);
TYPE categories_tab IS TABLE OF categories_rec;
FUNCTION query_categories
RETURN categories_tab;
END queries_pkg;
and the body:
<code>create or replace PACKAGE BODY queries_pkg
AS
FUNCTION query_categories
RETURN categories_tab
IS
l_v categories_tab;
BEGIN
SELECT ID, NAME, STATUS_ID, TYPE_ID
BULK COLLECT INTO l_v FROM CATEGORIES;
RETURN l_v;
END;
END queries_pkg;
</code>
<code>create or replace PACKAGE BODY queries_pkg
AS
FUNCTION query_categories
RETURN categories_tab
IS
l_v categories_tab;
BEGIN
SELECT ID, NAME, STATUS_ID, TYPE_ID
BULK COLLECT INTO l_v FROM CATEGORIES;
RETURN l_v;
END;
END queries_pkg;
</code>
create or replace PACKAGE BODY queries_pkg
AS
FUNCTION query_categories
RETURN categories_tab
IS
l_v categories_tab;
BEGIN
SELECT ID, NAME, STATUS_ID, TYPE_ID
BULK COLLECT INTO l_v FROM CATEGORIES;
RETURN l_v;
END;
END queries_pkg;
I want to create a classic report in APEX using the returned table. I chose the option PL/SQL Function Body returning SQL Query and used the following statement:
<code>RETURN myfin.queries_pkg.query_categories();
</code>
<code>RETURN myfin.queries_pkg.query_categories();
</code>
RETURN myfin.queries_pkg.query_categories();
I am getting this error:
<code>ORA-06550: line 1, column 52: PLS-00382: expression is of wrong type
</code>
<code>ORA-06550: line 1, column 52: PLS-00382: expression is of wrong type
</code>
ORA-06550: line 1, column 52: PLS-00382: expression is of wrong type
What am I doing wrong?
If this approach is not right, what is the right way to return a query (wrapped in function)?