How to insert huge data to "long" datatype in Oracle DB using sqlplus terminal?

0
votes

My requirement is to store a very big data to "LONG" datatype column in Oracle DB. I know that for CLOB and NCLOB it can be achieved using BFile (by reading it from file). But how do I do it for long?

Eg: Insert into test (LONG_COL) values('value more than 4000 characters');

Here LONG_COL is of type "LONG".

If I execute the query on SQLPlus I get below error.

SQL Error: ORA-01704: string literal too long
01704. 00000 -  "string literal too long"
*Cause:    The string literal is longer than 4000 characters.
*Action:   Use a string literal of at most 4000 characters.
           Longer values may only be entered using bind variables.

Please guide.

1
oraclesqlplus
FYI, LONGs are deprecated. Oracle suggests using CLOBs instead. I don't know if that's an option. – eaolson

1 Answers

0
votes

As the error message suggests, one method you can try is using Bind Variables. If you aren't familiar with them, they can be very useful for a plethora of things including getting around the ORA-01704 error when using SQL*Plus, or even protecting your database from potential SQL Injection attacks.

Here are a couple of examples that you can use:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2320123769177

https://asktom.oracle.com/pls/asktom/asktom.search?tag=pls-00172-string-literal-too-long

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2320123769177

https://oracle-base.com/articles/misc/literals-substitution-variables-and-bind-variables