I do this with the shown result in SQLPlus:
JWILLS@pprd>get newpermit
1 SELECT min (newpermit)
2 FROM (SELECT permit + 1 as newpermit
3 FROM swbpkpt a
4 WHERE permit between 10000 and 99999
5 AND a.expiry_year = 2011
6 AND NOT EXISTS (SELECT ‘x’
7 FROM swbpkpt b
8 WHERE b.permit = a.permit + 1
9 )
10* )
JWILLS@pprd>/
MIN(NEWPERMIT)
————–
10083
JWILLS@pprd>
When I put the statement into a PL/SQL program running on the same database, I use a program variable instead of 2011. By trace with htp.p I know that the variable has value 2011. The Select statement however yields null. Why does essentially the same SQL statement work differently in SQLPlus and PL/SQL, and what can I do to get it working in PL/SQL as it does in SQLPlus?
