Can a function execute dml stmts
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Can a function execute dml stmts

Hybrid View

  1. #1
    Join Date
    Jul 2001
    Posts
    2

    Can a function execute dml stmts.
    Below is the message when tried to execute a function with an update/insert stmts

    ORA-06571: Function TESTUPD does not guarantee not to update database

  2. #2
    Join Date
    Aug 2000
    Posts
    462
    You are trying to execute this function within a SQL statement. A function executing DML cannot be part of a SQL statement:

    To prevent side effects, the following rules apply
    to stored functions called from SQL statements:

    - The function cannot execute an INSERT, UPDATE, or DELETE statement.
    - Remote or parallelized functions cannot read or write the
    values of package variables.
    - Only functions called from a SELECT, VALUES, or SET clause can write the
    values of packaged variables.
    - The function cannot call another subprogram that does not follow the
    previous rules, and it cannot reference a view that does not follow the
    previous rules.





    You didn't provide much information on how/when you want this function to execute. If you don't need it to be in the SQL statement, just call it within the trigger body:

    declare
    x number;
    begin
    x :=myfunc;
    end;


    Oracle DBA and Developer

  3. #3

    pragma

    You may also need to add a PRAGMA RESTRICT_REFERENCES(function name, WNDS,RNDS,WNPS,RNPS) or some combination to make the dtabase let you use the function. I think you can get away without in 8i.

  4. #4
    Join Date
    Aug 2000
    Posts
    462
    the pragma is only needed to allow a function/procedure within a package to be executed within a sql statement. This is related to a concept called "Purity", which essentially means "how wide ranging are the effects of this function/procedure?". Since Oracle won't allow you to execute DML within a sql statement, it looks at the code in functions/procedures before executing them, to determine the level of purity. Oracle does not look at functions/procedures within packages. Thus if you call a procedure/function within a package in a sql statement, Oracle assumes the function is not "pure"; the assumption is that the function does have side effects on other data object and thus Oracle will not execute it. To get around this, you can more or less promise to Oracle that your intentions (and your function/procedure) is pure by including the pragma described by bmycroft.

    Hope this helps!
    Oracle DBA and Developer

  5. #5
    Join Date
    Jul 2001
    Posts
    8
    Gunas,
    In order to use user defined function in SQL stmt u need to define the Pragma Restrict referrence in u r function.

    --Sriram.

  6. #6
    Join Date
    Aug 2000
    Posts
    462
    In 8i, you do not need to use the pragma in a function. I just tested it.

    Oracle DBA and Developer

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Also, as kmesser has allready mentioned, you don't need pragma in pre-8i releases if the function is a standalon function. Pragma was required only for packaged functions if they ought to be called from SQL statements.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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