Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: September 29, 2008 - 1:55 pm UTC

Last updated: June 18, 2021 - 9:18 am UTC

Version: 10.2.0.3

Viewed 50K+ times! This question is

You Asked

Hi Tom,

When i try to insert huge data into a CLOB colum, i get "PLS-00172: string literal too long". Tried searching in web/metalink, but of no use. Please let me know if a workaround is available for this insert.

drop table t9;

create table t9(template clob);

create or replace procedure ins_template as
v_template clob;
begin
v_template:='<string containing more than 32767 characters but less than 500,000 characters>';
insert into t9 values(v_template);
end;
/

regards,
Vijaya Chander

and we said...

http://asktom.oracle.com/Misc/LargeStrings.html

you have to bind, piece wise modification of the clob - you would NEVER CONSIDER hard coding 1/2mb string literal into code in real life??


Rating

  (13 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, September 29, 2008 - 2:18 pm UTC

Tom,

Thanks for your reply. Our requirement is to insert binary code of an image into a CLOB column and retrieve when required. So the binary code can be of size 500K or above. Can you give an example of inserting string greater than 32767 characters using PL/SQL code.

regards,
Vijaya Chander
Tom Kyte
September 29, 2008 - 2:56 pm UTC

change your requirement.

Do you see what you just said:

insert BINARY CODE (raw bits and bytes) into a CLOB (a character string, something subject to character set conversions)


No one can give you an example of a string greater than 32k in plsql - such a thing does not exist (as stated on the linked to page - character string literals have limits)

Now, that said you will

a) use a blob, only a blob
b) do not even consider using a clob - it won't work for image data, it will CORRUPT IT
c) use dbms_lob.loadfromfile to load the image file into the database - search for that on this site for many exmaples OR check out the dbms_lob documentation on otn.oracle.com

Thanks Tom. It worked.

A reader, September 29, 2008 - 3:54 pm UTC


"PLS-00172: string literal too long", version 11.2

Swami, December 20, 2011 - 10:15 am UTC

Hi Tom,

I am getting a similar error ""PLS-00172: string literal too long" when I try to insert huge xml into a CLOB input column in stored procedure. Please let me know if there is a way to read large values without getting this error. I tried to go the link that you attached to the earlier question, but it does not work anymore. Please help.

Regards,
Swami
Tom Kyte
December 20, 2011 - 10:53 am UTC

sure there is, it is called bind variables.


the longest a varchar2 can be in plsql is 32k.

the longest a clob can be is many many many gigabytes.

I can successfully create and insert a "huge" xml document in plsql easily - you have to use clobs.


ops$tkyte%ORA11GR2> variable x clob
ops$tkyte%ORA11GR2> create table t ( x clob );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> begin
  2          :x := '<tag>hello world</tag>';
  3          loop
  4                  exit when dbms_lob.getlength(:x) > 1000000;
  5                  :x := :x || :x;
  6          end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select dbms_lob.getlength(:x) from dual;

DBMS_LOB.GETLENGTH(:X)
----------------------
               1441792

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> begin
  2          insert into t (x) values (:x);
  3  end;
  4  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select dbms_lob.getlength(x) from t;

DBMS_LOB.GETLENGTH(X)
---------------------
              1441792




feel free to share with us A TEENY TINY = as in small, as in short, as in terse, as in concise - example of what you are doing. remove everything from your sample that is not relevant to demonstrating the issue.


then we can tell you what you are doing wrong.

we have to be able to run this example ourselves
it has to be small - it shouldn't be more than a dozen or so lines of code
it must reproduce the issue
it must be representative of what you are actually doing.

"PLS-00172: string literal too long", version 11.2

Swami, December 20, 2011 - 12:30 pm UTC

Hi Tom,

See below for the sample code. Even if we add a dbms_output, it is not getting displayed. We only get the error mentioned above.

Regards,
Swami

