I am trying to generate something a little more complex. In order not to have many queries and maintenance points the idea is to use views.
create or replace view vw_test as
Select 1
from dual
where 1 = '#VARIABLE_FIX';
What I am trying to do is
DECLARE
sQuery VARCHAR2(32767);
sView VARCHAR2(32767);
BEGIN
SELECT a.TEXT
into sView
FROM all_views a
where a.VIEW_NAME = 'VW_TEST';
sQuery := REPLACE(sView, '''#VARIABLE_FIX''', :P1_ITEM);
return(sQuery);
END;
Errors returned:
ORA-20999: Parsing returned query results in "ORA-20999: Failed to parse SQL query! <p>ORA-06550: line 12, column 23: ORA-00936: missing expression</p>".
I even inserted the contents of the variable sQuery into a temporary table and the result is correct with the content of the view.
I used parameterized view, but when I return the query directly in oracle apex, the performance of the report is much faster.
If you pass
Squery: = 'Select 1 from dual where 1 =: P1_ITEM'
, The return is successful. Any ideas on how to get around the error and use this method?