Well, Oracle database has another way to run Python script, not need to involve Java this time. External table support external runnable thing (such as OS command, C program, script) with key word preprocessor! actually, it is a better deal when I need those Python script to feed Oracle database.
As oracle document said. ”
If the file you want to load contains data records that are not in a format supported by the ORACLE_LOADER access driver, then use the PREPROCESSOR clause to specify a user-supplied preprocessor program that will execute for every data file. Note that the program specification must be enclosed in a shell script if it uses arguments.
The preprocessor program converts the data to a record format supported by the access driver and then writes the converted record data to standard output (stdout), which the access driver reads as input.”
This feature started from 10.2.0.5.
I found 2 good examples in following URLs
-Linux shell script https://download.oracle.com/otndocs/products/database/enterprise_edition/utilities/pdf/xtables_preproc11g_1009.pdf
-Windows bat file
So I got my example to feed Oracle database from Python script output.
from faker import Faker
x = max(1,int(sys.argv))
x = 1
for i in range(x):
And it needs a simple shell script to hide this Python script plus parameters.
The key part is external table.
create directory Python_script as '/tmp/python/script';
create directory Python_conf as '/tmp/python/conf';
create directory Python_log as '/tmp/python/log';
grant read,execute on directory Python_script to system;
grant read on directory Python_conf to dbuser;
grant read,write on directory Python_log to dbuser;
DROP TABLE dbuser.myDemo_ext purge;
CREATE TABLE dbuser.myDemo_ext
DEFAULT DIRECTORY Python_conf
RECORDS DELIMITED by NEWLINE
BADFILE Python_log: 'myDemo.bad'
DISCARDFILE Python_log: 'myDemo.discard'
LOGFILE Python_log: 'myDemo.log'
FIELDS OPTIONALLY ENCLOSED BY '"' LRTRIM MISSING FIELD VALUES ARE NULL (
email char(30) TERMINATED BY '|' ,
phone char(30) TERMINATED BY ''
REJECT LIMIT UNLIMITED;
# don't forget create a dummy file
$ touch /tmp/python/conf/file
SQL> create table mydemo as select * from mydemo_ext;
SQL> select * from mydemo;
firstname.lastname@example.org 665-515-7129 x645
email@example.com (568) 409-4511
firstname.lastname@example.org (700) 240-4337
email@example.com 543 449 7777
It is easier than Java method I talked before.