Thanks for the question, ted.
Asked: September 26, 2000 - 11:18 am UTC
Last updated: September 26, 2000 - 11:18 am UTC
Version: 8.1.5
Viewed 1000+ times
You Asked
thomas,
1. can I use utl_raw.length to find out the length of long field ?
2. how do i convert long to a long raw field.
thnx in advance
ted chyn
and Tom said...
1) no. you can use
create or replace function long_length( p_cname in varchar2,
p_tname in varchar2,
p_rowid in rowid )
return number
as
l_cursor integer default dbms_sql.open_cursor;
l_n number;
l_long_val varchar2(32760);
l_long_len number;
l_buflen number := 32760;
l_curpos number := 0;
begin
dbms_sql.parse( l_cursor, 'select ' || p_cname ||
' from ' || p_tname ||
' where rowid = :rid',
dbms_sql.native );
dbms_sql.bind_variable( l_cursor, ':rid', p_rowid );
dbms_sql.define_column_long(l_cursor, 1);
l_n := dbms_sql.execute(l_cursor);
if (dbms_sql.fetch_rows(l_cursor)>0)
then
loop
dbms_sql.column_value_long(l_cursor, 1, l_buflen,
l_curpos ,
l_long_val, l_long_len );
l_curpos := l_curpos + l_long_len;
exit when l_long_len = 0;
end loop;
end if;
dbms_sql.close_cursor(l_cursor);
return l_curpos;
exception
when others then
if dbms_sql.is_open(l_cursor) then
dbms_sql.close_cursor(l_cursor);
end if;
raise;
end long_length;
/
and if you made that an AUTONOMOUS_TRANSACTION in Oracle8i release 8.1, you could even call that from SQL. As you can see -- use it sparingly, it'll take a while to run on lots of rows.
2) you can convert UPTO 32k of LONG to LONG RAW using PLSQL and UTL_RAW.CAST_TO_RAW. Beyond that -- you would need to use a 3gl such as C, Java, etc to perform this operation.
You can convert a LONG to a CLOB and then a CLOB to a BLOB all in PLSQL and in 8.0 and up -- you should really be using LOBS, not LONGS (all of these problems go away with LOBS)....
Is this answer out of date? If it is, please let us know via a Comment