On Apr 17, 8:32*am, Vabs <rajat...@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.