Hi,
Correct me if I am wrong, anybody can do so :-).
What you are saying is that "Getting the column name dynamically when
the columns are static" because all of us knows that update statements
cannot change the column name anyways.
Column name can be updated inside alter table statements.
So, Please let us know the exact requirement to get the solution you
want.
-Sonty
On Jul 25, 4:32 am, ddf <orat...@msn.com> wrote:
> On Jul 24, 5:31 pm, Priya <chavanpr...@gmail.com> wrote:
>
>
>
>
>
> > David,
> > thank you for the reply.I know that the problem can easily be solved
> > by using the actual column name mycol instead of the variable.But,
> > I want know if there is a way to get the column name dynamically from
> > a variable.
>
> > On Jul 24, 2:54 pm, ddf <orat...@msn.com> wrote:
>
> > > Comments embedded.
>
> > > On Jul 24, 1:55 pm, Priya <chavanpr...@gmail.com> wrote:
>
> > > > Hi,
>
> > > > I am a oracle PL/SQL newbie.Can someone please help me solve the below
> > > > problem?
>
> > > > Consider a table called mytable with one column called mycol .I have
> > > > written a on-update trigger.
>
> > > > CREATE OR REPLACE TRIGGER "testtrigger" BEFORE
> > > > UPDATE ON mytable FOR EACH ROW
> > > > DECLARE
> > > > Test VARCHAR(1000);
>
> > > > BEGIN
>
> > > > TEST:='mycol';
> > > > IF :OLD.TEST <> :NEW.TEST THEN
> > > > DBMS_OUTPUT.PUT_LINE ("Hi");
> > > > END IF
>
> > > > i get the error bad bind variable.This is obviously because the
> > > > compiler is thinking that test is a column and not a variable.
>
> > > Correct. That's not the only error in your code, though.
>
> > > > How
> > > > should I make the compiler aware that Test is a variable and the
> > > > compiler must resolver it to mycol?
>
> > > You cannot. This is a trigger, against a specific table, and the
> > > blocks you're referencing (:OLD and :NEW) only apply to the columns
> > > defined in that table, not to variables you may have a whim to create.
>
> > > > I know i can replace the variable
> > > > with mycol but that not what I want to do.
>
> > > You're trigger won't compile and won't run, and updates to mytable
> > > will fail:
>
> > > SQL> --
> > > SQL> -- Create test table
> > > SQL> --
> > > SQL> create table mytable(
> > > 2 mycol varchar2(20)
> > > 3 );
>
> > > Table created.
>
> > > SQL>
> > > SQL> --
> > > SQL> -- Create bad trigger
> > > SQL> --
> > > SQL> -- Won't work because TEST is not a column
> > > SQL> -- in mytable
> > > SQL> --
> > > SQL> CREATE OR REPLACE TRIGGER "testtrigger" BEFORE
> > > 2 UPDATE ON mytable FOR EACH ROW
> > > 3 DECLARE
> > > 4 Test VARCHAR(1000);
> > > 5
> > > 6
> > > 7 BEGIN
> > > 8
> > > 9
> > > 10 TEST:='mycol';
> > > 11 IF :OLD.TEST <> :NEW.TEST THEN
> > > 12 DBMS_OUTPUT.PUT_LINE ("Hi");
> > > 13 END IF;
> > > 14
> > > 15 END;
> > > 16 /
>
> > > Warning: Trigger created with compilation errors.
>
> > > SQL>
> > > SQL> show errors
> > > No errors.
> > > SQL>
> > > SQL> --
> > > SQL> -- Insert data
> > > SQL> --
> > > SQL>
> > > SQL> insert all
> > > 2 into mytable
> > > 3 values('Yarg')
> > > 4 into mytable
> > > 5 values('Yink')
> > > 6 into mytable
> > > 7 values('Yorg')
> > > 8 select * From dual;
>
> > > 3 rows created.
>
> > > SQL>
> > > SQL> commit;
>
> > > Commit complete.
>
> > > SQL>
> > > SQL> --
> > > SQL> -- Perform update which fails
> > > SQL> --
> > > SQL>
> > > SQL> set serveroutput on size 1000000
> > > SQL>
> > > SQL> update mytable set mycol='Fred';
> > > update mytable set mycol='Fred'
> > > *
> > > ERROR at line 1:
> > > ORA-04098: trigger 'BING.testtrigger' is invalid and failed re-
> > > validation
>
> > > SQL>
> > > SQL> --
> > > SQL> -- Create trigger
> > > SQL> --
> > > SQL> CREATE OR REPLACE TRIGGER "testtrigger" BEFORE
> > > 2 UPDATE ON mytable FOR EACH ROW
> > > 3 BEGIN
> > > 4
> > > 5
> > > 6 IF :OLD.mycol <> :NEW.mycol THEN
> > > 7 DBMS_OUTPUT.PUT_LINE ('Hi');
> > > 8 END IF;
> > > 9
> > > 10 END;
> > > 11 /
>
> > > Trigger created.
>
> > > SQL>
> > > SQL> show errors
> > > No errors.
> > > SQL>
> > > SQL> --
> > > SQL> -- Perform update
> > > SQL> --
> > > SQL>
> > > SQL> update mytable set mycol='Fred';
> > > Hi
> > > Hi
> > > Hi
>
> > > 3 rows updated.
>
> > > SQL>
>
> > > Sometimes what you want to do and what you must do are two different
> > > things.
>
> > > > Thanks in advance!
>
> > > > Priya
>
> > > David Fitzjarrell- Hide quoted text -
>
> > - Show quoted text -
>
> It's clear you do not understand the OLD and NEW 'blocks' used by a
> trigger. These 'blocks' reference the existing column values for the
> row being processed (:OLD) and the pending column values (:NEW) for
> that same row. As such these structures reference COLUMN names, not
> variables, and there is no possible way to do what you want. As I
> said in my prior post you're writing a trigger against a specific
> table which has a specific definition and the trigger knows the column
> names set by that definition. Please read here:
>
> http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_...
>
> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
