Skip to Main Content
  • Questions
  • Dynamic filters and arriving bind variables for them.

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Rajeshwaran.

Asked: December 08, 2019 - 6:16 am UTC

Answered by: Connor McDonald - Last updated: December 17, 2019 - 3:43 am UTC

Category: Database Development - Version: 18.0.0

Viewed 100+ times

You Asked

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');

and we said...

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.

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.