java.sql.SQLException: ORA-01704: string literal too long

[former_member223432]

Hi guys,

There is a requirement in PI where we are passing a XML payload to one of the field in database..

For which, i have used "Return as XML" feature  and passing the data to Field(type CLOB) at the database...our main intension of doing this to pass Huge volume of data at once....it wors fine for smaller volume of data..but when the volume is huge, it is giving this error

"structure 'StatementName'): java.sql.SQLException: ORA-01704: string literal too long" in Pi-adapter Engine..guess 4000 characters is the biggest size for a string..how can we insert the huge volume of data,may be more than 20,000 characters wiht the help of BLOB/CLOB and with "Return as XML" feature

thanks

[Former Member · Accepted solution]

Hi Smith,

You will have to change the datatype of the field. As per the error message, CLOB datatype can take maximum 4000 char only... Ask your DB team to change it to appropriate data type.

Regards,

Amol

[Former Member]

We had to deal with the same scenario

Even though the data type is CLOB on the database  when u write an insert statement through XI receiver passing the huge xml in a string variable , it tries to do an insert as a string and that why it fails . Because string has a limit of 4000 char.

. The work around was to write a Stored Procedure . The SP will take the string value passed and assign it to a CLOB variable first and then does an insert command using the variable .

here is a small peek at the stored procedure .

declare
  p_data_clob clob;
begin

  p_data_clob := xmlString ;
 
INSERT
INTO tablename
  (
    column
  )
  VALUES
  (  p_data_clob
    )
  );
end;

[former_member223432]

Thanks Rahul.

[baskar_gopalakrishnan2]

The better approach is store huge data in the compressed format and decompress it while retrieveing. This is the feasible solution. Create clob or blob in the db end. When you send the data through PI use UDF to compress it.

Example:

http://java.sun.com/developer/technicalArticles/Programming/compression/

[Former Member]

Dear Smith

The error shows that this error is out of the length limit for CLOB type from Oracle DB.

refer below

http://forums.oracle.com/forums/thread.jspa?messageID=1138117&tstart=0

->Oracle DB limit,workaround by reducing characters;

http://www.evilcyborg.com/lounge/?id=1245->workaround by using the tag

"cfqueryparam";

the solution to the Oracle CLOB size restriction is to use bind variables inside a stored

procedure.

[rajasekhar_reddy14]

Hi,

You no need to do anything from your end, DB team has to take care about this, check with your DB team they can accept more than 20k characters also.

Regards,

Raj