On Fri, July 24, 2009 18:31, Priya 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
> >
>
I know of no way to do it Priya. There might be some strange hack but I
cannot picture what it would be.
Why do you want to do it this way? what is the problem that you are trying
to solve?
Rob
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
