File transfer from source to destinatio

Abdullah — Wed, 25 Jun 2025 05:12:57 +0000

######################## SCRIPT START

[oracle@hbrtl filterFiles]$ cat /zfssa/dumps/LocalToDPCFTP/File_tranfer_dpcftp.sh

cd /zfssa/dumps/Losfile ///Path where file will generate//

for FILE in $(ls -lt --time=ctime | head -n 2)

do
cp ${FILE} /zfssa/dumps/filterFiles //Move current file to another folder//
done

////Actual code transfer the file//
curl --insecure --user USER:pass@123 -T //zfssa/dumps/filterFiles/*.csv sftp://dpcf.hostname.com:/FILES/
//////////END////

cd /zfssa/dumps/filterFiles

rm -rf *.csv

##################SCRIPT END

##############SCRIPT START

[oracle@hbnedm01 scripts]$ cat FileTrans.sh
SOURCE_DIR="/zfssa/Staging/LosFile"
DEST_USER="USERNAME"
DEST_HOST="hostname.com"

DEST_DIR="/FILES"

DEST_DIR="/BTG/LOS_PERF/FILES"

DEST_DIR="/FILES"

DEST_PASSWORD="PASSWORD"

Check if SSH key-based authentication is set up

ssh -q -o BatchMode=yes $DEST_USER@$DEST_HOST exit

if [ $? -ne 0 ]; then

echo "Error: SSH key-based authentication not set up for $DEST_USER@$DEST_HOST"

echo "Run: ssh-copy-id -i ~/.ssh/id_rsa.pub $DEST_USER@$DEST_HOST"

exit 1

fi

LATEST_FILE=$(find "$SOURCE_DIR" -maxdepth 1 -type f -printf "%T@ %p\n" | sort -nr | head -n 1 | cut -d' ' -f2-)

LATEST_FILE=$(ls -t "$SOURCE_DIR" | head -n 1)

if [ -z "$LATEST_FILE" ]; then
echo "Error: No files found in $SOURCE_DIR"
exit 1
fi

use expect to handle password

/usr/bin/expect << EOF
spawn scp "$LATEST_FILE" "$DEST_USER@$DEST_HOST:$DEST_DIR"

spawn sshpass -p "password@123" scp "$LATEST_FILE" "$DEST_USER@$DEST_HOST:$DEST_DIR"

expect "password:"
send "$DEST_PASSWORD\r"
expect off
EOF
if [ $? -eq 0 ]; then
echo "Successfully copied $LATEST_FILE to $DEST_HOST:$DEST_DIR"
else
echo "Error: Failed to copy $LATEST_FILE"
exit 1
fi

################## SCRIPT END

Shell Script to generate csv file.

Abdullah — Mon, 02 Jun 2025 13:18:10 +0000

##########################Single TABLE START

!/bin/bash

Set Oracle environment variables (modify as per your Oracle installation)

export ORACLE_BASE=/u02/app/oracle/
export ORACLE_HOME=/u02/app/oracle/product/19.3.0/dbhome
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export ORACLE_SID=DB_NAME

Database connection details

DB_USER="your_username"

DB_PASSWORD="your_password"

DB_SID="your_sid"

DB_SID="DB_NAME"

Get current date and time for filename (format: YYYYMMDD)

CURRENT_DATE=$(date +%Y%m%d%H%M)
OUTPUT_FILE="output_${CURRENT_DATE}.csv"
TEMP_HEADER_FILE="temp_header_${CURRENT_DATE}.csv"
TEMP_DATA_FILE="temp_data_${CURRENT_DATE}.csv"
TEMP_FILTERED_DATA_FILE="temp_filtered_data_${CURRENT_DATE}.csv"

SQL query to fetch column names as headers

HEADER_QUERY="
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 32767
SET TRIMSPOOL ON
SPOOL $TEMP_HEADER_FILE
SELECT LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY column_id) FROM DBA_tab_columns WHERE table_name in ('Z','Y','x') and owner='temp';
SPOOL OFF
EXIT;
"

SQL query to fetch data with filtering

