Testando algumas das compatibilidades do Postgres Plus Advanced Server com o Oracle

Olá,

Para quem teve a oportunidade de ler o artigo de Abril de 2009 intitulado de "Mercado de banco de dados mais acirrado ...", pôde perceber que o Postgres Plus Advanced Server (PAS) trouxe de forma embutida (através de um catálogo adicional), algumas funcionalidades compatíveis com o Oracle, como suporte ao Oracle PL/SQL, algumas views de dicionários de banco de dados USER/ALL/DBA_*, Public Synonyms, Sequences (CURRVAL, NEXTVAL), functions, packages, triggers, tipos de dados (CHAR, VARCHAR, VARCHAR2, NUMBER, BLOB, CLOB, DATE), pseudo-coluna ROWNUM, a tabela DUAL, entre algumas outras funcionalidades.

Apesar de não ser um artigo "diretamente" direcionado ao banco de dados Oracle, mas, já que envolve algumas funcionalidades do próprio Oracle, então irei demonstrar alguns testes que timidamente estou começando a realizar, afim de testar tais compatibilidades. Irei utilizar a versão 8.3 R2 (8.3.0.106) do Postgres PAS para realizar algumas operações básicas.

Vale a pena salientar que no caso do PostgreSQL, o mesmo utiliza um catálogo de dicionários de dados próprio (pg_catalog) e um outro catálogo conforme definido pelo padrão ANSI-92 (information_schema), na qual o Oracle não segue. Fazendo uma pequena comparação, um mesmo catálogo de dicionário de dados no Oracle, está sobre propriedade do usuário SYS através de inúmeras views de dicionário de dados. Uma outra forma de se obter informações de metadados no Oracle é utilizando o comando [DESC]RIBE no SQL*Plus ou através da package DBMS_METADATA disponível desde a versão Oracle 9i.

Portanto, no caso do Postgres PAS, foi criado um catálogo chamado de Redwood(sys) para fornecer algumas das views de dicionário de dados do Oracle. Em se tratando de algumas incompatibilidades conhecidas entre o PostgreSQL e o Oracle, posso citar que o pacote de compatibilidade com o Oracle visa estender algumas funcionalidades do Postgres como:

• Permitir o uso de views de dicionário de dados USER/ALL/DBA_* mais comuns;
• Permitir a execução de códigos escritos em PL/SQL estendendo o uso de stored procedures, entre outros;
• Permitir o uso do operador (+) em junções externas (outer joins);
• Permitir o uso de comandos como CREATE USER, DROP USER, ALTER USER, entre outros;
• Permitir o uso do tipo de dado VARCHAR2 pois no PostgreSQL apenas existe o tipo VARCHAR;
• Permitir o uso do tipo de dado NUMBER pois no PostgreSQL apenas existe o tipo NUMERIC;
• Permitir o uso do tipo de dado CLOB pois no PostgreSQL apenas existe o tipo TEXT;
• Permitir o uso do tipo de dado BLOB pois no PostgreSQL o mesmo é implementado através dos tipos BYTEA e OID;
• Permitir suporte à função DECODE, já que PostgreSQL isso é realizado através de expressões CASE;
• Permitir o uso da função NVL já que PostgreSQL temos apenas o suporte da função COALESCE;
• Permitir o uso das funções SYSDATE e SYSTIMESTAMP no PostgreSQL;
• Permitir o uso do operador MINUS já que PostgreSQL implementa o operador EXCEPT;
• Permitir o uso da pseudo-coluna ROWNUM já que o PostgreSQL implementa o uso da cláusula LIMIT e OFSET;
• Permitir o uso das funções (CURRVAL, NEXTVAL) no uso de seqüências utilizando a mesma sintaxe do Oracle;
• Permitir o uso da tabela DUAL já que no PostgreSQL a mesma não é necessária;
• entre outras funcionalidades ...

Agora indo para a prática, após instalado e configurado o Postgres PAS, foi criado o banco de dados chamado de BD01 como demonstrado na figura abaixo. A partir de agora, irei utilizar o utilitário EDB*Plus fornecido junto com o Postgres PAS, na qual poderemos perceber mais abaixo, ser nada mais do que um "clone magro" do SQL*Plus ...

