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?
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:
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.
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;