I'm getting the error below while trying to convert the datatype of the LONG value to char

select DBMS_LOB.SUBSTR(a.sql_statement_text)
from alr_alerts a,alr_actions aa where a.alert_id = aa.alert_id and a.alert_id =100081

sql_statement_text is defined as a LONG column.

CodePudding user response：

As mentioned in the comments, long cannot be used in SQL functions, so you'd better redesign table's structure and convert it to clob to make life easier.

But if you still need to access this data having long as a source type, you may use two approaches.

• Local function declaration that will fetch long (in PL/SQL) and convert it to clob. This is available since 12.1, but it will open a new child cursor per row.

with function f_get_long_as_clob(
  p_id int
) return clob
as
  l_long long;
begin
  select do_not_use_long
    into l_long
  from test_tab
  where id = p_id;

  return to_clob(l_long);
end;

select
  test_tab.id,
  f_get_long_as_clob(id) as clob_val
from test_tab
where id < 3

• Serialization of long to XML and subsequent deserialization as clob.

select *
from xmltable(
  '/ROWSET/ROW'
  passing dbms_xmlgen.getxmltype('select * from test_tab where id < 3')
  columns
    id int,
    clob_val clob path 'DO_NOT_USE_LONG'
)

For this sample data:

create table test_tab (
  id int,
  do_not_use_long long
);

insert into test_tab
select level, lpad(level, 10, '0')
from dual
connect by level < 3;

both solutions will return

db<>fiddle