Code example: plsql block in loop, and writing to file

========================
General
========================
Example of bash file calling sql file, which is executing a plsql block.
The plsql block is looping in endless loop, writing to a file.
The purpose is to check database connection.

========================
Code
========================

select_in_loop.sh

select_in_loop.sql

select_in_loop.sh

call sql file 

select_in_loop.sql

loop, and write to file

select_in_loop.sh
less select_in_loop.sh
#!/bin/bash
. .set_profile
sqlplus system/xen86pga@igt @select_in_loop.sql

select_in_loop.sql
less select_in_loop.sql
SET VERIFY OFF
SET PAGESIZE 0
SET HEADING OFF
SET FEEDBACK ON
SET NEWPAGE NONE
SET TERMOUT ON
SET UNDERLINE OFF
SET LINESIZE 120
SET SERVEROUTPUT OFF

DECLARE
  v_a     NUMBER;
  v_b     NUMBER;
  v_date  VARCHAR2(100);
  v_file  UTL_FILE.FILE_TYPE;
BEGIN
  v_a := 1;
  v_b :=10;
  DBMS_OUTPUT.put_line('Opening File');
  v_file := UTL_FILE.FOPEN('IG_EXP_DIR','connection_test.lst','w');
  UTL_FILE.PUT_LINE(v_file,'Start Loop'); 
  WHILE v_a < v_b LOOP 
    SELECT TO_CHAR(SYSDATE,'YYYYMMDD hh24:mi:ss') INTO v_date FROM DUAL;
    UTL_FILE.PUT_LINE(v_file,'Now time is '||v_date);  
    UTL_FILE.FFLUSH(v_file);
    DBMS_LOCK.sleep(30);
    --v_a := v_a + 1;
  END LOOP;
  UTL_FILE.PUT_LINE(v_file,'Finished Loop');
  UTL_FILE.PUT_LINE(v_file,'Closing File');
  UTL_FILE.FFLUSH(v_file);
  UTL_FILE.FCLOSE(v_file);
EXCEPTION
  WHEN OTHERS THEN
    UTL_FILE.PUT_LINE(v_file,'Unexpected Error: '||SQLERRM);
    UTL_FILE.PUT_LINE(v_file,'Closing File');
    UTL_FILE.FFLUSH(v_file);
    UTL_FILE.FCLOSE(v_file);
END;
/



Example of a procedure doing same thing:
CREATE OR REPLACE PROCEDURE extract_from_db (HEADER_FLAG IN NUMBER) IS
  vDBUser               VARCHAR2(50);
  vFileName             VARCHAR2(50);
  vDirectoryName        VARCHAR2(30);
  vFileHandle           UTL_FILE.FILE_TYPE;

  CURSOR data_cur IS
  SELECT A.country_name, B.network_name,C.operator_group_name 
   FROM GSM_COUNTRIES A, 
        GSM_NETWORKS B, 
        GSM_OPERATOR_GROUPS C, 
        GSM_IMSI_PREFIXES D, 
        GSM_MSC_PREFIXES E, 
        GSM_NDC_PREFIXES F
  WHERE B.country_id=A.country_id 
    AND B.operator_group_id=C.operator_group_id 
    AND B.network_id=D.network_id 
    AND B.network_id=E.network_id 
    AND B.network_id=F.network_id  
ORDER BY country_name, network_name;       

BEGIN
  vDirectoryName := 'IG_EXP_DIR';
  select user into vDBUser from dual;
    
  --ROAMING INFORMATION EXPORT
  vFileName   := vDBUser||'_RI.csv';
  vFileHandle := UTL_FILE.FOPEN(vDirectoryName, vFileName, 'W');
  IF HEADER_FLAG=1 THEN
    UTL_FILE.PUT_LINE(vFileHandle, 'date,country_name,network_name,operator_group_name);
  END IF;

  FOR data_rec IN data_cur LOOP
    UTL_FILE.PUT_LINE(vFileHandle,SYSDATE||';'||data_rec.country_name||';'||
                      data_rec.network_name||';'||data_rec.operator_group_name);
  END LOOP;

  UTL_FILE.FFLUSH(vFileHandle);  
  UTL_FILE.FCLOSE(vFileHandle);