oracle 使用 PL/SQL 如何将文件内容放入 blob？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/122909/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using PL/SQL how do you I get a file's contents in to a blob?
提问by Justsalt
I have a file. I want to get its contents into a blob column in my oracle database or into a blob variable in my PL/SQL program. What is the best way to do that?
我有一个文件。我想将其内容放入我的 oracle 数据库中的 blob 列或放入我的 PL/SQL 程序中的 blob 变量中。最好的方法是什么？
回答by cagcowboy
To do it entirely in PL/SQL, the file would need to be on the server, located in a directory which you'd need to define in the database. Create the following objects:
要完全在 PL/SQL 中完成，该文件需要位于服务器上，位于您需要在数据库中定义的目录中。创建以下对象：
CREATE OR REPLACE DIRECTORY
BLOB_DIR
AS
'/oracle/base/lobs'
/
CREATE OR REPLACE PROCEDURE BLOB_LOAD
AS
lBlob BLOB;
lFile BFILE := BFILENAME('BLOB_DIR', 'filename');
BEGIN
INSERT INTO table (id, your_blob)
VALUES (xxx, empty_blob())
RETURNING your_blob INTO lBlob;
DBMS_LOB.OPEN(lFile, DBMS_LOB.LOB_READONLY);
DBMS_LOB.OPEN(lBlob, DBMS_LOB.LOB_READWRITE);
DBMS_LOB.LOADFROMFILE(DEST_LOB => lBlob,
SRC_LOB => lFile,
AMOUNT => DBMS_LOB.GETLENGTH(lFile));
DBMS_LOB.CLOSE(lFile);
DBMS_LOB.CLOSE(lBlob);
COMMIT;
END;
/
回答by cagcowboy
Depends a bit on your environment. In Java you could do it something like this...
有点取决于您的环境。在 Java 中，你可以做这样的事情......
// Need as OracleConnection in mConnection
// Set an EMPTY_BLOB()
String update = "UPDATE tablename"+
" SET blob_column = EMPTY_BLOB()"+
" WHERE ID = "+id;
CallableStatement stmt = mConnection.prepareCall(update);
stmt.executeUpdate();
// Lock the row FOR UPDATE
String select = "BEGIN " +
" SELECT " + blob_column
" INTO ? " +
" FROM " + tablename +
" WHERE ID = '" + id + "'" +
" FOR UPDATE; " +
"END;";
stmt = mConnection.prepareCall(select);
stmt.registerOutParameter(1, java.sql.Types.BLOB);
stmt.executeUpdate();
BLOB blob = (BLOB) stmt.getBlob(1);
OutputStream bos = blob.setBinaryStream(0L);
FileInputStream fis = new FileInputStream(file);
// Code needed here to copy one stream to the other
fis.close();
bos.close();
stmt.close();
mConnection.commit();
But it really depends what environment / tools you're using. More info needed.
但这实际上取决于您使用的环境/工具。需要更多信息。
