Oracle, Unix, Windows : Examples of SQL, SH and BAT scripts

Hello,

In this post, I would like to keep in my “knowledge base” 2 examples of scripts : SHELLSCRIPT + SQL and SQL + BATCH.
 
Example 1 : SHELLSCRIPT + SQL : Export data to CSV file

• Creation of a SHELLSCRIPT script ex1_launcher_script.sh which is the launcher of a SQL script ex1_processing_script.sql. This SH script takes 2 parameters: the output CSV file and the log file.
  view source

  print?

  01	#!/bin/bash

  02

  03	# Extraction and export to CSV file

  04

  05	# Loading configuration parameters contains the variables $ORA_USER, $ORA_PASSWD, $ORA_INSTANCE @$SCRIPT_DIR and $SOURCE_FILES

  06	. ../config/config.cfg

  07

  08	# Input parameters

  09	export FILE_OUT=$1

  10	export FILE_LOG=$2

  11

  12	# Start of processing

  13

  date

  14

  15	sqlplus -l $ORA_USER/$ORA_PASSWD@$ORA_INSTANCE @$SCRIPT_DIR/ex1_processing_script.sql $SOURCE_FILES $FILE_OUT $FILE_LOG

  16

  ret=$?

  17

  18	# End of processing

  19

  date

  20

  21	echo 'return code: ' $ret

  22

  exit $ret

   
  

• Creation of SQL script ex1_processing_script.sql containing the execution of SQL queries and the processing of data returned in order to export to a CSV file.
  view source

  print?

  001	set serveroutput on  size 1000000

  002	set feed off

  003	set linesize 100

  004

  005

  DECLARE

  006

  007	-- SQL QUERY WITH CURSOR

  008	CURSOR c1 IS

  009	SELECT my_field1, my_field4, my_field2, my_field3,

  010	pays_nat.pay_lib my_field6, pays_fisc.pay_lib my_field7,

  011	pays_geo.pay_lib my_field8

  012	FROM my_table1, my_table2, my_table3, my_table4, my_table5, my_table6, my_table7

  013	WHERE ..... ;

  014

  015	-- Input parameters

  016	SOURCE_FILES    VARCHAR2(100);

  017	FILE_OUT    VARCHAR2(10000);

  018	FILE_LOG    VARCHAR2(10000);

  019

  020	-- Others variables

  021	f_out   UTL_FILE.FILE_TYPE;

  022	f_log   UTL_FILE.FILE_TYPE;

  023	l_out   VARCHAR2(10000);

  024	l_log   VARCHAR2(10000);

  025	is_my_field4_ok CHAR(1);

  026	cpt NUMBER;

  027

  028	err_num NUMBER;

  029	err_msg VARCHAR2(255);

  030

  031

  BEGIN

  032

  033	SOURCE_FILES:='&1';

  034	FILE_OUT:='&2';

  035	FILE_LOG:='&3';

  036

  037	-- Opening of files --

  038	f_out := UTL_FILE.FOPEN (SOURCE_FILES, FILE_OUT,'w', 10000);

  039	f_log := UTL_FILE.FOPEN (SOURCE_FILES, FILE_LOG,'w', 10000);

  040

  041	l_out := 'Field1;Field23;Field4;Field4_is_OK;Field6;Field7;Field8';

  042	UTL_FILE.PUT_LINE(f_out,l_out);

  043

  044	-- Counter --

  045

  cpt := 0;

  046

  047	-- Loop on cursor and Writing to file --

  048	FOR cur IN c1

  049

  Loop

  050

  051	if (substr(Cur.my_field4,1,2) = 'OK')

  052

  then

  053	is_my_field4_ok := 'O';

  054

  else

  055	is_my_field4_ok := 'N';

  056

  end if;

  057

  058	l_out := (

  059	to_char(Cur.my_field1)

  060

  ||';'

  061	||Cur.my_field2 || ' ' || Cur.my_field3

  062

  ||';'

  063	||to_char(Cur.my_field4)

  064

  ||';'

  065	||is_my_field4_ok

  066

  ||';'

  067	||Cur.my_field6

  068

  ||';'

  069	||Cur.my_field7

  070

  ||';'

  071	||Cur.my_field8

  072

  ||';'

  073

  );

  074

  075	UTL_FILE.PUT_LINE(f_out,l_out);

  076

  077	cpt := cpt + 1;

  078

  END Loop;

  079

  080	DBMS_OUTPUT.put_line ('Fin du traitement...');

  081	DBMS_OUTPUT.put_line (to_char(cpt) || ' my_table3s ecrites');

  082

  083

  084	-- Closure of files --

  085	UTL_FILE.fclose_all;

  086	DBMS_OUTPUT.put_line ('END');

  087

  088	-- Error management --

  089

  EXCEPTION

  090	WHEN NO_DATA_FOUND THEN

  091	DBMS_OUTPUT.put_line ('End of processing...');

  092	DBMS_OUTPUT.put_line (to_char(cpt) || ' rows written');

  093

  094	-- Closure of files --

  095	UTL_FILE.fclose_all;

  096	DBMS_OUTPUT.put_line ('END');

  097	UTL_FILE.fclose_all;

  098	WHEN OTHERS

  099

  THEN

  100	err_num := SQLCODE;

  101	err_msg := SUBSTR (SQLERRM, 1, 255);

  102	DBMS_OUTPUT.put_line ('ERROR: ' || err_num || ' ' || err_msg);

  103	UTL_FILE.put_line (f_log, 'ERROR: ' || err_num || ' ' || err_msg);

  104	UTL_FILE.fclose_all;

  105

  106

  END;

  107

  108

  /

  109

  exit

   

