Results 1 to 6 of 6

Thread: empty variable

  1. #1
    Join Date
    Nov 2007
    Posts
    14

    Unanswered: empty variable

    Hi,

    I have a trigger which selects data into a variable and uses an if statement. The trigger executed my else which was not was I was expecting so I inserted a record into one our error tables to see what is going on and the variable is empty. when I run the select statement outside the trigger (without the INTO) I get a value back. Inside the trigger I don't. My code is below. What am I missing? I have a feeling it's something really obvious but I have been starting at it so long I can't see it.

    Code:
    SELECT DECODE(custom_shipping_label.get_cstnum('900001930'),NULL,custom_shipping_label.get_soms_cstnum('900001930'),
                                                         custom_shipping_label.get_cstnum('900001930'))
         INTO lv_cstnum
         FROM dual;
       
         custom_shipping_label.insert_lodser_err_data
                (
                :old.ship_id,
                'after select '||lv_cstnum,
                lv_cstnum
                );
    TIA!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Here is 1 possible cause of problem.
    Privs acquired via ROLE do not apply within PL/SQL procedures (trigger, etc.)
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2007
    Posts
    14
    Well, if I put the exact same code into a procedure it works great. But in the table trigger, and it's an after update trigger BTW, it does not work.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > it does not work.
    While statement may be true, it is totally ambiguous.
    We can't see what is really happening (or not).
    What proof do you have that the trigger is actually firing?

    When things do not appear to be as they should, they probably aren't.
    Oracle is way too dumb to "make up" (mysterious?) results.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Nov 2007
    Posts
    14
    Hi,

    I hear what you're saying, but I know the trigger fires because a record gets inserted into my error table. Every time I do an update on the table I get a new record in my error table. And the insert statement is immediately following the select statement.

    In addition, I have another select statement before the one I mentioned above also selecting a value into a variable. When I pass that variable to my insert statement it has the correct value in it. But the variable from the other select statement is null. Again, if I run this Select statement in a SQL window I get a value back.

    Where do I go from here?

    Thanks!
    Last edited by kkc; 01-21-09 at 16:23.

  6. #6
    Join Date
    Nov 2007
    Posts
    14
    I figured it out - it's a mutating table problem. The function I am calling is using the same table as the trigger. It's been a while since that function was written and I did not think the same table was being used.

Posting Permissions

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