-- Conectando com o super-usuário
C:\>edbplus enterprisedb/manager@localhost:5444/bd01
Connected to EnterpriseDB 8.3.0.106 (localhost:5444/bd01) AS enterprisedb

EDB*Plus: Release 8.3 (Build 18)
Copyright (c) 2008-2009, EnterpriseDB Corporation.  All rights reserved.

-- Mostrando o usuário corrente que está logado
SQL> show user
USER is "enterprisedb"


-- Verificando a versão do banco de dados
SQL> select * from v$version;

BANNER
--------------------------------------------------
Oracle Database 10g Edition Release 10.2.0.1.0 - P
PL/SQL Release 10.2.0.1.0 - Production is similar
CORE 10.2.0.1.0 Production is similar

-- Mostrando a data atual
SQL> select to_char(sysdate,'dd/mm/yyyy') from dual;

TO_CHAR
----------
15/07/2009

-- Criando um usuário/schema de teste
SQL> create user scott identified by tiger;

Role created.

-- Concedendo algumas atribuições
SQL> grant connect,resource,dba to scott;

Role granted.

-- Concetando com o usuário SCOTT
SQL> connect scott/tiger@localhost:5444/bd01;
Disconnected from EnterpriseDB Database.
Connected to EnterpriseDB 8.3.0.106 (localhost:5444/bd01) AS scott

-- Mostrando o usuário corrente que está logado
SQL> show user
USER is "scott"

-- Mostrando as opções disponíveis no EDB*Plus
SQL> show all
autocommit      OFF
colsep          " "
define          "&"
echo            OFF
FEEDBACK ON for 6 row(s).
flush           ON
heading         ON
headsep         "|"
linesize        80
newpage         1
null            " "
pagesize        14
pause           OFF
serveroutput    OFF
spool           OFF
sqlcase         MIXED
sqlprompt       "SQL> "
sqlterminator   ";"
suffix          ".sql"
termout         ON
timing          OFF
verify          ON
USER is         "scott"
HOST is         "localhost"
PORT is         "5444"
DATABASE is     "bd01"
VERSION is      "8.3.0.106"

-- Obtendo informações da estrutura da view DBA_USERS
SQL> desc dba_users

 Name                                     Null?    Data Type
 ---------------------------------------- -------- ---------------------------
 USERNAME                                          CLOB
 USER_ID                                           OID
 PASSWORD                                          VARCHAR2(30)
 ACCOUNT_STATUS                                    VARCHAR2(32)
 LOCK_DATE                                         DATE
 EXPIRY_DATE                                       DATE
 DEFAULT_TABLESPACE                                VARCHAR2(30)
 TEMPORARY_TABLESPACE                              VARCHAR2(30)
 CREATED                                           DATE
 PROFILE                                           VARCHAR2(30)
 INITIAL_RSRC_CONSUMER_GROUP                       VARCHAR2(30)
 EXTERNAL_NAME                                     VARCHAR2(4000)

-- Verificando usuários/schemas disponíveis no banco de dados
SQL> set linesize 80
SQL> select username,password,default_tablespace,account_status from dba_users;

USERNAME             PASSWORD                       DEFAULT_TABLESPACE    ACCOUNT_STATUS
-------------------- ------------------------------ --------------------- --------------
ENTERPRISEDB         md59709ec8c91b5809603a24f09975 pg_default            OPEN
ADAM                 md546d5f32a35e4a038de7d0a7ae3c pg_default            OPEN
SCOTT                md5f5b3b8231a686f681fdee8f66bf pg_default            OPEN

-- Dropando o usuário/schema ADAM
SQL> drop user adam;

Role dropped.

-- Criando uma tabela de teste (T1)
SQL> create table t1 (a number, b varchar2(10), c date, d clob);

Table created.

-- Adicionando uma chave primária à tabela T1
SQL> alter table t1 add constraint pk_t1 primary key (a);

Table altered.

-- Verificando a estrutura da tabela T1
SQL> set linesize 80
SQL> desc t1

 Name                          Null?    Data Type
 ----------------------------- -------- ----------------------
 A                             NOT NULL NUMBER(38)
 B                                      VARCHAR2(10)
 C                                      DATE
 D                                      CLOB