Exemple 2 : SQL + BATCH : SQL launcher of SQL with BATCH connector to database

• Creation of a SQL script ex2_launcher_script.sql which is the launcher of a second SQL script ex2_processing_script.sql. These SQL scripts don’t take any parameter. The log file ex2_logging_file.log is fixed in each SQL script.
  view source

  print?

  01	/*==============================================================*/

  02	/* DBMS name:      ORACLE 11g V2.3                         */

  03	/*==============================================================*/

  04	SPOOL ex2_logging_file.log;

  05	SET ECHO ON;

  06	SET SERVEROUTPUT ON;

  07

  08	WHENEVER SQLERROR EXIT -1 ROLLBACK;

  09

  10	@@ ex2_processing_script.sql

  11

  12

  13

  COMMIT;

  14

  15	SPOOL OFF;

   

• Creation of second SQL script ex2_processing_script.sql containing the execution of SQL queries (INSERT/UPDATE).
  view source

  print?

  01	spool ex2_logging_file.log

  02	SET echo ON;

  03	WHENEVER SQLERROR EXIT -1 ROLLBACK;

  04	SET DEFINE OFF ;

  05

  06	--- Insert queries

  07	Insert into MYSCHEMA.MY_DOC_TYP (...) values (1,'001',2,'1',1,'0',null,...);

  08	Insert into MYSCHEMA.MY_DOC_TYP (...) values (1,'002',2,'1',1,'0',null,...);

  09	Insert into MYSCHEMA.MY_DOC_TYP (...) values (1,'003',2,'1',1,'0',null,...);

  10	Insert into MYSCHEMA.MY_DOC_TYP (...) values (1,'004',2,'1',0,'0',null,...);

  11	Insert into MYSCHEMA.MY_DOC_TYP (...) values (1,'005',2,'1',0,'0',null,...);

  12

  13	--- Update query

  14	UPDATE MYSCHEMA.MY_DOC_TYP SET MY_FLG=0 WHERE doc_id='123456789';

  15

  16

  17

  COMMIT ;

  18

  EXIT 0;

   

• As you may have noticed, there is no connection to the database (via SQL plus) in the SQL scripts above, so, this connection is done in a BATCH script ex2_batch_launcher.bat. This BATCH file launch SQL PLUS and allows user to execute every SQL script.
  view source

  print?

  1

  @echo off

  2

  cd

  3

  pause

  4	SQLPLUS MY_USER_HUO/MY_USER_PASSWORD@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mydbserver....java.lu)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MYDBSERVER....JAVA.LU)))

   

• The log file ex2_logging_file.log is generated during the execution:
  view source

  print?

  01	SQL> SET echo ON;

  02	SQL> WHENEVER SQLERROR EXIT -1 ROLLBACK;

  03	SQL> SET DEFINE OFF ;

  04

  SQL>

  05	SQL> --- Insert queries

  06	SQL> Insert into MYSCHEMA.MY_DOC_TYP (...) values (1,'001',2,'1',1,'0',null,...);

  07

  08	1 row inserted.

  09

  10	SQL> Insert into MYSCHEMA.MY_DOC_TYP (...) values (1,'002',2,'1',1,'0',null,...);

  11

  12	1 row inserted.

  13

  14	SQL> Insert into MYSCHEMA.MY_DOC_TYP (...) values (1,'003',2,'1',1,'0',null,...);

  15

  16	1 row inserted.

  17

  18	SQL> Insert into MYSCHEMA.MY_DOC_TYP (...) values (1,'004',2,'1',0,'0',null,...);

  19

  20	1 row inserted.

  21

  22	SQL> Insert into MYSCHEMA.MY_DOC_TYP (...) values (1,'005',2,'1',0,'0',null,...);

  23

  24	1 row inserted.

  25

  26

  SQL>

  27

  SQL>

  28	SQL> --- Update query

  29	SQL> UPDATE MYSCHEMA.MY_DOC_TYP SET MY_FLG=0 WHERE doc_id='123456789';

  30

  31	1 row updated.

  32

  33

  SQL>

  34

  SQL>

  35	SQL> COMMIT ;

  36

  37	Validation done.

  38

  39	SQL> EXIT 0;

   

That’s all!!!

Huseyin OZVEREN