1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

functions

Discussion in 'SQL PL/SQL' started by bnramesh8, May 12, 2015.

  1. bnramesh8

    bnramesh8 Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    Hi All,
    Can we call a procedure inside a function? If that procedure having dml operations?
     
  2. eras

    eras Active Member

    Messages:
    23
    Likes Received:
    9
    Trophy Points:
    90
    Location:
    Lithuania
    Yes, if procedure has pragma autonomous_transaction
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi,bnramesh8.
    if you are going to cause the function changing data from sql, such approach isn't absolutely correct.
    there can be problems with logical integrity of data
     
    bnramesh8 likes this.
  4. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    No. If you call such a function from a SQL statement, there will be absolutely no problems with data integrity.
    That is the case because the query will fail with an error. Test case below:

    First -- create the table to be updated as well as a simple procedure to perform DML and a function to call it:
    Code (Text):
    CREATE TABLE tmp_tab (
    col1    NUMBER);

    CREATE PROCEDURE tmp_tab_proc
    AS
    BEGIN
      INSERT INTO tmp_tab VALUES (1);
    END;

    CREATE FUNCTION tmp_tab_func
    RETURN VARCHAR2
    AS
    BEGIN
      tmp_tab_proc;
      RETURN 'It works!';
    END;
    If the function is called from SQL, it will fail:
    Code (Text):
    SELECT tmp_tab_func FROM dual;

    Error starting at line : 18 in command -
    SELECT tmp_tab_func FROM dual
    Error report -
    SQL Error: ORA-14551: cannot perform a DML operation inside a query
     
    However, the function can be called from PL/SQL without an issue:
    Code (Text):
    DECLARE
      v_retval    VARCHAR2(20);
    BEGIN
      v_retval := tmp_tab_func;
      DBMS_OUTPUT.PUT_LINE('The result of tmp_tab_func is: ' || v_retval);
    END;

    The result of tmp_tab_func is: It works!
     
     
  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi,Matthew.

    the technical problem isn't present to use autonomous transaction and to cause function in sql.

    for example:
    if function to change data on customer accounts in autonomous transaction - not difficultly to provide what there will be a result....


    ...after all use of autonomous transaction are specific tasks....