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: