My suggestion:

a) erase this code
b) follow the link to the code that does this and does it using binds so that it does it efficiently.

You have quite a few problems with this code:

1) DBMS_OUTPUT.PUT_LINE('HI IF' || ACCOUNT);

what is account?  it is not defined anywhere.  That bombs.

2)  IF p_sname <>'' THEN  is a bug.  You must use "if p_sname is not null then"

consider:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace procedure p( p_data in varchar2 := '' )
  2  is
  3  begin
  4          if ( p_data <> '' )
  5          then
  6                  dbms_output.put_line( 'not equal' );
  7          elsif ( p_data = '' )
  8          then
  9                  dbms_output.put_line( 'equal' );
 10          else
 11                  dbms_output.put_line( 'gee, I just don''t know' );
 12          end if;
 13  end;
 14  /

Procedure created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec p
gee, I just don't know

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec p( null );
gee, I just don't know

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec p( 'some stuff' );
gee, I just don't know

PL/SQL procedure successfully completed.


Using trivalued logic and the fact that '' is a NULL, x <> '' is never TRUE (or FALSE), it is UNKNOWN.

3) SQLQuery := SQLQuery || ' AND ACCOUNT_NAME LIKE '''|| p_sname || '%''';

does not use binds and that is SO important that I won't even look at fixing this code.  It needs to be FIXED to use binds more then anything else in the world.

 

OH, so close

SQL> set serveroutput on size 1000000

you need to tell sqlplus to dump the dbms_output buffer.

I use a login.sql file to have this always be set in my sessions.....  But you can just put it in the script.

It worked -- you just didn't see it! 

<b>SQL></b> set serveroutput on size 1000000


you do that at the SQL prompt.


scott@ORA817DEV.US.ORACLE.COM> exec dbms_output.put_line( 'hello world' );

PL/SQL procedure successfully completed.

scott@ORA817DEV.US.ORACLE.COM> set serveroutput on
scott@ORA817DEV.US.ORACLE.COM> exec dbms_output.put_line( 'hello world' );
hello world

PL/SQL procedure successfully completed.


<b>not in the plsql -- it is a sqlplus thing</b>
 

define "spell out the date"


ops$tkyte%ORA10GR2> select to_char( sysdate, 'Month DDth, YYYY' ) from dual;

