Re: Java Stored Proc Question

From: Grace <grace.wallen_at_boeing.com>
Date: Tue, 30 Apr 2002 16:21:53 GMT
Message-ID: <3CCEC4A1.192962AF@boeing.com>

Thank you. The code that worked was run in JDeveloper 3.1.1.2, using JDK 1.2.2 and
JDBC thin driver, 8.1.6. I have Oracle 8.1.6 client and Oracle 8.1.6 Jserver.
TO_LOB is not available until 8.1.7.
Here's the code for the stored proc:

import java.io.*;
import java.sql.*;

import oracle.sql.BLOB;
import oracle.jdbc.driver.*;
import oracle.jdeveloper.cm.*;  //Connection Mgr class

public class raw2blob extends Object {

    public static void convertRaw2Blob( String tbl, String colRaw, String colId, String inputId,

                        long[] newBlobId, String[] pSuccess ) throws

SQLException, IOException

    {

        pSuccess[0] = "Yes";


        // Create the database connections

        Connection conn =
DriverManager.getConnection("jdbc:default:connection:");
        conn.setAutoCommit(false);
        Statement stmt = null;
        CallableStatement cstmt = null;
        ResultSet rset = null;
        String sql_getraw = new String();
     try
     {


        // ********* First get the LONG RAW data ************/

        stmt = conn.createStatement();      
        sql_getraw = "select " + colRaw + " FROM " + tbl + " Where " +
colId
                            + " = " + inputId;
        rset = stmt.executeQuery(sql_getraw);


        // Get the column from the database as a byte array

        byte[] byteBuffer = rset.getBytes(1);


        // Determine the length of the raw data

        int amount = byteBuffer.length;



        // ********* Second, create an empty blob, return locator, write

raw buffer to output stream ********/
         cstmt = conn.prepareCall(
            "begin insert into blob_store (blob_id, blob_object,
create_date, create_user)"
            + " values ( blob_store_sq.nextval , empty_blob(), sysdate,
user)"
            + " returning blob_object, blob_id into ?, ?; end;" );

        cstmt.registerOutParameter(1, OracleTypes.BLOB);
        cstmt.registerOutParameter(2, Types.NUMERIC);
        cstmt.execute();
        oracle.sql.BLOB blob = ((OracleCallableStatement)
cstmt).getBLOB(1);
        long newId = cstmt.getLong(2);
        newBlobId[0] = newId;


        // Create a binary output stream, so can write the whole byte

array
        java.io.OutputStream  out = blob.getBinaryOutputStream();
        out.write(byteBuffer);
        out.close();
        conn.commit();

    } // end of try block
    catch (SQLException e) {

      if ( e.getMessage().equals("Exhausted Resultset") )
        pSuccess[0] = "SQL Error: Data does not exist";
      else
        pSuccess[0] = "SQL Error in raw2blob: " + e.getMessage();       
      conn.rollback();

    }
    catch (Exception e) {

      pSuccess[0] = "Error in raw2blob: " + e.getMessage();
      conn.rollback();

    }
    finally {

        try {
            if (null != stmt) stmt.close();
            if (null != cstmt) cstmt.close();
            /* Do NOT close connection if creating a stored proc
            if (null != conn) {
                conn.close();
                conn = null;
            } */
        } catch (Exception e) {}

    } // end of finally
} // end of main
} // end of class

---

"Vladimir M. Zakharychev" wrote:

> 
> This might be due to differences in server-side JDBC driver. You didn't
> post any code to dig, neither you gave the version of the Oracle client
> (and JDBC driver) where it works, so it's hard to tell what the problem
> actually is. Exact version of the database (5 digits) can also help. And
> you don't need Java to convert LONG RAW to BLOB. Look up TO_LOB
> function in SQL Reference.
> 
> --
> Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
> Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
> All opinions are mine and do not necessarily go in line with those of my employer.
> 
> "Grace" <gwallen_at_boeing.com> wrote in message news:3CCDBCFD.199A482A_at_boeing.com...
> > I have a Java JDBC program that selects a LONG RAW value from a table
> > and converts it to a BLOB in another table.
> >
> > This works fine as a standalone JDBC program, but when I register it as
> > a Java Stored Procedure in Oracle (8.1.6) and call it with a PL/SQL
> > script, it only writes a maximum of 32767 bytes.
> >
> > Anyone know why ?
> >
> > Thanks in advance.  Please post response or email to
> > grace.wallen_at_boeing.com
> >
> > Grace Wallen
> > Boeing SSG
> > 253-657-5810

Received on Tue Apr 30 2002 - 11:21:53 CDT