Search code examples
oracle-apex

apex_exec.open_query_context where clause with bind variables

Using Oracle Apex apex_data_export.download, the where clause is ignored. The output shows the contents of every row in the OUTPUT column.

The code is run in a process type execute code, execution after regions. I need to use the value of :P0_SUB which has a value before the page is loaded. Is the where clause wrong? How is the value of :P0_SUB accessed by the process? Do I need to change the execution point?

The code

    l_context := apex_exec.open_query_context(
        p_location    => apex_exec.c_location_local_db,
        p_sql_query   => 'select output from TFA_USER',
        p_where_clause => 'SUB = ' ||:P0_SUB);
 
    l_export := apex_data_export.export (
        p_context   => l_context,
        p_format    => apex_data_export.c_format_json,
        p_file_name => 'test' );

    apex_exec.close( l_context );

    apex_data_export.download( p_export => l_export );

EXCEPTION
    when others THEN
        apex_exec.close( l_context );
        raise;

  APEX_JSON.free_output;

WHEN I USE THE FOLLOWING CODE:

l_context := apex_exec.open_query_context(
        p_location    => apex_exec.c_location_local_db,
        p_sql_query   => 'select OUTPUT from TFA_USER where SUB = ' || :P0_SUB);

I get the following error

ORA-00904: "TEST"."USER": invalid identifier

What is the way to get just one row?

Solution

  • It's in the documentation of APEX_EXEC.OPEN_QUERY_CONTEXT, but kind of "between the lines". The parameters are similar to the parameters in an apex page region with a data source, like a classic report. If the Source > Type is "Table/View", there is an attribute "Where Clause". On the other hand, if the Source > Type is "SQL Query", there is no "Where Clause" since the where clause would just be part of the sql query itself. The documentation states: enter image description here

    The blue section as parameters for query type "TABLE" and the red section has parameters for query type "SQL Query". parameters for query type "TABLE" are ignored when query type = "SQL Query". This is explained in the description of parameter p_where_clause.

    enter image description here

    In your code, parameter p_sql_query is used, so query type is "SQL Query" which causes p_where_clause to be ignored.

    Just like in any apex region source, bind variables can be included in the sql query and will be parsed by the apex engine at runtime. It's not a good practice anywhere in pl/sql code to use concatenation for bind variables.

    Here is an example of working code. This example uses a page item P75_ENAME which has a before header computation. The code needs to go a page process, either Pre-Rendering or After Submit.

    DECLARE
    l_context         apex_exec.t_context; 
    l_export          apex_data_export.t_export;
BEGIN
    l_context := apex_exec.open_query_context(
        p_location    => apex_exec.c_location_local_db,
        p_sql_query   => 'select * from emp where ename = :P75_ENAME' );
    l_export := apex_data_export.export (
        p_context         => l_context,
        p_format          =>  apex_data_export.c_format_json);

    apex_exec.close( l_context );

    apex_data_export.download( p_export => l_export );
EXCEPTION
    when others THEN
        apex_exec.close( l_context );
        raise;
END;