SQLPlus spool to multiple files

Occasionally we need to output the results of a query into multiple files in SQLPlus. For example, in Healthcare IT domain, generating thousands of HL7 messages based on contents in a database.

Below I am giving an eample how to do it using the a pseudo table patient_update table. It has a patient table which contains 5 columns: old_pat_id, old_pat_id_issuer, new_pat_id, new_pat_id_issuer, pat_name. The goal is to generate HL7 ADT^A47 messages for each row based on entries in this able.

First we create a temp table to hold A47 messages — each row corresponds to a message:

CREATE SEQUENCE temp_jli_id_sq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 50000 cache 2;
CREATE SEQUENCE temp_jli_sq INCREMENT BY 1 START WITH 1001 MINVALUE 1 MAXVALUE 50000 cache 2;

create table tempjli1 as select temp_jli_id_sq.nextval msg_pk, 'MSH|^~\&|SEND_APP|SEND_FACILITY|||'||TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')||'||ADT^A47|'||TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')||temp_jli_sq.nextval||'|P|2.3.1'||chr(10)
||'EVN|A47|'||TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')||chr(10)
||'PID|||'||old_pat_id||'^^^'||old_pat_id_issuer||'||'||pat_name||chr(10)
||'MRG|'||new_pat_id||'^^^'||new_pat_id_issuer
as MSG
from patient_update;

drop sequence temp_jli_sq;
drop sequence temp_jli_id_sq;

Second we create a sql script which contains another set of spool and select for each row in the temporary table. So when executing this sql script, it will creates individual files — each file contain one HL7 ADT A47 message.

SET PAGES 0
SET ECHO     OFF
SET FEEDBACK OFF
SET TERMOUT  OFF

SPOOL create_msg.sql

SELECT 'SPOOL a47_' || msg_pk || '.hl7' || CHR (10) ||
       'SELECT MSG FROM tempjli1 WHERE msg_pk=' || msg_pk || ';' || CHR (10) ||
       'SPOOL OFF'
FROM tempjli1 order by msg_pk;

SPOOL OFF

@create_msg.sql

Now you will have multiple files (messages) which can be sent to another system to handle them.