




Remote DBA services Remote DBA Support Remote DBA RAC Remote DBA Reasons Remote Oracle Tuning Remote DBA Links Oracle DBA Support Oracle DBA Forum Oracle Disaster Oracle Training Oracle Tuning Oracle Training Remote DBA SQL Server Remote MSSQL Consulting Oracle DBA Hosting Oracle License Negotiation

EnterpriseDB: Compilation

Oracle Tips by Burleson Consulting

This item is not a compatibility issue but does impact performance and testing. Procedural code in EnterpriseDB is stored as a string. Instead of the database compiling the code at compile time and storing both the code and the p-code, the code is parsed by the tool being used to compile the code and just the uncompiled code is stored. That has several repercussions.

The first issue is that the success of the compilation and the value of the error messages returned to you are dependant on the tool you use to compile the code. You may get a different error from each of the development tools I spoke of in Chapter 5.

The next issue is related to the first issue in that you do not get the level of compile time checking in EnterpriseDB that you do with Oracle. If your code compiles in Oracle, there is a good chance that it will run at runtime. You may have logic errors, but your code should be valid.

In EnterpriseDB, there are many scenarios where code will not be validated until runtime which increases the volume of run time errors. An increase of runtime exception discovery requires an increase in pre-release testing. The short story here is that your testing requirements may go up.

The last issue is that if the code is not compiled at compile time, when is it compiled? It is in fact compiled at run time and cached. If you restart the database, any code will be recompiled the next time it is called. That does good things to dependancies (simplifies them) but it does bad things to performance.

There is no way to gauge the impact to your systems without trying them. If you put everything in packages and pre-initialize them after a database restart, you will probably see little performance impact.

Supplied Packages

In my opinion, the biggest pain point about migrating to EnterpriseDB is the lack of supplied packages. I am a heavy user of many of the packages that come preloaded with Oracle such as utl_file, dbms_application_info, dbms_aq, dbms_job, dbms_pipe, dbms_sql, utl_http, utl_smtp, etc.

Currently, the only package supported by EnterpriseDB is the dbms_output package. That one is almost mandatory.

While it is a pain point, it is not insurmountable. In most cases, I use very little of the functionality in the package. I usually only call one or two of the procedures or functions in the package. That means that I can easily mimic the package and replace it with my own code.

As a non-trivial example, I will use UTL_FILE. EnterpriseDB does not currently support utl_file and has no real support for file handling. To accommodate my file handling needs, I will write my own utl_file functionality in pl/perlu. I could do it in any of the languages supported by EnterpriseDB that also allow file handling. I will use pl/perlu because it has robust file handling capabilities. The u in pl/perlu is for unsafe because I will be accessing the file system which is a no-no in the PostgreSQL world.

To start, I will create a table to store directory information so that the procedures and functions I will use work much like they will in Oracle. Oracle supports a concepts of "directories". A directory is a database object that points to a physical location on disk. I will create my own "directory" structure:

CREATE TABLE ora_style_directory (
db_dir_name VARCHAR2(30),
os_dir_name VARCHAR2(4000) );

This table allows me to give a simple name to a complex directory structure. I can add a record for my examples below:

INSERT INTO ora_style_directory (db_dir_name, os_dir_name)
VALUES ('temp', 'C:\\temp\\');

