for anything >32k that is LONG RAW, you cannot use plsql.  This approach might work (to get the long raw length).  You can call this from sql or from plsql and would call it before reading out the long raw (to see that you CAN in fact read it out).

But you can use the same technique to read and write a long raw in java as well if you *really* need to copy it.


ops$tkyte@ORA9IR2> create or replace and compile java source named "long_raw" as
  2  import java.io.*;
  3  import java.lang.*;
  4  import java.sql.*;
  5  import java.util.*;
  6  import oracle.jdbc.driver.*;
  7  import java.math.*;
  8
  9  public class long_raw
 10  {
 11      public static java.math.BigDecimal getlength(java.lang.String p_query, oracle.sql.ARRAY p_binds) throws Exception
 12      {
 13                  int length;
 14                  int tot_length = 0;
 15                  Connection conn = DriverManager.getConnection("jdbc:default:connection:");
 16                  PreparedStatement pstmt = conn.prepareStatement(p_query);
 17                  String[] binds = (String[])p_binds.getArray();
 18                  for( int i = 0; i < p_binds.length(); i++ )
 19                  {
 20                          pstmt.setString( i+1, binds[i] );
 21                  }
 22          ResultSet rset = pstmt.executeQuery();
 23
 24                  if ( rset.next() )
 25          {
 26              InputStream is = rset.getBinaryStream(1);
 27                  byte buffer[] = new byte[1024];
 28              while ((length = is.read (buffer,0,1024)) != -1)
 29                                  tot_length += length;
 30              is.close();
 31          }
 32          rset.close();
 33                  pstmt.close();
 34                  return new java.math.BigDecimal( tot_length );
 35      }
 36  }
 37  /
 
Java created.
 
ops$tkyte@ORA9IR2> show error
No errors.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace function long_raw_length( p_query in varchar2, p_binds in strArray ) return number
  2  as language java
  3  name 'long_raw.getlength( java.lang.String, oracle.sql.ARRAY ) return java.math.BigDecimal';
  4  /
 
Function created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> drop table t;
 
Table dropped.
 
ops$tkyte@ORA9IR2> create table t ( x int, y int, z long raw );
 
Table created.
 
ops$tkyte@ORA9IR2> insert into t values ( 1, 2, '0a0a0a0a' );
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select x, y, long_raw_length('select z from t where x = ? and y = ?', strArray(x,y) ) lrl
  2    from t;
 
         X          Y        LRL
---------- ---------- ----------
         1          2          4
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select max( long_raw_length('select piece from sys.idl_ub1$ where obj#=to_number(?) and part=to_number(?) and
  2  version=to_number(?) and piece# = to_number(?)', strArray(obj#,part,version,piece#) ) ) from sys.idl_ub1$;
 
 
MAX(LONG_RAW_LENGTH('SELECTPIECEFROMSYS.IDL_UB1$WHEREOBJ#=TO_NUMBER(?)ANDPART=T
-------------------------------------------------------------------------------
                                                                          32767




That routine is not fully tested, just sort of cobbled it together.  Could be optimized I am sure (by a real java programmer :)  Just don't let the java programmer take out the bind variable bit!

 

ops$tkyte%ORA11GR2> create or replace function long_raw_length( p_query in varchar2, p_binds in sys.odciVarchar2List ) return number
  2  as language java
  3  name 'long_raw.getlength( java.lang.String, oracle.sql.ARRAY ) return java.math.BigDecimal';
  4  /

Function created.

ops$tkyte%ORA11GR2> drop table t;

Table dropped.

ops$tkyte%ORA11GR2> create table t ( x int, y int, z long raw );

Table created.

ops$tkyte%ORA11GR2> insert into t values ( 1, 2, '0a0a0a0a' );

1 row created.

ops$tkyte%ORA11GR2> select x, y, long_raw_length('select z from t where x = ? and y = ?', sys.odciVarchar2List(x,y) ) lrl
  2    from t;

         X          Y        LRL
---------- ---------- ----------
         1          2          4