Shell Programming and Scripting

Hi,

I ahve a unix code as below.

Code:

sqlTxt=$*
sqlReturn=`echo "set feedback off;
set heading off;
set term off;
set verify off;
set serveroutput on size unlimited
set lin 1000;
VARIABLE GV_return_val NUMBER;
VARIABLE GV_script_error varchar2(4000);
EXEC :GV_return_code := 0;
EXEC :GV_script_error := NULL;
WHENEVER SQLERROR EXIT ${FATAL}
$sqlTxt
/
print :GV_script_error;
exit :GV_return_code;
" | sqlplus -s ${connectStr}`

Now from another program GV_script_error variable is getting populated and they value of that charecter string is nearly 12000 charecter.

In sqlplus I can't print more than 4000 so when that huge amount of value is getting passed in this GV_script_error variable it's not priniting anything and the below error is coming as chareter size is greater than 4000.

"

Code:

PL/SQL: numeric or value error: character string buffer too small
"

So in this scenario I need to append/merge the value in two variables or I need to find out some other way to print all the charecters that is getting passed in GV_script_error variable whether it may be > 4000 .

Can you please give me a idea for this .

Thanks in advance.

So

It is obvious that with varchar2 datatype you can define maximum 4000 bytes. So did you consider using any Large Object (LOB) Datatypes?

Thanks for the suggestions.
I didn't consider LOB datatypes.
How I will do this?

Define your bind variable: GV_script_error as CLOB which can store up to 4GB of character data:

Code:

VARIABLE GV_script_error CLOB;

Hi Yoda,

I'm getting the error like this.

ORA-01461: can bind a LONG value


GV_script_error is assigned as a out parameter while calling a packaged function where out parameter in package is alos clob.

So I'm not getting why the error is coming.

The ORA-01461 error states that you are trying to bind a LONG datatype to a different datatype.

LONG and LONG RAW datatypes are deprecated, change it to CLOB datatype.