Substitution Variable limitation of 3000 characters?

From: Alan Long <tics28_at_>
Date: Mon, 09 Nov 1998 16:31:59 +0000
Message-ID: <tics28-0911981632000001@tics28.sps.mot.com>

I've got a big SQLplus statement along the lines of (much simplified):

select decode(col1, '&var1',1, 0),

       decode(col2, '&var2',1, 0),
       etc etc etc

from table
where col3 > 'b'
&var3_where
&var4_where

;

&var3_where is computed as something like:

    column var3_where noprint new_value var3_where     select 'and (col5 = &var5 or col6 = &var6)' var3_where from dual;

and so on. I guess you get the idea.

When I run this under Oracle 7.3.4.2.0, I get the response:

     line overflow during variable substitution (>3000 characters at line 7)

I guess this is telling me that I have hit some kind of limit. At the line where the error occurs, the substitution variable is only a few characters, so I guess Oracle is telling me that the whole select statement, including the substituted variables (around 30 of them, I guess), is too long.

I cannot find any reference to this in the Oracle SQLplus user guide and reference, or the Oracle server SQL reference.

To rewrite my code to work around this will be hard, if not impossible for what I am trying to do. Is there any workaround (buffer size, etc?) anyone can suggest?

--
Alan Long

email tics28@"spam-off"email.sps.mot.com (remove the "spam-off") Received on Mon Nov 09 1998 - 10:31:59 CST