Using a subquery in PLSQL conditional logic; error PLS-00405
I am creating an application that uses PHP to fetch data from an Oracle10g database server. My input form has a series of checkboxes that are sent to the processing page via an array (the code is filtered to the appropriate elements):
<form>
<input type="checkbox" name="p_queue_type[]" id="p_queue_type_CR" value="CR" class="subject_to_all toggle_subcategory required" required="">
<input type="checkbox" name="p_queue_type[]" id="p_queue_type_HOLD" value="HOLD" class="subject_to_all toggle_subcategory required" required="">
</form>
The processing page calls the Oracle procedure, which uses the "STRING_TABLE" ( CREATE OR REPLACE TYPE STRING_TABLE AS TABLE OF VARCHAR (1000);
) custom data type to translate the flag values, which PHP processes as an array, into a table.
My procedure takes these flags as an input parameter "p_queue_type" (code filtered to matching items):
PROCEDURE get_TLFQ_results (
p1 OUT SYS_REFCURSOR,
p_queue_type IN STRING_TABLE
)
IS
v_return_sql VARCHAR2(32767) := '';
BEGIN
IF ('HOLD' IN (SELECT COLUMN_VALUE AS queue_type FROM TABLE (p_queue_type))) THEN
--compile query string
END IF;
IF ('CR' IN (SELECT COLUMN_VALUE AS queue_type FROM TABLE (p_queue_type))) THEN
--compile query string
END IF;
-- Execute the query string and store the results in a cursor
OPEN p1 FOR v_return_sql;
END get_TLFQ_results;
When I try to compile my procedure, I get this Oracle error:
[Error] PLS-00405 (4215: 23): PLS-00405: context in this invalid subquery
What am I doing wrong? How can I use my row table in my PLSQL conditional logic?
source to share