Hi David,
My post was for the actual problem.
Anyways, your inputs are very right and very accurate, thanks for
them.
just one thing the person who rose the question is a "she" not "he".
-Sonty
On Jul 27, 11:23 pm, ddf <orat...@msn.com> wrote:
> Comments embedded.
>
> On Jul 27, 9:31 am, sonty <saurabh.zen...@gmail.com> wrote:
>
> > 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.
>
> I don't know what you mean by that, but a basic reading indicates an
> answer of 'no'. The OP is attempting to use
>
> :OLD.<variable_name>
> and
> :NEW.<variable_name>
>
> to reference values in the :OLD and :NEW 'blocks' populated during an
> insert/update/delete where <variable_name> stores the desired column
> name. Oracle will not process that type of construct in a trigger; it
> needs the actual column name:
>
> :OLD.mycol
> and
> :NEW.mycol
>
> (in this example). The example I provided proves this.
>
>
>
> > Column name can be updated inside alter table statements.
>
> Yes. What that has to do with the issue presented by the OP is a
> mystery to me.
>
>
>
> > So, Please let us know the exact requirement to get the solution you
> > want.
>
> It's clear, really: he wants to use some variable to identify the
> desired column name in a trigger and have the :OLD and :NEW
> constructs recognize that as valid. He has already seen such code
> fails, yet continues to try to make it work..
>
>
>
>
>
> > -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 -- Hide quoted text -
>
> > - Show quoted text -
>
> 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
-~----------~----~----~----~------~----~------~--~---
