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.
#!/bin/bash # Extraction and export to CSV file # Loading configuration parameters contains the variables $ORA_USER, $ORA_PASSWD, $ORA_INSTANCE @$SCRIPT_DIR and $SOURCE_FILES . ../config/config.cfg # Input parameters export FILE_OUT=$1 export FILE_LOG=$2 # Start of processing date sqlplus -l $ORA_USER/$ORA_PASSWD@$ORA_INSTANCE @$SCRIPT_DIR/ex1_processing_script.sql $SOURCE_FILES $FILE_OUT $FILE_LOG ret=$? # End of processing date echo 'return code: ' $ret 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.
set serveroutput on size 1000000 set feed off set linesize 100 DECLARE -- SQL QUERY WITH CURSOR CURSOR c1 IS SELECT my_field1, my_field4, my_field2, my_field3, pays_nat.pay_lib my_field6, pays_fisc.pay_lib my_field7, pays_geo.pay_lib my_field8 FROM my_table1, my_table2, my_table3, my_table4, my_table5, my_table6, my_table7 WHERE ..... ; -- Input parameters SOURCE_FILES VARCHAR2(100); FILE_OUT VARCHAR2(10000); FILE_LOG VARCHAR2(10000); -- Others variables f_out UTL_FILE.FILE_TYPE; f_log UTL_FILE.FILE_TYPE; l_out VARCHAR2(10000); l_log VARCHAR2(10000); is_my_field4_ok CHAR(1); cpt NUMBER; err_num NUMBER; err_msg VARCHAR2(255); BEGIN SOURCE_FILES:='&1'; FILE_OUT:='&2'; FILE_LOG:='&3'; -- Opening of files -- f_out := UTL_FILE.FOPEN (SOURCE_FILES, FILE_OUT,'w', 10000); f_log := UTL_FILE.FOPEN (SOURCE_FILES, FILE_LOG,'w', 10000); l_out := 'Field1;Field23;Field4;Field4_is_OK;Field6;Field7;Field8'; UTL_FILE.PUT_LINE(f_out,l_out); -- Counter -- cpt := 0; -- Loop on cursor and Writing to file -- FOR cur IN c1 Loop if (substr(Cur.my_field4,1,2) = 'OK') then is_my_field4_ok := 'O'; else is_my_field4_ok := 'N'; end if; l_out := ( to_char(Cur.my_field1) ||';' ||Cur.my_field2 || ' ' || Cur.my_field3 ||';' ||to_char(Cur.my_field4) ||';' ||is_my_field4_ok ||';' ||Cur.my_field6 ||';' ||Cur.my_field7 ||';' ||Cur.my_field8 ||';' ); UTL_FILE.PUT_LINE(f_out,l_out); cpt := cpt + 1; END Loop; DBMS_OUTPUT.put_line ('Fin du traitement...'); DBMS_OUTPUT.put_line (to_char(cpt) || ' my_table3s ecrites'); -- Closure of files -- UTL_FILE.fclose_all; DBMS_OUTPUT.put_line ('END'); -- Error management -- EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line ('End of processing...'); DBMS_OUTPUT.put_line (to_char(cpt) || ' rows written'); -- Closure of files -- UTL_FILE.fclose_all; DBMS_OUTPUT.put_line ('END'); UTL_FILE.fclose_all; WHEN OTHERS THEN err_num := SQLCODE; err_msg := SUBSTR (SQLERRM, 1, 255); DBMS_OUTPUT.put_line ('ERROR: ' || err_num || ' ' || err_msg); UTL_FILE.put_line (f_log, 'ERROR: ' || err_num || ' ' || err_msg); UTL_FILE.fclose_all; END; / 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.
/*==============================================================*/ /* DBMS name: ORACLE 11g V2.3 */ /*==============================================================*/ SPOOL ex2_logging_file.log; SET ECHO ON; SET SERVEROUTPUT ON; WHENEVER SQLERROR EXIT -1 ROLLBACK; @@ ex2_processing_script.sql COMMIT; SPOOL OFF;
- Creation of second SQL script ex2_processing_script.sql containing the execution of SQL queries (INSERT/UPDATE).
spool ex2_logging_file.log SET echo ON; WHENEVER SQLERROR EXIT -1 ROLLBACK; SET DEFINE OFF ; --- Insert queries Insert into MYSCHEMA.MY_DOC_TYP (...) values (1,'001',2,'1',1,'0',null,...); Insert into MYSCHEMA.MY_DOC_TYP (...) values (1,'002',2,'1',1,'0',null,...); Insert into MYSCHEMA.MY_DOC_TYP (...) values (1,'003',2,'1',1,'0',null,...); Insert into MYSCHEMA.MY_DOC_TYP (...) values (1,'004',2,'1',0,'0',null,...); Insert into MYSCHEMA.MY_DOC_TYP (...) values (1,'005',2,'1',0,'0',null,...); --- Update query UPDATE MYSCHEMA.MY_DOC_TYP SET MY_FLG=0 WHERE doc_id='123456789'; COMMIT ; 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.
@echo off cd pause 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:
SQL> SET echo ON; SQL> WHENEVER SQLERROR EXIT -1 ROLLBACK; SQL> SET DEFINE OFF ; SQL> SQL> --- Insert queries SQL> Insert into MYSCHEMA.MY_DOC_TYP (...) values (1,'001',2,'1',1,'0',null,...); 1 row inserted. SQL> Insert into MYSCHEMA.MY_DOC_TYP (...) values (1,'002',2,'1',1,'0',null,...); 1 row inserted. SQL> Insert into MYSCHEMA.MY_DOC_TYP (...) values (1,'003',2,'1',1,'0',null,...); 1 row inserted. SQL> Insert into MYSCHEMA.MY_DOC_TYP (...) values (1,'004',2,'1',0,'0',null,...); 1 row inserted. SQL> Insert into MYSCHEMA.MY_DOC_TYP (...) values (1,'005',2,'1',0,'0',null,...); 1 row inserted. SQL> SQL> SQL> --- Update query SQL> UPDATE MYSCHEMA.MY_DOC_TYP SET MY_FLG=0 WHERE doc_id='123456789'; 1 row updated. SQL> SQL> SQL> COMMIT ; Validation done. SQL> EXIT 0;
That’s all!!!
Huseyin OZVEREN