Live ORACLE

DROP TABLE debug_output CASCADE CONSTRAINTS PURGE;


-- create the debug table

CREATE TABLE debug_output ( msg VARCHAR2(200));


-- create the package spec

DROP PACKAGE Debugging ;


CREATE OR REPLACE PACKAGE debugging

AS

  FUNCTION log_msg(msg  VARCHAR2) RETURN VARCHAR2;

 

  PRAGMA RESTRICT_REFERENCES(log_msg,wnds,rnds);

END debugging;

/


DROP PACKAGE BODY Debugging ;


-- create the package body

CREATE OR REPLACE PACKAGE BODY debugging

AS

  FUNCTION Log_msg

       (msg  VARCHAR2)

  RETURN VARCHAR2

  IS

    PRAGMA AUTONOMOUS_TRANSACTION;

  BEGIN

    -- the following insert does not violate the constraint

    -- WNDS because this is an autonomous routine

    INSERT INTO debug_output

    VALUES     (msg);

    COMMIT;

    RETURN msg;

  END;

END debugging;

/


-- call the packaged function from a query

DECLARE

  my_emp_id     NUMBER(6);

  my_last_name  VARCHAR2(25);

  my_count      NUMBER;

BEGIN

  my_emp_id := 120;

  SELECT debugging.Log_msg(last_name)

  INTO   my_last_name

  FROM   employees

  WHERE  employee_id = my_emp_id;

  -- even if you roll back in this scope, the insert into 'debug_output' remains

  -- committed because it is part of an autonomous transaction

  ROLLBACK;

END;

/

Calling an Autonomous Function