Self Contained ksh script for SQL PLUS

[stewartdwest]

From Unix Korn shell script creating and Oracle External Table. The unix file name is typically provided via the LOCATION('myfile.txt'). My script has the problem that oracle rejects it as LOCATION(myfile.txt). I need to preserve the quotes around the filename.

The MYSQLCMD below would work fine from sqlplus prompt via @mysqlcmd.txt


When defined and invoked as in a script as:

#!/bin/ksh
export UID_PASSWD=myuser/mypass
export MYSQLCMD='CREATE TABLE MY_TBL (MYVAR NUMBER) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY mydir ACCESS PARAMETERS(RECORDS DELIMITED BY NEWLINE NOLOGFILE NOBADFILE FIELDS TERMINATED BY ',' MISSING FIELD VALUES ARE NULL ) LOCATION('myfile.txt'));'

sqlplus -s <<!>${TMPFILE} ${$UID_PASSWD}
${MYSQLCMD}
!

#-- end of script

Error at Line 1:
ORA-00905: missing keyword

The error is occuring because Oracle sees LOCATION(myfile.txt) without the quotes. I tried ''myfile.txt'' etc.. I don't want to maintan two files

 

[stewartdwest]

I found the answer to my question, for those who like self contained ksh scripts for oracle

A backslash cannot be used to escape a single quotation mark in a string that is set in single-quotation marks. An embedded quotation mark can be created by writing, for example: 'a'\''b', which yields a'b.

Therefore the definition was:

export SQLSTMT_CREA_TBL='
CREATE TABLE MYTBL ( .....blah blah see above
LOCATION'\(\'myfile.txt\'\)');'