isql

#automation #dataengineering #sql

!/bin/bash

Purpose: Export Sybase ZMemo table with safe CLOB text column for Oracle SQL*Loader

Compatible with Sybase ASE isql client

Leonid, 2025

--- Connection settings ---

SYBASE_SERVER_NAME="YOUR_SYBASE_SERVER"
SYBASE_DB_USER="YOUR_USER"
COMET_PSWD="YOUR_PASSWORD"
DB_NAME="YOUR_DB"
DATA_FILE_DIRECTORY="/path/to/output"
DELIMITER="¬" # field separator for SQL*Loader
TABLE_NAME="ZMemo"

--- Derived paths ---

CUR_DAT_FILE="${DATA_FILE_DIRECTORY}/${TABLE_NAME}.dat"
LOG_FILE="${DATA_FILE_DIRECTORY}/${TABLE_NAME}_export.log"

--- Create output directory if needed ---

mkdir -p "$DATA_FILE_DIRECTORY"

echo "Exporting ${TABLE_NAME} from ${DB_NAME} using isql..."

--- Run export query ---

isql -S${SYBASE_SERVER_NAME} \
-U${SYBASE_DB_USER} \
-P${COMET_PSWD} \
-w 32767 \
-b \
-s"${DELIMITER}" \
-o "${CUR_DAT_FILE}" <<EOF
SET NOCOUNT ON
SET TEXTSIZE 2147483647

SELECT
Col1,
Col2,
Col3,
-- Columns 4–19 as they are...
Col4,
Col5,
Col6,
Col7,
Col8,
Col9,
Col10,
Col11,
Col12,
Col13,
Col14,
Col15,
Col16,
Col17,
Col18,
Col19,
-- Column 20: OVERRIDERULESET (replace CR/LF with <> marker)
REPLACE(REPLACE(OVERRIDERULESET, CHAR(13), ''), CHAR(10), '<>'),
-- Columns after 20, if any...
Col21,
Col22,
Col23
FROM ${DB_NAME}..${TABLE_NAME}
GO
EOF

if [ $? -eq 0 ]; then
echo "Export completed successfully. Output: ${CUR_DAT_FILE}"
else
echo "Export failed. Check ${LOG_FILE} for details."
exit 1
fi