Home » SQL & PL/SQL » SQL & PL/SQL » Inserting into clob, data more than 4000 chars via pl/sql
Inserting into clob, data more than 4000 chars via pl/sql [message #280497] Tue, 13 November 2007 21:39 Go to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Hi,

I have a requirement to insert an xml file into a clob and while the attached procedure does insert the file data, but it truncates the data to 4000 characters, so the entire xml file is not inserted...can you please help correct the procedure..

Regarding the table structure -the table adp_appdefinition has a single column i.e. xmldef which is a clob.

With regards,
Nirav
Re: Inserting into clob, data more than 4000 chars via pl/sql [message #280536 is a reply to message #280497] Wed, 14 November 2007 00:56 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Show us your evidence that the table column is a CLOB:
DESC ADP_APPDEFINITION

Also show us evidence that the database column is truncated:
SELECT LENGTH(xmldef) FROM ADP_APPDEFINITION



Here's an example to show that it is possible:
SQL> create table x (a clob);

Table created.

SQL>
SQL> declare
  2          c clob;
  3  begin
  4          c := lpad('X', 10000, 'X');
  5          insert into x values(c);
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> select length(a) from x;

 LENGTH(A)
----------
     10000


Ross Leishman

Re: Inserting into clob, data more than 4000 chars via pl/sql [message #280582 is a reply to message #280497] Wed, 14 November 2007 02:38 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
If I try to execute the attached script (via sql*plus and/or TOAD) I get a "string literal too long" error.

Database version 10.1.0.2.0
Re: Inserting into clob, data more than 4000 chars via pl/sql [message #280586 is a reply to message #280582] Wed, 14 November 2007 02:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Script:
create table x (a clob);

declare
  c clob;
begin
  c := lpad('X', 10000, 'X');
  insert into x values(c);
end;
/

select length(a) from x;

drop table x;


I've just run it on SQL*Developer, TOAD and SQL*Plus against a 10.2.0.1.0 database and it works perfectly.

Can you show us a cut and paste of the script being run and the error (from SQL*Plus ideally)
Re: Inserting into clob, data more than 4000 chars via pl/sql [message #280588 is a reply to message #280497] Wed, 14 November 2007 02:53 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
I was referring to the OP's script, not yours.

If I run your sample, it works fine, as long as I'm not exagerating on the number of chars to pad Smile
Re: Inserting into clob, data more than 4000 chars via pl/sql [message #280607 is a reply to message #280588] Wed, 14 November 2007 04:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Ahhh....

I think the problem is that in the OPs script he is trying to assign a string value to XMLDEF, and the length of the string is about 79,000 chrs, which is too long for a string in pl/sql.
I supect that if you break that one long string into 3 smaller ones and concatenate them into XMLDEF then it should work.
Re: Inserting into clob, data more than 4000 chars via pl/sql [message #280634 is a reply to message #280497] Wed, 14 November 2007 05:05 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Quote:


I think the problem is that in the OPs script he is trying to assign a string value to XMLDEF, and the length of the string is about 79,000 chrs, which is too long for a string in pl/sql.




Yes, that is exactly the requirement...is there any solution other than breaking up the string?...Thank you very much for all the suggestions and help.

With thanks,
Nirav

[Updated on: Wed, 14 November 2007 05:06]

Report message to a moderator

Re: Inserting into clob, data more than 4000 chars via pl/sql [message #280635 is a reply to message #280634] Wed, 14 November 2007 05:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Where do you get this string from?

If it is coming from some front end application, then you should be able to pass it in to the database as a CLOB from the front end.

If it exists in a file, have a look at DBMS_LOB,partucularly the LOADCLOBFROMFILE procedure.
Re: Inserting into clob, data more than 4000 chars via pl/sql [message #280638 is a reply to message #280635] Wed, 14 November 2007 05:13 Go to previous message
orausern
Messages: 826
Registered: December 2005
Senior Member
Quote:


If it exists in a file, have a look at DBMS_LOB,partucularly the LOADCLOBFROMFILE procedure



I think you pointed to something I missed all the while earlier! I will work on it by tommorrow (due to several teething issues , I am kept running on urgent tasks today, but once I can breathe a little, I will check and this is MOST USEFUL!!

Nirav

[Updated on: Wed, 14 November 2007 05:14]

Report message to a moderator

Previous Topic: function restriction
Next Topic: How to get a current + last year data in a single record?
Goto Forum:
  


Current Time: Sat Dec 14 15:49:45 CST 2024