Oracle SQL creating a function - confusing compile errors

Question

Based off of how to return a dynamic result set in Oracle function

I'm trying to make a function that will return one row with several counts from several tables. Here's what I have so far:

CREATE OR REPLACE TYPE RESULT_ROW is OBJECT
(LOC_TABLE_ENTRY_KY VARCHAR2(50),
LOCATION_NAME VARCHAR2(50),
A_ASSIGN_CNT VARCHAR2(50),
B_ASSIGN_CNT VARCHAR2(50),
C_ASSIGN_CNT VARCHAR2(50),
D_ASSIGN_CNT VARCHAR2(50),
E_ASSIGN_CNT VARCHAR2(50),
F_ASSIGN_CNT VARCHAR2(50),
G_ASSIGN_CNT VARCHAR2(50),
H_ASSIGN_CNT VARCHAR2(50));
/
CREATE OR REPLACE TYPE RESULT_TABLE AS TABLE OF RESULT_ROW;
/
CREATE OR REPLACE FUNCTION LOCATION_RULE_LOOKUP(P_LOCATION_VAR IN NUMBER)
RETURN RESULT_TABLE
IS
OUT_REC RESULT_TABLE;
BEGIN
WITH LOC AS
(SELECT LOC_TABLE_ENTRY_KY,
LOCATION_NAME
FROM LOCATION_CODE
WHERE LOC_TABLE_ENTRY_KY = P_LOCATION_VAR
),
ONE AS
(SELECT COUNT(*) AS A_ASSIGN_CNT
FROM COLLECTOR_ASSIGNMENT
WHERE LOCATION_CODE     = P_LOCATION_VAR
AND FUNCTION_STATE_CODE = '   '
OR FUNCTION_STATE_CODE  = '***'
),
TWO AS
(SELECT COUNT(*) AS B_ASSIGN_CNT
FROM COMM_PLAN_ASSGN_RULE
WHERE LOCATION_CODE = P_LOCATION_VAR
),
THREE AS
(SELECT COUNT(*) AS C_ASSIGN_CNT
FROM INPUT_TRANS_ASGN
WHERE LOCATION_CODE = P_LOCATION_VAR
),
FOUR AS
(SELECT COUNT(*) AS D_ASSIGN_CNT
FROM RECALL_DAYS_ASSGN_RULE
WHERE LOCATION_CODE = P_LOCATION_VAR
),
FIVE AS
(SELECT COUNT(*) AS E_ASSIGN_CNT
FROM SCRIPT_VIEW_ASSIGNMENT
WHERE LOCATION_CODE     = P_LOCATION_VAR
AND FUNCTION_STATE_CODE = '   '
OR FUNCTION_STATE_CODE  = '***'
AND SCRIPT_TYPE         = 'V'
),
SIX AS
(SELECT COUNT(*) AS F_ASSIGN_CNT
FROM SCRIPT_VIEW_ASSIGNMENT
WHERE LOCATION_CODE     = P_LOCATION_VAR
AND FUNCTION_STATE_CODE = '   '
OR FUNCTION_STATE_CODE  = '***'
AND SCRIPT_TYPE         = 'D'
),
SEVEN AS
(SELECT COUNT(*) AS G_ASSIGN_CNT
FROM TSR_ASSGN_RULE
WHERE LOCATION_CODE = P_LOCATION_VAR
),
EIGHT AS
(SELECT COUNT(*) AS H_ASSIGN_CNT
FROM TRAN_STATE_ASSGN_RULE
WHERE LOCATION_CODE = P_LOCATION_VAR
)
SELECT * INTO OUT_REC
FROM ONE, TWO, THREE, FOUR, FIVE, SIX, SEVEN, EIGHT, LOC;
RETURN OUT_REC;
END LOCATION_RULE_LOOKUP;

But no matter what I do to make this look like existing function examples, both here and on other sites, it doesn't accept anything within the BEGIN block. The SQL inside the BEGIN block works; I can run all those WITH AS SELECT etc. and it'll give me one row as the result with the counts I'm looking for. But this function will just NOT compile. Depending on where I put a space or a semicolon, the errors change. Current errors:

Error(7,3): PL/SQL: SQL Statement ignored
Error(62,3): PL/SQL: ORA-00947: not enough values

Help? D:

Answer 1

You're very close, but you didn't follow the linked question quite faithfully enough. The reason for the not enough values message is that you only have one value in your INTO clause, but Oracle is expecting you to have eight, since that's how many expressions are (effectively) in your SELECT *. You need to cast those eight expressions into a single value. To do this, you need to change your WITH ... SELECT * INTO out_rec ... into either SELECT CAST(MULTISET(WITH ... SELECT * ...) AS result_table) INTO out_rec FROM dual or WITH ... SELECT CAST(MULTISET(SELECT * ...) AS result_table) INTO out_rec FROM dual, as you prefer.

Also, you need to put your FROM clause in the right order, with LOC preceding ONE through EIGHT; the cast is performed based on the relative positions of the expressions, not based on the field aliases. (Alternatively, you could change your SELECT * to explicitly identify the fields, in the right order, rather than relying on the FROM-clause order.)