ORA-00997: illegal use of LONG datatype
I am trying to create a function that takes LONG datatype as a parameter and then return a value in VARCHAR2 as follows. Then, I got "ORA-00997: illegal use of LONG datatype" when I used this function in my select statement shown below as if LONG is not acceptable as a parameter to PL/SQL function. Could anyone please shed me some light of what I might have done wrong here? Thank you!
/**** This is my function ****/
create or replace function ahs_longtext_to_char (input_long_text in long)
return varchar2
as
buff_long_to_char varchar2(32000);
begin
buff_long_to_char := trim(substr(input_long_text, 1, 32000));
return buff_long_to_char;
end;
/**** This is the select statement using the function created above ****/
select ahs_longtext_to_char(t.long_text) from long_text t
ERROR MESSAGE: "ORA-00997: illegal use of LONG datatype"
I did verify that LONG_TEXT column in LONG_TEXT table has LONG as datatype shown below..
SQL> desc long_text;
Name Type Nullable Default Comments
---------------------- ------------ -------- ---------------------------------------------------------- --------
LONG_TEXT_ID NUMBER 0
UPDT_CNT NUMBER 0
UPDT_DT_TM DATE SYSDATE
UPDT_ID NUMBER 0
LONG_TEXT LONG Y ' '