Problem in spool file

Shell Programming and Scripting
1

Hi,

Iam a newbie. When I give the below query at SQL prompt.

SQL> select col1||'`�`'||col2 from tablename where rownum<2;
1-J7WGX`�`1-7OKC-23

Iam getting � within appostropies...... If I remove appostropies and give the query it is throwing an error.

If I give the same query in spool as below.

SqlExport=`sqlplus -s username/password@databasename <<EOF
spool test1.sh;
set linesize 1000;
set PAGESIZE 0 space 0 feedback off echo off verify off heading off;
set termout off trimspool on;
select col1||'`�`'||col2 from tablename where rownum<2;
exit sql.sqlcode;
spool off;
EXIT;
EOF`
test1.sh11: ||col2 from tablename where rownum<2;^Jexit sql.sqlcode;^Jspool off;^J EXIT;^J EOF: not found
SqlExport=�

Please let me know any reason behind this.

I have set language parameters in .profile as

#EXTENDED CHARACTER SET SUPPORT
set NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
set NLS_LIST_SEPARATOR=�
export NLS_LIST_SEPARATOR

2

Try this:

SqlExport=`sqlplus -s username/password@databasename <<EOF
spool test1.sh;
set linesize 1000;
set PAGESIZE 0 space 0 feedback off echo off verify off heading off;
set termout off trimspool on;
select col1||'\`�\`'||col2 from tablename where rownum<2;
exit sql.sqlcode;
spool off;
EXIT;
EOF`

HTH, :cool:

Regards,

Praveen