Home » SQL & PL/SQL » SQL & PL/SQL » How to insert a txt file into a Blob Column? (Oracle, 10g Rel-2, Windows )
How to insert a txt file into a Blob Column? [message #427102] Wed, 21 October 2009 02:41 Go to next message
tamzidulamin
Messages: 132
Registered: October 2009
Location: Dhaka
Senior Member
Hello Dear's,

I want to insert a txt file or pdf file into a blob column.

any can help me...?

Tamzid.
Re: How to insert a txt file into a Blob Column? [message #427104 is a reply to message #427102] Wed, 21 October 2009 02:43 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do it in the same way that an image.
Precise your programming language

Regards
Michel
Re: How to insert a txt file into a Blob Column? [message #427105 is a reply to message #427102] Wed, 21 October 2009 02:50 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
SCOTT@orcl_11g> CREATE TABLE my_files
  2    (id   NUMBER,
  3  	doc  BLOB)
  4  /

Table created.

SCOTT@orcl_11g> CREATE OR REPLACE DIRECTORY DIR_TESTCASE AS 'c:\oracle11g'
  2  /

Directory created.

SCOTT@orcl_11g> declare
  2    Dest_loc BLOB;
  3    Src_loc	BFILE;
  4  BEGIN
  5    INSERT INTO my_files (id, doc) VALUES (1, EMPTY_BLOB())
  6    RETURNING doc INTO Dest_loc;
  7    Src_loc := BFILENAME ('DIR_TESTCASE', 'banana.pdf');
  8    DBMS_LOB.FILEOPEN (Src_loc, DBMS_LOB.LOB_READONLY);
  9    DBMS_LOB.LOADFROMFILE (Dest_loc, Src_loc, dbms_lob.getlength (Src_loc));
 10    DBMS_LOB.FILECLOSE (Src_loc);
 11    INSERT INTO my_files (id, doc) VALUES (2, EMPTY_BLOB())
 12    RETURNING doc INTO Dest_loc;
 13    Src_loc := BFILENAME ('DIR_TESTCASE', 'test1.doc');
 14    DBMS_LOB.FILEOPEN (Src_loc, DBMS_LOB.LOB_READONLY);
 15    DBMS_LOB.LOADFROMFILE (Dest_loc, Src_loc, dbms_lob.getlength (Src_loc));
 16    DBMS_LOB.FILECLOSE (Src_loc);
 17  END;
 18  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> INSERT INTO my_files (id, doc)
  2  VALUES (3, UTL_RAW.CAST_TO_RAW ('blob data for id 3'))
  3  /

1 row created.

SCOTT@orcl_11g> COMMIT
  2  /

Commit complete.

SCOTT@orcl_11g> SELECT id, DBMS_LOB.GETLENGTH (doc) FROM my_files
  2  /

        ID DBMS_LOB.GETLENGTH(DOC)
--------------------------------------------------------------------------------
-----------------------
         1                  222824
         2                   19968
         3                      18

SCOTT@orcl_11g> CREATE INDEX my_files_idx ON my_files(doc)
  2  	INDEXTYPE IS CTXSYS.CONTEXT
  3  /

Index created.

SCOTT@orcl_11g> SELECT COUNT (*) FROM dr$my_files_idx$i
  2  /

  COUNT(*)
--------------------------------------------------------------------------------
       308

SCOTT@orcl_11g> COLUMN first_45 FORMAT A45
SCOTT@orcl_11g> SELECT id,
  2  	    UTL_RAW.CAST_TO_VARCHAR2 (DBMS_LOB.SUBSTR (doc, 18, 1)) AS first_45
  3  FROM   my_files
  4  /

        ID FIRST_45
--------------------------------------------------------------------------------
---------------------------------------------
         1 %PDF-1.5
%âãÏÓ
           1

         2 ÐÏࡱá
         3 blob data for id 3

SCOTT@orcl_11g> EXEC CTX_DOC.SET_KEY_TYPE ('ROWID')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> COLUMN keywords_in_context FORMAT A45 WORD_WRAPPED
SCOTT@orcl_11g> SELECT id,
  2  	    CTX_DOC.SNIPPET
  3  	      ('MY_FILES_IDX',
  4  	       ROWID,
  5  	       'contents OR (fruit of the month) OR data',
  6  	       '<<',
  7  	       '>>')
  8  	      AS keywords_in_context
  9  FROM   my_files
 10  WHERE  CONTAINS (doc, 'contents OR (fruit of the month) OR data') > 0
 11  /

        ID KEYWORDS_IN_CONTEXT
--------------------------------------------------------------------------------
---------------------------------------------
         1 <<Fruit of the Month>>
           Banana
           Bananas are the most popular

         2 This is the original <<contents>> of
           test1.doc.

         3 blob <<data>> for id 3

SCOTT@orcl_11g> 



Re: How to insert a txt file into a Blob Column? [message #427109 is a reply to message #427102] Wed, 21 October 2009 03:23 Go to previous messageGo to next message
tamzidulamin
Messages: 132
Registered: October 2009
Location: Dhaka
Senior Member
Dear Cadot,

I m writting the following Procedure to convert a blob file form a Clob file.But when i call the procedure, the eror message raised :
ORA-06502: PL/SQL Numeric or Value error: invalid LOB Locator Specified: ORA-22275


CREATE OR REPLACE Procedure MICR.Dpr_fileToBlob(Fname in VARCHAR2, Fdir in VARCHAR2, OutBlob out BLOB)

IS

fclob CLOB;
fblob BLOB;
theBFile BFILE;

Csrc_offset NUMBER :=1;
Cdest_offset NUMBER :=1;
Clang_context NUMBER :=1;
CWarning NUMBER :=0;

Bdest_offset Integer:=1;
Bsrc_offset Integer:=1;
BLang_Context Integer:=1;
BWarning Integer:=0;

BEGIN

dbms_lob.createtemporary(fclob,FALSE,DBMS_LOB.SESSION);

theBFile := BFileName(Fdir,Fname);

dbms_lob.fileOpen(theBFile);

dbms_lob.loadClobFromFile(dest_lob => fclob,
src_bfile => theBFile,
amount => dbms_lob.getLength(theBFile),
dest_offset => Cdest_offset,
src_offset => Csrc_offset,
bfile_csid => DBMS_LOB.default_csid,
lang_context => Clang_context,
warning => CWarning
);

dbms_lob.convertToBlob(dest_lob => fblob,
src_clob => fclob,
amount => dbms_lob.getLength(fclob),
dest_offset => Bdest_offset,
src_offset => Bsrc_offset,
blob_csid => DBMS_LOB.default_csid,
lang_context => BLang_Context,
warning => BWarning);

dbms_lob.fileClose(theBFile);

OutBlob := fblob;

End;
/

Re: How to insert a txt file into a Blob Column? [message #427115 is a reply to message #427102] Wed, 21 October 2009 03:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You should really put a TXT file (assuming it's just plain text) into a CLOB rather than a BLOB.
Re: How to insert a txt file into a Blob Column? [message #427116 is a reply to message #427109] Wed, 21 October 2009 03:49 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-22275: invalid LOB locator specified
 *Cause:  There are several causes:  (1) the LOB locator was never
          initialized; (2) the locator is for a BFILE and the routine
          expects a BLOB/CLOB/NCLOB locator; (3) the locator is for a
          BLOB/CLOB/NCLOB and the routine expects a BFILE locator;
          (4) trying to update the LOB in a trigger body -- LOBs in
          trigger bodies are read only; (5) the locator is for a
          BFILE/BLOB and the routine expects a CLOB/NCLOB locator;
          (6) the locator is for a CLOB/NCLOB and the routine expects
          a BFILE/BLOB locator;
 *Action: For (1), initialize the LOB locator by selecting into the locator
          variable or by setting the LOB locator to empty.  For (2),(3),
          (5) and (6)pass the correct type of locator into the routine.
          For (4), remove the trigger body code that updates the LOB value.

At which line have you this error?

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Use SQL*Plus and copy and paste your session.

Regards
Michel

[Updated on: Wed, 21 October 2009 03:50]

Report message to a moderator

Re: How to insert a txt file into a Blob Column? [message #427118 is a reply to message #427109] Wed, 21 October 2009 03:59 Go to previous messageGo to next message
tamzidulamin
Messages: 132
Registered: October 2009
Location: Dhaka
Senior Member
Dear Cadot,

I Solve my problem from the following Code.

Thanks a lot.

Tamzid.

CREATE OR REPLACE Procedure MICR.Dpr_fileToBlob(Fname in VARCHAR2, Fdir in VARCHAR2, OutBlob out BLOB)

IS

fblob BLOB;
theBFile BFILE;

Bsrc_offset NUMBER :=1;
Bdest_offset NUMBER :=1;

BEGIN

dbms_lob.createtemporary(fblob,FALSE,DBMS_LOB.SESSION);

theBFile := BFileName(Fdir,Fname);

dbms_lob.fileOpen(theBFile);

dbms_lob.loadblobfromfile(dest_lob => fblob ,
src_bfile => theBFile ,
amount => dbms_lob.getLength(theBFile),
dest_offset => Bdest_offset,
src_offset => Bsrc_offset
);

dbms_lob.fileClose(theBFile);

OutBlob := fblob;

End;
/
Re: How to insert a txt file into a Blob Column? [message #427151 is a reply to message #427118] Wed, 21 October 2009 05:58 Go to previous message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for your feedback, however please read how to format your post before your next question.

Regards
Michel
Previous Topic: Execute Procedure
Next Topic: Date format
Goto Forum:
  


Current Time: Sun Sep 25 23:13:34 CDT 2016

Total time taken to generate the page: 0.04571 seconds