Re: Selecting LONG RAWs

From: Morten <usenet_at_kikobu.com>
Date: Thu, 18 Jul 2002 09:32:25 +0200
Message-ID: <3D366F09.506@kikobu.com>

Anthony Jackson wrote:
> After reading up some on LONG RAWs I found out that a LONG RAW column
> can store upto 2gb, but a PL/SQL LONG RAW has a 32k limit.
>
> So, How can I SELECT a LONG RAW > 32k in PL/SQL?
>
> Is there a way to do it like dbms_lob does?
>
> Is there a way to get all 2GBs using Java, ProC or *cringe* OCI?

What's your target? Another table? The filesystem? There are several ways of reading the data, the proper way depends on what you wish to accomplish. You can read chunks using PL/SQL,

   v_LongCursorSQL := 'SELECT data FROM table WHERE id = '||v_ID;    v_LongCursorID := DBMS_SQL.OPEN_CURSOR;

   DBMS_SQL.PARSE(v_LongCursorID, v_LongCursorSQL, DBMS_SQL.NATIVE);    DBMS_SQL.DEFINE_COLUMN_LONG (v_LongCursorID, 1);

   v_LongRowCount := DBMS_SQL.EXECUTE_AND_FETCH (v_LongCursorID);

   IF v_LongRowCount > 0 THEN

     DBMS_SQL.COLUMN_VALUE_LONG(v_LongCursorID, 1, 30000, 0,
                                v_LongContent, v_LongLength);

   END IF;    --v_LongContent now contains the first 30k bytes from the data column    --Do your stuff here...

Or you can stream the data from the table using Java.

try {

   conn = connect();
   stmt = conn.createStatement();
   set  = stmt.executeQuery("SELECT id, content FROM test_long\n"+
			   "WHERE length = "+length);

   while(set.next()) {                 

     InputStream is  = set.getAsciiStream(2);
     OutputStream os = new FileOutputStream(...);
		
     int bytes = -1;
     byte[] buf = new byte[1024];
		
     while((bytes = is.read(buf)) != -1) {
	    os.write(buf,0,bytes);
     }
		
     os.flush();

     try {
       is.close();
       os.close();
     }
     catch(Exception e) {
       System.out.println("E (close): "+e);
     }

   }
}
catch(Exception e) {

   System.out.println("E: "+e);
   return;
}

HTH, Morten Received on Thu Jul 18 2002 - 02:32:25 CDT