You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Querying long char filed

10 replies [Last post]
Hari_aindian's picture
User offline. Last seen 6 years 35 weeks ago. Offline
Joined: 08/02/2010
Posts: 84

Hi,

I need to query the PSSQLTEXT defn record by having the condition SQLTEXT like '%recname%';

how to accomplish this in Oracle. In DB2 i did it many times but in oracle this is not running.

Thanks,
Hari.A

Ashar's picture
User offline. Last seen 4 years 34 weeks ago. Offline
Joined: 03/26/2008
Posts: 227
Re: Querying long char filed

You can try CAST(SQLTEXT As Char(2000)) LIKE '%recname%'

Hari_aindian's picture
User offline. Last seen 6 years 35 weeks ago. Offline
Joined: 08/02/2010
Posts: 84
Re: Querying long char filed

Ahsar, unfortunately the above said piece of code is not working.

Got the error like "ORA-00932: inconsistent datatypes: expected NUMBER got LONG"

Ashar's picture
User offline. Last seen 4 years 34 weeks ago. Offline
Joined: 03/26/2008
Posts: 227
Re: Querying long char filed

Did you try some other functions available in Oracle e.g: to_char
I don't have access to the database right now, so couldn't try out myself..

Lepa's picture
User offline. Last seen 2 years 1 week ago. Offline
Joined: 06/23/2008
Posts: 591
Re: Querying long char filed

Try TO_LOB

select to_lob(field_name) from table where field_name like '%something%';

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Hari_aindian's picture
User offline. Last seen 6 years 35 weeks ago. Offline
Joined: 08/02/2010
Posts: 84
Re: Querying long char filed

Basically I am trying to query the PSSQLTEXTDEFN record by having the condition SQLTEXT like "%JOB%".

 SELECT SQLID FROM PSSQLTEXTDEFN WHERE SQLTEXT LIKE 
"%JOB%";

.

SQLID field is not long char field to you TO_LOB function. The above sql will be executed with out any prob in DB2. But on ORACLE am unable to do.

Regards,
Hari.A

Lepa's picture
User offline. Last seen 2 years 1 week ago. Offline
Joined: 06/23/2008
Posts: 591
Re: Querying long char filed

I just ran this in our oracle database and didn't get any errors. I got the matched rows back. What version of oracle are you on?

SELECT *
FROM pssqltextdefn
WHERE sqltext LIKE '%ps_gl_account_lang%';

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Krishna's picture
Krishna (not verified)
Re: Querying long char filed

select * FROM PSSQLTEXTDEFN WHERE SQLTEXT LIKE 'PS_PERSON_NAME%'

Use the above code.

Hari_aindian's picture
User offline. Last seen 6 years 35 weeks ago. Offline
Joined: 08/02/2010
Posts: 84
Re: Querying long char filed

we are using Oracle10G.

Sorry for the delay in response.

Lepa's picture
User offline. Last seen 2 years 1 week ago. Offline
Joined: 06/23/2008
Posts: 591
Re: Querying long char filed

Ya we are on 11 so it looks like its not possible to have a blob field in your criteria!

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Hari_aindian's picture
User offline. Last seen 6 years 35 weeks ago. Offline
Joined: 08/02/2010
Posts: 84
Re: Querying long char filed

Why there is a difference like this?
means DB2 we can do this comfortably but the same thing is not possible in Oracle.

Am just trying understand the difference.