Looping through Files

Hi all ,

I am new on this forum . I have to face a particoular implementation issue and I need some help .

Requirement :
I need to read a particoular file (an xml file) and after reading it I need to call an Oracle Stored Procedure passing the content of the file as paramenter , in order to update a particoular CLOB column on a table .

This the code I used (I'm using ksh on AIX 5.3 o.s.) .

#!/bin/ksh
# Shell Type : Korn Shell

SetXMLClob()
{
	echo "Starting SetXMLClob function....."
	LOGID=$2
	FILENAME=$3
	PROCEDURE_NAME=$4
	LOGFILE=$1/Log/$PROCEDURE_NAME"_"$LOGID.log
	export LOGFILE
	VALUE=$(cat $FILENAME | sed -e ':a' -e 'N' -e '$!ba' -e 's/\n//g')	
	LENGTH=`expr length "$VALUE"`
	VALUE=`expr substr "$VALUE" 3 "$LENGTH"`
	echo $VALUE
	CHAR="'"
	while [ $LENGTH -gt 1 ];
	do
		SUBVALUE=`expr substr "$VALUE" 1 999`
		SUBLENGTH=`expr length "$SUBVALUE"`
		SUBVALUE=$CHAR$SUBVALUE$CHAR
		echo $SUBVALUE
		echo $LENGTH
		export SUBVALUE
		$1/spool_procedure.sh "$PROCEDURE_NAME" "'"$LOGID"'"
		
		if [ $SUBLENGTH -ge $LENGTH ]; then
			LENGTH=0
		else
			INDEX=`expr $SUBLENGTH + 1`
			VALUE=`expr substr "$VALUE" "$INDEX" "$LENGTH"`
			LENGTH=`expr length "$VALUE"`
		fi
	done
}

################################################### MAIN BODY
case $1"" in	
	"/SET") 	
		echo "----------------------" >> $LOGFILE
		SetXMLClob $2 $3 $4 $5;;
esac

Here the sub procedure called

PROC_NAME=$1
ROWID=$2

USER=$USERNAME
PASS=$PSW
CONNECTSTRING=$SID_ORACLE
#cd $LOCAL_CORE_FOLDE

echo ------------------------- >> $LOGFILE
echo Start: $DATESTAMP     >> $LOGFILE
echo LogId: $ROWID     >> $LOGFILE
# Now call the file spool sqlplus script 
( echo "CONN $USER/$PASS@$CONNECTSTRING"
  echo "EXEC $PROC_NAME("$ROWID","$SUBVALUE")"
  echo "EXIT"
) | sqlplus -s /NOLOG >> $LOGFILE

SUBVALUE=""
DATESTAMP_LOG=`date`
echo End: $DATESTAMP >> $LOGFILE

But this is not with too large files (>300 K)

I think the best solution is to loop through the file and extract at each interaction the first 1000 bytes of the file but I don't know how to do this in ksh .

Can someone help me?

thanks in advance ,

Kolas

You can send anything in an update or insert through sqlplus if the quoting is good. You need to process the xml file to handle any quotes matching your column. Doubling them or escaping them should work. How does one escape special characters when writing SQL queries? | Oracle FAQ

Hi thanks a lot for your answer .

I solved the issue using sql loader instead of Sql Plus .

sqlldr $USERNAME/$PSW@$SID_ORACLE data=$CSV_FILE control=$1/NextdeCustLoad.ctl log=$1/Log/$LOGID.log bad=$1/bad/$LOGID.bad rows=10000;

It doesn't have any limitation about the query number of chars .

Thanks a lot again ,

Kolas

Good choice, sqlplus has all sorts of hassles as a batch tool. I think I would rather use xigole jisql qith jdbc jars.