Hello Everyone,
I am trying to write a shell script that will read from a file and will call a pl/sql procedure that takes clob as input.
Now as varchar2 has a limit much less than clob so i can't just pass the input as in one variable.(size may be >32K).
This is what i tried. Not posting the whole code. I am using korn shell.--
sqltext="declare i_clob clob; begin "
sqltmp=""
while read -r line
do
file="$file\n$line"
done < $filename
length=${#file}
while [ $count -lt $length ]
do
tmp=`expr substr "$file" 1 32000`
sqltml="$sqltmp i_clob:=i_clob||to_clob('$tmp');"
done
sqltext="$sqltext $sqltmp procedure1(i_clob); end;"
sqlplus -s user/pass@sid > output.log <<EOF
$sqltext
/
EOF
I am getting an error saying::
sp2 0027 input is too long (greather than 2499 ..
Any help will be greatly appreciated.
Thanks in advance
MB