Change in variable lengths while passing from shell to sqlplus

Question

While passing 1 character variables from sqlplus to shell, I find this annoying anomaly.

echo "Please enter the upgrade option: "
read type

if [[ "$type" != "1" ]] && [[ "$type" != "2" ]]
then
            echo "You have entered an invalid response. Exiting."
    exit 0;
fi

sql_result=$($ORACLE_HOME/bin/sqlplus -s /nolog <<-EOF
connect $eval_user/$eval_pass@$db_name

SPOOL results.txt;
WHENEVER OSERROR EXIT 9;

DEFINE vType = '$type'
DEFINE type_flag = '$t_flag'
DEFINE vOrigowner = '$origOwner'
@@EV_DBUPGRADE.sql '&vType' '&vOrigowner' '&vAppConvFromDt' '&vAppConvThruDt' '&vAppConvStatusFlg' '&type_flag'

Under EV_DBUPGRADE.sql, I find this error while using the variable vType:

vOption        varchar2(1)  := UPPER('&1');

I get this annoying error that complains of buffer length

declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 7

the error doesn't happen if the varchar2(10) is used. But, I don't want to change it in a million different places. Is there anyway to restrict it? And can anyone please tell me how to print debug messages in sqlplus outside of the BEGIN?

FYI -- using a variable $foo in shell is not guaranteed to pass it in the form it was given -- it can be split into multiple arguments, expanded as a pattern, removed entirely (if all characters are in $IFS, etc). Always use quote your variables in shell, as in "$foo". — Charles Duffy
– Charles Duffy, Commented Feb 16, 2013 at 0:30

rgettman · Accepted Answer · 2013-02-15 23:58:34Z

2

I'm not sure exactly what is going on here, but the shell is doing something to your shell variable.

I was able to duplicate your error in ksh ("testme.ksh")

#!/bin/ksh

type="1"

sqlresult=$($ORACLE_HOME/bin/sqlplus -s /nolog << EOF
connect user/pswd@database
define vType = '$type'
@test.sql &vType
exit
/
EOF)

echo $sqlresult

The SQL script ("test.sql"):

declare
   vOption varchar2(1) := UPPER('&1');
begin
   dbms_output.put_line('Test: ' || vOption);
end;
/

The output:

Connected. old 2: vOption varchar2(1) := UPPER('&1'); new 2: vOption varchar2(1)
:= UPPER('$type'); declare test.sql testme.ksh ERROR at line 1: ORA-06502: PL/SQ
L: numeric or value error: character string buffer too small ORA-06512: at line
2

I'm not sure why this works, but I was able to work around it by passing the shell variable directly to the SQL script execution and bypassing the "define" statement:

#!/bin/ksh

type="1"

sqlresult=$($ORACLE_HOME/bin/sqlplus -s /nolog << EOF
connect user/pswd@database
@test.sql $type
exit
/
EOF)

echo $sqlresult

The output:

Connected. old 2: vOption varchar2(1) := UPPER('&1'); new 2: vOption varchar2(1)
:= UPPER('1'); PL/SQL procedure successfully completed.

answered Feb 15, 2013 at 23:58

rgettman

179k30 gold badges282 silver badges365 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Alex Poole Over a year ago

Interesting... I tried it with various shells, including ksh and ksh93, under Linux, and didn't get that effect. What OS was this on? Update: OK, I do get that on Solaris 10. Good spot.

Alex Poole Over a year ago

It also works if you use backticks instead of $() to enclose the sqlplus command. Now I think I've seen that before somewhere.

rgettman Over a year ago

You're right, just replacing the "$( )" command substitution with backticks "` `" seems to work also.

Alex Poole Over a year ago

My Solaris 10 install seems to still have ksh88, so it seems to be OK in ksh93. You can also remove the single quotes in the define as long as you will never have multiple tokens, or replace them with double-quotes if you might. Or - without wishing to upset anyone - switch to bash.

roymustang86 Over a year ago

wow, thanks for taking time to run these tests on different shells. ANd yes, I have a Solaris 10 machine too.

Alex Poole · Accepted Answer · 2013-02-16 00:13:21Z

Well, seems rgettman has the actual answer, but I'll leave this here for the bit about prompt...

The quick version for adding debugs is to use prompt:

prompt Arg 1 is &1

or you could select the value from dual, which is a bit messier, or have a quick anonymous block around a dbms_output - but for this kind of quick check a prompt ought to be fine. If you're only looking for substitution values you can set verify on, which is the default so your script is probably turning it off at the moment.

The only way I can seem to get that effect is if I have a set define off (or scan off in old syntax) in the called script, so it tries to put the literal string &1 into the variable (which obviously won't fit) instead of the value it represents. Presumably, from the second part of the question, you can't see what's being put in there?

Anyway... if that isn't the problem - and it seems unlikely if it works called in isolation - then I'd wondered if it was a character set issue, but that doesn't sound right either. And I also wondered why you're forcing it to be a numeric value and then passing it as a string, which isn't really relevant to the problem. Perhaps adding more of the SQl script might shed some light - at least from the top to where the error is occurring.

Collectives™ on Stack Overflow

Change in variable lengths while passing from shell to sqlplus

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related