Re: Subject: SQL*Plus: Release 3.2.2.0.0

In article <4eau4a$kqu_at_news.ccit.arizona.edu> dylan_at_pictor.ccit.arizona.edu (Dylan Cooper) writes:

> Can anyone explain the following behavior? (I think it's a SQL*Plus bug,
> but maybe it's a feature.)
 

> getting:
> ORA-01008: not all variables bound
> when I should not. This is causing significant problems.
>
> Here is an example. I run this SQL script:
>
> variable period_text varchar2(40)
> variable bill_contact varchar2(100)
> execute :bill_contact := 'AAAA';
> execute :period_text := 'BBBB';
>
> select decode ( :bill_contact, 'a', 'A', :bill_contact), :period_text
> from dual
> /
>
> And I get:
>
> PL/SQL procedure successfully completed.
> PL/SQL procedure successfully completed.
> ERROR:
> ORA-01008: not all variables bound
> no rows selected

It is carefully documented that bind variables can only be used within PL/SQL blocks. You are attempting to use it within a pure SQL statement. (You can use them with SQL statements enclosed in a PL/SQL block).

This restriction has been bugged with the RDBMS.

Chris

-- 
Christopher Jones, cjones_at_au.oracle.com