CREATE OR REPLACE PROCEDURE CLOB_TEST( PIN_XML_DATA IN CLOB,po_cResponse OUT CLOB)
IS
vResultInfo CLOB:=EMPTY_CLOB();
BEGIN
DBMS_LOB.CREATETEMPORARY(vResultInfo,TRUE);
DBMS_LOB.CREATETEMPORARY(po_cResponse,TRUE);

DBMS_LOB.OPEN(vResultInfo,dbms_lob.lob_readwrite);
DBMS_LOB.OPEN(po_cResponse,dbms_lob.lob_readwrite);
BEGIN
dbms_lob.append(po_cResponse,'<TXLife>');
dbms_lob.append(po_cResponse,'<TXLifeResponse>');
BEGIN
xData := XMLTYPE.createxml(PIN_XML_DATA);
END;
BEGIN
SELECT extractvalue(value(t),'/TXLifeRequest/TransRefGUID')
INTO vTransrefguid
FROM TABLE (XMLSEQUENCE (EXTRACT (xData,'/TXLife/TXLifeRequest'))) t
WHERE existsnode(value(t),'/TXLifeRequest/TransRefGUID') =1
AND extractvalue(value(t),'/TXLifeRequest/TransRefGUID') IS NOT NULL;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ncode :=5;
nErrcode :=200;
vStatus :='FAILURE';
vStatusinfo:='General Data Error';
Raise_application_error(-20201,'Transrefguid does not exist');
END;
dbms_lob.close(vResultInfo);
dbms_lob.freetemporary(vResultInfo);
dbms_lob.append(po_cResponse,'</TransResult>');
dbms_lob.append(po_cResponse,'</TXLifeResponse>');
dbms_lob.append(po_cResponse,'</TXLife>');
END CLOB_TEST;

Tom Kyte
December 20, 2011 - 2:09 pm UTC

ops$tkyte%ORA11GR2> CREATE OR REPLACE PROCEDURE CLOB_TEST( PIN_XML_DATA IN CLOB,po_cResponse OUT CLOB)
  2  IS
  3      vResultInfo     CLOB:=EMPTY_CLOB();
  4  BEGIN
  5    DBMS_LOB.CREATETEMPORARY(vResultInfo,TRUE);
  6    DBMS_LOB.CREATETEMPORARY(po_cResponse,TRUE);
  7  
  8    DBMS_LOB.OPEN(vResultInfo,dbms_lob.lob_readwrite);
  9    DBMS_LOB.OPEN(po_cResponse,dbms_lob.lob_readwrite);
 10    BEGIN
 11      dbms_lob.append(po_cResponse,'<TXLife>');
 12       dbms_lob.append(po_cResponse,'<TXLifeResponse>');
 13        BEGIN
 14          xData := XMLTYPE.createxml(PIN_XML_DATA);
 15        END;
 16        BEGIN
 17          SELECT extractvalue(value(t),'/TXLifeRequest/TransRefGUID')
 18          INTO vTransrefguid
 19          FROM TABLE (XMLSEQUENCE (EXTRACT (xData,'/TXLife/TXLifeRequest'))) t
 20          WHERE existsnode(value(t),'/TXLifeRequest/TransRefGUID')  =1
 21          AND extractvalue(value(t),'/TXLifeRequest/TransRefGUID') IS NOT NULL;
 22        EXCEPTION
 23        WHEN NO_DATA_FOUND THEN
 24          ncode      :=5;
 25          nErrcode   :=200;
 26          vStatus    :='FAILURE';
 27          vStatusinfo:='General Data Error';
 28          Raise_application_error(-20201,'Transrefguid does not exist');
 29        END;
 30    dbms_lob.close(vResultInfo);
 31    dbms_lob.freetemporary(vResultInfo);
 32    dbms_lob.append(po_cResponse,'</TransResult>');
 33    dbms_lob.append(po_cResponse,'</TXLifeResponse>');
 34    dbms_lob.append(po_cResponse,'</TXLife>');
 35    end;  /* I added this <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<============== */
 36  END CLOB_TEST;
 37  /

