Not able to pass CLOB in Oracle function

Question

I'm trying to pass CLOB as input parameter in oracle function. The function is created successfully, but when I try to pass a lengthy string, it gives

ora-01704 string literal too long

error.

Below is my function:

CREATE OR REPLACE FUNCTION mySchema.TESTFUNCTION(myData IN CLOB)
  RETURN INT
AS
BEGIN
  DBMS_OUTPUT.PUT_LINE(myData);
  RETURN 1;
END;

When I try to call this function by passing lengthy string more than 5000 characters, it gives the error.

Can anybody help please

yes, I pass as a string only. Eg: select TESTFUNCTION('more than 5000 chars') from dual; will throw error. Please tell me how do i pass them? — superachu

Nick Krasnov Nick Krasnov · Accepted Answer · 2013-12-27T19:30:20

yes, I pass as a string only. Eg: select TESTFUNCTION('more than 5000 chars') from dual;

No, it cannot be done like that. That error is the expected one, simply because SQL(Oracle versions prior to 12c) cannot handle character literals that are more than 4000 bytes in length.

If you need to test your function use PL/SQL, where character literal can be up to 32767 characters(single byte character set) in length:

Here is our function:

Note: Starting from Oracle 10g R2 dbms_output.put_line() line limit is 32767 bytes, in versions prior to 10g R2 the line limit is 255 bytes.

create or replace function f1(
  p_clob in clob
) return number is
begin
  dbms_output.put_line(p_clob);
  return 1;
end;

Here is our anonymous PL/SQL block to test that function:

clear screen;
set serveroutput on;

declare
  l_var clob;
  l_res number;
begin
  l_var := 'aaaaaaaaaaaaaaaaaa.. more than 5000 characters';
  l_res := f1(l_var);
end;

Result:

anonymous block completed
aaaaaaaaaaaaaaaaaa.. more than 5000 characters

Not able to pass CLOB in Oracle function

1 Answers