I am really happy that I get the chance again to ask you question.

Tom, I have requirement to access pl/sql table(table(cast) within definition of views as I want to push predicate into the view to increase performance as if I join view with pl/sql table on fly then performance degrades.

Currently I am using global variable within packages in order to implement this approach. Although this approach is working fine but still I am not satisfied with approach 100% because this involves using of global variables. Here are the sample examples of this approach.

create or replace type key_array_t as table of number;
/

create table t as select * from all_objects
/

create or replace view myview
as select * from t where object_id in(select column_value from table(cast(test.get_input_list()as key_array_t)))
/
create or replace package test as
type t_ref_cursor is ref cursor;
function get_input_list return key_Array_t;
procedure get_data(p_in_list key_Array_t,
P_CUR OUT t_ref_cursor);
end;
/

create or replace package body test as
g_input_list key_array_t;
function get_input_list return key_array_t is
begin
return g_input_list;
end;
procedure get_data(p_in_list key_Array_t,
P_CUR OUT t_ref_cursor) is
begin
g_input_list:=p_in_list;
open p_cur
for select * from myview;
end;
end;
/
var cur refcursor;
EXEC TEST.GET_DATA(KEY_ARRAY_T(100),:cur);
print :cur;
/

Is the above approach is the only solution or is there any other solution that more viable than above mentioned one?

BTW,I really want to avoid using of global variables in my application as they are always error prone.

Thanks in advance for your help.