Prev: HowTo find out used (useful) and unused (usesless) indexes?
Next: Q: something like rownum
From: Vabs on 17 Apr 2008 08:32
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...
From: fitzjarrell on 17 Apr 2008 08:48
On Apr 17, 7:32 am, Vabs <rajat...(a)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...

Why do you 'need' this? It seems like a bad idea to me; the :new
and :old 'blocks' aren't available outside of a trigger which is
written against a specific table (and you should know the columns for
that table if you're writing a trigger against it).

I really see no purpose in what you're asking.


David Fitzjarrell
From: Ken Denny on 17 Apr 2008 14:01
On Apr 17, 8:32 am, Vabs <rajat...(a)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.
From: Frank van Bortel on 17 Apr 2008 14:33
fitzjarrell(a)cox.net wrote:
> On Apr 17, 7:32 am, Vabs <rajat...(a)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...
>
> Why do you 'need' this? It seems like a bad idea to me; the :new
> and :old 'blocks' aren't available outside of a trigger which is
> written against a specific table (and you should know the columns for
> that table if you're writing a trigger against it).
>
> I really see no purpose in what you're asking.
>
>
> David Fitzjarrell
The Quest for the Universal Application?
 | 
Pages: 1
Prev: HowTo find out used (useful) and unused (usesless) indexes?
Next: Q: something like rownum