Insert file content (with white line and special characters ) in bash variable for insert in oracle table with sqlplus

Question

I write this simpe test.ksh scipt:

#!/bin/ksh

TEST=$(cat $1 )
sqlplus -S *****/*****@*****<<- EOF

set define off;
SET SQLBLANKLINES ON;
INSERT INTO DBADOC.TEST
(
        "TEST"
)
 VALUES
(
      q'[ $TEST ]'
)
;
commit;
EOF

exit

---

Now if i run it with a simple file (3 lines without special char):

./test.ksh /home/oracle/scripts/test.txt
1 row created.


Commit complete.

It runs perfectly.

If I choose a complex file (with blank lines and special character ), it doesn't work:

./test.ksh /home/oracle/scripts/check_free_space_dg.ksh
SP2-0734: unknown command beginning "# ========..." - rest of line ignored.
SP2-0734: unknown command beginning "# @(#) NOM..." - rest of line ignored.
SP2-0734: unknown command beginning "# @(#) GRU..." - rest of line ignored.
SP2-0734: unknown command beginning "# @(#) DES..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
[...]

Answer 1

SQL-Plus doesn't support hash as a comment character. You can use these to make comments:

• REM or REMARK at the beginning of a line
• -- (double dashes) at the beginning of a line
• or paired /* with */ for block comment, as seen in C.