DBMS_LOB

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Poston Wed, 24 May 2006 9:56 am

Ol guys, given the very large amount of data in the file, I'm trying to insert him in a CLOB field, by m:
Code: Select all
        DBMS_LOB.fileopen( arq, DBMS_LOB.FILE_READONLY );
       
        DBMS_LOB.loadfromfile(CLOB_LOC,arq,dbms_lob.getlength(arq));
       
        Dbms_Lob.FileClose ( arq );
to efeutar the loadfromfile in a clob type vari CLOB_LOC level he returns the error: Target session 9:52:49: ORA-06502: PL/SQL: error: invalid LOB locator specified: select from ALL_DIRECTORIES where DIRECTORY_NAME DIRECTORY_PATH = ' DIR_TMP ';

The directory in which the file is being read correctly inserted in ALL_DIRECTORIES est and n the error occurs in the Open, it opens the file and closes, by m n o loads the contents of the file in vari vel CLOB.

Grateful for the help!

[] 's
MuLtAnI
Location: Videira - SC


Poston Thu, 25 May 2006 4:48 pm

Hi, I created a function to read the file and she's reading in good, if not for the problem of the Clob to returns does not contain the entire contents of the file ... Let's say the half of it.

Code: Select all
FUNCTION FLEARQUIVO( arq IN varchar2, dir IN varchar2 )
  RETURN clob IS
BEGIN
DECLARE
    arquivo BFILE;

    dest_clob   CLOB;
    dst_offset  number := 1 ;
    src_offset  number := 1 ;
    warning     number;
   
    BEGIN
        arquivo := BFILENAME( dir, arq );
       
        INSERT INTO test_clob(id, file_name, arquivo, timestamp)
        VALUES(1001, arq, empty_clob(), sysdate)
        RETURNING arquivo INTO dest_clob;
       
        DBMS_LOB.OPEN(arquivo, DBMS_LOB.LOB_READONLY);
       
        DBMS_LOB.LoadFromFile(
          DEST_LOB => dest_clob
        , SRC_LOB  => arquivo
        , AMOUNT   => DBMS_LOB.GETLENGTH( arquivo ) );
       
        DBMS_LOB.CLOSE( arquivo );
       
        commit;

        RETURN dest_clob;
    END;
END;
does anybody know why this might be happening? Since my Amount is the Length of the file ... thanks ...

[] 's
MuLtAnI
Location: Videira - SC



  • See also
    Replies
    Views
    Last Post


      Return to PL/SQL

      Who is online

      Users browsing this forum: No registered users and 2 guests