External table preprocessor on Windows

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 Smile. 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:

8 comments

  1. 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.

    1. 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

      1. 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!

        1. 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.

          1. 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.

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.