Using Oracle's SQLPlus with Greenplum external tables

I previously blogged about using a java client to access oracle tables from Greenplum external tables. That demo below is handy for any SQL or RDBMS so a generic solution.

http://theblasfrompas.blogspot.com.au/2013/09/greenplum-command-based-web-external.html

Here is a simpler example this time using sqlplus knowing I am only accessing oracle tables here.

run.sh

  
cd /Users/gpadmin/demos/sqlplus-externaltable

source setup.sh

sqlplus -s scott/tiger@10.32.243.155:1521/ora11gr2 <<!
@emps.sql
!

emps.sql

  
set feedback off
set heading off
set pagesize 0

select empno||'|'||ename||'|'||deptno
from emp
/ 

exttab.sql

  
drop external table oracle_emps_with_sqlplus;

CREATE EXTERNAL WEB TABLE oracle_emps_with_sqlplus
(empno int, ename character varying(20), deptno int)
EXECUTE '/Users/gpadmin/demos/sqlplus-externaltable/run.sh' on host
FORMAT 'TEXT' (DELIMITER '|');

Example

  
gpadmin=# select * from oracle_emps_with_sqlplus where deptno = 30;
 empno | ename  | deptno 
-------+--------+--------
  7499 | ALLEN  |     30
  7521 | WARD   |     30
  7654 | MARTIN |     30
  7698 | BLAKE  |     30
  7844 | TURNER |     30
  7900 | JAMES  |     30
(6 rows)

Time: 88.829 ms