DATA_QUERY="
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 32767
SET TRIMSPOOL ON
SET COLSEP ','
SPOOL $TEMP_DATA_FILE
SELECT * from table_name
SPOOL OFF
EXIT;
"

Execute query to fetch headers

echo "$HEADER_QUERY" | sqlplus -s $DB_USER/$DB_PASSWORD@$DB_SID

echo "$HEADER_QUERY" | sqlplus "/ as sysdba"

Execute query to fetch data

echo "$DATA_QUERY" | sqlplus -s $DB_USER/$DB_PASSWORD@$DB_SID

echo "$DATA_QUERY" | sqlplus "/ as sysdba"

Merge header and data files into final CSV

if [ -f "$TEMP_HEADER_FILE" ] && [ -f "$TEMP_DATA_FILE" ]; then
cat "$TEMP_HEADER_FILE" "$TEMP_DATA_FILE" > "$TEMP_FILTERED_DATA_FILE"
echo "CSV file generated successfully: $TEMP_FILTERED_DATA_FILE"

# Clean up temporary files
rm "$TEMP_HEADER_FILE" "$TEMP_DATA_FILE"

else
echo "Error: Failed to generate header or data file"
exit 1
fi

Merge header and filtered data files into final CSV

if [ -f "$TEMP_HEADER_FILE" ] && [ -f "$TEMP_FILTERED_DATA_FILE" ]; then

cat "$TEMP_HEADER_FILE" "$TEMP_FILTERED_DATA_FILE" > "$OUTPUT_FILE"

echo "CSV file generated successfully: $OUTPUT_FILE"

# Clean up temporary files

rm "$TEMP_HEADER_FILE" "$TEMP_DATA_FILE" "$TEMP_FILTERED_DATA_FILE"

else

echo "Error: Failed to generate header or filtered data file"

exit 1

fi

Post-process to remove specific lines (e.g., lines 2 and 3)

if [ -f "$TEMP_FILTERED_DATA_FILE" ]; then
sed '/^SQL/d' "$TEMP_FILTERED_DATA_FILE" > "$OUTPUT_FILE"
echo "CSV file generated successfully: $OUTPUT_FILE"

# Clean up temporary files
rm "$TEMP_FILTERED_DATA_FILE"

else
echo "Error: Data file not generated"
exit 1
fi

##################Single TABLE END

##################Multiple TABLE Script Start

!/bin/bash
export ORACLE_BASE=/u02/app/oracle/
export ORACLE_HOME=/u02/app/oracle/product/19.3.0/dbhome
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export ORACLE_SID=DB_name

Get current date and time for filename

CURRENT_DATE=$(date +%Y%m%d%H%M)
OUTPUT_FILE="output_${CURRENT_DATE}.csv"
FILE_PATH="/zfssa/PATH"
FINAL_OUTPUT_FILE="${FILE_PATH}/XNS_DUMP_PERFIOS_SME_${CURRENT_DATE}.csv"
TEMP_DATA_FILE="temp_data_${CURRENT_DATE}.csv"

SQL query to fetch data with headers

SQL_QUERY=$(cat << EOF
SET PAGESIZE 0
SET FEEDBACK OFF
SET ECHO OFF
SET HEADING ON
SET LINESIZE 32767
SET TRIMSPOOL ON
SET MARKUP CSV ON DELIMITER ',' QUOTE ON
SPOOL $TEMP_DATA_FILE
SELECT * from tablename --Complete query in one line for multiple tables
SPOOL OFF
EXIT;
EOF
)

Execute the query and write to a temporary file

echo "$SQL_QUERY" | sqlplus "/ as sysdba"

Clean up the output: remove extra spaces, ensure proper CSV format

if [ -f "$TEMP_DATA_FILE" ]; then

WORKING

sed 's/ , */,/g' "$TEMP_DATA_FILE" | sed 's/ *"([^"])" */"\1"/g' > "$OUTPUT_FILE"
sed '/^SQL/d' "$TEMP_DATA_FILE" > "$OUTPUT_FILE"
sed '/^$/d' "$OUTPUT_FILE" > "$FINAL_OUTPUT_FILE"

sed 's/ , /,/g' "$TEMP_DATA_FILE" | sed 's/ "([^"])" */"\1"/g' > "$OUTPUT_FILE"

