EnterpriseDB: SQL Data Types Oracle Tips by Burleson Consulting

Data type support in EnterpriseDB Advanced Server is very robust (see Chapter 3 for details).  In addition to supporting almost all of the Oracle data types, EnterpriseDB also supports all of the PostgreSQL data types.  Not all PostgreSQL data types are compatible with SPL.

Simple data types such as VARCHAR2 or NUMBER are transparently translated to PostgreSQL style data types by the EnterpriseDB server.  The DATE data type, when used in Redwood mode, is automatically translated to a Timestamp data type.

EnterpriseDB does not currently support Oracle style Object types.  EnterpriseDB does support PostgreSQL style object types but those types do not allow OOP style methods and are not compatible with Oracle object types. 

EnterpriseDB, the company, has publicly stated that they intend to support Oracle style objects with methods.  Once they do provide support for those object types an entirely new class of enterprise application will be open to easy migration to EnterpriseDB.  In the meantime, object based applications cannot be automatically migrated. 

While it is not possible to automate the migration, it is possible to use built-in support in EnterpriseDB to make it possible to manually migrate an application.  For example, because EnterpriseDB does not support Object types, it also does not support the XMLType (which is an implementation of an Oracle style object).

It is not that difficult to create a code-based solution, though.  In this example, I will emulate a solution to using SQLX in your application. SQLX is an ANSI standard method for generating XML documents from databases.  Oracle provides a very robust implementation of SQLX.

An example SQLX query might be:

SELECT XMLElement("Employee",
  XMLAttributes(deptno as "deptno", (select dname
                           from dept
                           where dept.deptno = emp.deptno) as "dname"),
  XMLForest( emp.ename,
             emp.empno,
             emp.job ))
FROM emp
HERE ename = 'FORD'

The output from this select would be:

<Employee deptno="20" dname="RESEARCH">
  <ENAME>FORD</ENAME>
  <EMPNO>7902</EMPNO>
  <JOB>ANALYST</JOB>
</Employee>

EnterpriseDB cannot generate this data in this way out of the box.  We can however emulate this functionality by writing some of our own code.

The first thing to do is to create an XMLElement function that does what we want it to do.  Remember when porting an application, you don't have to meet the ANSI standards for that functionality, you just need to support those features that are required to port the application.

In this case, we need a function that has a signature as close as possible to the ANSI function and that will return the data we need.

