21st Century PL/SQL

January 30, 2005

Make PL/SQL dynamic at the API level

PL/SQL is generally not a very dynamic language. There are strict checks at compile time to ensure that other program units referred to exist and are accessible. To get around this, it is possible to use dynamic PL/SQL. This goes to the other extreme and allows completely arbitrary blocks of code to be executed at runtime. I'd like some kind of middle ground, whereby an API object very much like a package spec is created as a standalone object. Then multiple package bodies can be created which don't have a package spec, but implement the API. Which package body is used can be determined at runtime. Example, where LOG is the API and UTL_FILE_LOG and DBMS_OUTPUT_LOG are packages which implement it:

DECLARE
  log_pkg LOG%APITYPE;
BEGIN
  IF x=1 THEN
    log_pkg := API.GET_PACKAGE('UTL_FILE_LOG');
  ELSE
    log_pkg := API.GET_PACKAGE('DBMS_OUTPUT_LOG');
  END IF;

  log_pkg.log('Something happened');
END;

So in this case the log message goes to either a file, or to console output depending on a value at runtime.

Posted by Chris Rimmer at 03:35 PM | Add your thoughts (2)

February 03, 2005

Allow a way to unambiguously reference objects

Suppose you create a package BBB with a procedure CCC in a schema AAA and grant access to this to a second schema DDD. This schema can refer to this object as AAA.BBB.CCC. Then, when a new object called AAA is created in the DDD schema, this reference will break. I suggest allowing objects to be referenced using the syntax .AAA.BBB.CCC (with a leading dot) to force the first element to be interpreted as a schema.

Posted by Chris Rimmer at 10:26 AM | Add your thoughts (0)

April 03, 2005

Namespaces

Provide the concept of namespaces so package qualifiers can be eliminated. This makes code more reusable and easier to manage and refactor.

For example, instead of coding:

log_pkg.logit(str);

I could do something like:

pragma namespace log_pkg;
logit(str);

If an unqualified reference is ambiguous, the compiler will return an error. The pragma can be used locally within a subprogram, or globally within a package.

Other modern languages (like C#) also provide namespaces.

Posted by at 11:43 AM | Add your thoughts (0)

April 04, 2005

Introduce WITH as in VB

allow code blocks referring to a specific object to have a WITH wrapper:

Instead of:

 FOR lr_Post_Grad_Students IN c_Post_Grad_Students LOOP
  lr_Post_Grad_Students.name := 'Joe Schmo';
  lr_Post_Grad_Students.grant := 0;
  lr_Post_Grad_Students.job_prospects := 0;
END LOOP;

Have:

 FOR lr_Post_Grad_Students IN c_Post_Grad_Students LOOP
  WITH lr_Post_Grad_Students
    .name := 'Joe Schmo';
    .grant := 0;
    .job_prospects := 0;
  END WITH;
END LOOP;

Posted by David Torbett at 10:56 AM | Add your thoughts (6)

April 08, 2005

Add support for $INCLUDE to conditional compilation

Oracle10g Release 2 now includes support for conditional compilation, as in:

$IF DBMS_DB_VERSION.VER_LE_10_2
$THEN
Use this code.
$ELSIF DBMS_DB_VERSION.VER_LE_11
This is a placeholder for future.
$ENDIF

It would also be quite wonderful to allow us to include a file using $INCLUDE or some other similar directive.

Posted by sf at 09:02 AM | Add your thoughts (0)

October 06, 2005

Shortcuts to packages and commands, PL/SQL takes too much to type

Let me specify within my program or session alternative (short) names for packages or commands, and let me specify default packages in my block, so I don't have to repeat the package name again and again.

Posted by Juan Carlos Reyes at 02:04 PM | Add your thoughts (0)

January 30, 2006

Import or Namespaces

ADA allows one package to import the spec from another, so that once imported, you can locally refer to procedures / functions / constants, and even (god forbid) variables in the imported package, without a qualifier. I\'d find this very useful - I have some packages that consist of almost nothing but calls to a single underlying package. As I try to follow good practice and ensure that any constants passed into my packages, and exceptions returned, are defined in their own headers, I quite often end up with code that looks like : pkg.proc_name( param_1 => pkg.CONSTANT, param_2 => pkg.CONSTANT2, param_3 => local_variable); . . . WHEN pkg.EXC_NO_MSG_FOUND THEN except obviously more complex. The tendency becomes to put the code into the \'library\' package itself, resulting in a massive library, rather than a series of libraries built on top of the other.

Posted by Julian Lawton at 03:17 PM | Add your thoughts (0)