In the final, part 3 of the article, I’ll show you how we’re pulling from ServersCheck sensor the current temperature and relative humidity in our data center with simple SQL statement, like this:

If you recall, in Part 1 of the article I published Go “script” that could be compiled on all supported Go platforms, including Linux.
We decided that we’ll be using one of our existing Oracle11g XE instance running on Oracle Linux 6.4 (x64) to host our “vpctemp” application written in go. On this Linux server we already had the latest Go compiler installed (go 1.1.2 at the time of this writing), but we could as easily compile the vpctemp.go on some other box and then simply copy executable to our production Oracle Linux server — not worrying about some potential missing library, thanks to “go” statically compiled nature.

We copied vpctemp binary to /oracle/dba/go directory and turning on execution bit to allow vpctemp to be executed by oracle user:

[alesk@oraxe go]$ ls -l /oracle/dba/go
total 5056
-rwxr-xr-x 1 oracle dba 5177336 Jul 31 12:17 vpctemp

Then all that we did was to create “pseudo” external table. Pseudo in a sense that external table has no reference to external data file, instead all that it includes is a call to preprocessor directive. Preprocessor directive points to our golang program, which pulls data from ServersCheck sensor and prints the result to standard output, which is send to a client. Very simple and convenient way for ad hoc checks. (Oracle introduced preprocessor directive for external table in 11gR2 and later on backporting feature to 10.2.0.5 as well)

// SQL*Plus...connect alesk@oraxe
  
create or replace directory goexec as '/oracle/dba/go';
grant execute on directory goexec to dba;
 
create table vpctemp (
line varchar2(80)
)
organization external
(
     type oracle_loader
     default directory goexec
     access parameters
     (
          records delimited by newline
          preprocessor goexec:'vpctemp'
     )
     location
     (
          goexec:'vpctemp'
     )
);

create public synonym vpctemp for alesk.vpctemp;
grant select on alesk.vpctemp to dbateam;

And that’s it. For a DBA team getting the current temperature and humidity in a data center becomes as easy as:

SQL> select * from vpctemp;

** UPDATE May 2019 **
During the migration of golang vpctemp application from 11g to 18c database we got an error when querying from vpctemp external table:

SQL> select * from vpctemp;                             
select * from vpctemp                                   
*                                                       
ERROR at line 1:                                        
ORA-29913: error in executing ODCIEXTTABLEFETCH callout 
ORA-30653: reject limit reached

We found out that Oracle external table “preprocessor” in 18c doesn’t like the first record (blank line) and since original external table (see DDL above) was created without an explicit reject limit, the Oracle created external table wit the default valu, “reject limit 0”. That’s why query failed immediately.
The workaround was newly created external table:

CREATE TABLE KAVSEK.VPCTEMP
(
  LINE  VARCHAR2(80 CHAR)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY GOEXEC
     ACCESS PARAMETERS 
       ( records delimited by newline	
         preprocessor goexec:'vpctemp'
         nologfile
         nobadfile
	 )
     LOCATION (GOEXEC:'vpctemp')
  )
REJECT LIMIT 10
NOPARALLEL
NOMONITORING;

The result:

SQL> select * from vpctemp;                    
                                               
LINE                                           
-----------------------------------------------
VPCtemp v0.10        Location: VPC1            
***********************************            
Timestamp     : 2019-5-31 13:19:09             
Temp. internal: 13.88                          
Temp. external: ?                              
Humidity      : ?                              
                                               
6 rows selected.