PL/SQL Function body returning SQL query from view

Question

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_teste 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_TESTE';

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!
ORA-06550: line 12, column 23: ORA-00936: missing expression
"

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?

My first guess would be that you are replacing #VARIABLE_FIX with an inputed parameter which you arent providing. Try using ':P1_ITEM' in the replace instead of :P1_ITEM — TineO
Seems like you're going the long way around to solve a problem, potentially introducing performance problems and SQL injection risk. What is it you're really trying to solve? Can it be solved with sys_context? — Scott
There is another issue with your code Selection from all_views without owner can lead to duplicate values ORA-01422: exact fetch returns more than requested number of rows — Thomas Strub
Using views, in general, to help simplify business logic, common joins, etc. is good. But, personally, I think what you're trying to do an over-optimization and a potentially limiting one. PL/SQL Function Returning SQL Query is supported by differently by Classic Reports (supports generic columns) and Interactive Reports (doesn't support generic columns), and not at all Interactive Grids, LOVs, etc. When you use this flexibility, it comes with a technical debt and complexity cost that may exceed any benefits. — Dan McGhan
Also, what is the advantage to using views over a package with functions which return the SQL query based on the input? — Dan McGhan

Koen Lostrie Koen Lostrie · Accepted Answer · 2019-11-06T18:51:56

Your statement

sQuery := REPLACE(sView, '''#VARIABLE_FIX''', :P1_ITEM);

has too many quotes - there is no need to escape the quotes, since you already have them in your view definition. Replace that line with

sQuery := REPLACE(sView, '#VARIABLE_FIX', :P1_ITEM);

and you're good to go. Verified on 19.1. However, I probably would go with Dan's solution and put the queries in a package with functions. That is much more transparent than querying the data dictionary for a view definition.

PL/SQL Function body returning SQL query from view

1 Answers