Geek Answers Handbook

Oracle SQL creates a function - obfuscates compilation errors

Based on how to return a dynamic result set in an Oracle function

I am trying to create a function that will return a single row with multiple counts from multiple tables. Here is what I still have:

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 it look like existing examples of functions, both here and on other sites, it does not accept anything in the BEGIN block. SQL inside a BEGIN block works; I can run all those with AS SELECT etc., and this will give me one line as a result with the counts I'm looking for. But this function is simply NOT compiled. Depending on where I put the space or 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:

+5
2

, . not enough values , INTO, Oracle , , () SELECT *. . WITH ... SELECT * INTO out_rec ... SELECT CAST(MULTISET(WITH ... SELECT * ...) AS result_table) INTO out_rec FROM dual WITH ... SELECT CAST(MULTISET(SELECT * ...) AS result_table) INTO out_rec FROM dual, .

, FROM , LOC, ONE, EIGHT; , . ( SELECT *, , FROM -clause.)

+6

@ruakh CAST MULTISET , . RESULT_ROW BULK COLLECT :

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 'blarg' AS loc_table_entry_ky, 'foo' AS location_name FROM DUAL),
       one AS (SELECT COUNT( * ) AS a_assign_cnt FROM DUAL),
       two AS (SELECT COUNT( * ) AS b_assign_cnt FROM DUAL),
       three AS (SELECT COUNT( * ) AS c_assign_cnt FROM DUAL),
       four AS (SELECT COUNT( * ) AS d_assign_cnt FROM DUAL),
       five AS (SELECT COUNT( * ) AS e_assign_cnt FROM DUAL),
       six AS (SELECT COUNT( * ) AS f_assign_cnt FROM DUAL),
       seven AS (SELECT COUNT( * ) AS g_assign_cnt FROM DUAL),
       eight AS (SELECT COUNT( * ) AS h_assign_cnt FROM DUAL)
   SELECT result_row(loc_table_entry_ky,
                     location_name,
                     a_assign_cnt,
                     b_assign_cnt,
                     c_assign_cnt,
                     d_assign_cnt,
                     e_assign_cnt,
                     f_assign_cnt,
                     g_assign_cnt,
                     h_assign_cnt)
   BULK COLLECT INTO out_rec
   FROM   one,
          two,
          three,
          four,
          five,
          six,
          seven,
          eight,
          loc;
   RETURN out_rec;
END location_rule_lookup;
/

(, . , dual .)

+4

More articles:


All Articles