Warning: Procedure created with compilation errors.

ops$tkyte%ORA11GR2> show err
Errors for PROCEDURE CLOB_TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
14/9     PL/SQL: Statement ignored
14/9     PLS-00201: identifier 'XDATA' must be declared
17/9     PL/SQL: SQL Statement ignored
19/43    PL/SQL: ORA-00904: "XDATA": invalid identifier
24/9     PL/SQL: Statement ignored
24/9     PLS-00201: identifier 'NCODE' must be declared
25/9     PL/SQL: Statement ignored
25/9     PLS-00201: identifier 'NERRCODE' must be declared
26/9     PL/SQL: Statement ignored
26/9     PLS-00201: identifier 'VSTATUS' must be declared
27/9     PL/SQL: Statement ignored
27/9     PLS-00201: identifier 'VSTATUSINFO' must be declared
ops$tkyte%ORA11GR2> 



strange, it seems to no compile for me.


I'll state it again:

we have to be able to run this example ourselves
it has to be small - it shouldn't be more than a dozen or so lines of code
it must reproduce the issue
it must be representative of what you are actually doing.

"PLS-00172: string literal too long", version 11.2

Swami, December 20, 2011 - 2:28 pm UTC


Sorry about that Tom. I have attached the right code with some more modification. This one works fine with a smaller input xml. But fails with an error when the input is a larger xml.

Thanks,
Swami

CREATE OR REPLACE PROCEDURE CLOB_TEST( PIN_XML_DATA IN CLOB,po_cResponse OUT CLOB)
IS
xData XMLTYPE:=NULL;
vResultInfo CLOB:=EMPTY_CLOB();
BEGIN
DBMS_LOB.CREATETEMPORARY(vResultInfo,TRUE);
DBMS_LOB.CREATETEMPORARY(po_cResponse,TRUE);

DBMS_LOB.OPEN(vResultInfo,dbms_lob.lob_readwrite);
DBMS_LOB.OPEN(po_cResponse,dbms_lob.lob_readwrite);
BEGIN
dbms_lob.append(po_cResponse,'<TXLife>');
dbms_lob.append(po_cResponse,'<TXLifeResponse>');
BEGIN
xData := XMLTYPE.createxml(PIN_XML_DATA);
END;
END;
dbms_lob.close(vResultInfo);
dbms_lob.freetemporary(vResultInfo);
dbms_lob.append(po_cResponse,'</TransResult>');
dbms_lob.append(po_cResponse,'</TXLifeResponse>');
dbms_lob.append(po_cResponse,'</TXLife>');
END CLOB_TEST;
/
Tom Kyte
December 20, 2011 - 2:56 pm UTC

ops$tkyte%ORA11GR2> CREATE OR REPLACE PROCEDURE CLOB_TEST( PIN_XML_DATA IN CLOB,po_cResponse OUT CLOB)
  2  IS
  3      xData         XMLTYPE:=NULL;
  4      vResultInfo     CLOB:=EMPTY_CLOB();
  5  BEGIN
  6    DBMS_LOB.CREATETEMPORARY(vResultInfo,TRUE);
  7    DBMS_LOB.CREATETEMPORARY(po_cResponse,TRUE);
  8  
  9    DBMS_LOB.OPEN(vResultInfo,dbms_lob.lob_readwrite);
 10    DBMS_LOB.OPEN(po_cResponse,dbms_lob.lob_readwrite);
 11    BEGIN
 12      dbms_lob.append(po_cResponse,'<TXLife>');
 13       dbms_lob.append(po_cResponse,'<TXLifeResponse>');
 14        BEGIN
 15          xData := XMLTYPE.createxml(PIN_XML_DATA);
 16        END;
 17    END;
 18    dbms_lob.close(vResultInfo);
 19    dbms_lob.freetemporary(vResultInfo);
 20    dbms_lob.append(po_cResponse,'</TransResult>');
 21    dbms_lob.append(po_cResponse,'</TXLifeResponse>');
 22    dbms_lob.append(po_cResponse,'</TXLife>');
 23  END CLOB_TEST;
 24  /

