Reading ServersCheck Status page from command line – Part 3.
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;
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.