News, views, tips and tricks on Oracle and other fun stuff

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:


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.


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.


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
        return peslik(str, pat);

    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.

Filed in Interesting, Oracle on 24 May 06 | Tags: , ,

Reader's Comments

  1. |

    Hi Eddie,

    Nice post!!

    A couple of comments. Normally FIPS stands for Federal Information Processing Standards, I don’t know but maybe its related?

    Also on the Pragma interface C, if you read further in my first big Oracle paper (Expoliting and protecting Oracle) you will see that i tried to use the syntax myself but if fails with an ORA-6509 – ICD vector Processing error. I assumed at the time that Oracle implements a function call table. Like a table of structs that includes details for each function implemented as a pragma interface C call. This table or linked list would include function pointers for each C function, hence you cannot simply call your own C directly from PL/SQL unless you can update this table to add the address of the function you add. This is a great interface for calling C directly without the extproc overheads if only we coluld find a way to make it work..:-)



  2. |

    Pete, thanks for your work on this and the extra clarification.

  3. |

    I would also guess that the pragma FIPSFLAG is related with the FIPS compliance. There is a session parameter FLAGGER which causes an error message to be generated when a SQL statement issued is an extension of ANSI SQL92. This would somehow explain why SQLERRM is declared with this pragma.

  4. |

    […] Since my last post about the undocumented pragmas in Oracle, I have found more information about the FIPSFLAG pragma directive and more specifically about the FIPS part of it. Jens commented about the existence of a session parameter FLAGGER and its possible relationship with FIPSFLAG and FIPS. After a bit of searching, here is what I found: […]