User defined functions is SQL??
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: User defined functions is SQL??

  1. #1
    Join Date
    Aug 2002
    Location
    Brighton, England
    Posts
    93

    User defined functions is SQL??

    I've always thought that user defined functions could not upate the database unless they were autonomous at least thats what i read in my plsql book. Yet i find i can do the following, here i find my function 'change_emp' is able to update the emp_ids of the table 'EMP'. What have i misunderstood?
    Cheers Gus

    CREATE TABLE EMP(emp_id NUMBER(3), NAME VARCHAR2(30), job VARCHAR2(50));

    CREATE OR REPLACE FUNCTION change_emp
    RETURN VARCHAR2
    IS
    --PRAGMA autonomous_transaction;
    BEGIN
    UPDATE EMP
    SET emp_id = emp_id + 5;
    RETURN 'Latest entry';
    END;

    INSERT INTO EMP VALUES(1, 'John Mills', 'Writer');

    INSERT INTO EMP VALUES(2, 'David Gray', 'Singer/song writer');


    Now if i use my user-defined function it makes changes to the DB??

    INSERT INTO EMP VALUES(3, 'Blue Nile', change_emp);

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    After this
    INSERT INTO EMP VALUES(3, 'Blue Nile', change_emp);

    What happened?
    Tamil

  3. #3
    Join Date
    Feb 2005
    Posts
    158
    What you are not allowed to do is call a user-defined function that updates the database (non-autonomous) as part of a select. IE a select is not allowed to update the database.
    In the example,the function is able to be executed and the value returned, and then the insert done.
    You probably wouldn't be able to do an INSERT ... SELECT change_emp
    Probably not an UPDATE either (the update needs to select the rows to be updated)

  4. #4
    Join Date
    Aug 2002
    Location
    Brighton, England
    Posts
    93
    What if you didn't use the pragma autonomous transaction ...without the pragma the table cannot be updated by a UDF in a query as expected but from the rules a table should not be updateable by a UDF from within a dml statment either but it can be as I've shown below even though the rule states...

    "When called from an INSERT, UPDATE, or DELETE statement, the function cannot query or modify any database tables modified by that statement."

    ...I did, how can this be!


    DevSQL>CREATE TABLE testtab (col1 NUMBER(3), remark varchar2(30));

    Table created.

    DevSQL>
    DevSQL>CREATE OR REPLACE FUNCTION addone
    2 RETURN VARCHAR2
    3 IS
    4 BEGIN
    5 UPDATE testtab
    6 SET col1 = col1 + 3;
    7 RETURN 'Inserted by a UDF';
    8 END;
    9 /

    Function created.

    DevSQL>
    DevSQL>INSERT INTO testtab VALUES(1, 'one');

    1 row created.

    DevSQL>
    DevSQL>INSERT INTO testtab VALUES(2, 'two');

    1 row created.

    DevSQL>
    DevSQL>INSERT INTO testtab VALUES(3, 'three');

    1 row created.

    DevSQL>
    DevSQL>SELECT * FROM testtab;

    COL1 REMARK

    ---------- ------------------------------
    1 one
    2 two
    3 three

    DevSQL>
    DevSQL>SELECT col1, remark, addone "Updated Value" FROM testtab;
    SELECT col1, remark, addone "Updated Value" FROM testtab
    *
    ERROR at line 1:
    ORA-14551: cannot perform a DML operation inside a query
    ORA-06512: at "HR.ADDONE", line 5


    DevSQL>
    DevSQL>SELECT * FROM testtab;

    COL1 REMARK
    ---------- ------------------------------
    1 one
    2 two
    3 three

    DevSQL>
    DevSQL>INSERT INTO testtab VALUES(4, addone);

    1 row created.

    DevSQL>
    DevSQL>SELECT * FROM testtab;

    COL1 REMARK
    ---------- ------------------------------
    4 one
    5 two
    6 three
    4 Inserted by a UDF

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    I think after the function is created, you executed it once.
    I am not getting the wrong answer in 9iR2.
    See below:
    Code:
    SQL> select * from F1 ;
    
           COL REMARK
    ---------- ------------------------------
             1 one
             2 two
             3 three
             4 four
    
    SQL> set autocommit on
    SQL> select a.*, upd_col() as abc from F1 a ;
    select a.*, upd_col() as abc from F1 a
                *
    ERROR at line 1:
    ORA-14551: cannot perform a DML operation inside a query
    ORA-06512: at "TAMIL.UPD_COL", line 5
    
    
    SQL> select * from F1 ;
    
           COL REMARK
    ---------- ------------------------------
             1 one
             2 two
             3 three
             4 four
    Tamil

  6. #6
    Join Date
    Feb 2005
    Posts
    158
    Broadly, if a function updates the database and multiple rows are being acted upon, there is the potential that if the rows are picked up in A,B,C order they will return one result, but another if they are picked up in C,B,A order.
    To ensure this doesn't happen, it errors out.

    INSERT VALUES is a special case. It knows that the INSERT will only be for one row and that executing the function cannot affect the outcome of the INSERT.

    Autonomous transactions can cause the same problems, which is why they should be treated with great caution.

Posting Permissions

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


Click Here to Expand Forum to Full Width