Home » SQL & PL/SQL » SQL & PL/SQL » substr of clob (oracle database 11g)
substr of clob [message #584422] Tue, 14 May 2013 05:04 Go to next message
mahnazzz
Messages: 96
Registered: July 2011
Location: Iran
Member
Dear members.
I have a table with a column data type CLOB.
I want some part of this column.I used DBMS_LOB.SUBSTR(ClobColumn,amount,offset)
but it have restriction in amount argument(4000).the length of column is more than 32767 in some rows.Is there any solution for that?
thanks

[Updated on: Tue, 14 May 2013 05:24]

Report message to a moderator

Re: substr of clob [message #584427 is a reply to message #584422] Tue, 14 May 2013 05:33 Go to previous messageGo to next message
Michel Cadot
Messages: 58902
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SUBSTR.

Regards
Michel
Re: substr of clob [message #584428 is a reply to message #584422] Tue, 14 May 2013 05:36 Go to previous messageGo to next message
rajiv.v
Messages: 16
Registered: February 2013
Location: Bangalore
Junior Member
Use substring function substring(1234567,4) gives the output 4567 substring(1234567,4,1) gives only 4 and so on and so forth.
Re: substr of clob [message #584431 is a reply to message #584428] Tue, 14 May 2013 05:45 Go to previous messageGo to next message
Michel Cadot
Messages: 58902
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
substring does not exist.
Please test your "solution" before posting it.

Regards
Michel
Re: substr of clob [message #584434 is a reply to message #584431] Tue, 14 May 2013 05:58 Go to previous messageGo to next message
rajiv.v
Messages: 16
Registered: February 2013
Location: Bangalore
Junior Member
substr to be precise.Its spelled as substring,but used as substr.Its just substr..Am sorry michel
Re: substr of clob [message #584435 is a reply to message #584434] Tue, 14 May 2013 06:00 Go to previous messageGo to next message
sss111ind
Messages: 473
Registered: April 2012
Location: India
Senior Member

you have to do like this
DECLARE
  v_clob CLOB   :='but it have restriction in amount argument(4000).the 
length of column is more than 32767 in some rows.Is there any solution for that';
  amount INTEGER:=20;
  offset INTEGER:=1;
  LEN    INTEGER;
BEGIN
  LEN :=dbms_lob.getlength(v_clob);
  WHILE offset<LEN
  LOOP
    dbms_output.put_line(dbms_lob.substr(v_clob,amount,offset));
    offset:=offset+amount;
  END LOOP;
END;
Re: substr of clob [message #584438 is a reply to message #584428] Tue, 14 May 2013 06:25 Go to previous messageGo to next message
mmi78
Messages: 22
Registered: April 2013
Location: dhaka
Junior Member
rajiv.v wrote on Tue, 14 May 2013 05:36
Use substring function substring(1234567,4) gives the output 4567 substring(1234567,4,1) gives only 4 and so on and so forth.


substr
Re: substr of clob [message #584495 is a reply to message #584438] Tue, 14 May 2013 21:36 Go to previous messageGo to next message
mahnazzz
Messages: 96
Registered: July 2011
Location: Iran
Member
Quote:

Michel Cadot
Use SUBSTR.

I used it in pl/sql but I got error Ora-32767
Re: substr of clob [message #584496 is a reply to message #584495] Tue, 14 May 2013 21:56 Go to previous messageGo to next message
BlackSwan
Messages: 22703
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

use COPY & PASTE so we see what you do & how Oracle responds
Re: substr of clob [message #584507 is a reply to message #584495] Tue, 14 May 2013 23:15 Go to previous messageGo to next message
mmi78
Messages: 22
Registered: April 2013
Location: dhaka
Junior Member
mahnazzz wrote on Tue, 14 May 2013 21:36
Quote:

Michel Cadot
Use SUBSTR.

I used it in pl/sql but I got error Ora-32767


ORA-32767 No server connection for this operation
Cause: Establish a client/server connection.
Action: The client side sql or plsql function operation requires a connection to the server, but currently no client/server connection existed.
Re: substr of clob [message #584516 is a reply to message #584495] Wed, 15 May 2013 00:15 Go to previous messageGo to next message
Michel Cadot
Messages: 58902
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
mahnazzz wrote on Wed, 15 May 2013 04:36
Quote:

Michel Cadot
Use SUBSTR.

I used it in pl/sql but I got error Ora-32767


Use SQL*Plus and copy and paste your session, the WHOLE session including the code and error.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel

icon14.gif  Re: substr of clob [message #584594 is a reply to message #584516] Wed, 15 May 2013 21:52 Go to previous message
mahnazzz
Messages: 96
Registered: July 2011
Location: Iran
Member
Michel Cadot you are right as always .than you so much.
Previous Topic: Help in SQL
Next Topic: Want to display the data from two tables in SQL
Goto Forum:
  


Current Time: Wed Aug 27 06:20:18 CDT 2014

Total time taken to generate the page: 0.11216 seconds