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

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;
/