Re: sql*plus limits user variable length to 240

nekolim_at_gmail.com wrote:
> Hi,
>
> Thank you very much for all the advises and help. I apologise for my
> tardiness in the posting as this is my first time sending a posting to
> a forum. It is also my first time working on ksh and Oracle so I'm not
> very clear on the terminalogy and filtering through the resources
> efficiently.
>
> The various versions are
> OS: SunOS 5.9 Generic 112233-11 Dec 2003
> DB: Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit
> SQL*Plus: Release 9.2.0.5.0
> PL/SQL: Release 9.2.0.5.0
>
> The error I got is
> "string beginning "'M123456 Y..." is too long. maximum size is 239
> characters."
> ("'M123456 Y..." being the beginning of my very long parameter input.)
>
> I read from http://www.etse.urv.es/DEI/informacio/Base_dades/a53717.pdf
> (under SQL*Plus Limits) that user variable value length is 240 char.
>
> My korn script executes a sqlplus command with a sql script. Info
> below:
> <part of ksh>
> sqlplus -s $LOGIN _at_$SCRIPTDIR/sp_batch.sql $line $rownumber
>
> <part of sp_batch.sql>
> exec Sp_Batch ('&1', &2);
>
> And finally, my stored procedure looks like this:
> CREATE OR REPLACE PROCEDURE Sp_Batch (
> p_dataline IN VARCHAR2,
> row_count NUMBER
> ) IS
> ....
>
> I'm not sure if I'm looking at the right direction to tackle this
> issue. I noticed other SQL*Plus limitations such as LINESIZE which are
> system dependent. I'm googling for more info.
>
> Thanks once again for all your help and time. I hope you can point me
> in the right direction.

A not so elegant workaround would be to split your string in the shell script.

239 * 5 = 1195
Split your string into 5 strings then pass them as &1 thru &5 to sqlplus. Current &2 would move right to position &6, sqlplus user/pwd _at_script.sql $V1 $V2 $V3 $V4 $V5 $V6

In your sqlplus script you can concatenate them as, declare

   str_ varchar2(1200) := '&1' || '&2' || '&3' || '&4' || '&5'; begin

   Sp_Batch (str_, &6);
end;
/ Received on Tue Jul 05 2005 - 12:19:14 CEST