Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2014
    Posts
    3

    Question Unanswered: Update statement - SET from function

    Hi,
    I have a function that I would like the results used in my update statement

    i.e.

    Update mytable
    set myfield = myfunction ('parameter1','paramater2');
    where criteria = 'a' and criteria2 = 'b'


    The only issue is that the mytable is ALSO referenced in a select statement within the myfunction function.

    regards,
    Craig.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    How is Oracle supposed to decide to use or return "new" or "old" values from MYTABLE?

    Might you nave a fatal design flaw?
    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
    Dec 2013
    Posts
    14
    In this case you would be getting mutating error.
    Issue pragma autonomous transaction in your function.

    Eg.
    CREATE OR REPLACE FUNCTION func_update_dummy_table
    ( pv_class_type IN VARCHAR2,pv_file_name IN VARCHAR2) RETURN NUMBER IS
    ln_result NUMBER;
    PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
    INSERT INTO abhi_test_array_temp
    (pv_class_type_fk ,pv_file_name)
    VALUES
    (pv_class_type ,pv_file_name);
    COMMIT;
    ln_result := 1;
    RETURN(ln_result);
    END func_update_dummy_table;

    update abhi_test_array_temp set pv_review_id = func_update_dummy_table('FC','Some_dummy_test');

    This works in my case.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >This works in my case.
    for some indeterminate value of "work" when ROLLBACK is issued to original transaction

    with free advice sometime you get what you paid for it.
    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
    Apr 2014
    Posts
    3
    hmm. I'm not sure if I'm making myself clear.. the function DOESNT update or insert any records into mytable.. It just reads 2 columns and returns a hash of the value which I then use in my update statement.

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by CJOnline View Post
    hmm. I'm not sure if I'm making myself clear.. the function DOESNT update or insert any records into mytable.. It just reads 2 columns and returns a hash of the value which I then use in my update statement.
    Then create a function that instead of reading the values from the same table, will use those 2 values from the update table and return the hash something similar to this:
    Code:
    CREATE OR REPLACE FUNCTION Myfunction2 ( Col1 VARCHAR2, Col2 VARCHAR2)
       RETURN VARCHAR2
    IS
    BEGIN
       RETURN ORA_HASH ( Col1, Col2);
    END;
    /
    
    --
    -- Then use it to do the update:
    --
    UPDATE Mytable U
       SET Myfield     = Myfunction2 ( U.Col1, U.Col2)
     WHERE Criteria    = 'a'
       AND Criteria2   = 'b'
    /
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    Apr 2014
    Posts
    3
    Quote Originally Posted by LKBrwn_DBA View Post
    Then create a function that instead of reading the values from the same table, will use those 2 values from the update table and return the hash something similar to this:
    Code:
    CREATE OR REPLACE FUNCTION Myfunction2 ( Col1 VARCHAR2, Col2 VARCHAR2)
       RETURN VARCHAR2
    IS
    BEGIN
       RETURN ORA_HASH ( Col1, Col2);
    END;
    /
    
    --
    -- Then use it to do the update:
    --
    UPDATE Mytable U
       SET Myfield     = Myfunction2 ( U.Col1, U.Col2)
     WHERE Criteria    = 'a'
       AND Criteria2   = 'b'
    /
    excuse my ignorance.. but I already have a function that I am using to hash the values I require.. this works.. I just need to get the result of this function into my update statement.. the problem is that oracle complains due to the fact that both the update statement and the function both reference the same table. p.s. I'm new to Oracle.

  8. #8
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by CJOnline View Post
    excuse my ignorance.. but I already have a function that I am using to hash the values I require.. this works.. I just need to get the result of this function into my update statement.. the problem is that oracle complains due to the fact that both the update statement and the function both reference the same table. p.s. I'm new to Oracle.
    Although you did not post what exactly that function does, I will make some assumptions (as LKBrwn_DBA did).

    If the functions "reads" the column values from the updated row (e.g. based on primary key value passed as a parameter), then it would be better pass those values directly - there is no need to query them from the table. Of course, this means slight implementation change of that function - removal the query and using the passed parameters directly.

    If other rows than the updated one (one at a time) are involved, then the whole design is at least suspicious and the best way would be deciding its change. Of course the best possible approach would depend on the accurate requirements, but your posts do not contain them at all.

Posting Permissions

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