Re: Oracle Trigger Late binding

From: Ken Denny <ken_at_kendenny.com>
Date: Thu, 17 Apr 2008 11:01:43 -0700 (PDT)
Message-ID: <1553f3ac-12ed-4076-bdae-c16f1aa765cd@f63g2000hsf.googlegroups.com>

On Apr 17, 8:32 am, Vabs <rajat..._at_gmail.com> wrote:
> i want to use if(:new.column_name is not null) which works perfect...
> however my problem is that i want to set this column_name as a run
> time variable (as the table columns are not known to me)
>
> I tried using a variable but oracle throws a PLS-00049 bad bind
> variable error.
>
> Please help...

I agree with David about not understanding the usefulness of this but I'll attempt anyway. Since this will be in a trigger then the name of the column that needs to be checked will be in one of the :new column values. You will have to hard code all possible column names no way around that.

DECLARE
  v_column_val VARCHAR2(32767);
BEGIN
  v_column_value := CASE :new.col_to_check

                      WHEN 'COL1' THEN :new.col1
                      WHEN 'COL2' THEN :new.col2
                      WHEN 'COL3' THEN :new.col3
                      WHEN 'COL4' THEN :new.col4
                      -- repeat for every possible column name
                    END;

  IF v_column_value IS NOT NULL
  THEN OK. That's the simplest most straightforward way to do it but in case you have 3000 columns in this table and don't want to have to hard code every one of them there is another way.

DECLARE
  v_column_value VARCHAR2(32767);
BEGIN
  EXECUTE IMMEDIATE 'select to_char(:new.'||:new.col_to_check||') from dual'

    INTO v_column_value;
  IF v_column_value IS NOT NULL
  THEN I don't know if this second one will work or not. I've never used dynamic SQL in a trigger before so I don't know if using ':new' in the dynamic SQL will work. Received on Thu Apr 17 2008 - 13:01:43 CDT