2

I have a stored procedure which has a CLOB field. When i invoke the stored procedure with a large value (over 33K characters) I get the error "PLS-00172: string literal too long"

My stored proc

create or replace procedure P(c clob) is
 begin
   dbms_output.put_line('dbms_lob.getlength(c)');
 end;

Invoking the stored procedure

declare
    C CLOB := 'large text over 33k characters';
begin
    P(C => C);
end;
/

Issue:

"PLS-00172: string literal too long" error is thrown while invoking stored proc

Sujitmohanty30
  • 3,256
  • 2
  • 5
  • 23
user1455719
  • 1,045
  • 4
  • 15
  • 35

2 Answers2

3

'<Text>' is a varchar2-Literal that then is automatically converted to CLOB. To Convert a large literal to clob you can split it to different part and then concat them:

declare
  c clob := TO_CLOB('First 32k characters')||TO_CLOB('Second 32k characters')||...;
begin
    P(C => C);
end;
/
Radagast81
  • 2,921
  • 1
  • 7
  • 21
0

Sorry to say that, but it is not allowed in Oracle to type more that 32K symbols in a string literal (A string literal is everything that stays between two apostrophes).

You have to split it up in order to be able to proceed this way.

declare
  C CLOB;
begin
  c := 'a long string literal';
  c := c || 'another long string literal';
  P(C => C);
end;

//Output is: 63058

I'd suggest you to read data in some clob column first

PS. And keep in mind your procedure P is going to print a string "dbms_lob.getLength(c)" instead of printing length of variable c. You need to remove apostrophes.

dbms_output.put_line(dbms_lob.getlength(c));
ekochergin
  • 4,109
  • 2
  • 12
  • 19