Re: HELP- Code for SQL Single Row Character Function?

Mario King wrote:
> I'm trying to write a function long2ch() to convert
> table's LONG column to varchar2 so that I could run
> select * from tab where long2ch(long_col) like '%MY String%'.
> -- I want to make long2ch() similar to upper(), trim()...

Sorry, but that's just not going to happen:

SQL> create or replace function long2ch( p_long in long ) return varchar2 is
2 begin
3 return p_long;
4 end;
5 /

Function created.

SQL> select * from user_views
2 where long2ch(text) like '%EMP%';
where long2ch(text) like '%EMP%'
*
ERROR at line 2:
ORA-00997: illegal use of LONG datatype

The problem is that you can't pass the LONG value INTO the function at all!

Maybe you could be using CLOB instead? Received on Thu Sep 30 2004 - 11:50:36 CDT