Retrieving length of long values

Question

I have a table in Oracle that contains a field with the data type of LONG. With the following function I'm trying to find out how many characters are stored in this LONG field for a particular record in the table.

CREATE OR REPLACE FUNCTION Find_Length(i_nwt_id number)
RETURN NUMBER

 IS
    long_var LONG;

BEGIN
    SELECT NWT_TEXT
      INTO long_var
      FROM qf.NWT
     WHERE nwt_id=i_nwt_id;

    RETURN length(long_var);

END;

I try to call it like the following:

select nwt_id, find_length(nwt_id)leng  from nwt ;

Anyhow this select throws me an exception:

Numeric or value error!

Anyone could give me an hints what am I doing wrong?

Thanks!

probable duplicate http://stackoverflow.com/questions/5497238/get-the-length-of-a-long-raw — Ben, Feb 15 '12 at 17:17

score 1 · Accepted Answer · answered Feb 15 '12 at 18:14

Your issue is when you hit a size > 32k. Either follow Vincent's java stored proc solution for long raw, or do this:

create global temporary table ltt(x clob)
on commit delete rows;

create or replace function length_of_long(p_tname in varchar2, p_cname in varchar2, p_rowid in rowid)
return number authid current_user
as
  pragma autonomous_transaction;
  l_length number;
begin
  execute immediate
  'insert into ltt(x)
  select to_lob(' || p_cname || ')
  from ' || p_tname || '
  where rowid = :x' using p_rowid;

  select dbms_lob.get_length(x) into l_length from ltt;

  commit;

  return l_length;
end;

And use it like this:

select id, length_of_long('MY_TABLE', 'LONG_COL', rowid) as len from MY_TABLE;

Disclaimer, I found this approach from Tom Kyte article sometime ago, similar will work for LONG RAW, just need the temp table as BLOB.

You should've called the function `how_long`! That is a good solution; just seems horribly round-about; but then everything with `long` is. — Ben, Feb 15 '12 at 18:57

Retrieving length of long values

1 Answers1