sql*plus limits user variable length to 240

[nek...@gmail.com]

Hi,

I read that SQL*Plus has a limitation on the user's variable/parameter
for the Stored Procedure to 240 characters. Is there anyway to increase
this max length?

I pass in a variable of type varchar2 from a Korn script through
sql*plus. This variable is actually 1129 long. SQL*Plus throws an error
that this is too long. Please advise if the SQL*Plus session can be set
to have longer variable length.

Thank you.

[DA Morgan]

nek...@gmail.com wrote:
> Hi,
>
> I read that SQL*Plus has a limitation on the user's variable/parameter
> for the Stored Procedure to 240 characters.

Where? For which version? Did you test it?
--
Daniel A. Morgan
http://www.psoug.org
damo...@x.washington.edu
(replace x with u to respond)

[IANAL_VISTA]

nek...@gmail.com wrote in
news:1120473748.5...@o13g2000cwo.googlegroups.com:

> Hi,
>
> I read that SQL*Plus has a limitation on the user's variable/parameter
> for the Stored Procedure to 240 characters.

Read where? Please cite URL.

> Is there anyway to increase this max length?

Yes

>
> I pass in a variable of type varchar2 from a Korn script through

What OS name & version?
What version of Oracle to 4 decimal places.

> sql*plus. This variable is actually 1129 long. SQL*Plus throws an error
> that this is too long.

Error? What error. I don't see any error.
It would be most helpful if you actually showed EXACTLY what yuo are doing
and EXACTLY how Oracle is reposnding.

> Please advise if the SQL*Plus session can be set
> to have longer variable length.
>
> Thank you.
>

SET LONG 32000
might help, then again it might not.

[mdem...@gmail.com]

nek...@gmail.com schrieb:

There are (at least) 2 Notes on Metalink regarding limitations of
quoted strings (aka literals) - 1004514.6 and 1068616.6 .
Workarounds are provided as well.

Best regards

Maxim

[Paul]

nek...@gmail.com wrote:

A) As Daniel suggested, RTM.

and/or

B) Change the Korn script so that it doesn't return a ridiculously
long variable name.

Paul...

--

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2,

Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.1.0;

When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.

Furthermore, as a courtesy to those who spend
time analysing and attempting to help, please
do not top post.

[nek...@gmail.com]

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 @$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.

[Rauf Sarwar]

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 @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;
/