Home » SQL & PL/SQL » SQL & PL/SQL » Passing CLOB to procedure in sqlplus
Passing CLOB to procedure in sqlplus [message #206248] Wed, 29 November 2006 05:39 Go to next message
Messages: 35
Registered: August 2005
We are trying to execute a PLSQL procedure that takes a clob as a parameter. The CLOB parameter that I am passing in is approx 7500 characters, however, I get this message from sqlplus SP2-0027: Input is too long (> 2499 characters) - line ignored

I tried to create a temp table to store this clob value, however, I get the same message when trying to insert the clob value into the temporary table. The code that I am using to insert the temp table is below

<service_request xmlns="http://www.luton.gov.uk"> <service_request xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" id="27002" name="Dog Fouling"> <pid>577B</pid> <state>New</state> <secondary_pid>C22</secondary_pid> '  || (chr(13)||chr(10)) || 
'<authentication_level>WMO</authentication_level> <lob>FLARE</lob> <keywords>dog mess, poo, shit</keywords> <page number="1"> <attributes> <attribute sequence="10" component_type="R"> <resource_text name="generic.resource_bundle_map_disclaimer_terms"/> <code>MAP_TNC</code> <meaning>MAP: agree map terms</meaning> <description>'  || (chr(13)||chr(10)) || 
'I have read and agree to be bound by the terms and conditions of Do it on-line</description> <value>Y</value> <required>Y</required> <displayed>Y'  || (chr(13)||chr(10)) || 
'</displayed> <branch_logic>Y:2,:6</branch_logic> <list name="XX_YES_OR_NO"> <value> <code>N</code> <meaning>No</meaning> <description>No</description> </value> <value> <code>Y</code> <meaning>Yes</meaning> <description>Yes</description> </value> </list> </attribute> </attributes> </page>'  || (chr(13)||chr(10)) || 
' <page number="2" type="MAP"> <attributes> <attribute sequence="10" component_type="B"> <resource_text/> <code>LOCATION</code> <meaning>Attribute for call to a Location Page</meaning> <description>Attribute for call'  || (chr(13)||chr(10)) || 
' to a location page</description> <required>Y</required> <displayed>Y</displayed> <branch_logic>:3</branch_logic> <spare3>MAP</spare3> </attribute> <attribute sequence="10" component_type="D"> <resource_text/> '  || (chr(13)||chr(10)) || 
'<code>SPATIAL_OWNERSHIP</code> <meaning>Spatial Land Ownership Analysis</meaning> <description>Please tell us more about where the incident actually occured</description> <value>Outside this address, on private ground</value> <required>N</required>'  || (chr(13)||chr(10)) || 
' <displayed>N</displayed> <spare3>MAP</spare3> </attribute> <attribute component_type="T"> <code>LocationId</code> <description>Location ID</description> <value>37050</value> <displayed>N</displayed> </attribute> <attribute component_type="T"> <code>SearchMethod</code> <description>Search Method</description> <value>'  || (chr(13)||chr(10)) || 
'Gazetteer</value> <displayed>N</displayed> </attribute> <attribute component_type="T"> <code>FeatureID</code> <description>Feature ID</description> <displayed>N</displayed> </attribute> <attribute component_type="T">'  || (chr(13)||chr(10)) || 
' <code>UPRN</code> <description>UPRN</description> <value>100080170327</value> <displayed>N</displayed> </attribute> <attribute component_type="T"> <code>Easting</code> <description>Easting</description> <value>512089</value> <displayed>N</displayed> </attribute> <attribute component_type="T"> <code>Northing</code> <description>Northing'  || (chr(13)||chr(10)) || 
'</description> <value>222861</value> <displayed>N</displayed> </attribute> <attribute component_type="T">'  || (chr(13)||chr(10)) || 
' <code>HouseName</code> <description>House Name</description> <displayed>Y</displayed> </attribute> <attribute component_type="T"> <code>Street</code> <description>Street</description> <value>18, somewhere road</value> <displayed>Y</displayed> </attribute> '  || (chr(13)||chr(10)) || 
'<attribute component_type="T"> <code>Locality</code> <description>District</description> <displayed>Y</displayed> </attribute> <attribute component_type="T"> <code>Town</code> <description>Town</description> <value>aaaaa</value> <displayed>Y</displayed>'  || (chr(13)||chr(10)) || 
' </attribute> <attribute component_type="T"> <code>PostCode</code> <description>Post Code</description> <value>LUX XXX</value> <displayed>Y</displayed> </attribute> <attribute component_type="A"> <code>LocationDescription</code> <description>Text description</description> '  || (chr(13)||chr(10)) || 
'<value>I have a square of grass to the side of my house which dog walkers are frequently letting their dogs foul on and not clearing up. The council will not let me put a '  || (chr(13)||chr(10)) || 
'fence around this grass so instead I'd like some advice on what I can do to combat this problem. It really is becoming nuisance. Would appreciate a reply, my e-mail is aaaaa.aaaaaa@aaaaaaaaaaaaaaaa.co.uk</value> <displayed>Y</displayed> </attribute> </attributes> </page> <page number="3"> <attributes> <attribute sequence="10" component_type="R"> '  || (chr(13)||chr(10)) || 
'<resource_text name="577.B.resource_bundle_label_3"/> <code>DOG_ID</code> <meaning>ID of dog or owner</meaning> <description>Do you know the identity of the dog or its owner?</description> <value>N</value> <required>N</required> <displayed>Y</displayed> <branch_logic>Y:4,:5</branch_logic> <list name="XX_YES_OR_NO_DOG"> <value> <code>N</code> <meaning>No</meaning> <description>No</description> </value> <value> <code>Y</code> <meaning>:NSE:emailatest@domain.co.uk:NSE: Yes</meaning> '  || (chr(13)||chr(10)) || 
'<description>Yes</description> </value> </list> </attribute> </attributes> </page> <page number="4"> <attributes> <attribute sequence="10" '  || (chr(13)||chr(10)) || 
'component_type="A"> <resource_text/> <code>DOGFOUL_DETAILS</code> <meaning>Details to ID dog owner</meaning> <description>Please provide any details that may help us to identify the dog owner</description> <required>N</required> <displayed>Y</displayed> <branch_logic>:5</branch_logic> </attribute> </attributes> </page> <page number="5"> <attributes> <attribute '  || (chr(13)||chr(10)) || 
'sequence="10" component_type="A"> <resource_text/> <code>DOGFOUL_GENINFO</code> <meaning>General info re dog fouling</meaning> <description>Please provide any further information'  || (chr(13)||chr(10)) || 
' that may be useful</description> <required>N</required> <displayed>Y</displayed> <branch_logic>:7</branch_logic> </attribute> </attributes> </page> <page number="6" type="EXIT"> <attributes> <attribute sequence="10" component_type="X"> <resource_text name="generic.resource_bundle_map_exit_message"/> <code>ABV-EXIT</code> <meaning>'  || (chr(13)||chr(10)) || 
'ABV:exitpage</meaning> <description>ABV exit page</description> <required>Y</required> '  || (chr(13)||chr(10)) || 
'<displayed>Y</displayed> <spare3>EXIT</spare3> </attribute> </attributes> </page> <page number="7"> <attributes> <attribute sequence="10" component_type="D"> <resource_text name="577.B.resource_bundle_label_1"/> <code>CONC1_TITLE</code> <meaning>conc1_ title</meaning> <description>Title</description> <value>MISS</value> <required>N</required> <displayed>Y</displayed> <list name="TITLE_GEN">'  || (chr(13)||chr(10)) || 
' <value> <code>DR</code> <meaning>Dr</meaning> <description>Dr</description> </value> <value> <code>MISS</code> <meaning>Miss</meaning> <description>Miss'  || (chr(13)||chr(10)) || 
'</description> </value> <value> <code>MR</code> <meaning>Mr</meaning> <description>Mr</description> </value> <value> <code>MRS</code> <meaning>Mrs</meaning> <description>Mrs</description> </value> <value> <code>MS</code> <meaning>Ms</meaning> <description>Ms</description> </value> <value> <code>PROF</code> <meaning>Prof</meaning> <description>Prof</description> </value> '  || (chr(13)||chr(10)) || 
'<value> <code>REV</code> <meaning>Rev'  || (chr(13)||chr(10)) || '</meaning> <description>Rev</description> </value> <value> <code>SIR</code> <meaning>Sir</meaning> <description>Sir</description> </value> </list> </attribute> <attribute sequence="20" component_type="T"> <resource_text/> <code>CONC1_FIRST</code> <meaning>conc1_ first</meaning> <description>First name</description> <value>aaaaa</value> <required>N</required> <displayed>Y</displayed> </attribute> <attribute sequence="30" '  || (chr(13)||chr(10)) || 
'component_type="T"> <resource_text/> <code>CONC1_LAST</code> <meaning>conc1_ last</meaning> <description>Last name</description> <value>aaaaaa'  || (chr(13)||chr(10)) || '</value> <required>N</required> <displayed>Y</displayed> </attribute> <attribute sequence="40" component_type="T"> <resource_text/> <code>CONC2_HPHONE</code> <meaning>CONC2_Daytime Phone</meaning> <description>Daytime phone</description> <value>00000000000</value> <required>N</required> <displayed>Y</displayed> </attribute> </attributes> </page> </service_request> </service_request>');

