Team,
we have an application, that used to search using any kind of filters on any colums - something like below.
the procedure is used to return the resultset to the application, based on the WHERE clause being passed as input.
when running this procedure concurrently with 30+ users - we are getting "Latch:Library Cache" issues.
Have read about the significance of bind variables @
https://blogs.oracle.com/oraclemagazine/successful-sql-processing-in-oracle-autonomous-database the table 'T' in the below code has about 180+ columns.
was checking with the application team to send the 3 Dimensional array of inputs like (Column_name, comparision_operator , column_value , logical operator) - so that we can parse the array and bind the inputs during execution.
but no luck of positive sign yet.
given this context, any inputs or directions on how to apply bind variables to this programs?
create or replace procedure get_data( p_where varchar2,
p_out out sys_refcursor)
as
l_cnt number;
begin
execute immediate ' select count(*) from t where || p_where
into l_cnt;
open p_out for ' select :l_cnt as Total_Cnt, t.* from T where '|| p_where
using l_cnt ;
end;
/
Sample values passed into the procedure are like this
where c1 =55
and c2 like 'ABC%'
and c3 > sysdate
and ( c4 <> 0
or c5 < '$'
or c6 = ':'
or c9 in (42,34,17) )
and c7 between systimestamp + 1 and systimestamp + 7
and dbms_lob.substr( c8, 20,1) = upper('TomKyte');
OK, a couple of things
1) That approach scares me .... scares me a lot. Think of what someone could put into an "innocent" where clause which does terrible terrible things. Search this site for "SQL Injection", in fact, search google for it...
2) To avoid every query being a brand new query, you could do this:
create or replace procedure get_data( p_where varchar2,
p_out out sys_refcursor)
as
l_cnt number;
begin
execute immediate ' select count(*) from t where || p_where
into l_cnt;
execute immediate 'alter session set cursor_sharing = force';
open p_out for ' select :l_cnt as Total_Cnt, t.* from T where '|| p_where
using l_cnt ;
execute immediate 'alter session set cursor_sharing = exact';
end;
/
So the variations are then limited to the predicates as opposed to all of the literals within the predicates.
But please...make sure you code is cast iron again SQL injection. Don't get hacked.