The XMLElement function takes two parameters.  The first parameter is an alias or name that will be used within an element tag.  Because it is an alias, it is passed in with double quote (") instead of a single quote (').  This will impact the implementation below.

The second parameter is the tag value.  So if we call XMLElement( "name", 'Lewis') we should see XML that looks like <name>Lewis</name>.  Alternatively, if we call it within a SQL statement, we would expect to see data values.

I will create the XML function by using simple SPL:

CREATE OR REPLACE FUNCTION public.xmlelement (
    p_name VARCHAR,
    p_value VARCHAR
    )
RETURN varchar AS
    v_ret_string VARCHAR2(32000);
  BEGIN 

    v_ret_string := '<' || p_name; 

    IF p_value IS NULL
    THEN
      v_ret_string := v_ret_string || ' />';
    ELSE
      v_ret_string := v_ret_string || '>' ||
           p_value ||
           '</' || p_name || '>';
    END IF; 

    RETURN v_ret_string;
  END;

I will call the XMLElement function in Oracle:

SELECT XMLElement ("Employee", ename)
FROM emp
WHERE ename = 'FORD'

I get this result back:

<Employee>FORD</Employee>

I compile the above function in EnterpriseDB and call the new XMLElement function (notice that the double quote is now a single quote):

SELECT XMLElement ('Employee', ename)
FROM emp
WHERE ename = 'FORD'

I get this result back from EnterpriseDB:

<Employee>FORD</Employee>

The original query also contains the XMLForest function.  XMLForest is another SQLX function.  This function accepts a varying list of parameters and creates an XML Document from those parameters.  It uses the parameter name to alias (which names the XML tag) the values.

So in Oracle, when I run this query:

SELECT XMLForest ( emp.ename,
             emp.empno,
             emp.job )
FROM emp
WHERE ename = 'FORD'

I get this output:

<ENAME>FORD</ENAME>
<EMPNO>7902</EMPNO> 

<JOB>ANALYST</JOB>

In EnterpriseDB, I will write another very basic SPL procedure: 

CREATE OR REPLACE FUNCTION XMLFOREST(
    p_attr1 IN VARCHAR2,
    p_label1 IN VARCHAR2,
    p_attr2 IN VARCHAR2 DEFAULT NULL,
    p_label2 IN VARCHAR2 DEFAULT NULL,
    p_attr3 IN VARCHAR2 DEFAULT NULL,
    p_label3 IN VARCHAR2 DEFAULT NULL,
    p_attr4 IN VARCHAR2 DEFAULT NULL,
    p_label4 IN VARCHAR2 DEFAULT NULL,
    p_attr5 IN VARCHAR2 DEFAULT NULL,
    p_label5 IN VARCHAR2 DEFAULT NULL)
  RETURN VARCHAR2
  AS
    v_return_string VARCHAR2(32000);
  BEGIN
    v_return_string := XMLELEMENT( p_label1, p_attr1 ); 

    IF p_attr2 IS NOT NULL
      AND
       p_label2 IS NOT NULL
    THEN
      v_return_string := v_return_string || XMLELEMENT( p_label2, p_attr2 );
    END IF;
 
    IF p_attr3 IS NOT NULL
      AND
       p_label3 IS NOT NULL
    THEN
      v_return_string := v_return_string || XMLELEMENT( p_label3, p_attr3 );
    END IF; 

    IF p_attr4 IS NOT NULL
      AND
       p_label4 IS NOT NULL
    THEN
      v_return_string := v_return_string || XMLELEMENT( p_label4, p_attr4 );
    END IF; 

    IF p_attr5 IS NOT NULL
      AND
       p_label5 IS NOT NULL
    THEN
      v_return_string := v_return_string || XMLELEMENT( p_label5, p_attr5 );
    END IF; 

    RETURN v_return_string;
  END;

This function is slightly more complicated than the XMLElement function but not by much.  Because the XMLForest function accepts a varying number of parameters, I chose to create up to five columns.  You can extend that by adding additional parameters.

In addition, SPL is not able to deduce the column names from within the function.  That data is not available.  Instead of feeling stuck, I chose to slightly alter my query by passing in alias names:

SELECT XMLForest (ename, 'ENAME',
                 to_char(empno), 'EMPNO',
                 job, 'JOB')
  FROM emp
  WHERE ename = 'FORD'

I get this output from EnterpriseDB:

<ENAME>FORD</ENAME>
<EMPNO>7902</EMPNO>
 

<JOB>ANALYST</JOB>

Finally, the original query included an XMLAttribute.  XMLAttribute modifies an XMLElement call by adding attributes to an element.  In the case of the original query, it added deptno and dname. 

I found that I could not easily emulate the behavior of XMLAttribute with pure SPL.  Instead, I chose to use PL/TCLU and create a function using that language.  It is a very simple function:

CREATE OR REPLACE FUNCTION XMLAttributes(text) RETURNS text AS $$
  set val " " 

  spi_exec -array C $1 

  foreach key [array names C] {
    if {$key == ".tupno"} {
    } else {
    set val "$val$key=\"$C($key)\" " }
  } 

  return $val
$$ LANGUAGE pltclu; 

This function accepts a SQL statement (as a string) as input and outputs an attribute list.  For example, if I run this query (Note XMLAttribute by definition in SQLX is meant to be embedded within an XMLElement call):

SELECT  XMLAttributes('select deptno, dname
                         from dept
                         where dept.deptno = ' || emp.deptno)
FROM emp
WHERE ename = 'FORD'

In EnterpriseDB, I get this output:

  dname="RESEARCH" deptno="20" 

The original query and output:

SELECT XMLElement ("Employee",
  XMLAttributes(deptno as "deptno", (select dname
                           from dept
                           where dept.deptno = emp.deptno) as "dname"),
  XMLForest ( emp.ename,
             emp.empno,
             emp.job ))
FROM emp
WHERE ename = 'FORD'

The output from this select would be:

<Employee deptno="20" dname="RESEARCH">
  <ENAME>FORD</ENAME>
  <EMPNO>7902</EMPNO>
  <JOB>ANALYST</JOB>
</Employee>

Can now be changed and run in EnterpriseDB as:

SELECT XMLElement ('Employee',
       XMLAttributes('select dname, deptno
                        from dept
                        where deptno = ' || deptno),
       XMLForest ( ename, 'ENAME',
                  to_char(empno), 'EMPNO',
                  job, 'JOB'))
FROM emp
WHERE ename = 'FORD'

The output is:

<Employee dname="RESEARCH" deptno="20" >
  <ENAME>FORD</ENAME>
  <EMPNO>7902</EMPNO>
  <JOB>ANALYST</JOB>
</Employee>

This is exactly what I wanted and expected.  If you look at the two queries closely, you can see the obvious differences.  Those differences are fairly minor and once the rules are known, could be automated.

The short story here is that while EnterpriseDB may not support every feature you might need, it provides many tools to work around those deficiencies.  Over time, EnterpriseDB will support more and more of these features.  At that time, you can choose to migrate your custom code to the newly enhanced functionality or stay with your custom code.

You may download the entire set of functions (XMLElement, XMLForest and XMLAttribute) from the code depot.  These functions are in the file chap8_sqlx.sql.

* chap8_sql.sql

-- Create an SPL function that emulates SQL/X xmlElement

CREATE OR REPLACE FUNCTION public.xmlelement (
    p_name VARCHAR,
    p_value VARCHAR
    )
RETURN varchar AS
    v_ret_string VARCHAR2(32000);

  BEGIN

    v_ret_string := '<' || p_name; 

    IF p_value IS NULL
    THEN
      v_ret_string := v_ret_string || ' />';
    ELSE
      v_ret_string := v_ret_string || '>' ||
           p_value ||
           '</' || p_name || '>';
    END IF;

    RETURN v_ret_string;
  END;

-- Test a function that emulates SQL/X xmlElement

SELECT XMLElement ('Employee', ename)
FROM emp
WHERE ename = 'FORD'

-- Create an SPL function that emulates SQL/X xmlForest

CREATE OR REPLACE FUNCTION XMLFOREST(
    p_attr1 IN VARCHAR2,
    p_label1 IN VARCHAR2,
    p_attr2 IN VARCHAR2 DEFAULT NULL,
    p_label2 IN VARCHAR2 DEFAULT NULL,
    p_attr3 IN VARCHAR2 DEFAULT NULL,
    p_label3 IN VARCHAR2 DEFAULT NULL,
    p_attr4 IN VARCHAR2 DEFAULT NULL,
    p_label4 IN VARCHAR2 DEFAULT NULL,
    p_attr5 IN VARCHAR2 DEFAULT NULL,
    p_label5 IN VARCHAR2 DEFAULT NULL)
  RETURN VARCHAR2
  AS
    v_return_string VARCHAR2(32000);
  BEGIN
    v_return_string := XMLELEMENT( p_label1, p_attr1 );
 
    IF p_attr2 IS NOT NULL
      AND
       p_label2 IS NOT NULL
    THEN
      v_return_string := v_return_string || XMLELEMENT( p_label2, p_attr2 );
    END IF;
 
    IF p_attr3 IS NOT NULL
      AND
       p_label3 IS NOT NULL
    THEN
      v_return_string := v_return_string || XMLELEMENT( p_label3, p_attr3 );
    END IF; 

    IF p_attr4 IS NOT NULL
      AND
       p_label4 IS NOT NULL
    THEN
      v_return_string := v_return_string || XMLELEMENT( p_label4, p_attr4 );
    END IF; 

    IF p_attr5 IS NOT NULL
      AND
       p_label5 IS NOT NULL
    THEN
      v_return_string := v_return_string || XMLELEMENT( p_label5, p_attr5 );
    END IF;

    RETURN v_return_string;  

  END;

-- Test a function that emulates SQL/X xmlForest

SELECT XMLForest (ename, 'ENAME',
                 to_char(empno), 'EMPNO',
                 job, 'Job')
  FROM emp
  WHERE ename = 'FORD'

-- Create a TCL function that emulates SQL/X xmlAttribute

CREATE OR REPLACE FUNCTION XMLAttributes(text) RETURNS text AS $$
  set val " " 

  spi_exec -array C $1 

  foreach key [array names C] {
    if {$key == ".tupno"} {
    } else {
    set val "$val$key=\"$C($key)\" " }
  } 

  return $val
$$ LANGUAGE pltclu;

-- Test a function that emulates SQL/X xmlAttribute

SELECT  XMLAttributes('select deptno, dname
                         from dept
                         where dept.deptno = ' || emp.deptno)
FROM emp
WHERE ename = 'FORD';

-- Test all of the SQL/X functions

SELECT XMLElement ('Employee',
       XMLAttributes('select dname, deptno
                        from dept
                        where deptno = ' || deptno),
       XMLForest ( ename, 'ENAME',
                  to_char(empno), 'EMPNO',
                  job, 'JOB'))
FROM emp
WHERE ename = 'FORD';



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