How to write dynamic select stored Procedure

Sai Ram

How to write dynamic select stored Procedure
I wrote some code like this ...Please correct it and revert back..

create or replace
PROCEDURE SAMPLE
( p_filter_column IN out Varchar2,
p_filter_operator_name IN out varchar2,
p_filter_value IN out varchar2,
p_page_size IN out varchar2,
p_current_page IN out INT,
p_sort_column IN out varchar2,
p_sort_direction IN out varchar2,
p_table_name IN out varchar2
)

IS
condition_string varchar(300);
row_start int;
excute_string varchar(300);

Begin
condition_string:= p_filter_column || p_filter_operator_name || p_filter_value;

If (p_current_page > 1) then
row_start:= to_number(p_current_page) * to_number(p_page_size);
else
row_start:=1;
end If;
excute_string:='SELECT * FROM ' || p_table_name || 'WHERE' || condition_string || 'AND ROWNUM >' || row_start || 'order by' || p_sort_column || p_sort_direction;
EXECUTE IMMEDIATE excute_string;

End;

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Shankar M13y ago

Hi User,

The only advantage of Using Dynamic SQL, is that if the objects are not known Until runtime.

And, When you use Dynamic SQL,

As, the Code becomes large it might be littered with mistakes, but
they Still Compile.

Objects Validation are not done until runtime.

which are great disadvantages using Dynamic SQL.

Thanks,

Shankar
Javeed M Shaikh14y ago
Hi Ravindranath,

what is the error that you are getting?

try this..

create or replace
PROCEDURE SAMPLE
( p_filter_column IN out Varchar2,
p_filter_operator_name IN out varchar2,
p_filter_value IN out varchar2,
p_page_size IN out varchar2,
p_current_page IN out INT,
p_sort_column IN out varchar2,
p_sort_direction IN out varchar2,
p_table_name IN out varchar2
)

IS
condition_string varchar(300);
row_start int;
excute_string varchar(300);

Begin
condition_string:= p_filter_column || p_filter_operator_name || p_filter_value;

If (p_current_page > 1) then
row_start:= to_number(p_current_page) * to_number(p_page_size);
else
row_start:=1;
end If;
excute_string:='SELECT * FROM ' || p_table_name || 'WHERE ' || condition_string || ' AND ROWNUM > ' || row_start || ' order by ' || p_sort_column || p_sort_direction;
EXECUTE IMMEDIATE excute_string;

End;