Home » SQL & PL/SQL » SQL & PL/SQL » Problem while getting a line (Oracle 10g)
Problem while getting a line [message #406816] Sat, 06 June 2009 01:54 Go to next message
raghavakumarjoshi
Messages: 9
Registered: May 2007
Location: Bangalore
Junior Member
Hi,

I am facing a problem while getting a line. I am trying to read the clob object from my table and using this stub i am able to get theline by line, but one of my line size is 34340. Not able to get that line and insert into table. Can anyone tell me my mistake in this stub.
DECLARE
   v_file_bf    BFILE;
   v_manual_cl  CLOB;
   l_last    NUMBER;
   l_current NUMBER;
   v_output CLOB;
BEGIN
     BEGIN
         SELECT mastertxt_bf, manual_cl
         INTO   v_file_bf, v_manual_cl
         FROM   iftb_clob_catalog
         WHERE  id = '2' FOR UPDATE;
      EXCEPTION
         WHEN no_data_found THEN
            v_file_bf   := NULL;
            v_manual_cl := NULL;
      END;
   l_last := 1;
   WHILE l_last <= dbms_lob.getlength(v_manual_cl)
   LOOP
      dbms_output.put_line('Lenght is..'||dbms_lob.getlength(v_manual_cl));
      l_current := dbms_lob.instr(v_manual_cl, chr(10), l_last, 1);
      EXIT WHEN(nvl(l_current, 0) = 0);
      dbms_output.put_line('l_current..'||l_current);
      dbms_output.put_line('l_last..'||l_last);
      v_output:= (rtrim(dbms_lob.substr(v_manual_cl, l_current - l_last + 1, l_last),
                     chr(13) || chr(10)));
   --   dbms_output.put_line((rtrim(dbms_lob.substr(v_manual_cl, l_current - l_last + 1, l_last),
   --                 chr(13) || chr(10))));
    INSERT INTO clob_output VALUES(v_output,l_last);
    dbms_output.put_line(v_output);
    l_last := l_current + 1;
     dbms_output.put_line('l_last value is...'||l_last);
  END LOOP;
   dbms_output.put_line((rtrim(dbms_lob.substr(v_manual_cl, 32767, l_last), chr(13) || chr(10))));
END;

Appreciates a valuable response.
Thanks & Regards
Joshi

[EDITED by LF: applied [code] tags]

[Updated on: Sat, 06 June 2009 10:06] by Moderator

Report message to a moderator

Re: Problem while getting a line [message #406824 is a reply to message #406816] Sat, 06 June 2009 03:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"I am facing a problem" and "I am unable" are not Oracle errors.
Copy and paste your SQL*Plus session showing the problem.

Before 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.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Problem while getting a line [message #406831 is a reply to message #406824] Sat, 06 June 2009 03:52 Go to previous messageGo to next message
raghavakumarjoshi
Messages: 9
Registered: May 2007
Location: Bangalore
Junior Member
Micheal,

I got this error while executing the previous block. Sorry for not posting correctly. I am new to this forums. Will follow all the guidelines from my next posting.

Error:ORA-21560: argument 2 is null, invalid, or out of range

Thanks & Regards
Raghava Joshi
Re: Problem while getting a line [message #406839 is a reply to message #406831] Sat, 06 June 2009 05:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste your session.
And follow the guidelines now.

Regards
Michel
Re: Problem while getting a line [message #406847 is a reply to message #406816] Sat, 06 June 2009 09:08 Go to previous messageGo to next message
raghavakumarjoshi
Messages: 9
Registered: May 2007
Location: Bangalore
Junior Member
Thank you,

I got the solution.
Re: Problem while getting a line [message #406849 is a reply to message #406847] Sat, 06 June 2009 09:16 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which is?

Regards
Michel
Previous Topic: update with no wait
Next Topic: Closing Balance Problem
Goto Forum:
  


Current Time: Thu Dec 08 18:40:12 CST 2016

Total time taken to generate the page: 0.21087 seconds