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