Procedure created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> variable x clob
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2          x clob;
  3          y clob;
  4  begin
  5          x := '<my_doc>';
  6          for i in 1 .. 10
  7          loop
  8                  x := x || '<tag>hello world</tag>';
  9          end loop;
 10          x := x || '</my_doc>';
 11          clob_test( x, y );
 12  end;
 13  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> declare
  2          x clob;
  3          y clob;
  4  begin
  5          x := '<my_doc>';
  6          for i in 1 .. 100000
  7          loop
  8                  x := x || '<tag>hello world</tag>';
  9          end loop;
 10          x := x || '</my_doc>';
 11          dbms_output.put_line( 'xml is ' || dbms_lob.getlength(x) || ' bytes' );
 12          clob_test( x, y );
 13  end;
 14  /
xml is 2200017 bytes

PL/SQL procedure successfully completed.



I'll say it one more time, fingers crossed this time:

we have to be able to run this example ourselves
it has to be small - it shouldn't be more than a dozen or so lines of code
it must reproduce the issue <<<<<<<<<=================
it must be representative of what you are actually doing.


what do I need to do to see your issue. I went all of the way up to 22mb and no problem.

PLS-00172: string literal too long

Swami, December 21, 2011 - 2:46 pm UTC

Hi Tom,

I did some more tests based on your input on the sample that I gave you. I am getting the error when I pass the entire string to the stored procedure as a clob rather than appending a smaller string within a loop. Is there a way to convert a huge XML string into a clob without breaking them into smaller strings ?

Regards,
Swami
Tom Kyte
December 21, 2011 - 3:10 pm UTC

you cannot have a string literal over

a) 4000 bytes in SQL
b) 32k in PLSQL


period. that is why I keep saying "use binds", if you *bind* this value in in your application - using a character stream or whatever in java - you'll be ok.

You *cannot* have a literal that large - period.

PLS-00172: string literal too long

Swami, December 21, 2011 - 3:26 pm UTC

Tom,

Thank you so much for your input. This was really helpful. Will try to test this from Java through bind variables.

Regards,
Swami


Answer to use "bind variables" is simplistic

Tom Jackson, December 10, 2016 - 9:06 pm UTC

As per my title, the answer to use "bind variables" is extremely overly-simplistic. It gives no code on how to do this, and leaves the developer out to dry. While I know you guys can't support every programming language, even one example of the Oracle SQL that would result in updating a BLOB would have been very beneficial.

But one has to ask, why would you impose such arbitrary limits as 4000 and 32767 on data that will go in a CLOB or BLOB field that can store 4 GB in the first place, anyway, without supplying examples of how this might even be done?!!?? Did anyone at Oracle really think this through? The HEXTORAW command cannot even take a hex string greater than 4000 directly, but I later found you can use variables to store and append chunks of 32767 to a CLOB, then do HEXTORAW on a CLOB created using dbms_lob.createtemporary() and append that to a BLOB. So why can't you do this with a BLOB with a direct update to it, using the original hex string? And you guys expect us to break up a file before we can upload it? Say its only a 39K Word document file - did you know this would breach the 32767 limit and require the hex to be broken into chunks before it can be uploaded? I figured out you could break it up with .Subtring() in C#, add each hex string to a temporary CLOB, and then do HEXTORAW on the CLOB variable and append that to a temporary BLOB one, and set the database BLOB field equal to that. But why was that not stated in the answer to this question, and why does Oracle even require it?

And did you guys not even realize that if you do:

dbms_lob.append(buf, HEXTORAW('...hex bytes...'));

, where buf is a BLOB variable, and if you then do that line again, with the next chunk of hex bytes, that it appends a 0 hex byte at the beginning of that new chunk and corrupts the file, if you then update your DB field with buf?

