The shell script is shown below:-

#----------------------------------------------------------------------------------------
#!/bin/bash
#!/usr/bin/bsh

#TEST_HOME : Path of application home
#SDF_CONFIG.DAT : Configuration file defining variables for schema,port,host,MySQL Home,Application home,file dump path etc.
#MYSQL_HOME : MySQL Home path defined in SDF_CONFIG.DAT file
#ROOT_PSWD : Root Password as defined in SDF_CONFIG.DAT file
#MYSQL_HOST : MySQL Host IP as defined in SDF_CONFIG.DAT file
#MYSQL_PORT : Port as defined in SDF_CONFIG.DAT file
#DUMP_HOST_PATH: Path of the generated tranasaction dump file, defined in SDF_CONFIG.DAT file

TEST_HOME=/root/Test

if test -f $TEST_HOME/Program/SysConfigs/SDF_CONFIG.DAT; then
source $TEST_HOME/Program/SysConfigs/SDF_CONFIG.DAT
else
echo "Error: SDF_CONFIG.DAT not found"
exit
fi;

DG=$(date +%d-%m-%Y_%T) # date of generation

#Call MySQL Procedure that will generate the transaction files
#Required to generate file with a configured size (fetched from database or hardcoded)
#also if the file size exceeds the specified size, write into a new file (again of max size and so on till data is exhausted)
$MYSQL_HOME/bin/mysql -e 'Call transaction.offline_transaction_proc()' -u root -p$ROOT_PSWD -h $MYSQL_HOST -P$MYSQL_PORT --skip-column-names


echo "File generation Completed !!"
exit 0;
#---------------------------------------------------------------------------------------


########################################################################################


The MySQL stored procedure being called is :-

----------------------------------------------------------------------------------------

DELIMITER $$

DROP PROCEDURE IF EXISTS `tranasction`.`offline_transaction_proc`$$

CREATE PROCEDURE `offline_transaction_proc`()
BEGIN
DECLARE v_reason_code int(2);
DECLARE v_offline_file_path_name varchar(750);
DECLARE v_offline_query varchar(5000);
DECLARE v_offline_where_clause varchar(1024);
DECLARE v_csv_path varchar(512);
DECLARE v_from_date varchar(30);
DECLARE v_from_date_temp varchar(30);
DECLARE v_to_date varchar(30);
DECLARE v_current_date varchar(30);
label: BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION SET v_reason_code = -99; 

--datetime from which to generate the transaction dump
SELECT param_value INTO v_from_date
FROM configuration.configuration_detail
WHERE param_id=4;

--update param value to the datetime upto which the transactions are picked up for processing
UPDATE configuration.configuration_detail
SET param_value= now()
WHERE param_id=5;

--fetch and store in variable the datetime upto which to be fetched
SELECT param_value INTO v_to_date
FROM rt_configuration.configuration_detail
WHERE param_id=5;

--fetch the CSV path wherin to store the file
SELECT param_value INTO v_csv_path
FROM rt_configuration.configuration_detail
WHERE param_id=7;

SET v_from_date_temp=v_from_date;

--do following until the from datetime range is less than current time fetched in variable
--ie;increment the from time by one hour and see if it is less than to time, if not continue 
WHILE DATE_ADD(v_from_date_temp, INTERVAL 1 hour) < v_to_date 
DO

--read tranasctions from main table for writing to file
SET v_reason_code = 0;
--Prepared statement using SELECT INTO OUTFILE <filename>
--filename is dynamic based on time of generation
SET v_offline_query = "SELECT * FROM offline_transaction WHERE dump_time BETWEEN ";
SET v_offline_file_path_name = concat(v_csv_path, "/OFFLINE/OFFLINE_", DATE_FORMAT(DATE_ADD(v_from_date_temp, 
INTERVAL 1 hour),'%Y%m%d.%H.%i.%s'), ".TXT");
SET v_offline_file_path_name = concat("'", v_offline_file_path_name, "'");
SET @v_offline_where_clause = concat( "'", v_from_date_temp , "' AND '", 

DATE_ADD(v_from_date_temp, INTERVAL 1 hour), "' INTO OUTFILE ");
SET @v_offline_final_query = concat(v_offline_query, @v_offline_where_clause, 

v_offline_file_path_name, " FIELDS TERMINATED BY ',' ");
PREPARE offline_stmt FROM @v_offline_final_query;
EXECUTE offline_stmt;
DEALLOCATE PREPARE offline_stmt;
--increment and update the configuration table to fromtime plus one hour
UPDATE configuration.configuration_detail
SET param_value=DATE_ADD(v_from_date_temp, INTERVAL 1 hour)
WHERE param_id=4;

SET v_from_date_temp = DATE_ADD(v_from_date_temp, INTERVAL 1 hour);

-- call another procedure to drop and recreate the partition corresponding to the dump time
-- from the offline_transaction table inorder to purge the data.
Call partition_proc('offline');

END WHILE; --continue loop till from time is less than to time.
--update the dumptime completion time
UPDATE configuration.configuration_detail
SET param_value=now()
WHERE param_id=6;
END;
END$$

DELIMITER ;

----------------------------------------------------------------------------------------