Re: Getting length og a LONG record.

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 13 Oct 1999 10:21:33 -0400
Message-ID: <spQEOAB6r9SS8R0XJGO1akDm+4AV@4ax.com>

A copy of this was sent to Martin Hepworth <maxsec_at_totalise.co.uk> (if that email address didn't require changing) On Wed, 13 Oct 1999 14:54:36 +0100, you wrote:

>
>HI
>I'm running Oracle 7.3.4 (On solaris 2.6) and we have a little
>challenge. In Oracle 8 theres the DBMS_LOB package to get the actual
>length of a LONG record, but this doesn't exist in 7.x. So how do we do
>it (other than writing a Pro/C program :-)
>
>Martin

you can use plsql to piecewise read it and report back the length.

I would recommend that if you use a long/long raw, the client application ALWAYS inserts the Length into a column as well as the data itself....

Here is an example of getting the length of a long (no, you cannot call this from SQL as it uses dbms_sql and dbms_sql does not guarantee to not update the database)....

tkyte_at_8i> create or replace function long_length( p_query in varchar2 ) return number
  2 as

  3      l_cursor    integer default dbms_sql.open_cursor;
  4      l_n         number;
  5      l_long_val  varchar2(250);
  6      l_long_len  number;
  7      l_buflen    number := 250;
  8      l_curpos    number := 0;
  9  begin
 10      dbms_sql.parse( l_cursor, p_query, dbms_sql.native );
 11  
 12      dbms_sql.define_column_long(l_cursor, 1);
 13      l_n := dbms_sql.execute(l_cursor);
 14  
 15      if (dbms_sql.fetch_rows(l_cursor)>0)
 16      then
 17          loop
 18              dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos ,
 19                                          l_long_val, l_long_len );
 20              l_curpos := l_curpos + l_long_len;
 21              exit when l_long_len = 0;
 22        end loop;
 23     end if;
 24     dbms_sql.close_cursor(l_cursor);
 25     return l_curpos;
 26  exception
 27     when others then
 28        if dbms_sql.is_open(l_cursor) then
 29           dbms_sql.close_cursor(l_cursor);
 30        end if;
 31        raise;

 32 end long_length;
 33 /

Function created.

tkyte_at_8i> 
tkyte_at_8i> set echo on
tkyte_at_8i> begin
  2      dbms_output.put_line( long_length( 'select text from all_views where

rownum = 1' ) );
  3 end;
  4 /
1323

PL/SQL procedure successfully completed.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Oct 13 1999 - 09:21:33 CDT