* In most cases, EnterpriseDB will allow you to chose between a forward slash ("/") and a back slash ("\"). If you chose to use a back slash, you will need to use a double back slash ("\\") for EnterpriseDB to interpret it correctly. I tend to use the forward slash as it is very portable, easier to read and easier to write.

The use of this table will make a lot more sense when I get into the code below.

The important point to remember is that when migrating, I do not need all of the supported functionality. I only need what I actually plan to use and what I do use does not have to be the same in Oracle, it just needs to work the same as in Oracle.

Where I am going with that, is that if I were doing this in Oracle, I would create a package much like the one below:

CREATE OR REPLACE PACKAGE utl_file AS

TYPE file_type IS RECORD (
file TEXT );

-- Open the file

-- max_linesize is for compatibility only and is ignored

FUNCTION FOPEN(
    location     IN VARCHAR2,
    filename     IN VARCHAR2,
    open_mode    IN VARCHAR2,
    max_linesize IN INTEGER DEFAULT 1)
RETURN FILE_TYPE;

-- Put a string in the file

PROCEDURE PUT (
file IN FILE_TYPE,
buffer IN VARCHAR2);

-- Put a string in the file followed by a linefeed

PROCEDURE PUT_LINE (
    file      IN FILE_TYPE,
    buffer    IN VARCHAR2,
    autoflush IN BOOLEAN DEFAULT FALSE);

-- Get a line FROM the file

PROCEDURE GET_LINE (
    file        IN FILE_TYPE,
    buffer      OUT VARCHAR2,
  len         IN INTEGER DEFAULT NULL);

-- Close the file

PROCEDURE FCLOSE (
file IN OUT FILE_TYPE);

END;

From the specification, it is obvious that I plan to create the five procedures that I use most often in my programs. I need to be able to open and close files, read lines of text from a file and write lines to a file (with and without closing line feeds).

Since I am not doing this in Oracle, I will do this the EnterpriseDB way which is a mix of PostgreSQL functionality and SPL.

The first step is to create a schema call UTL_FILE:

CREATE SCHEMA utl_file;

This schema will be a sort of database level package. The next step is to create the file type that will be used as the file handle.

CREATE TYPE utl_file.file_type AS (file TEXT);

That means that when I refer to this type in my code, I will use utl_file.file_type. Look familiar, yet?

The next step is to create the underlying perl routines. I start with the open file function. I expect to send this function a directory (as defined in my ora_style_directories table), a file name and a file open mode and this function will open it. Valid open modes are: "<" = read, ">" = open and ">>" = append.

CREATE OR REPLACE FUNCTION lrc_openfile(TEXT, TEXT, TEXT) RETURNS TEXT AS $$
# Set Directory to Parm 1
$parm1 = $_[0];

    # Select the os directory using the db dir
    $query = "SELECT os_dir_name FROM ora_style_directory
                 where db_dir_name = '$parm1'";

# Execute the query
$cursor = spi_exec_query($query, 1);

# Retrieve the dir name
$foo = $cursor->{rows}[0]->{ os_dir_name };

# Create a variable to hold the second parameter, my file name
$myfile = $_[1];

# Concatenate the directory name to the file name
$myFileHandle = $foo . $myfile;

    # Open the file
    open my $fh, $_[2], $myFileHandle
        or elog(ERROR, qq{Could not open the file "$myFileHandle": $!});

# Save the file handle to a global (session) variable
$_SHARED{$myFileHandle} = $fh;

# Return the global session variable name
return $myFileHandle;

$$ LANGUAGE plperlu;

The next function I will write is the close function. This is a much easier function to write. All this function does is retrieve the correct file handle from a global array and close the file.

CREATE OR REPLACE FUNCTION lrc_closefile(TEXT) RETURNS TEXT AS $$
    # store the first parameter, the global session variable name
    # to a variable
    my $tmpfile = $_[0];

    # Close the file
    close( $_SHARED{ $tmpfile } ) or
          elog(ERROR, qq{Could not close the file "$tmpfile": $!});

# Return the handle name
return $tmpfile;

$$ LANGUAGE plperlu;

Reading a line of text from a file is also very easy. This function is sent the file global variable and reads a single line from it.

CREATE OR REPLACE FUNCTION lrc_read(TEXT) RETURNS TEXT AS $$

    # store the first parameter, the global session variable name
    # to a variable
    my $tmpfile = $_[0];

#point to the file handle
$read_file = $_SHARED{$tmpfile};

# read a line of text
$read_buffer = <$read_file>;

# return the buffer
return $read_buffer ;

$$ LANGUAGE plperlu;

Lastly, I will write the write function. This function accepts a file handle (like all of the others), a string to write and a Boolean that indicates whether or not to add a newline "\n" to the end of the string.

CREATE OR REPLACE FUNCTION lrc_write(TEXT, TEXT, BOOLEAN) RETURNS TEXT AS $$

   # store the first parameter, the global session variable name
    # to a variable
    my $tmpfile = $_[0];

# Store the text input to a buffer variable
my $prtdata = $_[1];

    # If a newline is requested, do so
    if ($_[2]) {
      print {$_SHARED{$tmpfile} } $prtdata, "\n"; }
   else {
      print {$_SHARED{$tmpfile} } $prtdata }

return $_SHARED{$tmpfile} ;
$$ LANGUAGE plperlu;

Now that I have all of my supporting perl routines, I can start on the package body. Before I do that though, I want to test my routines. I have a test block that I can execute.

declare
v_txt varchar2(3000);
v_txt2 varchar2(3000);

BEGIN

-- Open a file for write

v_txt := lrc_openfile('temp', 'test4.txt', '>');
dbms_output.put_line('Open Write: ' || v_txt);

-- write some data

v_txt2 := lrc_write(v_txt, 'Guten Tag!', TRUE);
dbms_output.put_line('WriteLine: ' || v_txt2);
v_txt2 := lrc_write(v_txt, 'Good Morning! How are you?', FALSE);
dbms_output.put_line('Write: ' || v_txt2);

-- Close the file

v_txt2 := lrc_closefile(v_txt);
dbms_output.put_line('Close: ' || v_txt);

-- Open a file for read

v_txt := lrc_openfile('temp', 'test4.txt', '<');
dbms_output.put_line('Open Read: ' || v_txt);

-- Read some data

v_txt2 := lrc_read(v_txt);
dbms_output.put_line('Read Line 1: ' || v_txt2);
v_txt2 := lrc_read(v_txt);
dbms_output.put_line('Read Line 2: ' || v_txt2);
v_txt2 := lrc_read(v_txt);
dbms_output.put_line('Read Line 3: ' || v_txt2);

-- Close the file

v_txt2 := lrc_closefile(v_txt);
dbms_output.put_line('Close: ' || v_txt);

END;

You should see as output:

INFO: Open Write: e:/directories/temp/test4.txt
INFO: WriteLine: GLOB(0x98c08cc)

INFO: Write: GLOB(0x98c08cc)
INFO: Close: e:/directories/temp/test4.txt
INFO: Open Read: e:/directories/temp/test4.txt
INFO: Read Line 1: Guten Tag!

INFO: Read Line 2: Good Morning! How are you?

INFO: Read Line 3
INFO: Close: e:/directories/temp/test4.txt

EDB-SPL Procedure successfully complete

Now, the last and final step is to create functions and procedures, in the utl_file schema, that mimics the Oracle supplied package.

I will create these in the same order that I created my perl routines.

The fopen function takes exactly the same parameters as defined in the package specification above:

CREATE OR REPLACE FUNCTION utl_file.FOPEN(
    location     IN VARCHAR2,
    filename     IN VARCHAR2,
    open_mode    IN VARCHAR2,
    max_linesize IN INTEGER DEFAULT 1)
RETURN utl_file.file_type
IS
    v_fh utl_file.file_type;
    v_openmode VARCHAR2(10);
BEGIN

    CASE
    WHEN UPPER(open_mode) = 'R' THEN v_openmode := '<';
    WHEN UPPER(open_mode) = 'A' THEN v_openmode := '>>';
    WHEN UPPER(open_mode) = 'W' THEN v_openmode := '>';
    END CASE;

v_fh.file := lrc_openfile( location, filename, v_openmode );
RETURN v_fh;
END;

Notice that besides translating perl style file modes into oracle style file modes, the real gist of the code is just calling the perl routine defined above.

I'm keeping the code in these examples to a minimum. You may want to add additional functionality, particularly additional exception handling.

The next step is the close function:

CREATE OR REPLACE PROCEDURE utl_file.FCLOSE (
    file IN utl_file.FILE_TYPE)
IS
    v_buffer VARCHAR2(2000);
BEGIN
    v_buffer := lrc_closefile(file.file);
END;

The read function:

CREATE OR REPLACE PROCEDURE utl_file.GET_LINE (
    file        IN utl_file.FILE_TYPE,
    buffer      OUT VARCHAR2,
   len         IN INTEGER DEFAULT NULL)
IS
BEGIN
    buffer := lrc_read(file.file);
END;

And finally, both write functions:

CREATE OR REPLACE PROCEDURE utl_file.PUT (
    file      IN utl_file.FILE_TYPE,
    buffer    IN VARCHAR2)
IS
    v_buffer VARCHAR2(2000);
BEGIN
    v_buffer := lrc_write(file.file, buffer, FALSE);
END;

CREATE OR REPLACE PROCEDURE utl_file.PUT_LINE (
    file      IN utl_file.FILE_TYPE,
    buffer    IN VARCHAR2,
  autoflush IN BOOLEAN DEFAULT FALSE)
IS
    v_buffer VARCHAR2(2000);
BEGIN
    v_buffer := lrc_write(file.file, buffer, TRUE);
END;

Now we have the bulk of utl_file support. These are the main routines that I use and with these routines, I could port 95% of all my file manipulations programs.

Here is a test script for this new functionality:

declare
v_txt varchar2(3000);

v_fh utl_file.file_type;
BEGIN

-- Open a file for write
v_fh := utl_file.fopen('temp', 'test5.txt', 'w');

-- write some data
utl_file.put_line(v_fh, 'Guten Tag!');
utl_file.put_line(v_fh, 'Good Morning! How are you?');

-- Close the file
utl_file.fclose(v_fh);

-- Open a file for read
v_fh := utl_file.fopen('temp', 'test5.txt', 'r');

-- Read some data
utl_file.get_line( v_fh, v_txt);
dbms_output.put_line('Read Line 1: ' || v_txt);
utl_file.get_line( v_fh, v_txt);
dbms_output.put_line('Read Line 2: ' || v_txt);
utl_file.get_line( v_fh, v_txt);
dbms_output.put_line('Read Line 3: ' || v_txt);

-- Close the file
utl_file.fclose(v_fh);
END;

If you run this, you should see something very much like:

INFO: Read Line 1: Guten Tag!
INFO: Read Line 2: Good Morning! How are you?
INFO: Read Line 3:
EDB-SPL Procedure successfully complete

I would not recommend using schemas as a replacement for packages. Packages do offer additional functionality that schemas do not. However, when you are porting your applications, it makes sense to be creative when crafting solutions that minimize modifications in your existing code base. In this case, using a schema is appropriate.

You can download this entire code from the code depot. The code is stored in the file chap8_utl_file.sql.

* chap8_utl_file.sql

-- Create the dirtecory table

CREATE TABLE ora_style_directory (
db_dir_name VARCHAR2(30),
os_dir_name VARCHAR2(4000) );

-- Insert a sample record

INSERT INTO ora_style_directory (db_dir_name, os_dir_name)
VALUES ('temp', 'C:/temp/');

-- Create the "package" schema
CREATE SCHEMA utl_file;

-- Create the file handle file type
CREATE TYPE utl_file.file_type AS (file TEXT);

-- Create a perl function to open a file
CREATE OR REPLACE FUNCTION lrc_openfile(TEXT, TEXT, TEXT) RETURNS TEXT AS $$
# Set Directory to Parm 1
$parm1 = $_[0];

    # Select the os directory using the db dir
    $query = "SELECT os_dir_name FROM ora_style_directory
                 where db_dir_name = '$parm1'";

# Execute the query
$cursor = spi_exec_query($query, 1);

# Retrieve the dir name
$foo = $cursor->{rows}[0]->{ os_dir_name };

# Create a variable to hold the second parameter, my file name
$myfile = $_[1];

# Concatenate the directory name to the file name
$myFileHandle = $foo . $myfile;

    # Open the file
    open my $fh, $_[2], $myFileHandle
        or elog(ERROR, qq{Could not open the file "$myFileHandle": $!});

    # Save the file handle to a global (session) variable
    $_SHARED{$myFileHandle} = $fh;

# Return the global session variable name
return $myFileHandle;

$$ LANGUAGE plperlu;

-- Create a perl function to close a file

CREATE OR REPLACE FUNCTION lrc_closefile(TEXT) RETURNS TEXT AS $$
    # store the first parameter, the global session variable name
   # to a variable
    my $tmpfile = $_[0];

    # Close the file
    close( $_SHARED{ $tmpfile } ) or
          elog(ERROR, qq{Could not close the file "$tmpfile": $!});

# Return the handle name
return $tmpfile;
$$ LANGUAGE plperlu;

-- Create a perl function to read a file

CREATE OR REPLACE FUNCTION lrc_read(TEXT) RETURNS TEXT AS $$
    # store the first parameter, the global session variable name
    # to a variable
    my $tmpfile = $_[0];

#point to the file handle
$read_file = $_SHARED{$tmpfile};

# read a line of text
$read_buffer = <$read_file>;

# return the buffer
return $read_buffer ;
$$ LANGUAGE plperlu;

-- Create a perl function to write a file

CREATE OR REPLACE FUNCTION lrc_write(TEXT, TEXT, BOOLEAN) RETURNS TEXT AS $$

    # store the first parameter, the global session variable name
    # to a variable
    my $tmpfile = $_[0];

# Store the text input to a buffer variable
my $prtdata = $_[1];

    # If a newline is requested, do so
    if ($_[2]) {
      print {$_SHARED{$tmpfile} } $prtdata, "\n"; }
   else {
      print {$_SHARED{$tmpfile} } $prtdata }

return $_SHARED{$tmpfile} ;
$$ LANGUAGE plperlu;

-- SPL anonymous block to test the perl code

declare
v_txt varchar2(3000);
v_txt2 varchar2(3000);

BEGIN

-- Open a file for write
v_txt := lrc_openfile('temp', 'test4.txt', '>');
dbms_output.put_line('Open Write: ' || v_txt);

-- write some data
v_txt2 := lrc_write(v_txt, 'Guten Tag!', TRUE);
dbms_output.put_line('WriteLine: ' || v_txt2);
v_txt2 := lrc_write(v_txt, 'Good Morning! How are you?', FALSE);
dbms_output.put_line('Write: ' || v_txt2);

-- Close the file
v_txt2 := lrc_closefile(v_txt);
dbms_output.put_line('Close: ' || v_txt);

-- Open a file for read
v_txt := lrc_openfile('temp', 'test4.txt', '<');
dbms_output.put_line('Open Read: ' || v_txt);

-- Read some data
v_txt2 := lrc_read(v_txt);
dbms_output.put_line('Read Line 1: ' || v_txt2);
v_txt2 := lrc_read(v_txt);
dbms_output.put_line('Read Line 2: ' || v_txt2);
v_txt2 := lrc_read(v_txt);
dbms_output.put_line('Read Line 3: ' || v_txt2);

-- Close the file
v_txt2 := lrc_closefile(v_txt);
dbms_output.put_line('Close: ' || v_txt);

END;

-- SPL procedure to open a file

    CASE
    WHEN UPPER(open_mode) = 'R' THEN v_openmode := '<';
    WHEN UPPER(open_mode) = 'A' THEN v_openmode := '>>';
    WHEN UPPER(open_mode) = 'W' THEN v_openmode := '>';
    END CASE;

v_fh.file := lrc_openfile( location, filename, v_openmode );
RETURN v_fh;
END;

-- SPL procedure to close a file
CREATE OR REPLACE PROCEDURE utl_file.FCLOSE (
    file IN utl_file.FILE_TYPE)
IS
    v_buffer VARCHAR2(2000);
BEGIN
    v_buffer := lrc_closefile(file.file);
END;

-- SPL procedure to read a file

CREATE OR REPLACE PROCEDURE utl_file.GET_LINE (
    file        IN utl_file.FILE_TYPE,
    buffer      OUT VARCHAR2,
    len         IN INTEGER DEFAULT NULL)
IS
BEGIN
    buffer := lrc_read(file.file);
END;

-- SPL procedure to write to a file without line feed
CREATE OR REPLACE PROCEDURE utl_file.PUT (
    file      IN utl_file.FILE_TYPE,
    buffer    IN VARCHAR2)
IS
  v_buffer VARCHAR2(2000);
BEGIN
    v_buffer := lrc_write(file.file, buffer, FALSE);
END;

-- SPL procedure to write to a file with line feed
CREATE OR REPLACE PROCEDURE utl_file.PUT_LINE (
    file      IN utl_file.FILE_TYPE,
    buffer    IN VARCHAR2,
   autoflush IN BOOLEAN DEFAULT FALSE)
IS
    v_buffer VARCHAR2(2000);
BEGIN
    v_buffer := lrc_write(file.file, buffer, TRUE);
END;

-- SPL anonymous block to test the SPL code
declare
v_txt varchar2(3000);
v_fh utl_file.file_type;

BEGIN

-- Open a file for write
v_fh := utl_file.fopen('temp', 'test5.txt', 'w');

-- write some data
utl_file.put_line(v_fh, 'Guten Tag!');
utl_file.put_line(v_fh, 'Good Morning! How are you?');

-- Close the file
utl_file.fclose(v_fh);

-- Open a file for read
v_fh := utl_file.fopen('temp', 'test5.txt', 'r');

-- Close the file
utl_file.fclose(v_fh);

END;

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.