Home » SQL & PL/SQL » SQL & PL/SQL » Reading a CLOB object (Oracle 10g)
Reading a CLOB object [message #406741] Fri, 05 June 2009 10:12 Go to next message
raghavakumarjoshi
Messages: 9
Registered: May 2007
Location: Bangalore
Junior Member
Hi All,

I have used dbms_lob.loadclobfromfile method to load a clob from file. my clob column in the table is Tilda Seperated values (TSL). I have used this method in two different functions. Parsing of first function is proper i got the following error in the second funtion when i used the same method.
dbms_lob.loadclobfromfile(v_manual_cl,
v_file_bf,
--dbms_lob.getlength(v_file_bf),
dbms_lob.lobmaxsize,
l_src_offset,
l_dst_offset,
l_charset_id,
l_lang_ctx,
l_warning);
i got the following error:
ORA-22994: source offset is beyond the end of the source LOB

in the second function my Source and Destination offsets( l_src_offset,l_dst_offset)are initialized to 1.

Appreciates a valuable feedback.

Thanks & Regards
Joshi
Re: Reading a CLOB object [message #406742 is a reply to message #406741] Fri, 05 June 2009 10:16 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

using sqlplus now CUT & PASTE whole session so we can see exactly what you are doing & how Oracle responds.

22994, 00000, "source offset is beyond the end of the source LOB"
// *Cause: The source offset for a LOB COPY or LOB LOADFROMFILE
// is beyond the end of the source LOB.
// *Action: Check the length of the LOB and then adjust the source offset.

[Updated on: Fri, 05 June 2009 10:17]

Report message to a moderator

Re: Reading a CLOB object [message #406745 is a reply to message #406742] Fri, 05 June 2009 10:29 Go to previous messageGo to next message
raghavakumarjoshi
Messages: 9
Registered: May 2007
Location: Bangalore
Junior Member
Hi,

I am executing a package from a small interface. As per my previous posting, i have explained the issue. Herewith i am posting the code snippet for the function used. Please see the code and guide me some logic. Please check the attachment for the code.

Thanks in advance.

Regards
Raghava Joshi
Re: Reading a CLOB object [message #406747 is a reply to message #406741] Fri, 05 June 2009 10:36 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Good Luck with finding a solution.
Re: Reading a CLOB object [message #406753 is a reply to message #406747] Fri, 05 June 2009 11:30 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
This may help - it parses a clob into lines delimited by CR-LF or LF. Once you have that, it's easy to parse each line individually.
CREATE OR REPLACE function clob2line (p_clob in clob) 
   return row_type pipelined 
is
 -- create or replace type ROW_TYPE as table of varchar2(4000)
 l_last    number;
 l_current number;
begin
  l_last := 1;
   while l_last <= dbms_lob.getlength(p_clob) loop
      l_current := dbms_lob.instr( p_clob, chr(10), l_last, 1 );
        exit when (nvl(l_current,0) = 0);
      -- chr(13)||chr(10) is set of characters in rtrim. handles LF or CRLF
      pipe row (rtrim(dbms_lob.substr( p_clob, l_current-l_last+1,l_last ), chr(13)||chr(10)));
      l_last := l_current+1;
   end loop;
   -- remaining piece if no newline at end. cleanup to be safe
   pipe row (rtrim(dbms_lob.substr( p_clob, 32767, l_last ), chr(13)||chr(10)));
   return;
end;
/

In my experience external table is a faster approach, but needs to be called serially becuase the external table definition is altered for each specific file you want to load. I haven't explored this much - it didn't impact me. Soemthing like this (untested)...
   --grant create any directory to SCOTT;

   --create or replace directory MY_DIR as 'c:\my_dir';
   --CREATE TABLE EXT_FILE
   --(
   --  LINE     VARCHAR2(4000)
   --)
   --ORGANIZATION EXTERNAL
   --  (  TYPE ORACLE_LOADER
   --     DEFAULT DIRECTORY MY_DIR
   --     ACCESS PARAMETERS
   --       ( RECORDS DELIMITED BY NEWLINE
   --      NOBADFILE
   --      NODISCARDFILE
   --      NOLOGFILE
   --      DATE_CACHE 0
   --      FIELDS
   --         MISSING FIELD VALUES ARE NULL
   --         REJECT ROWS WITH ALL NULL FIELDS
   --         (  LINE          CHAR (4000)  )
   --                         )
   --     LOCATION ('my_file.txt')
   --  )
   --REJECT LIMIT UNLIMITED;


   PROCEDURE loadfile (p_file IN VARCHAR2)
   AS
      v_ddl   VARCHAR2 (200);
   BEGIN
      v_ddl := 'alter table EXT_FILE location(' || '''' || p_file || ''')';

      EXECUTE IMMEDIATE v_ddl;

      for i in (SELECT ROWNUM rn, line FROM ext_file) loop
         dbms_output.put_line('line='||to_char(i.rn)||'-->'||i.line);
      end loop;

   END;




Please update this thread with your outcome.



Re: Reading a CLOB object [message #406790 is a reply to message #406741] Fri, 05 June 2009 22:36 Go to previous messageGo to next message
raghavakumarjoshi
Messages: 9
Registered: May 2007
Location: Bangalore
Junior Member
I will try with this algorithm.

Thank you.
Re: Reading a CLOB object [message #406806 is a reply to message #406741] Sat, 06 June 2009 00:27 Go to previous messageGo to next message
raghavakumarjoshi
Messages: 9
Registered: May 2007
Location: Bangalore
Junior Member
Hi,

Algorithm is clear, i have never worked with pipelined functions. When i surfed net the explanation is not that clear for me. How to convert this function as a normal function where i can include this in my package and call from the calling area with appropriate parameters. When i have incorporated this code and tried i am not able to compile the package. I have also created type
CREATE OR REPLACE TYPE ROW_TYPE AS TABLE OF VARCHAR2(32767);

Please give your valuable advice.

Thanks & Regards
Joshi
Re: Reading a CLOB object [message #407327 is a reply to message #406806] Tue, 09 June 2009 11:15 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
There is nothing special about calling a pipelined function. It's main apeal is performance. I forget whether it'll work with 32767. Try with 4000 first then increase to 32k once the 4000 works.
Previous Topic: sql for incremental load
Next Topic: Dynamic RowType Columns
Goto Forum:
  


Current Time: Sat Dec 03 05:38:46 CST 2016

Total time taken to generate the page: 0.06450 seconds