ok, you should *never* code what you've code - ever.
You are easily subject to sql injection
you are generating non-scalable code that runs slow and eats shared pool memory for lunch.
there is literally nothing good to say about this bit of code - it makes many of the biggest mistakes.
problems with it:
a) subject to sql injection
b) doesn't use binds
c) v_month != '' will *never* be true by the way, this is not sqlserver
d) 'Month' format would have blank padding, need to use a different format
e) your building of strsql doesn't work at all, you quoted w_survey in the building of it, making your where clause a string
please read this article to see how to do this properly and safely (no sql injection, no scalability issues, best plan possible, doesn't eat memory for lunch)
http://www.oracle.com/technetwork/issue-archive/2009/09-jul/o49asktom-090487.html here is how that procedure might be written:
ops$tkyte%ORA11GR2> create table myTab ( name varchar2(10), surveyDate date );
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into myTab
2 select 'name ' || level, add_months( trunc(sysdate,'y'), level-1 )
3 from dual
4 connect by level <= 11;
11 rows created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace PROCEDURE "MyProc"
2 ( v_month in varchar2,
3 mySurveyCursor in out sys_refcursor
4 )
5 IS
6 l_sql long := 'select name, surveydate from myTab ';
7 BEGIN
8
9 if ( v_month is not null )
10 then
11 l_sql := l_sql || q'| where to_char(surveyDate, 'fmMonth' ) = :x |';
12 else
13 l_sql := l_sql || ' where (1=1 or :x is null)';
14 end if;
15
16 open mySurveyCursor for l_sql using v_month;
17 end;
18 /
Procedure created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> variable x refcursor
ops$tkyte%ORA11GR2> exec "MyProc"( 'January', :x )
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> print x
NAME SURVEYDAT
------------------------------ ---------
name 1 01-JAN-12
ops$tkyte%ORA11GR2> exec "MyProc"( 'December', :x )
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> print x
no rows selected
ops$tkyte%ORA11GR2>
On lines 9-14 - we use the technique from the above reference article to build a query using BIND VARIABLES. This is not subject to sql injection, this is scalable and shared pool friendly.
On line 11 we use a format modifier (fm) to get the trailing blanks to not be part of our to_char (you should use an ordinal number - it would be more "global" - January isn't spelled the same everywhere!