TO_CHAR(SYSDATE,'MON
--------------------
September 19TH, 2006


to me, that is spelled out.. 

eh?  they both are fired, unless you accidently named them the same and they overwrote eachother.

ops$tkyte%ORA10GR2> create table t ( x int );

Table created.

ops$tkyte%ORA10GR2> create or replace trigger t1 after insert on t for each row
  2  begin
  3          dbms_output.put_line( 'Greetings from T1!!' );
  4  end;
  5  /

Trigger created.

ops$tkyte%ORA10GR2> create or replace trigger t2 after insert on t for each row
  2  begin
  3          dbms_output.put_line( 'Greetings from T2!!' );
  4  end;
  5  /

Trigger created.

ops$tkyte%ORA10GR2> insert into t values ( 1 );
Greetings from T2!!
Greetings from T1!!

1 row created.
 

ops$tkyte@ORA817DEV> create table awc_tab ( u_uid number, user_id varchar2(30), pswd varchar2(30),
  2  user_nm varchar2(30), first_login date, last_login_date date, grp_id number, active_ind varchar2(1) );

Table created.

ops$tkyte@ORA817DEV> create table awc_tab_grp ( grp_id number, grp_descr varchar2(10), outlet_cd number, is_admin varchar2(10) );

Table created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> variable pUserId varchar2(30)
ops$tkyte@ORA817DEV> variable pPassWord varchar2(30)
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> exec :pUserId := 'tkyte'

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV> exec :pPassword := 'hello';

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> select case when awc_tab.user_id = d.pUserId and awc_tab.pswd = d.pPassword then 1
  2              when awc_tab.user_id = d.pUserId then 2
  3              else 3
  4          end silly_flag
  5    from awc_tab, (select :pUserId pUserId, :pPassword pPassword from dual) d
  6   where awc_tab.user_id(+) = d.pUserId
  7  /

SILLY_FLAG
----------
         3

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> insert into awc_tab( user_id, active_ind ) values ('tkyte', 'Y');

1 row created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> select case when awc_tab.user_id = d.pUserId and awc_tab.pswd = d.pPassword then 1
  2              when awc_tab.user_id = d.pUserId then 2
  3              else 3
  4          end silly_flag
  5    from awc_tab, (select :pUserId pUserId, :pPassword pPassword from dual) d
  6   where awc_tab.user_id(+) = d.pUserId
  7  /

SILLY_FLAG
----------
         2

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> update awc_tab set pswd = 'hello';

1 row updated.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> select case when awc_tab.user_id = d.pUserId and awc_tab.pswd = d.pPassword then 1
  2              when awc_tab.user_id = d.pUserId then 2
  3              else 3
  4          end silly_flag
  5    from awc_tab, (select :pUserId pUserId, :pPassword pPassword from dual) d
  6   where awc_tab.user_id(+) = d.pUserId
  7  /

SILLY_FLAG
----------
         1

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> select *
  2    from ( select case when awc_tab.user_id = d.pUserId and awc_tab.pswd = d.pPassword then 1
  3                       when awc_tab.user_id = d.pUserId then 2
  4                       else 3
  5                   end silly_flag, awc_tab.*
  6             from awc_tab, (select :pUserId pUserId, :pPassword pPassword from dual) d
  7            where awc_tab.user_id(+) = d.pUserId
  8          ) awc_tab, awc_tab_grp
  9   where awc_tab.grp_id = awc_tab_grp.grp_id(+)
 10     and (awc_tab.active_ind = 'Y' or awc_tab.silly_flag in (2,3))
 11  /

SILLY_FLAG      U_UID USER_ID
---------- ---------- ------------------------------
PSWD                           USER_NM                        FIRST_LOGIN
------------------------------ ------------------------------ -----------
LAST_LOGIN_     GRP_ID A     GRP_ID GRP_DESCR   OUTLET_CD IS_ADMIN
----------- ---------- - ---------- ---------- ---------- ----------
         1            tkyte
hello
                       Y


ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> create or replace package types
  2  as
  3      type refcur is ref cursor;
  4  end;
  5  /

Package created.

ops$tkyte@ORA817DEV> create or replace procedure we_do_not_use_sp_as_a_prefix( p_cur in out types.refcur, p_userId in varchar2, p_password in varchar2 )
  2  as
  3  begin
  4  open p_cur for '
  5  select *
  6    from ( select case when awc_tab.user_id = d.pUserId and awc_tab.pswd = d.pPassword then 1
  7                       when awc_tab.user_id = d.pUserId then 2
  8                       else 3
  9                   end silly_flag, awc_tab.*
 10             from awc_tab, (select :pUserId pUserId, :pPassword pPassword from dual) d
 11            where awc_tab.user_id(+) = d.pUserId
 12          ) awc_tab, awc_tab_grp
 13   where awc_tab.grp_id = awc_tab_grp.grp_id(+)
 14     and (awc_tab.active_ind = ''Y'' or awc_tab.silly_flag in (2,3))' using p_userid, p_password;
 15  end;
 16  /

Procedure created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> variable x refcursor
ops$tkyte@ORA817DEV> exec we_do_not_use_sp_as_a_prefix( :x, 'tkyte', 'hello' )

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV> print x

SILLY_FLAG      U_UID USER_ID
---------- ---------- ------------------------------
PSWD                           USER_NM                        FIRST_LOGIN
------------------------------ ------------------------------ -----------
LAST_LOGIN_     GRP_ID A     GRP_ID GRP_DESCR   OUTLET_CD IS_ADMIN
----------- ---------- - ---------- ---------- ---------- ----------
         1            tkyte
hello
                       Y

ops$tkyte%ORA10GR2> create table t ( x varchar2(30) );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
  2          insert into t values ( 'hello
  3  world' );
  4          insert into t values ( 'hello world' );
  5  end;
  6  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select * from t;

X
------------------------------
hello
world

hello world

ops$tkyte%ORA10GR2> declare
  2          l_cnt number;
  3  begin
  4          execute immediate 'select count(*) from dual' into l_cnt;
  5  end;
  6  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> declare
  2          l_cnt number;
  3  begin
  4          execute immediate 'select count(*) from dual' into l_cnt;
  5  /
        execute immediate 'select count(*) from dual' into l_cnt;
                                                                 *
ERROR at line 4:
ORA-06550: line 4, column 59:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the
following:
begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << close current delete fetch lock insert
open rollback savepoint set sql execute commit forall merge
pipe

<b>Looks similar, both things "look" the same, but a dump of the file shows:
</b>
ops$tkyte%ORA10GR2> !od -c test.sql | tail
0000360   (   *   )       f   r   o   m       d   u   a   l   '       i
0000400   n   t   o       l   _   c   n   t   ;  \r  \n   e   n   d   ;
0000420  \r  \n   /  \r  \n   d   e   c   l   a   r   e  \r  \n  \t   l
0000440   _   c   n   t       n   u   m   b   e   r   ;  \r  \n   b   e
0000460   g   i   n  \r  \n  \t   e   x   e   c   u   t   e       i   m
0000500   m   e   d   i   a   t   e       '   s   e   l   e   c   t
0000520   <b>c   o   u   n   t   (   *   ) 001       f   r   o   m</b>       d
0000540   u   a   l   '       i   n   t   o       l   _   c   n   t   ;
0000560  \r  \n   /  \r  \n
0000565

create or replace procedure plp_myapp_selectnew(my_flag in varchar2, p_userid in varchar2, p_panno number) 
is 
l_username varchar2(40);
l_panno varchar2(40);
l_companyname varchar2(100) ;

begin 

if my_flag = 'username' then 
select user_id into l_username from user_master where user_id=p_userid; 
elsif my_flag = 'validatepan' then 
select pannumber into l_panno from OTC_REGISTRAION where pannumber =p_panno; 
elsif my_flag = 'companyname' then 
SELECT companyname into l_companyname
FROM OTC_INS_COMPANY; 
end if; 
end;