handle text

!/usr/bin/env bash

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

Configuration

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

DATAFILE="ZMemo.dat" # Original Sybase dump
OUTPUTFILE="ZMemo_clean.dat" # SQL*Loader input file
DELIM="¬" # Sybase field delimiter
TARGET_COL=20 # OVERRIDERULESET column
PLACEHOLDER="<>" # Placeholder for newlines

Remove old output if exists

rm -f "$OUTPUTFILE"

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

Processing

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

awk -F"$DELIM" -v OFS="$DELIM" -v col="$TARGET_COL" -v ph="$PLACEHOLDER" '
{
# Remove CR
gsub(/\r/, "", $col);
# Replace all LF with placeholder
gsub(/\n/, ph, $col);
print
}' "$DATAFILE" > "$OUTPUTFILE"

echo "Processing complete."
echo "Oracle loader file ready: $OUTPUTFILE"

echo "Newlines in column $TARGET_COL replaced with placeholder '$PLACEHOLDER'"

CREATE TABLE ZMemo (
id NUMBER,
other_column1 VARCHAR2(100),
other_column2 VARCHAR2(100),
...
overrideruleset CLOB,
other_columnN VARCHAR2(100),
PRIMARY KEY(id)
);

---

LOAD DATA
INFILE 'ZMemo_clean.dat'
BADFILE 'ZMemo.bad'
DISCARDFILE 'ZMemo.dsc'
APPEND
INTO TABLE ZMemo
FIELDS TERMINATED BY '¬' TRAILING NULLCOLS
(
id INTEGER EXTERNAL,
other_column1 CHAR,
other_column2 CHAR,
...
overrideruleset CHAR(32767),
other_columnN CHAR

)

UPDATE ZMemo
SET overrideruleset = REPLACE(overrideruleset, '<>', CHR(10));