-- Selecionando dados da view USER_CONSTRAINTS
SQL> select schema_name,constraint_name,table_name
  2  from user_constraints
  3  where table_name='T1';

SCHEMA_NAME   CONSTRAINT_NAME    TABLE_NAME
------------- ------------------ -------------------
SCOTT         PK_T1              T1

-- Realizando um procedimento PL/SQL simples
SQL> set serveroutput on
SQL> begin
  2    for i in 1..10
  3    loop
  4     insert into t1 values (i,to_char(sysdate,'mm/yyyy'),sysdate,'teste CLOB');
  5     dbms_output.put_line(i);
  6    end loop;
  7  end;
  8  /
1
2
3
4
5
6
7
8
9
10

EDB-SPL Procedure successfully completed.

SQL> commit;

COMMIT completed.

-- Selecionando registros da tabela T1
SQL> select * from t1 order by a;

         A B          C                             D
---------- ---------- ----------------------------- -----------
         1 07/2009    2009-07-15 10:13:17           teste CLOB
         2 07/2009    2009-07-15 10:13:17           teste CLOB
         3 07/2009    2009-07-15 10:13:17           teste CLOB
         4 07/2009    2009-07-15 10:13:17           teste CLOB
         5 07/2009    2009-07-15 10:13:17           teste CLOB
         6 07/2009    2009-07-15 10:13:17           teste CLOB
         7 07/2009    2009-07-15 10:13:17           teste CLOB
         8 07/2009    2009-07-15 10:13:17           teste CLOB
         9 07/2009    2009-07-15 10:13:17           teste CLOB
        10 07/2009    2009-07-15 10:13:17           teste CLOB

10 rows retrieved.

-- Testando a funcionalidade da pseudo-coluna ROWNUM
SQL> select * from t1 where rownum <=5;

         A B          C                             D
---------- ---------- ----------------------------- ----------
         1 07/2009    2009-07-15 10:13:17           teste CLOB
         2 07/2009    2009-07-15 10:13:17           teste CLOB
         3 07/2009    2009-07-15 10:13:17           teste CLOB
         4 07/2009    2009-07-15 10:13:17           teste CLOB
         5 07/2009    2009-07-15 10:13:17           teste CLOB


SQL> create table t2 as select * from t1;

Table created.

SQL> insert into t2 values (11,'teste',sysdate,'teste CLOB');

1 row INSERTED.

-- Testando a funcionalidade do operador MINUS
SQL> select * from t2
  2  minus
  3  select * from t1;

         A B          C                             D
---------- ---------- ----------------------------- ----------
        11 teste      2009-07-15 10:20:25           teste CLOB

-- Testando a funcionalidade da função DECODE
SQL> select decode(a,1,'um',2,'dois',3,'tres','outro') from t1;

DECODE
--------------------------------------------
um
dois
tres
outro
outro
outro
outro
outro
outro
outro

10 rows retrieved.

-- Testando a funcionalidade da função NVL
SQL> select nvl(null,0) from dual;

       NVL
----------
         0

-- Testando a funcionalidade da função NVL2
SQL> select nvl2(null,0,1) from dual;

       NVL2
-----------
          1

-- Testando a funcionalidade da função TRANSLATE, apesar do PostgreSQL já ter suporte
-- para a mesma. O SQL abaixo foi executado no pgAdmin III pelo fato de eu ainda não
-- ter conseguido habilitar caracteres especiais no EDB*Plus
SQL> select upper (
  2          translate( 'Exceção, Única, Rápido',
  3                     'ÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜáçéíóúàèìòùâêîôûãõëü',
  4                     'ACEIOUAEIOUAEIOUAOEUaceiouaeiouaeiouaoeu'))
  5  from   dual;

UPPER
--------------------------------------------
EXCECAO, UNICA, RAPIDO

SQL> drop table t2;

Table dropped.

-- Testando a compatibilidade em relação à sequências de banco de dados
SQL> create sequence seq_doc increment by 1 start with 1 nomaxvalue minvalue 0 nocache;

Sequence created.

SQL> select seq_doc.nextval from dual;

             NEXTVAL
--------------------
                   1

SQL> select seq_doc.nextval from dual;

             NEXTVAL
--------------------
                   2

SQL> select seq_doc.currval from dual;

             CURRVAL