The only way I found to deal with this was then to create a CLOB variable and append chunks of hex string to that, then do:

dbms_lob.append(buf, HEXTORAW(cBuf));

where cBuf is a CLOB variable. But none of that was even talked about! And why should we even have to do this for such small files as something like 39K is, when compared to PowerPoint files (usually in the MB). This makes storing files in Oracle very tedious and only invites more of these such questions by developers. To avoid a straight resolution in the answer is very irresponsible, and to support Oracle's limitations, than to try to work on ways to modernize them to support today's file sizes, of which most fully exceed the 32767 limit, is even more so.

See http://stackoverflow.com/questions/18116634/oracle-10-using-hextoraw-to-fill-in-blob-data/41079642#41079642 for my full example and resolution, and merely Google "string literal too long oracle" to see over 120,000 results of issues these arbitrary limits have imposed with no way around them in Oracle's documentation.
Connor McDonald
December 12, 2016 - 1:39 am UTC

You've lost me...

If I have a file that is accessible to the database server, then dbms_lob has loadfromfile/loadblobfromfile/loadclodfromfile and I'm done.

If the file is not accessible to the database server, then by definition, I'll be using some sort of client software to access it. And most of them have a file (aka binary object) level of support.

For exmaple, in ODP.NET, plenty of examples trivially found on the 'net.

http://aspalliance.com/570_Read_and_Write_BLOB_Data_to_a_Database_Table_with_ODPNET.all

http://www.akadia.com/services/dotnet_orablobs.html

http://www.oracle.com/technetwork/testcontent/o65odpnet-085139.html

going back over 10 years.

I'm not sure where the use case of:

"I've got some binary data from an unknown source that I only have the literal hex codes for, that I need to store"

could come from.

Yes, we need to be able to update BLOBs from bytes

Tom Jackson, December 13, 2016 - 9:51 pm UTC

I looked over the examples on that follow-up. They don't address the use-case, because yes, I DID have the need to update a BLOB only from the bytes/hex of a file within an application, under different circumstances than when a file would normally be expected to be loaded.

And apparently "dbms_lob.loadfromfile" has its own share of problems: http://www.oracledba.co.uk/tips/load_lob.htm
They used append statements, instead, which was where I ended up. But these have their limits (32767).

1. & 2. Each of these sites shows the update of a BLOB using an OracleParameter - which I can and do use in my application when I am connected to Oracle and someone uploads a file. The problem is if I am disconnected from Oracle, when I swap to a SQLite instance, but still need to automatically update Oracle later with that file. So meanwhile, the bytes from the file, along with the full update query information, still need to be stored for that purpose, somewhere, along with all the other Oracle update queries that didn't get made, and then be re-read-in in a way that does not require a special circumstance from one to the other when re-processing them. Because how can you even denote a file upload query, if stored in a file to be re-ran later with the other non-file-upload queries, to indicate that it requires the extra attention of grabbing the bytes out and creating an OracleParameter out of it? Parse it for "hextobytes", since long hex strings can't be ran directly like this, anyway, and grab the hex, convert it back to bytes, assign it to an OracleParameter and re-insert after grabbing the rest of the query string parts? Sure, I GUESS....!!! But I'd rather build SQL that I don't have to parse and run any special way from any other query to require this much extra work and testing. So there is very little documentation out there on how to do this while avoiding all of Oracle's limits to strings and variable usage and somehow getting that user to re-upload that file to bytes, again.

3. This showed how to do a read operation, using GetOracleBlob(), and so not applicable to the use-case. (I actually did use this same documentation when adding in my code to read BLOB bytes back out of the database.)

Regarding my earlier review, though, I actually did later find that if you use an odd chunk size, that is when the extra "0" hex byte gets appended in hextoraw. If you split the hex into even chunks less than 32767, you can build a BLOB through dbms_createtemporary() and dbms_lob.append() and then convert that variable's hex to RAW (assigning the variable's content back to itself as RAW), set your database field equal to that variable, and it works fine.

