Home » SQL & PL/SQL » SQL & PL/SQL » substr prob (Oracle 9.2.0.1)
substr prob [message #366352] Fri, 12 December 2008 23:18 Go to next message
avik2009
Messages: 61
Registered: November 2008
Member
Please suggest where I am wrong !
=CREATE PROCEDURE ( p_usr_nme IN VARCHAR2)

is
  v_txt   VARCHAR2(65);
  v_first_nme VARCHAR2(25);
  v_last_nme VARCHAR2(35);
BEGIN

---P_USR_NME IS AN IN PARAMETER THAT IS BEING PASSED 
---e.g, 'David Bechham'
-- user can pass either last name ,first name or both or none



IF instr(ltrim(p_usr_nme), ' ') <> 0 then
     v_txt:= ltrim(p_usr_nme);
  ELSE 
     v_txt:=ltrim(p_usr_nme) || ' ';
  END IF;
   
  v_first_nme:= trim(upper(SUBSTR(v_txt,1,instr(v_txt,' '))));
  v_last_nme:= trim(upper(substr(v_txt,instr(v_txt,' '), length(v_txt))));

OPEN RC1 FOR
SELECT * FROM EMPLOYEES WHERE

       (
           ( v_first_nme        IS NULL
               OR ( v_first_nme          IS NOT NULL
               AND ( b.FIRST_NME LIKE '%'||v_first_nme||'%')
               )
               )
       OR (
           ( v_last_nme          IS NULL
               OR ( v_last_nme          IS NOT NULL
               AND ( b.LAST_NME LIKE '%'||v_last_nme||'%')
               )
       )  )
       )
..


When I tried , I am not getting record for the input first name/lastname or both.help!!

[Updated on: Sat, 13 December 2008 01:20] by Moderator

Report message to a moderator

Re: substr prob [message #366354 is a reply to message #366352] Fri, 12 December 2008 23:31 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
If you will follow forum rules, it will provide us better understanding of problem.
and of course, you will get better and quick answer.

regards,
Delna
Re: substr prob [message #366361 is a reply to message #366352] Sat, 13 December 2008 01:16 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
Please suggest where I am wrong !

CREATE PROCEDURE ( p_usr_nme IN VARCHAR2)

is
v_txt VARCHAR2(65);
v_first_nme VARCHAR2(25);
v_last_nme VARCHAR2(35);
BEGIN

---P_USR_NME IS AN IN PARAMETER THAT IS BEING PASSED 
---e.g, 'David Bechham'
-- user can pass either last name ,first name or both or none



IF instr(ltrim(p_usr_nme), ' ') <> 0 then
v_txt:= ltrim(p_usr_nme);
ELSE 
v_txt:=ltrim(p_usr_nme) || ' ';
END IF;

v_first_nme:= trim(upper(SUBSTR(v_txt,1,instr(v_txt,' '))));
v_last_nme:= trim(upper(substr(v_txt,instr(v_txt,' '), length(v_txt))));

OPEN RC1 FOR
SELECT * FROM EMPLOYEES WHERE

(
( v_first_nme IS NULL
OR ( v_first_nme IS NOT NULL
AND ( b.FIRST_NME LIKE '%'||v_first_nme||'%')
)
)
OR (
( v_last_nme IS NULL
OR ( v_last_nme IS NOT NULL
AND ( b.LAST_NME LIKE '%'||v_last_nme||'%')
)
) )
)
..


When I tried , I am not getting record for the input first name/lastname or both.help!!
Re: substr prob [message #366364 is a reply to message #366361] Sat, 13 December 2008 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste your session.

Quote:
When I tried , I am not getting record for the input first name/lastname or both.help!!

Of course, with what you posted you didn't output/return anything.

Regards
Michel
Re: substr prob [message #375961 is a reply to message #366361] Mon, 15 December 2008 08:22 Go to previous message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
avik2009 wrote on Sat, 13 December 2008 02:16
Please suggest where I am wrong !

CREATE PROCEDURE ( p_usr_nme IN VARCHAR2)

is





The first thing is that this is not valid syntax to create a procedure in Oracle.
Previous Topic: Merge in Oracle V/S Merge in DB2
Next Topic: ORA-06533: Subscript beyond count
Goto Forum:
  


Current Time: Wed Dec 07 16:13:24 CST 2016

Total time taken to generate the page: 0.08780 seconds