Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2008
    Posts
    1

    Question Unanswered: PL/SQL - Using a Param both as Value and Key

    Hello.

    I am writing a trigger in PL/SQL that needs to use a parameter both as a value and as key.

    Will try to elustrate what I mean:

    Referencing NEW as new_row OLD as old_row
    Declare
    mParam varchar2(30);
    mRow table1%rowtype;

    mRow.FIELD_NAME := mParam; -- Using the value in mParam to assign a diff variable

    mRow.NEW_VALUE := new_row.mParam -- Using the value inside mParam as the name of a column in new_row


    The only way I can think thats close to completing it is using EXECUTE IMMEDIATE, but then my parameters are not recognizable in the executable block.

    Would appreciate input,
    Thanks,
    AlexD

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    No, you can't do it dynamically. You could use a CASE expression:
    Code:
    mRow.NEW_VALUE := CASE mParam
                        WHEN 'col1' THEN :new_row.col1
                        WHEN 'col2' THEN :new_row.col2
                        WHEN 'col3' THEN :new_row.col3
                        END;
    (Incidentally, why the REFERENCING clause? What's wrong with using the default :NEW?)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •