在Java存储过程中创建java.sql.blob实例 [英] Create java.sql.blob instance in java stored procedure

问题描述

这是我第一次在stackoverflow中发布问题， 我需要编写一个Java存储过程来创建一个excel文件，并返回一个包含以字节为单位的文件数据的blob.

This is my first time posting a question in stackoverflow, I need to write a java stored procedure that creates an excel file and returns a blob containing the file data in bytes.

我的pl/sql函数的格式如下

My pl/sql function is in the following form

function test_create_excel(i_username IN varchar2) return BLOB
   AS LANGUAGE JAVA NAME 'NTO.Excel.ExcelFunctions.PushToExcel( java.lang.String ) return java.sql.Blob';

我的Java方法如下

public static java.sql.Blob TestPushToExcel(String username) throws IOException, SQLException{
        //create excel file, read content to byte array and set to a blob
    }

我的问题是我找不到任何方法来创建java.sql.Blob的实例，因此我可以使用blob.setBinaryStream(..)方法写入文件数据字节数组.

My problem is that I cannot find any way to create an instance of java.sql.Blob so that i can use the blob.setBinaryStream(..) method to write the file data byte array.

我尝试使用 SerialBlob 实现，但是会导致以下oracle错误

I tried to use the SerialBlob implementation but it results in the following oracle error

ORA-00932:数据类型不一致:预期返回值为 用户定义的Java类的实例，可转换为Oracle类型 有一个无法转换的对象

ORA-00932: inconsistent datatypes: expected a return value that is an instance of a user defined Java class convertible to an Oracle type got an object that could not be converted

有人遇到过这个问题吗?如果可以，您可以分享一下如何解决这个问题.

has anyone come across this issue and if so can you share on how you got through it.

先谢谢您

编辑 JAVA

public static oracle.sql.BLOB getBlob(byte[] data) throws SQLException, IOException{
        oracle.jdbc.OracleConnection conn = (oracle.jdbc.OracleConnection)new OracleDriver().defaultConnection();

        oracle.sql.BLOB retBlob = oracle.sql.BLOB.createTemporary(conn, true, oracle.sql.BLOB.DURATION_SESSION);

        java.io.OutputStream outStr = retBlob.setBinaryStream(0);
        outStr.write(data);
        outStr.flush();

        return retBlob;
  }


public static ExcelFileStore PushToExcel(String userId) throws IOException, SQLException{

        ExcelFileStore fileStore = new ExcelFileStore();        
        fileStore.NU_USERID = userId;
        fileStore.CreatedTime = new java.sql.Date(new Date().getTime());
        fileStore.Last_Updated = new java.sql.Date(new Date().getTime());        
        fileStore.FileSize = fileData.length;
        fileStore.FileData = getBlob(fileData);        
        return fileStore;
    }

PL/SQL

 function test_create_excel(i_username IN varchar2) return EXCELFILESTORE    AS LANGUAGE JAVA NAME 'NTO.Excel.ExcelFunctions.PushToExcel( java.lang.String, ) return OracleObjects.ExcelFileStore';

OracleObject.ExcelfileStore 是实现java.sql.SqlData的类，而 EXCELFILESTORE 是oracle中的UDT.

OracleObject.ExcelfileStore is a class that implements java.sql.SqlData and EXCELFILESTORE is a UDT in oracle.

我使用' sys.dbms_java.loadjava '

我希望您能理解我的问题，因为我对pl/sql编程还很陌生

I hope you understand my question, as i'm quite new to pl/sql programming

推荐答案

我错了. 可以完成.花了我一段时间使它开始工作，但是最后，这是一个有效的示例:

I was wrong. It can be done. Took me a while to get it to work, but, finally, here is a working example:

import oracle.jdbc.driver.*;

public class TestBlob {
  public static oracle.sql.BLOB getBlob(String username) throws Exception {
    oracle.jdbc.OracleConnection conn = 
      (oracle.jdbc.OracleConnection)new OracleDriver().defaultConnection();

    oracle.sql.BLOB retBlob =
      oracle.sql.BLOB.createTemporary(conn,
                                      true,
                                      oracle.sql.BLOB.DURATION_SESSION);

    java.io.OutputStream outStr = retBlob.setBinaryStream(0);
    outStr.write(username.getBytes());
    outStr.flush();

    return retBlob;
  }
}

如您所见，我将oracle.sql.BLOB用于结果.我使用BLOB类的静态createTemporary方法创建它，并指定应在会话持续时间内创建它(oracle.sql.BLOB.DURATION_SESSION参数).

As you can see, I have used the oracle.sql.BLOB for the result. I create it with the static createTemporary method of the BLOB class, specifying that it should be created for the duration of the session (oracle.sql.BLOB.DURATION_SESSION parameter).

然后，获取OutputStream并写入数据.需要冲洗.

Then, I obtain the OutputStream and write the data. Flush was needed.

create or replace FUNCTION getBlobWrp (username IN VARCHAR2) RETURN BLOB
  AS LANGUAGE JAVA NAME
              'TestBlob.getBlob(java.lang.String) return oracle.sql.BLOB';

测试:

DECLARE
  l_blob BLOB;
BEGIN
  l_blob := getBlobWrp('test');

  dbms_output.put_line(UTL_RAW.CAST_TO_VARCHAR2(l_blob));
END;

输出:

test

(先前的回答)

我认为您应该在test_create_excel函数中具有一个IN OUT BLOB参数(将其更改为过程)，并在Java存储方法中对该参数进行操作.我曾经见过这种方法.

I think you should have an IN OUT BLOB parameter in your test_create_excel function (change it to a procedure), and operate on that parameter inside your Java stored method. I saw that approach once.

在调用test_create_excel之前，您应该创建一个BLOB对象:

Before calling the test_create_excel, you should create a BLOB object:

DECLARE
  l_blob BLOB;
BEGIN
  DBMS_LOB.createtemporary(l_blob, TRUE);
  test_create_excel('username', l_blob);
END;

修改

我认为您要执行的操作是不可能的.但是，您可以将上面的代码包装在另一个函数中.有点混乱，但是随后您将拥有一个返回blob的函数:

I don't think what you're trying to do is possible. However, you could wrap the above code in another function. It's a bit messy, but then you'll have a function which returns the blob:

CREATE OR REPLACE FUNCTION get_excel_blob(p_username VARCHAR2) RETURN BLOB
AS
  l_blob BLOB;
BEGIN
  DBMS_LOB.createtemporary(l_blob, TRUE);
  test_create_excel(p_username, l_blob);
  RETURN l_blob;
END;

这篇关于在Java存储过程中创建java.sql.blob实例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！