Cron not executing Oracle Spool Script Correctly.

[idrivq]

Hi.

I currently have an Oracle table which contains Dutch characters, among others, such as é, à, ø, õ and æ. When I run a .sql spool script via the sql> command prompt, everything seems to get spooled out correctly. However, when I set this script to run via cron, all the special characters i listed above appear as ? (question marks). When I run the spool via command line, the special characters appear exactly as they are in the table. I know I need to setup my cron environment variable in the same manner as when I login to sqlplus, by setting the PATHs, etc, but I am still having a problem with it and can't seem to make it work.

I have created a env.sh script in which i am currently exporting the ORACLE_HOME, ORACLE_SID. BASH_ENV.

In my main run_spool.sh script that the cron is running, i am first calling the env.sh script with a
. env.sh
and then below this script i am running my .sql script, such as this:
$ORACLE_HOME/bin/sqlplus -s username/password@sid @/location/of/script/spool.sh

So my final spool.sh script looks like this:

#!/bin/bash

. /location/of/script/env.sh

$ORACLE_HOME/bin/sqlplus -s username/password@sid @/location/of/scripts/spool.sh

Can anyone please help me out with this. Much help would be appreciated.

Thanks.

[A. Clay Stephenson · Accepted solution]

Your env.sh probably contains a subset of your needed variables. For example, LANG may need to be defined. You should also note that the characters actually displayed are a function of the actual display device (terminal, terminal emulator, printer). Your output is not really characters as such but simply a stream of values which MIGHT be interpreted as characters. As an example, the output file might have a byte with the value 65 (decimal) or 41 (hex); Now whether or not that displays as "A" or "*" or "a" depends solely upon the character mapping of the display device. The first thing that I would do is view the output of your file using xd so that you know the hex value of each byte. You can then determine if your problem is a display problem or a data problem.

I would issue an "env > /tmp/myenv" under a shell that "correctly" executes your SQL and then put a very similar command, "env > /tmp/cronenv", in your croned script after your sourced env.sh. Diff those two files and you might find that you need a few more environment variables than you thought you did.

[Hein van den Heuvel]

It's probably to do with the NLS_LANG environment variable.

Now when you run teh script directly, do you connect directly perhaps (BEQ?) or also through SQL*net/TNSNAMES as per cron setup?
You might need WE8DEC but have US7ASCII?


To Oracle doc has of course a bunch on NLS, but here is a quick overview from the web:

http://www.dbazine.com/oracle/or-articles/hordila7

[Yogeeraj_1]

hi,

can you try the following:

$ORACLE_HOME/bin/sqlplus << EOF
connect username/password@sid
--run your script
@/location/of/scripts/spool.sql
--
EOF

Concerning your NLS settings, the NLS_LANG variable looks like this:


NLS_LANG=FIELD1_FIELD2.FIELD3

Field 1 corresponds to NLS_LANGUAGE and specifies things like the language for Oracle messages, language for day and months names, writing direction etc.

Field 2 corresponds to NLS_TERRITORY and specifies things like date format, decimal and group separator symbols, the week start day etc.

Field 3 corresponds to the characterset of the OS environment of this machine. This field must be set to the equivalent value of the OS environment characterset.

Please adjust accordingly and let us know if OK.

kind regards
yogeeraj

[idrivq]

Had to edit the oraenv file to export the NLS_LANG environment variable. It now works. Thank you all for your advise. Hopefully this can help other people.