Re: Oracle Trigger Late binding

On Apr 17, 8:32=A0am, Vabs <rajat...@[EMAIL PROTECTED]
> 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 :=3D 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.