Home » SQL & PL/SQL » SQL & PL/SQL » Data value size exceeding 4000 characters (Oracle 11g R2)
Data value size exceeding 4000 characters [message #568425] Thu, 11 October 2012 08:26 Go to next message
srinivas.k2005
Messages: 289
Registered: August 2006
Senior Member
Hi,

I have a insert statement where i need to insert data to a column where i need to insert value more than 4000 char into column.

Different approaches and condition:
1. CLOB should not be used
2. Need full value to be stored

Approach:
1.I created few more dummy columns to insert the data , was inserting 4000 char and if exceeded i was inserting to next column, but this will be tedious if we have 35000 char
2.Insert in the same column as different rows

Let me know best approach for the same and solution


[EDITED by LF: fixed topic title typo (at least, I think so); was "date" (date size > 4000 characters?)]

[Updated on: Fri, 12 October 2012 01:18] by Moderator

Report message to a moderator

Re: Date value size exceeding 4000 characters [message #568428 is a reply to message #568425] Thu, 11 October 2012 08:30 Go to previous messageGo to next message
Michel Cadot
Messages: 58523
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Master/detail tables architecture is your best choice.

Regards
Michel
Re: Date value size exceeding 4000 characters [message #568429 is a reply to message #568425] Thu, 11 October 2012 08:32 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1950
Registered: January 2010
Senior Member
srinivas.k2005 wrote on Thu, 11 October 2012 09:26
CLOB should not be used


Why? Seems like an artificail restriction to me.

SY.
Re: Date value size exceeding 4000 characters [message #568433 is a reply to message #568429] Thu, 11 October 2012 09:28 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
Dear Srinivas,

I also faced this issue previously when loading .xml into table column and that time i used CLOB only.
I hope CLOB will resolve your problem.Otherwise you have to create particular function for storing data
into input/output stream and put that data into oracle table rows and maintain sequence number for
tracking complete query.
Long back,I have created Input/Ouput stream and populated data into oracle table using user defined function.

[Updated on: Thu, 11 October 2012 09:29]

Report message to a moderator

Re: Date value size exceeding 4000 characters [message #568461 is a reply to message #568425] Fri, 12 October 2012 01:01 Go to previous messageGo to next message
srinivas.k2005
Messages: 289
Registered: August 2006
Senior Member
Any solution to :

Insert in the same column as different rows



Regards,
SRK
Re: Date value size exceeding 4000 characters [message #568462 is a reply to message #568461] Fri, 12 October 2012 01:06 Go to previous message
Michel Cadot
Messages: 58523
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 11 October 2012 15:30
Master/detail tables architecture is your best choice.

Regards
Michel

Previous Topic: Spooling every 25 rows in excel
Next Topic: How to get the time from datetime?
Goto Forum:
  


Current Time: Thu Jul 24 04:25:12 CDT 2014

Total time taken to generate the page: 0.06008 seconds