Very long text in Oracle

Question

I would like to know which is the best practice for saving very long text in an ORACLE database field?

I save with Coldfusion from a form the text content of a mail in an Oracle database. I use currently the type LONG in my database field. I encounter problems for saving very long text (with many special characters like /, ", %, $, & and others) I receive an error 502 (Bad gateway). If the very long text contains some special characters it's not a problem.

Could you help me with that?

Thanks in advance for your help.

Regards,

Answer 1

Use a CLOB column in the database.

Use bind parameters in ColdFusion to insert the value.

<cfquery name="insert_long_text" datasource="your_datasource_name">
INSERT INTO your_table(
  id,
  email
) VALUES (
  <cfqueryparam value="#your_id_value#"    cfsqltype="CFL_SQL_INTEGER" />,
  <cfqueryparam value="#your_email_value#" cfsqltype="CFL_SQL_CLOB" />
)
</cfquery>

Or pass the values (again using bind parameters) to an Oracle stored procedure.

If the message is particularly long and loading it into memory is going to create performance issues then you may need to use Java (which ColdFusion is built upon) to stream the file (example streaming BLOBs).