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

Here is How to Unpersist Your Persistent PL/SQL Package Data

Avoid standalone procedures and functions and always use packages to construct your application. That is one of the “best practices” when developing Oracle PL/SQL programs.

Of course, as an Oracle PL/SQL programmer you must be familiar with PL/SQL packages and you know how powerful they are in organizing your functions and enhancing the maintenance of your code.

In this post, I am going to concentrate on one feature that makes PL/SQL packages even more powerful, session persistence.

As a refresher, let’s go through some facts: Continue reading…

7 Comments | Filed in Oracle | Tags: ,

Go ahead, turn your FIPS flagging on

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: Continue reading…

Comments Off on Go ahead, turn your FIPS flagging on | Filed in Oracle | Tags: , ,

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.

4 Comments | Filed in Interesting, Oracle | Tags: , ,

PRAGMAtism in Oracle PL/SQL

What is a pragma? A pragma is compiler directive. Pragmas are processed at compile time, not at run time. They pass information to the compiler.

The pragma notion is not limited to PL/SQL. Other programming languages have pragmas too. defines a pragma, in the context of Computer Science, as:

A message written into the source code that tells the compiler to compile the program in some fashion that differs from the default method. For example, pragmas may alter the kinds of error messages that are generated or optimize the machine code in some way.

Wikipedia defines pragma, in the context of the word origin, as:

a Greek word (πραγμα), plural pragmata (πραγματα), whose root meaning is “that which has been done, an act, a deed, a fact”, and whose connotations and more extended senses cover a wealth of meanings, including: action, affair, annoyance, business, …

and in the context of Computer Science as:

a compiler directive, data embedded in source code by programmers to tell compilers some intention about compilation. A compiler directive often tells the compiler how to compile; other source code tells the compiler what to compile.

Back to PL/SQL. A PRAGMA is a reserved PL/SQL keyword. In your code, you write a pragma like this: Continue reading…

5 Comments | Filed in Oracle | Tags: ,