About The BUILTIN, FIPSFLAG and INTERFACE Pragmas in Oracle

A few weeks ago I blogged about PRAGMAs in PL/SQL. As you all know, as of the current release of PL/SQL, we have 4 documented pragma directives: AUTONOMOUS_TRANSACTION, RESTRICT_REFERENCES, EXCEPTION_INIT and SERIALLY_REUSABLE.

Karl posted a comment to draw my attention to the fact that in the SYS.STANDARD PL/SQL package, Oracle uses three additional undocumented pragma directives: BUILTIN, FIPSFLAG and INTERFACE. Just to satisfy my curiosity, I tried to find more information about them. Here is what I found:

PRAGMA BUILTIN:

An example of its usage in the SYS.STANDARD PL/SQL package:

function SQLCODE return PLS_INTEGER;
  pragma BUILTIN('SQLCODE',45, 10, 0);

This is what Pete Finnigan wrote about it on Nov 22 2003:

I don’t know the exact mechanism for the pragma builtin keyword but the function is almost certainly implemented in C and would be called in the SQL engine directly so should be reasonably efficient.

PRAGMA FIPSFLAG:

An example of its usage in the SYS.STANDARD PL/SQL package:

function SQLERRM return varchar2;
 pragma FIPSFLAG('SQLERRM', 1452);

I could not find any meaningful information about this directive.

PRAGMA INTERFACE:

An example of its usage in the SYS.STANDARD PL/SQL package:

function pesacos(n NUMBER) return NUMBER;
 pragma INTERFACE (c,pesacos);

I found three sources of information about this INTERFACE directive:

  • The STANDARD Package description from chapter 1 of Oracle Built-in Packages book by Steven Feuerstein. Here is what he wrote:

    Here is the implementation of LIKE:

      function 'LIKE' (str varchar2, pat varchar2) return boolean is
  begin
    return peslik(str, pat);
  end;

    What is this peslik function? Ah, that is where, when, and how Oracle “cheats” (or, at least, makes the rest of us PL/SQL developers jealous):

      function peslik(str varchar2, pat varchar2) return boolean;
      pragma interface (c,peslik);

    The peslik function is a stub program for a callout to C.

  • PL/SQL wrap utility from Exploiting and Protecting Oracle paper by Pete Finnigan. Here is what he wrote:

Yes, that’s right most of PL/SQL and Oracle’s built in packages are written in ‘C’ and these ‘C’ functions are called through a different mechanism than the one used by user programs from Oracle 8. The syntax is as follows:

procedure do_something(a_var binary_integer, another_var binary_integer);
    pragma interface (C, do_a_c_function);

PLS-00129: Pragma INTERFACE only supports C as its first argument

Cause: The first parameter passed to pragma INTERFACE specified a host language other than C. Currently, C is the only host language supported. The parameter might be misspelled, or the pragma syntax might be faulty (for example, a comma might be missing between two parameters).

Action: Check the spelling of the first parameter, which should be C, and make sure the proper syntax was used.

Now that you know about these undocumented pragma directives, what next? nothing. It’s just interesting to know that they exist and to see how Oracle implements calls to its built-in functions behind the scenes. There is however some mystery surrounding the use of PRAGMA FIPSFLAG. If you have any additional information, please share with the rest of us. Thanks.

Possibly related:

Tagged , , | Post a Comment