OS: SunOS 5.8 Generic_117350-07 sun4u sparc SUNW,Ultra-Enterprise
Oracle 9i

This is probably obvious but I just have an uneasy feeling about it.

I have a stored procedure where I fetch my cursor into 3 pl/sql tables
and then I want to update the loantd table for each lnkey in the
t_lnkey mem table, only where the corresponding e_newtd element
(2nd byte) in the t_newtd mem table has a valid, non-null value.

Is this a valid sql reference on my update statement?:

and substr ( e_newtd ( j )
, 2
, 1
) is not null

It compiles fine and logically it makes sense to me but the fact that
it is a pl/sql table reference bothers me. Can someone confirm for me
that this is valid?

TIA

Luke

Here is the code snippet:

type t_rowid is table of rowid index by binary_integer ;
type t_lnkey is table of mortgage.lnkey%type index by binary_integer ;
type t_newtd is table of varchar2 ( 2 ) index by binary_integer ;

e_rowid t_rowid ;
e_lnkey t_lnkey ;
e_newtd t_newtd ;

begin
open c_rowid_lnkey_newtd ;
loop
fetch c_rowid_lnkey_newtd BULK COLLECT
into e_rowid
, e_lnkey
, e_newtd
limit p_array_occurrences
;

forall j in e_lnkey.first..e_lnkey.last
update loantd td
set prepaypenalty = decode ( to_number ( substr ( e_newtd ( j )
, 2
, 1
)
)
, 1
, 'N'
, 'Y'
)
where td.lnkey = e_lnkey ( j )
and substr ( e_newtd ( j )
, 2
, 1
) is not null
;

commit ;

exit when c_rowid_lnkey_newtd%notfound ;

end loop ;

why don't you try it yourself?

Oracle Database 10g Release 10.1.0.2.0 - Production

SQL> select substr('12345',6,2) , nvl(substr('12345',6,2),'NULL') from
dual;

S NVL(
- ----
NULL

SQL>


Ed.