"Accessible by" clause

1. "Accessible by" clause is used to declare a particular unit may be referenced only by given list of units.
2. Anonymous block cannot be listed under "Accessible by" Clause, hence the units declared as "Accessible by" cannot be called dynamically.
3. "Accessible by" declared units cannot be invoked from outside of database

CREATE OR replace PROCEDURE proc_for_sal_increment ( pin_empno NUMBER,
                                                    pin_pct    NUMBER ) 
accessible BY (update_sal_wrapper)
AS
BEGIN
  UPDATE emp
  SET    sal = ( sal ) + ( sal * pin_pct / 100 )
  WHERE  empno = pin_empno;
  
  COMMIT;
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line('Unable to Update sal : '
  || SQLCODE);
END;

---

BEGIN
    Proc_for_sal_increment(7698, 10);
END;
/


exec proc_for_sal_increment(7698, 10); 

CREATE OR replace PROCEDURE Update_sal_wrapper
AS
BEGIN
    Proc_for_sal_increment(7698, 10);
END;
/

exec update_sal_wrapper; 

Pragma UDF (User Defined Function)

CREATE OR REPLACE FUNCTION fn1 (
    p_input1 NUMBER
) RETURN NUMBER
    AS
BEGIN
    RETURN p_input1 * 2;
END;
/

SELECT
    SUM(fn1(ROWNUM) )
FROM
    dual
CONNECT BY
    level <= 2000000;

-----------------------------------------------------

WITH
    FUNCTION fn1 (
        p_input1 NUMBER
    ) RETURN NUMBER
        AS
    BEGIN
        RETURN p_input1 * 2;
    END;

SELECT
    SUM(fn1(ROWNUM) )
FROM
    dual
CONNECT BY
    level <= 2000000;

-----------------------------------------------------

CREATE OR REPLACE FUNCTION fn1 (
    p_input1 NUMBER
) RETURN NUMBER
    AS
PRAGMA UDF;
BEGIN
    RETURN p_input1 * 2;
END;
/

SELECT
    SUM(fn1(ROWNUM) )
FROM
    dual
CONNECT BY
    level <= 2000000;

-----------------------------------------------------