sed '/[Ss][Qq][Ll]/d' | sed '/^SQL/d' "$TEMP_DATA_FILE" > "$OUTPUT_FILE"

sed 's/ , /,/g' "$TEMP_DATA_FILE" |\

sed 's/ "([^"])" */"\1"/g' > " |\

sed '/^$/d' |

sed '/[Ss][Qq][Ll]/d' |

sed '/^SQL/d' "$TEMP_DATA_FILE" > "$OUTPUT_FILE"

echo "CSV file generated successfully: $OUTPUT_FILE"
else
echo "Error: Data file not generated"
exit 1
fi

Clean up temporary files

rm "$TEMP_DATA_FILE"

DEV Community: Abdullah

File transfer from source to destinatio

######################## SCRIPT START

##################SCRIPT END

##############SCRIPT START

DEST_DIR="/FILES"

DEST_DIR="/FILES"

Check if SSH key-based authentication is set up

ssh -q -o BatchMode=yes $DEST_USER@$DEST_HOST exit

if [ $? -ne 0 ]; then

echo "Error: SSH key-based authentication not set up for $DEST_USER@$DEST_HOST"

echo "Run: ssh-copy-id -i ~/.ssh/id_rsa.pub $DEST_USER@$DEST_HOST"

exit 1

fi

LATEST_FILE=$(ls -t "$SOURCE_DIR" | head -n 1)

use expect to handle password

spawn sshpass -p "password@123" scp "$LATEST_FILE" "$DEST_USER@$DEST_HOST:$DEST_DIR"

################## SCRIPT END

Shell Script to generate csv file.

##########################Single TABLE START

!/bin/bash

Set Oracle environment variables (modify as per your Oracle installation)

Database connection details

DB_USER="your_username"

DB_PASSWORD="your_password"

DB_SID="your_sid"

Get current date and time for filename (format: YYYYMMDD)

SQL query to fetch column names as headers

SQL query to fetch data with filtering

Execute query to fetch headers

echo "$HEADER_QUERY" | sqlplus -s $DB_USER/$DB_PASSWORD@$DB_SID

Execute query to fetch data

echo "$DATA_QUERY" | sqlplus -s $DB_USER/$DB_PASSWORD@$DB_SID

Merge header and data files into final CSV

Merge header and filtered data files into final CSV

if [ -f "$TEMP_HEADER_FILE" ] && [ -f "$TEMP_FILTERED_DATA_FILE" ]; then

cat "$TEMP_HEADER_FILE" "$TEMP_FILTERED_DATA_FILE" > "$OUTPUT_FILE"

echo "CSV file generated successfully: $OUTPUT_FILE"

# Clean up temporary files

rm "$TEMP_HEADER_FILE" "$TEMP_DATA_FILE" "$TEMP_FILTERED_DATA_FILE"

else

echo "Error: Failed to generate header or filtered data file"

exit 1

fi

Post-process to remove specific lines (e.g., lines 2 and 3)

##################Single TABLE END

##################Multiple TABLE Script Start

Get current date and time for filename

SQL query to fetch data with headers

Execute the query and write to a temporary file

Clean up the output: remove extra spaces, ensure proper CSV format

WORKING

sed 's/ , */,/g' "$TEMP_DATA_FILE" | sed 's/ *"([^"])" */"\1"/g' > "$OUTPUT_FILE"

sed '/[Ss][Qq][Ll]/d' | sed '/^SQL/d' "$TEMP_DATA_FILE" > "$OUTPUT_FILE"

sed 's/ *, */,/g' "$TEMP_DATA_FILE" |\

sed 's/ "([^"])" */"\1"/g' > " |\

sed '/^$/d' |

sed '/[Ss][Qq][Ll]/d' |

sed '/^SQL/d' "$TEMP_DATA_FILE" > "$OUTPUT_FILE"

Clean up temporary files

###############Multiple tables script End

sed 's/ , /,/g' "$TEMP_DATA_FILE" | sed 's/ "([^"])" */"\1"/g' > "$OUTPUT_FILE"

sed 's/ , /,/g' "$TEMP_DATA_FILE" |\