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?

+3


source to share


1 answer


You cannot use a SELECT statement in a PL / SQL IF statement this way. However, you can do this:



IF ('HOLD'  member of p_queue_type) THEN
...

      

+4


source







All Articles