The || (chr(13)||chr(10)) || are being used to ensure that each line does not contain more than 2499 characters. Is this the right way of inserting a clob value into a table, if not please help.


[Edited to make the width slightly smaller than the distance from here to Aldebaran]

[Updated on: Wed, 29 November 2006 06:05]

Report message to a moderator

Re: Passing CLOB to procedure in sqlplus [message #206256 is a reply to message #206248] Wed, 29 November 2006 05:52 Go to previous messageGo to next message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Just a word of advice - it looks like you're working for Local government in the UK.
I wouldn't want to be in your shoes if your Data Protection people find out that you're posting live data on a public message forum.

If that is test data, then fine, but that's not what it looks like.
Re: Passing CLOB to procedure in sqlplus [message #206267 is a reply to message #206256] Wed, 29 November 2006 06:19 Go to previous messageGo to next message
Messages: 7
Registered: November 2006
Location: Lausanne, Switzerland
Junior Member

don't worry the address is "somewhere road 18" and the email aaa@aaaaa.aa Wink

found in file: <keywords>dog mess, poo, shit</keywords>

Damn! it REALLY is about UK gov! Very Happy

Re: Passing CLOB to procedure in sqlplus [message #206271 is a reply to message #206267] Wed, 29 November 2006 06:55 Go to previous message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Too true.....

The SP2-0027 error occurs when you have a single line of text more than 7500 chrs long, so you need to break the text up with actual hard returns, ie
rather than

Once you've done that, you'll hit a problem with the 4000 chr limit on Varchar2s in SQL.

You get round this by wrapping the whole insert statement in an anonymous pl/sql block, like this:
  v_clob   clob;
  v_clob := <Xml here>;
  insert into table <table_name> values v_clob;

Really, Sql*Plus isn't a good way to do this sort of thing.
Previous Topic: Solve this classic query
Next Topic: Sort for Text
Goto Forum:

Current Time: Mon Oct 24 02:49:37 CDT 2016

Total time taken to generate the page: 0.09411 seconds