0

I am trying to load some data from a file to NCLOB type in Oracle DB. File contains some multi-byte characters.

I followed this link and wrote a procedure to do that. But I see that some additional characters are being added to DB. Can anyone let me know what is wrong with the below code?

CREATE OR REPLACE PROCEDURE TEST_CLOB1_TGT_1537868348743 
 AS 
dest_TEXT3 NCLOB; 
src_TEXT3 BFILE := BFILENAME('EXAMPLE_LOB_DIR', 'TEXT3_TEST_CLOB1_TGT_1537868348743.lob'); 
dst_offset_TEXT3 number := 1 ;
src_offset_TEXT3 number := 1 ;
lang_ctx_TEXT3 number := DBMS_LOB.DEFAULT_LANG_CTX;
warning_TEXT3  number;

 BEGIN 

INSERT INTO "TEST_CLOB1_TGT" ("ID", "TEXT3") VALUES ( '1', EMPTY_CLOB() ) RETURNING TEXT3 INTO dest_TEXT3; 

DBMS_LOB.OPEN(src_TEXT3, DBMS_LOB.LOB_READONLY);
DBMS_LOB.LoadCLOBFromFile(
DEST_LOB => dest_TEXT3
,SRC_BFILE => src_TEXT3
,AMOUNT => DBMS_LOB.GETLENGTH(src_TEXT3) 
, DEST_OFFSET  => dst_offset_TEXT3 
, SRC_OFFSET  => src_offset_TEXT3 
, BFILE_CSID   => DBMS_LOB.DEFAULT_CSID 
, LANG_CONTEXT => lang_ctx_TEXT3 
, WARNING => warning_TEXT3 
); 
DBMS_LOB.CLOSE(src_TEXT3);

 COMMIT; 
 END; 
 /
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
user001
  • 991
  • 6
  • 16
  • 34
  • What extra characters are being added? I imagine it's a character set/encoding issue, so look at the encoding of the file and the DB character sets; but also check whether the problem is actually in the loaded data or just how you're viewing it after it's loaded. The `dump()` function can be useful to check what is really stored in the DB. – Alex Poole Sep 25 '18 at 10:42
  • As you mentioned it could be the issue with encoding as I see some "squares []" in DB. Now how should I resolve this? Should I use any conversion? – user001 Sep 25 '18 at 11:02
  • I tried loading the same file to CLOB datatype and it works fine. With NCLOB alone, I see this issue. File encoding is of UTF-8. I need a generic approach as the input file given to my code can be of any encoding and I am looking for Oracle API which handles that. Please advise. – user001 Sep 25 '18 at 12:09

0 Answers0