Posted by There are plenty of blog posts about using the pre-processor facility in external tables to get OS level information available from inside the database. Here’s a simple example of getting a directory listing:
run_os.bat
==========
@echo off
cd \oracle
dir /b
SQL> create table fs_size (
2 disk varchar2(64)
3 )
4 organization external
5 ( type oracle_loader
6 default directory temp
7 access parameters
8 ( records delimited by newline
9 preprocessor temp:'run_os.bat'
10 )
11 location ( temp:'empty.txt' )
12 ) reject limit unlimited ;
Table created.
SQL>
SQL> select * from fs_size;
DISK
------------------------------------------------
18c_cloud
admin
adw_cloud
atp_cloud
audit
cfgtoollogs
checkpoints
datamodeler
dbsat
diag
instantclient
jdbc183
ords.184
ords122
ords181
ords184
ordsconf
product
sql
sqlcl
sqldeveloper
swingbench
wallet
So far so good. But on Windows, it can be quite particular about the runtime environment. For example, if I fire up a command prompt, as a logged in user I can easily get a listing of disk partitions on my machine:
C:\>wmic logicaldisk get caption
Caption
C:
D:
E:
F:
G:
M:
P:
X:
Z:
It would seem logical that this would be a simple drop-in replacement for the directory listing batch file I used before. I’ll amend the batch file to list out disk partitions
run_os.bat
==========
@echo off
wmic logicaldisk get caption
and now I’ll run my query again.
SQL> select * from fs_size;
no rows selected
Hmmm….that didn’t go as well as planned 
. Looking in the log file, there are no clues about the cause.
LOG file opened at 03/06/19 14:33:42
KUP-05007: Warning: Intra source concurrency disabled because the preprocessor option is being used.
Field Definitions for table FS_SIZE
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted
Fields in Data Source:
DISK CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
The issue here is that many Windows programs require a minimum environment configuration before they will run. In this case, I need to let Windows know the location of the OS installation.
run_os.bat
==========
@echo off
set SystemRoot=C:\WINDOWS
wmic logicaldisk get caption
And voila! I get access to many more Windows command to probe out information from the Operating System.
SQL> select * from fs_size;
DISK
--------------------------------
Caption
C:
D:
E:
F:
G:
M:
P:
X:
Z:
Learning is not a spectator sport 
External table preprocessor scripts can be really handy…
https://oracleexpert.net/no-direct-access-to-database-server/
Nice stuff, although I’d probably move off dbms_support and onto dbms_monitor
Any idea why this does not work with Oracle 12.2 or Oracle 19? For both environments I get “no rows selected”, same as you describe in your article. Same steps applied for Oracle 11g works as expected. For 12.2 or 19 I get no error, no complains in the log file, just “no rows selected”. It is like the BAT script does not get executed. The preprocessor has access to the BAT file because the moment I rename it, it complains that BAT file cannot be found. Really curios why is not working anymore for upper releases.
I don’t think its a version thing, eg
SQL> create table fs_size (
2 disk varchar2(64)
3 )
4 organization external
5 ( type oracle_loader
6 default directory temp
7 access parameters
8 ( records delimited by newline
9 preprocessor temp:’run_os.bat’
10 )
11 location ( temp:’empty.txt’ )
12 ) reject limit unlimited ;
Table created.
SQL> select * from fs_size;
DISK
—————————————————————-
Caption
C:
D:
E:
F:
M:
P:
U:
X:
Z:
11 rows selected.
SQL> select banner from v$version;
BANNER
——————————————————————————–
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
SQL> host cat x:\temp\run_os.bat
@echo off
set SystemRoot=C:\WINDOWS
wmic logicaldisk get caption
So it must be something else
If your Oracle 19 works, I guess I have an issue with my installation. The “host” command via SQL Developer works for me as well. I assume somehow Windows does not allow Oracle to execute the BAT script for some reason via the external table preprocessor. Strange that nothing suspicions is logged into the external table log file. Any hints what I should look for? Appreciate your answer, thank you!
Check the user that the oracle service is running as. It might be the case that you need to set its permissions. I think there is a “logon to run batch job” or similar in Windows that might need to be set.
My databases use the defaults, so they created a virtual account eg “NT Service\OracleServiceDB19” etc for each windows service running a database.
Thank you so much for replying. I had to install Oracle 19 locally and everything worked as you describe in this article. Discovered as well this “logon to run batch job”, but nothing wrong in there, my local env. and the one where I have the issue look the same. All env. use the virtual account. For the env. where I have the issue, I assume there is some windows policy somewhere that does not allow to execute the batch file because querying the table returns “no rows selected” no mater what I type in as batch script, even nonsense. I get error only if the batch script is blank. I hope to find out what’s going on, at least is not Oracle.