--------------------
                   2

Agora irei testar a criação de procedimentos armazenados (stored procedures), pois no PostgreSQL esse conceito, e normalmente esses objetos, são criados criados como funções (functions)

SQL> create table t3 (id number);

Table created.

SQL> create or replace procedure sp_insere (p_num number) as
  2    begin
  3      declare
  4         v_num number;
  5      begin
  6       v_num := p_num;
  7       insert into t3 values (v_num);
  8      end;
  9    end;
 10  /

Procedure created.

SQL> desc sp_insere

PROCEDURE SP_INSERE
 Argument Name                    Type                   In/Out Default?
 -------------------------------- ---------------------- ------ --------
 P_NUM                            NUMBER                 IN


SQL> exec sp_insere(100);

EDB-SPL Procedure successfully completed.

SQL> select * from t3;

        ID
----------
       100

Em relação ao famoso operador (+) do Oracle para construção de junções externas entre tabelas, e, já que o PostgreSQL suporta apenas o padrão ANSI LEFT/RIGHT JOIN, irei abaixo realiar um teste com o operador (+):

SQL> create table t4 (id number);

Table created.

SQL> create table t5 (id number);

Table created.

SQL> insert into t4 values (1);

1 row INSERTED.

SQL> insert into t4 values (2);

1 row INSERTED.

SQL> insert into t5 values (1);

1 row INSERTED.

-- Testando SQL join padrão ANSI
SQL> select t4.id,t5.id from t4 left outer join t5 on (t4.id = t5.id);

        ID         ID
---------- ----------
         1          1
         2

-- Testando SQL join padrão Oracle com o sinal (+)
SQL> select t4.id,t5.id  from t4,t5 where t4.id = t5.id(+);

        ID         ID
---------- ----------
         1          1
         2

Bom, após a realização de testes de alguns comandos básicos, deparei-me com uma de minhas funções de banco de dados que funciona perfeitamente no Oracle, mas que apresentou problemas ao ser executada no Postgres Plus Advanced Server. Percebi que os procedimentos PL/SQL armazenados no Postgres PAS são validados apenas quando os mesmos são executados. O problema que encontrei está relacionado ao tipo de dado retornado na realização de uma conta matemática como demonstrado abaixo:

-- Realizando o teste no Oracle 10g via SQL*Plus
ORACLE10g> select 2.2*100 VAL from dual;  
   
       VAL  
----------  
       220  
   
-- Realizando o teste no Postgres PAS via EDB*Plus
SQL> select 2.2*100 val from dual;  
  
       VAL  
----------  
     220.0  

Portanto, sem entrar em muitos detalhes, a lógica na minha função não espera o resultado com "0" (zeros à direita) e para que seja retornado o mesmo valor retornado pelo Oracle, precisei realizar um CAST explícito como demonstrado abaixo:

SQL> select cast(2.2*100 as integer) val from dual;  
  
        VAL  
-----------  
        220  
  

Apenas para ficar bem claro como isso impactou no resultado de retorno da função de banco de dados, abaixo está um trecho da função na qual o resultado precisaria ser exatamente como este:

-- Realizando no Oracle 10g via SQL*PLUS
ORACLE10g> select lpad(to_char(2.2*100),14,'0') from dual;  

LPAD(TO_CHAR(2  
--------------  
00000000000220  

No Postgres Plus Advanced Server o tipo retornado não foi do tipo INTEGER:

-- Realizando no Postgres PAS via EDB*PLUS  
SQL> select lpad(to_char(2.2*100),14,'0') from dual;  
  
LPAD  
--------------------------------------------
000000000220.0  

Como demonstrado abaixo, precisei realizar explicitamente um CAST de forma a alterar o tipo de dado retornado para o tipo INTEGER:

-- Realizando no Postgres PAS via EDB*PLUS  
SQL> select lpad((cast(2.2*100 as integer)),14,'0') from dual;  
  
LPAD  
--------------------------------------------
00000000000220  

SQL> exit
Disconnected from EnterpriseDB Database.

No mais, ainda precisarei realizar muitos testes, mas acredito que a compatiblidade atual do Postgres Plus Advanced Server com algumas funcionalidades do Oracle ainda irão me surpreender muito ...