##########################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"
Top comments (0)