Oracle从目录中读取文件异常 [英] Oracle read File from Directory with exception

问题描述

  CREATE OR REPLACE FUNCTION loadBlobFromFile（p_file_name VARCHAR2）返回BLOB AS 
 dest_loc BLOB：= empty_blob（）; 
 src_loc BFILE：= BFILENAME（'TMT'，p_file_name）; 
 BEGIN 
  - 从OS 
 DBMS_LOB.OPEN（src_loc，DBMS_LOB.LOB_READONLY）开源源代码文件; 
 
  - 创建临时LOB对象
 DBMS_LOB.CREATETEMPORARY（
 lob_loc => dest_loc 
，cache => true 
，dur => dbms_lob.session 
）; 
 
  - 打开临时lob 
 DBMS_LOB.OPEN（dest_loc，DBMS_LOB.LOB_READWRITE）; 
 
 
  - 将二进制文件加载到临时LOB 
 DBMS_LOB.LOADFROMFILE（
 dest_lob => dest_loc 
，src_lob => src_loc 
，amount => DBMS_LOB.getLength（src_loc））; 
 
异常
当DBMS_LOB.operation_failed 
然后
返回empty_blob（）; 
 
 
  - 关闭lob对象
 DBMS_LOB.CLOSE（dest_loc）; 
 DBMS_LOB.CLOSE（src_loc）; 
 
  - 返回临时LOB对象
 RETURN dest_loc; 
 
 END loadBlobFromFile; 
 / 
  

我用

 从双重选择loadBlobFromFile（'TESTl.doc'）; 
  

当目录中存在文件时，我获取该文件。当文件不存在时，我得到空的Blob。

现在有一个最后一个问题。当文件的大小为0字节时，我会收到一个错误。

有人可以帮助我改进异常的函数，并在dbms_lob.open失败时返回一个empty_blob（）？

非常感谢你！

解决方案

我找到了解决方案。
这是我的最终代码，帮助
http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/DLP1/Default.aspx

函数：

  CREATE OR REPLACE FUNCTION loadBlobFromFile（p_file_name VARCHAR2）RETURN BLOB AS 
 dest_loc BLOB：= empty_blob（） ; 
 src_loc BFILE：= BFILENAME（'Directoryname'，p_file_name）; 
 BEGIN 
 BEGIN 
 
  - 从OS 
开始源二进制文件DBMS_LOB.OPEN（src_loc，DBMS_LOB.LOB_READONLY）; 
 
  - 创建临时LOB对象
 DBMS_LOB.CREATETEMPORARY（
 lob_loc => dest_loc 
，cache => true 
，dur => dbms_lob.session 
）; 
 
  - 打开临时lob 
 DBMS_LOB.OPEN（dest_loc，DBMS_LOB.LOB_READWRITE）; 
 
 
  - 将二进制文件加载到临时LOB 
 DBMS_LOB.LOADFROMFILE（
 dest_lob => dest_loc 
，src_lob => src_loc 
，amount => DBMS_LOB.getLength（src_loc））; 
 
 
异常
当DBMS_LOB.operation_failed --Wenn死亡日期nicht vorhanden ist 
然后
 DBMS_LOB.CLOSE（src_loc）; 
 return empty_blob（）; 
当DBMS_LOB.INVALID_ARGVAL  -  Wenn die Datei 0 KB总重量。 
然后
 DBMS_LOB.CLOSE（dest_loc）; 
 DBMS_LOB.CLOSE（src_loc）; 
 return empty_blob（）; 
 END; 
 
  - 关闭lob对象
 DBMS_LOB.CLOSE（dest_loc）; 
 DBMS_LOB.CLOSE（src_loc）; 
 
  - 返回临时LOB对象
 RETURN dest_loc; 
 
 END loadBlobFromFile; 
 / 
  

调用函数：

从双重选择loadBlobFromFile（'test.doc'）

greetz

I'm reading files from a Directory with the following function:

CREATE OR REPLACE FUNCTION loadBlobFromFile(p_file_name VARCHAR2) RETURN BLOB AS
  dest_loc  BLOB := empty_blob();
  src_loc   BFILE := BFILENAME('TMT', p_file_name);
BEGIN
  -- Open source binary file from OS
  DBMS_LOB.OPEN(src_loc, DBMS_LOB.LOB_READONLY);

  -- Create temporary LOB object
  DBMS_LOB.CREATETEMPORARY(
        lob_loc => dest_loc
      , cache   => true
      , dur     => dbms_lob.session
  );

  -- Open temporary lob
  DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE);


    -- Load binary file into temporary LOB
    DBMS_LOB.LOADFROMFILE(
        dest_lob => dest_loc
      , src_lob  => src_loc
      , amount   => DBMS_LOB.getLength(src_loc));

  exception 
    when DBMS_LOB.operation_failed
    then
     return empty_blob();


 -- Close lob objects
  DBMS_LOB.CLOSE(dest_loc);
  DBMS_LOB.CLOSE(src_loc);

   -- Return temporary LOB object
  RETURN dest_loc;

END loadBlobFromFile;
/

I call the function with

select loadBlobFromFile('TESTl.doc') from dual;

When the File exists in the directory, I get the file. When the file doesn't exist, I get the empty Blob.

Now there is one last problem. When the File has a size of 0 Byte I get an error.

Can somebody help me to improve the function with an exception and return an empty_blob() when the dbms_lob.open fails?

Thank Your Very Much!

解决方案

I found the resolution. Here is my final code with help from http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/DLP1/Default.aspx

The code of the function:

    CREATE OR REPLACE FUNCTION loadBlobFromFile(p_file_name VARCHAR2) RETURN BLOB AS
  dest_loc  BLOB := empty_blob();
  src_loc   BFILE := BFILENAME('Directoryname', p_file_name);
BEGIN
  BEGIN

    -- Open source binary file from OS
    DBMS_LOB.OPEN(src_loc, DBMS_LOB.LOB_READONLY);

    -- Create temporary LOB object
    DBMS_LOB.CREATETEMPORARY(
          lob_loc => dest_loc
        , cache   => true
        , dur     => dbms_lob.session
    );

    -- Open temporary lob
    DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE);


    -- Load binary file into temporary LOB
    DBMS_LOB.LOADFROMFILE(
        dest_lob => dest_loc
      , src_lob  => src_loc
      , amount   => DBMS_LOB.getLength(src_loc));


   exception 
    when DBMS_LOB.operation_failed --Wenn die Datei nicht vorhanden ist
    then
      DBMS_LOB.CLOSE(src_loc);
       return empty_blob();
    when DBMS_LOB.INVALID_ARGVAL  --Wenn die Datei 0 KB gross ist.
    then
      DBMS_LOB.CLOSE(dest_loc);
  DBMS_LOB.CLOSE(src_loc);
       return empty_blob();
  END;

 -- Close lob objects
  DBMS_LOB.CLOSE(dest_loc);
  DBMS_LOB.CLOSE(src_loc);

     -- Return temporary LOB object
  RETURN dest_loc;

END loadBlobFromFile;
/

Call the function:

select loadBlobFromFile('test.doc') from dual;

greetz

这篇关于Oracle从目录中读取文件异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！