Home » SQL & PL/SQL » SQL & PL/SQL » Handling different datatypes (Oracle 10g)
Handling different datatypes [message #296291] Fri, 25 January 2008 10:36 Go to next message
gems22
Messages: 1
Registered: January 2008
Junior Member
Hi Everyone,

I have a CODE field in my database which is of datatype NUMBER. I have created a parameter in my stored procedure something like this

pCODE IN C.CODE%TYPE;


I compare this parameter in my WHERE clause as follows
 (C.CODE = pCODE OR pCODE IS NULL) OR
 (pCODE = 'UN' AND (C.CODE) > 10) OR
 (pCODE = 'SC' AND (C.CODE) IN (1,2,3,4,5,6,7,8,9,10))


Users can enter text 'SC' or 'UN' or can enter code category number to return data. I am not sure how to handle this. Right now I get an error if I enter 'SC' as pCODE is expecting a number. How do I handle two different datatypes for the same parameter?

Thanks,

Emily
Re: Handling different datatypes [message #296296 is a reply to message #296291] Fri, 25 January 2008 10:43 Go to previous message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use a varchar2 datatype instead this is the only only that can handle number and string at the same type.
Of course, it should be better that your application handle this and pass 2 parameters of correct datatypes to your procedure.

Regards
Michel
Previous Topic: FIND table FROM all_tab_cols WHERE column value = something
Next Topic: Row Order in External Table
Goto Forum:
  


Current Time: Mon Dec 05 11:05:53 CST 2016

Total time taken to generate the page: 0.08388 seconds