Is this sql reference valid?

From: <luke_airig_at_hotmail.com>
Date: 18 May 2005 17:56:49 -0700
Message-ID: <1116464209.627043.248620@g14g2000cwa.googlegroups.com>

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 ; Received on Wed May 18 2005 - 19:56:49 CDT