The problem with all this was learning:
1) that there is a 4000 character limit when setting field values, even if they are BLOB/CLOB fields that can store 4 GBs of data! (very odd, to me),
2) the chunk size limit (32767), (very small in comparison to today's file sizes, if you ask me)
3) the bug with using odd-numbered chunk sizes, (why would "hextoraw" try to compensate like this and corrupt the file in the process, instead of just giving an error that there aren't enough bytes to convert it to hex?) and
4) how you have the limit on putting the hex directly into an UPDATE/INSERT query to update/insert to a field, but can use a BLOB variable, append to it in chunks, then set the field equal to that variable without issue -- so why can't you just have the query itself allow the field to be set directly? (makes little sense, to me, how Oracle can't be made to self-divide these chunks - it has to be told to do so with individualized instructions...)

Requiring someone to have to build a way to programmatically break apart the hex and append it like this I thought was a little much - like no one at Oracle dreamed there would ever be a use-case for allowing a query to set a field equal to a long set of hex in one swoop - longer than 4000 chars, I mean. And I think the response I got - to use loadfromfile, which doesn't always work, and OracleParameter, for which I'd have to do even more (parsing out the hex from the queries that SHOULD actually work, but can't, due to extremely small size limits, when compared to the 4 GB BLOB limit, or relative size of average files, today), then re-convert the hex to bytes and assign to them, shows that this is the case.

more info

Martin Lourduswamy, August 06, 2018 - 6:02 pm UTC

I have sql file that has many statements like creating a package or inserting or deleting. Do I have to run each sql statement separately for this parse to work,
Thanks
Chris Saxon
August 07, 2018 - 3:32 pm UTC

You what now? What exactly are you doing? And how does this relate to the original question?

Link dead

Happy, September 06, 2018 - 4:29 am UTC

Connor McDonald
September 10, 2018 - 4:58 am UTC

That's a typo - it should be:

https://asktom.oracle.com/Misc/LargeStrings.html

I've corrected it in the question as well

Remove the limit

Someone, June 16, 2021 - 5:37 am UTC

Hey Oracle,

just remove that limit. It‘s the year 2021, we don‘t want to use your Software like it‘s 1980. And fire those passive agressive people that claim eveyone is doing it wrong.

Regards
Connor McDonald
June 16, 2021 - 6:43 am UTC

I'll repeat the common sense I've already posted....

You've lost me...

If I have a file that is accessible to the database server, then dbms_lob has loadfromfile/loadblobfromfile/loadclodfromfile and I'm done.

If the file is not accessible to the database server, then by definition, I'll be using some sort of client software to access it. And most of them have a file (aka binary object) level of support.

For exmaple, in ODP.NET, plenty of examples trivially found on the 'net.

http://aspalliance.com/570_Read_and_Write_BLOB_Data_to_a_Database_Table_with_ODPNET.all

http://www.akadia.com/services/dotnet_orablobs.html

http://www.oracle.com/technetwork/testcontent/o65odpnet-085139.html

going back over 10 years.

I'm not sure where the use case of:

"I've got some binary data from an unknown source that I only have the literal hex codes for, that I need to store"

could come from.

Just raise it

Someone, June 16, 2021 - 6:47 pm UTC

It‘s not the responsibility of the database to make any assumptions about use cases. Just seeing an earlier reply „then change your requirements“ makes me angry.

Raise the damn limit of 4k characters and everyone will be happy. Because that‘s something that happens in the wild, if you can imagine use cases or not.
Chris Saxon
June 18, 2021 - 9:18 am UTC

The limit is now 32k in SQL and has been since 12.1 - though you do need to enable extended data types to use this

https://oracle-base.com/articles/12c/extended-data-types-12cR1

And it's worth putting the "change your requirement" quote in context - this was in reference to storing binary data in a clob, which is... not a good idea.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library