Para melhor visualização, recomendo resolução de no mínimo 1024 x 768 e navegador Mozilla Firefox


quarta-feira, 15 de julho de 2009

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

Por Eduardo Legatti

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 ...

Google+

5 comentários:

Júlio C. Corrêa disse...

Cara,li os artigos sobre testes com o Postgre + Oracle.Parabéns pelos artigos.São mesmos ótimos, interessantes e prático-teórico(existe essa expressão?) !
Continue desta forma Eduardo,muita gente aprende com os seus artigos.

Abs,

Júlio César,

Eduardo Legatti disse...

Olá Júlio,

Obrigado pelos elogios. Já quanto ao termo prático-teórico, eu sei que o vocábulo "teoria" é usado normalmente em oposição à prática, mas a expressão existe sim. ;-)

Até mais ...

Legatti

Edson Lidorio disse...

Olá Eduardo!

Esse Postgres Plus Advanced Server ele é free?

Eduardo Legatti disse...

Olá Edson,

Em princípio, o mesmo é gratuito para download e avaliação do produto, mas de acordo com a página Database Subscription Plans and Pricing no site da EnterpriseDB, no caso do Postgres Plus Advanced Server, a licença "Free" está limitada para armazenar no máximo 6GB de informações em uma máquina com 1 CPU e até 1GB de memória RAM, contudo não está explícito se este limite é físico.

Abraços e até mais ...

David Ricardo disse...

Sensacional, como sempre um ótimo material Eduardo, meus sinceros parabéns pela qualidade dos post's.

Forte abraço!!!!

David

Postagens populares