We help IT Professionals succeed at work.

queries and the LONG datatype

_TAD_
_TAD_ asked
on
Medium Priority
1,265 Views
Last Modified: 2013-12-19
As I understand it, the only way to convert a long data type to VarChar2 is within PL/SQL.  But then how can I create a query that looks inside that field for specific values?


In short we have a table that holds some audit information about various emails that went out.  The field that holds the list of email addresses is a LONG datatype.  

Now I want to:
   Select * from MY_EMAIL_AUDIT_TBL where Emails_Sent like '%TAD@email.com%'

But I can't do that because Emails_Sent is of type long and I get a data conversion error.
Comment
Watch Question

Data Architect
CERTIFIED EXPERT
Commented:
Convert your LONG columns to LOB data types. LONGs doesnt support the string functions. LONG is depricated. Use the function to_lob to convert LONG to LOB.
The usage is:
create table tbl2 as select id, to_lob(val) val from tbl1;

LOBs will not break any of the existing code. Instead LOBs support all the string functions.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:

Is that the only viable solution?  

This is a vendor provided software package (PeopleSoft).  I cannot simply change the layout of the table.  Even if I was sure it wouldn't break anything it would still be considdered a customization - something the business would never approve of.
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT
Commented:
I agree with sujith80, converting to an LOB column looks like your best option.  If you can't do that for some reason, you may be able to write a PL\SQL function that reads the LONG value into a data type that supports string operations, then do the check and let the function return the value you want.  A function like this could be called from a simple SQL query or even in a view definition statement.  But I would expect performance of this apporach to be slower than if you can convert the column to a LOB.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
If you can't modify the vendor's objects, how about creating a copy table that snaps your email table every so often and converts the data into clob's or varchar2 (if you can live with the truncation)
then read that?

Or, if you absolutely must have live data queries then I'll suggest a pipelined function (this will be sloooooooooooow) that reads the table and returns a collection with clobs/varchar2 where the longs ought to be.

Author

Commented:

Ick, ick, ick.....

All very good answers and all of them suck.  :-P

You have confirmed my concerns.  


The end resultant is going to be a report that is run once a month - maybe once a week.  

I will experiment with creating a trigger on this table to write data to an identicle table with a LOB field instead of a long.  Failing that, I'll create an ETL process to run each night and copy data over.  

Thank everyone for you assistance
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.