SQL*Loader error only when calling via Python subprocess

Question

I have a python script that receives a large csv file and should load the data into an Oracle table. The plan is to use SQL Loader to accomplish this.

I have Instant Client 19.8 installed with SQL Loader. My Python code is run via this script

#!/bin/sh

PY=py

APP_HOME=/C/Users/.../Projects/.../updates
APP_BIN=$APP_HOME/bin
APP_LIB=$APP_HOME/lib
APP_CONFIG=$APP_HOME/config
APP_LOGS=$APP_HOME/logs
APP_DATA=$APP_HOME/data
APP_OUTPUT=$APP_HOME/output

GLOBAL_ENV_DIR=$APP_HOME/ENV

export APP_HOME
export APP_BIN
export APP_LIB
export APP_CONFIG
export APP_LOGS
export APP_DATA
export APP_OUTPUT
export GLOBAL_ENV_DIR

export LD_LIBRARY_PATH=/C/Oracle/instantclient_19_8
export PYTHONPATH=$PYTHONPATH:$APP_HOME:$APP_BIN:$APP_LIB:$APP_LIB/util:$APP_CONFIG:$APP_LOGS:$APP_DATA:$APP_OUTPUT:

echo $PYTHONPATH

echo
echo $PY $APP_BIN/update/update_processor.py --globalenvdir $GLOBAL_ENV_DIR --apphomedir $APP_HOME  --date 01012020 $1 $2 $3 $4
echo

$PY $APP_BIN/update/update_processor.py --globalenvdir $GLOBAL_ENV_DIR --apphomedir $APP_HOME  --date 01012020 $1 $2 $3 $4

Then, I attempt to run SQL Loader like this

subprocess.call('sqlldr user/pass@.../... control=config/update_processor.ctl data=data/data.dat')

And see this error

Message 2100 not found; No message file for product=RDBMS, facility=ULMessage 2100 not found; No message file for product=RDBMS, facility=UL

I can of course run SQL Loader on its own or from a separate Python script, so I assume something is not configured correctly in my shell script?

I just spent some time cobbling together something similar and did not see that error. If you can update your question with a runnable example then I can look again. Otherwise, check that the environment variables are being propagated. You may find it safer to use ldconfig instead of setting LD_LIBRARY_PATH (see the Instant Client installation instructions). — Christopher Jones
– Christopher Jones, Commented Jun 21, 2021 at 23:21
I quite literally just created an empty /instantclient/rdbms/mesg folder which I deleted after one run and it started working correctly... Not sure why it would have needed that since I was under the impression that folder was only needed with full installs of an Oracle client. — Russell Quinlan
– Russell Quinlan, Commented Jun 22, 2021 at 15:49

Sayan Malakshinov · Accepted Answer · 2021-06-21 17:54:55Z

1

You should have set ORACLE_HOME env variable. For example you can set it in command line:

subprocess.call('ORACLE_HOME=/path/to/oracle-home sqlldr user/pass@.../... control=config/update_processor.ctl data=data/data.dat')

answered Jun 21, 2021 at 17:54

Sayan Malakshinov

8,7501 gold badge21 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Russell Quinlan Over a year ago

So would ORACLE_HOME point to the instant client location in this case?

Sayan Malakshinov Over a year ago

@RussellQuinlan yes

Christopher Jones Over a year ago

With Instant Client, don't set ORACLE_HOME

Collectives™ on Stack Overflow

SQL*Loader error only when calling via Python subprocess

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related