oracle database 11g cap1

Prévia do material em texto

Parte 
i
Fundamentos PL/SQL
CaPítulo 
1
Visão Geral do 
Oracle Pl/SQL
4 Oracle Database 11g PL/SQL Programação
 
ste capítulo introduz o Procedure Language/Structured Query Language (PL/
SQL), explica a história, a arquitetura e a estrutura de bloco do PL/SQL, examina 
e discute as novas características do Oracle 10g e do Oracle 11g. O capítulo é 
dividido nas seguintes seções:
História e seu background ■
Arquitetura ■
Estruturas de bloco básicas ■
Oracle 10g: novas características ■
Oracle 11g: novas características ■
História e Pano de Fundo
O PL/SQL foi desenvolvido pelo Oracle no final dos anos 80. Originalmente, PL/SQL tinha 
capacidade limitada, mas isto mudou no começo dos anos 90. O PL/SQL oferece um banco de 
dados Oracle interpretado pronto e um ambiente de programação de sistema de operação inde-
pendente. SQL statements são originalmente integrados na linguagem PL/SQL, onde é possível 
acionar diretamente PL/SQL através da linha de comando interface SQL*Plus. Ações diretas pa-
recidas podem ser feitas em suas ligações de linguagem de programação externa, como ilustrado 
nos Apêndices C e D.
O Oracle 8 Database introduziu tipos de objetos dentro do banco de dados. Ele transformou 
o banco de dados Oracle de um modelo puramente relacional em um modelo objeto-relacional 
(ou relacional estendido).
Estes tipos tinham valor limitado como coleções de variáveis escalares até se tornarem 
instanciáveis no Oracle 9i, Release 2. A capacidade instanciável de tipos de objeto SQL tornou 
os objetos internos do Oracle compatíveis com C++, Java, ou tipos objetos C#.Tipos de objetos 
SQL são implementados em PL/SQL e descritos no Capítulo 15. 
PL/SQL evoluiu com o advento das capacidades oferecidas de programação completa de 
objeto-orientado pelo Oracle 9i, Release 2. PL/SQL não é mais uma linguagem puramente de 
procedimento. É agora uma linguagem de programação de procedimento e de objeto-orientado.
O Oracle 11g Database também desenvolveu o PL/SQL de uma linguagem interpretada para 
uma linguagem compilada nativamente. Você pode perguntar: “Isto não elimina o benefício de 
uma linguagem de sistema de operação independente?” A resposta a esta questão é: de maneira 
alguma. Agora é possível escrever PL/SQL em uma forma de sistema de operação independente. 
Depois, pode desdobrá-lo e deixar o Oracle gerenciar sua compilação nativa. O Oracle g auto-
matiza o processo para você em plataformas que dão suporte a ele. 
Versões PL/SQL
Versões iniciais PL/SQL não eram seqüenciadas com a versão do banco de dados. Por 
exemplo, o PL/SQL 1.0 enviado com o Oracle 6 Database. PL/SQL 2.x enviado com o 
Oracle 7.x Databases. Começando com o Oracle 8, as versões PL/SQL correspondiam 
com os números liberados pelo banco de dados, como PL/SQL 11.1 no Oracle 11g 
Release 1 Database.
Como também tem sido exigido nos programas externos, PL/SQL é também o principal 
portão de entrada para as bibliotecas externas. O rótulo bibliotecas externas é enganador, visto 
que as bibliotecas Java podem ser armazenadas dentro do banco de dados. Oracle aciona os 
procedimentos externos das bibliotecas externas através do PL/SQL independente de onde estão 
e
Capítulo 1: Visão Geral do Oracle Pl/SQL 5
armazenadas. Os programas PL/SQL servem como wrappers para as bibliotecas externas. Os 
Wrappers são interfaces que mascaram o tipo de conversão entre o banco de dados e os progra-
mas externos. 
Você pode estender a funcionalidade do Oracle 11g Database ao construir funções arma-
zenadas e procedimentos no PL/SQL, C, C++, ou Java. Programas Java podem ser diretamente 
armazenados dentro do Oracle 11g Database em todas as suas versões, exceto na Edição Oracle 
Express. O Capítulo 12 demonstra como construir e executar procedimentos externos. O capítu-
lo 14 cobre como construir e alavancar bibliotecas Java dentro do banco de dados. 
O PL/SQL continua a se desenvolver e tornou-se mais robusto. Isto é excelente para aqueles 
treinados em PL/SQL, assim como a evolução entre as versões Java é excelente para os progra-
madores treinados em Java. A programação PL/SQL apresenta desafios para os novatos desta 
linguagem porque serve a muitos MASTERS no banco de dados Oracle. Na medida em que você 
desenvolve suas habilidades nesta linguagem, aprenderá como usar PL/SQL para resolver até 
mesmo os problemas mais complexos.
A Programação PL/SQL é Magia Negra?
No início, o PL/SQL 1.0 era pouco mais do que uma ferramenta de apresentação. Agora 
a frase CASE em SQL oferece a maior parte daquela funcionalidade. No meio dos anos 
90, os desenvolvedores descreviam a programação PL/SQL 2.x como Magia Negra. Na 
época, este rótulo era apropriado, pois não havia muita literatura sobre a linguagem e 
a disponibilidade de amostras de códigos na web era limitada porque a web ainda não 
existia como hoje. 
Hoje, ainda existem pessoas que vêem o PL/SQL como Magia Negra. Eles também 
são apaixonados em escrever código neutro de banco de dados em Java ou outras lingua-
gens. É politicamente correto falar isto para evitar soluções PL/SQL apesar de suas vanta-
gens. Por que o Oracle PL/SQL é ainda uma Magia Negra para muitos, quando existem 
hoje em dia tantos livros publicados sobre PL/SQL? 
Poderia dizer que são seus cursores, mas eles existem em quaisquer programas co-
nectados através da Oracle Call Interface (OCI) ou Java Database Connectivity (JDBC). 
Se não são os cursores, você poderia dizer que é a sua sintaxe, tipos definidos de 
usuário ou nuances de funções e procedimentos. 
Será que estes são realmente tão diferentes assim das outras linguagens de programação? 
Se a resposta a esta questão for não, acabou de ser iniciado no mundo do PL/SQL. Se 
a resposta foi sim ou se pensa que existe alguma outra mágica na linguagem, ainda não 
foi iniciado. 
E como se tornar um iniciado? A resposta mais atrativa é ler este livro. 
A verdadeira resposta é para desfazer o jargão do Oracle que assombra a linguagem 
PL/SQL. Por exemplo, uma variável é sempre uma variável de algum tipo e uma função 
ou procedimento é sempre uma sub-rotina que gerencia parâmetros formais pela referên-
cia ou valor que pode ou não retornar um resultado como um operand certo. 
Estes tipos de regras simples alimentam a verdade para cada componente nesta 
linguagem.
Arquitetura
A linguagem PL/SQL é uma ferramenta robusta com muitas opções. A PL/SQL permite es-
crever um código uma vez e alavancá-lo no banco de dados mais próximo aos dados. A PL/SQL 
6 Oracle Database 11g PL/SQL Programação
pode simplificar o desenvolvimento de uma aplicação, otimizar execução e melhorar a utiliza-
ção de recursos no banco de dados. 
A linguagem é de programação case-insensitive, como SQL. Isto leva a numerosas direções 
de melhores práticas de formatação. Em vez de repetir aqueles argumentos para um estilo ou 
outro, recomenda que encontre um estilo consistente com seus padrões de organização e aplicá-
lo consistentemente. 
O código PL/SQL neste livro usa letras maiúsculas para palavras comando e minúsculas para 
variáveis, nomes de colunas e chamadas de programas armazenados. 
O PL/SQL foi desenvolvido pelos conceitos de modelagem de programação estruturada, di-
gitação de dados estáticos, modularidade, gerenciamento de exceção e processamento (concor-
rente) paralelo encontrado na programação de linguagem Ada, desenvolvida pelo Departamento 
de Defesa dos Estados Unidos, foi desenhada para dar suporte militar em tempo real e encaixa-
da nos sistemas de segurança críticos, como aqueles em aeronaves e mísseis. A linguagem de 
programação Ada pegou emprestada a sintaxe significante da linguagem e programação Pascal, 
incluindo os operadores de tarefa e de comparação e os delimitadores de cotação-única. 
Estas escolhas também ativaram a inclusão direta dos statements SQL em códigos de bloco 
PL/SQL.Eles eram importantes porque SQl adotava os mesmos operadores Pascal, delimitadores 
string e datatipos escalares declarativos que não mudam o tempo de execução e são conhecidos 
como datatipos fortes, essenciais para a integração do Oracle e as linguagens PL/SQL. 
A PL/SQL aceita tipos de dados dinâmicos, mapeando-os durante o tempo de execução 
contra tipos definidos no catálogo do Oracle 11g Database. A compatibilidade de operadores 
e delimitadores string [em série] significa análise de palavras simplificadas porque os statements 
SQL são nativamente embebidos em unidade de programação PL/SQL. 
NOTA
Os comandos básicos da linguagem de programação Java 
descrevem variáveis escalares que guardam apenas uma coisa 
de cada vez. 
O tempo original de desenvolvimento do PL/SQL fez estas escolhas cuidadosamente. O 
banco de dados Oracle foi recompensado ao longo dos anos por causa destas escolhas. Uma 
escolha que se destaca como uma decisão maravilhosa é deixar você conectar variáveis PL/SQL 
no catálogo do banco de dados. Esta é uma forma de herança de tipo run time. É possível usar 
os pseudo-tipos %TYPE e %ROWTYPE para herdar as variáveis fortemente digitadas definidas no 
catálogo do banco de dados (discutido nos Capítulos 3 e 9). 
Ancorar as variáveis PL/SQL no catálogo de objetos do banco de dados é uma forma efetiva 
de acoplamento estrutural. Elas podem minimizar o número de alterações que precisa fazer em 
seus programas Pl/SQL. No mínimo, limitam a freqüência que você recodifica como resultado 
de mudanças entre os tipos bases, como a mudança de uma VARCHAR2 para uma DATE. Elas 
eliminam também a necessidade de redefinir os tamanhos das variáveis. Por exemplo, você não 
precisa modificar seu código quando uma tabela muda o tamanho do comprimento de uma 
variável de coluna string. 
O Oracle também tomou outras decisões estratégicas quando limitou o número de tipos 
bases SQL e começou um subtyping no catálogo do banco de dados. Ao subtyping, os tipos base 
permitem ao Oracle desenvolver uma árvore de objetos de múltipla hierarquia que continua 
a crescer e amadurecer. O enfoque do objeto orientado contribui e continua a contribuir para 
que o Oracle desenvolvesse o modelo relacional para um modelo objeto relacional (também 
conhecido como o modelo relacional estendido). O PL/SQL tira total vantagem ao subtyping os 
tipos variáveis do SQL. 
A máquina do tempo de execução do PL/SQL existe como um recurso dentro do ambiente 
SQL*Plus. O ambiente SQL*Plus é tanto interativo quanto acessível. Cada vez que conecta o 
Oracle 11g Database, o banco de dados cria uma nova sessão. Nesta sessão, é possível execu-
Capítulo 1: Visão Geral do Oracle Pl/SQL 7
tar statements SQL ou PL/SQL vindos do ambiente SQL*Plus. As unidades do programa PL/SQL 
podem, então, executar os statements SQL ou procedimentos externos, como demonstrado na 
Figura 1.1. 
Os statements SQL podem também acionar as funções ou procedimentos PL/SQL, interagin-
do diretamente com os dados reais. 
A chamada direta para PL/SQL pode ser feita através do Oracle Call Interface (OCI) ou Java 
Database Connectivity (JDBC), que permite alavancar PL/SQL diretamente em suas aplicações 
no banco de dados. Isto é importante porque permite gerenciar o escopo da transação em suas 
unidades armazenadas de programa PL/SQL e simplifica tremendamente as diversas tarefas fre-
quentemente colocadas na camada de abstração das aplicações.
O PL/SQL também dá suporte à construção de statements SQL no run time. Os statements 
run time SQL são SQL dinâmicos podendo usar dois enfoques para o SQL dinâmico: um é o 
Native Dynamic SQL (NDS) e o outro é o pacote DBMS_SQL. O Oracle 11g Database oferece 
novas características NDS e melhora a velocidade de execução. Com esta nova versão, apenas 
necessita usar o pacote DBMS_SQL quando não souber o número de colunas que sua chamada 
(call) SQl dinâmica exige. 
O Capítulo 11 demonstra SQl dinâmico e apresenta ambos os pacotes NDS e DBMS_SQL. 
Agora tem uma visão de alto nível da linguagem PL/SQL. A próxima seção lhe dará uma 
visão geral rápida das estruturas de bloco do PL/SQL.
FIGURA 1-1 Arquitetura de Processamento do Banco de Dados
Procedimentos 
Externos
Sessão Banco de Dados
Ambiente 
SQL Plus*
Dados
Ler 
Input 
Externo
Escrever
 Output 
Externo
Ler 
Input 
Externo
Escrever 
Output 
Externo
Interactive Interactive
Call Response
SQL Statement
Engine
Buffer
PL/SQL
Engine
8 Oracle Database 11g PL/SQL Programação
Estruturas Básicas de Bloco
PL/SQL é uma linguagem de programação em blocos. As unidades do programa podem ser 
blocos nomeados ou não. Blocos não nomeados são conhecidos como blocos anônimos e são 
rotulados durante o livro. 
O estilo de código PL/SQL difere dos usados nas linguagens de programação C, C++ e Java. 
Por exemplo, as chaves não delimitam os blocos na PL/SQL.
Programas de blocos anônimos são efetivos em algumas situações. Você geralmente usa blo-
cos anônimos quando constrói script para gerar dados ou executar atividades de processamento 
de one-time (uma vez). Eles também são efetivos quando quer alojar uma atividade em outra se-
ção de execução de outro bloco PL/SQL. A estrutura básica de bloco anônimo deve conter uma 
seção de execução. Também é possível colocar uma declaração opcional e seção de exceção 
em blocos anônimos. A seguir demonstramos um protótipo de bloco anônimo:
[DECLARE]
declaration_statements
BEGIN
execution_statements
[EXCEPTION]
 exception_handling_statements
END;
/
O bloco de declaração permite definir datatipos, estruturas e variáveis. Definir uma variável 
significa que pode dar um nome a ela e um datatipo. Você pode também declarar uma variável 
dando a ela um nome, um datatipo e um valor, como também definir e projetar um valor ao 
declarar uma variável. 
Alguns tipos de objetos não podem ser definidos como variáveis escopo de modo limita-
do, mas devem ser definidos como tipos no catálogo de banco de dados, como discutido no 
Capítulo 14. 
As estruturas são variáveis compostas, como coleções, estruturas de gravação ou cursores 
de sistema de referência. Também podem ser chamadas de funções, procedimentos ou cursores, 
que agem como pequenas funções. Os cursores têm nomes, assinaturas e um tipo de retorno–as 
colunas output de uma query ou statemente SELECT. A palavra reservada DECLARE começa o 
bloco declaração e a palavra reservada BEGIN o encerra. 
O bloco de execução permite processar dados. O bloco de execução pode conter variáveis, 
desígnios, comparações, operações condicionais e iterações. Além disso, o bloco execução é 
onde você acessa cursores e outras unidades nomeadas de programa. Funções, procedimentos e 
alguns tipos de objetos são unidades nomeadas de programa. Também é possível alojar pro-
gramas de blocos anônimos dentro do bloco de execução. A palavra reservada BEGIN inicia o 
bloco de exceção e a opcional ou a palavra reservada exigida END o encerra. É preciso ter ao 
menos um statement dentro de um bloco de execução. O statement mínimo de bloco anônimo a 
seguir inclui um statement NULL:
BEGIN
 NULL;
END;
/
Isto não faz nada, exceto permitir que a fase compilação complete sem um erro. A compi-
lação em qualquer linguagem inclui uma frase sintática. A falta de um statement no bloco faz 
surgir um erro de frase como demonstrado no Capítulo 5.
Capítulo 1: Visão Geral do Oracle Pl/SQL 9
O bloco de manejo de exceções permite gerenciar exceções. Você pode pegá-los e geren-
ciá-los aí. 
O bloco de exceção permite um processamento alternativo; em muitos casos ele age como 
combinação de um bloco catch (capturar) e finally (finalmente) na programação da linguagem 
Java (ver Apêndice D para mais informações sobre Java). A palavra reservada EXCEPTION inicia 
a seção e a END termina.
DICA
Você tem a mesma regra que exige o mínimode um statement 
para qualquer bloco em um bloco statement condicional 
(como um statement IF) e loops.
Programas de bloco nomeados têm uma ligeira diferença na estrutura do bloco, pois eles são 
armazenados no banco de dados. Também possuem uma seção declaração, a qual é conhecida 
como um cabeçalho (header). O nome, a lista de parâmetros formais e qualquer tipo de retorno 
de blocos nomeados PL/SQL são definidos pelo cabeçalho (header). O nome e a lista de parâ-
metros formais são conhecidos como a assinatura de uma sub-rotina. A área entre o cabeçalho 
e os blocos de execução age como o bloco de declaração para o bloco nomeado. Esta mesma 
regra é verdadeira para os corpos de tipo objeto discutidos no Capítulo 14. A seguir ilustramos 
um protótipo de uma função de bloco nomeado:
FUNCTION function_name
[( parameter1 [IN][OUT] [NOCOPY] sql_data_type | plsql_data_type 
 , parameter2 [IN][OUT] [NOCOPY] sql_data_type | plsql_data_type 
 , parameter(n+1) [IN][OUT] [NOCOPY] sql_data_type | plsql_data_type )]
RETURN [ sql_data_type | plsql_data_type ]
 [ AUTHID {DEFINER | CURRENT_USER}]
 [ DETERMINISTIC | PARALLEL_ENABLED ]
 [ PIPELINED ]
 [ RESULT_CACHE [RELIES ON table_name]] IS
declaration_statements
BEGIN
execution_statements
 [EXCEPTION]
exception_handling_statements
END;
/
O Capítulo 6 discute as regras que governam as funções, que podem se comportar como 
sub-rotinas pass-by-value ou pass-by-reference. Sub-rotinas pass-by-value definem parâmetros 
formais usando apenas um modo IN. Isto significa que a variável passada não pode mudar du-
rante a execução da sub-rotina. 
As sub-rotinas pass-by-reference definem parâmetros formais usando IN e OUT ou apenas 
modos OUT. 
O Oracle 11g continua passando cópias de variáveis ao invés de referências para variáveis, 
a menos que designe uma dica (hint) NOCOPY. 
Os implementos Oracle pass-by-reference comportam-se desta maneira para garantir a inte-
gridade das variáveis dos modos IN OUT. Este modelo garante variáveis sem alteração, a menos 
que uma ação (call) subprograma se complete com sucesso. Este comportamento padrão pode 
ser apagado usando um NOCOPY. 
10 Oracle Database 11g PL/SQL Programação
O Oracle não recomenda o uso do NOCOPY porque, ao usá-lo, pode resultar em mudanças 
parciais em seus valores de parâmetros reais. Finalmente, o banco de dados escolhe se age sobre 
uma dica (hint) ou envia uma referência.
As funções podem solicitar dados usando statements SELECT, mas não podem executar sta-
tements DML, como INSERT, UPDATE, ou DELETE. Todas as outras regras se aplicam às funções 
armazenadas, as mesmas que se aplicam aos blocos anônimos. As funções que definem parâ-
metros formais ou tipos de retorno que usam datatipos PL/SQL não podem ser acionadas através 
de uma linha de comando SQL. Entretanto, é possível acionar funções que usam datatipos SQL 
através da linha de comando SQL. 
O valor padrão do AUTHID é DEFINER, conhecido como definidor de direitos. Isso significa 
que qualquer pessoa com privilégios para executar programas armazenados o faz com os mes-
mos privilégios que o usuário da conta definiu. 
O alternativo CURRENT_USER permite àqueles com privilégios de execução acionar o pro-
grama armazenado e executá-lo apenas contra seus dados do esquema/usuário. Isto é conhecido 
como direitos invoker e descreve o processo de acionar uma fonte comum de programa em 
frente a contas individuais e dados. 
É recomendável evitar usar a cláusula DETERMINISTIC quando as funções dependerem das 
variáveis dos estados de nível de sessão. As cláusulas DETERMINISTIC se adaptam melhor a 
índices baseados em função e views materializados (materialized views). 
A cláusula PARALLEL_ENABLE deve ser ativada para funções que planeja acionar a partir 
das cláusulas SQL que poderiam ser usados em capacidade query paralelas. É bom olhar de 
perto esta cláusula para usos em data warehouse. 
A cláusula PIPELINED oferece uma melhora no desempenho quando funções retornam às 
coleções, como tabelas alojadas ou VARRAYs. Melhoras no desempenho também serão notadas 
quando retornar dos cursores do sistema de referência usando a cláusula PIPELINED.
A cláusula RESULT_CACHE indica uma função que é escondida apenas uma vez na SGA e 
está disponível através das seções. Ela é nova no Oracle 11g Database. Funções através da seção 
funcionam apenas com modo IN de parâmetros formais. 
O Capítulo 6 contém os detalhes de implementação sobre estas cláusulas e oferece exem-
plos mostrando como usá-los. 
A seguir ilustramos um protótipo de um procedimento de bloco nomeado:
PROCEDURE procedure_name
( parameter1 [IN][OUT] [NOCOPY] sql_data_type | plsql_data_type, 
parameter2 [IN][OUT] [NOCOPY] sql_data_type | plsql_data_type, 
parameter(n+1) [IN][OUT] [NOCOPY] sql_data_type | plsql_data_type )
[ AUTHID {DEFINER | CURRENT_USER}]
declaration_statements
BEGIN
execution_statements
[EXCEPTION]
exception_handling_statements
END;
/
O Capítulo discute as regras que governam os procedures agindo como funções em muitas 
maneiras, mas não podem retornar um datatipo. Isto significa que não pode usá-los como 
operandos direitos. 
Diferente de funções, os procedures devem ser acionados pelos blocos PL/SQL. Os Procedu-
res podem tanto solicitar os dados quanto manipulá-los. Procedures também são as sub-rotinas 
de fundação para valores, passando de/para linguagens externas como, por exemplo, C, C++, 
Java e PHP.
Esta seção apresenta e discute as estruturas básicas das unidades do programa PL/SQL. 
Capítulo 1: Visão Geral do Oracle Pl/SQL 11
As seções seguintes revisarão as características mais recentes no Oracle 10g Database e as 
novas características no Oracle 11g Database.
Novas Características do Oracle 10g
Diversas mudanças foram introduzidas no Oracle 10g Database. Nem todas estavam disponíveis 
quando a edição anterior foi escrita porque não foram enviadas até o segundo lançamento do 
banco de dados. 
Novas características PL/SQL introduzidas no Oracle 11g incluem:
 ■ Pacotes embutidos
 ■ Avisos de tempo de compilação
Comportamentos de número datatipo ■
Um compilador PL/SQL otimizado ■
 ■ Expressões regulares
Alternativas de ■ citação
Coleção de operadores ■
 ■ Busca de Erros Stack
Encapsulamento de programas PL/SQL armazenados ■
As subseções cobrem características recentes introduzidas no Oracle 10 g. Elas também 
cruzam referência Oracle 11g Database–características relacionadas que serão discutidas mais 
tarde neste capítulo.
Pacotes Embutidos (Built-in)
Começando com o Oracle 10g Release 2, é possível ter acesso a diversos pacotes embuti-
dos, novos ou melhorados. Três que merecem ser mencionados aqui são:
 ■ DBMS_SCHEDULER Substitui o DBMS_JOB embutido e oferece nova funciona-
lidade para planejamento e execução de trabalhos em lote.
 ■ DBMS_CRYPTO Agora inclui a habilidade para codificar e decodificar grandes 
objetos e para dar suporte à globalização através de ajustes de múltiplos carac-
teres.
 ■ DBMS_MONITOR Oferece uma busca de suporte API e acumulação estatística 
feita por sessões.
Avisos de Compile-Time 
Começando com o Oracle 10g Release 1, é possível ter uma idéia do desempenho de seus 
programas PL/SQL através da ativação do parâmetro PLSQL_WARNINGS em suas instâncias de 
desenvolvimento. Ajuste isto para uma sessão ou o banco de dados. O anterior é a prática reco-
mendada pelo fato do overhead (elevado) imposto sobre o banco de dados. 
Para ajustar este parâmetro use o seguinte comando:
ALTER SESSION SET plsql_warnings = ‘enable:all
12 Oracle Database 11g PL/SQL Programação
Compilação Condicional
Começando com Oracle 10g Release 2, é possível usar compilação condicional. Esta permite 
incluir lógica de purificação ou lógica de propósitos especiais que funcionam apenas quando as 
variáveis nível-sessão estão ajustadas. 
O comandoseguinte ajusta uma variável PL/SQL tempo de compilação DEBUG igual a 1:
ALTER SESSION SET PLSQL_CCFLAGS = ‘debug:1’;
Este comando ajusta uma variável de compilação de tempo PL/SQL DEBUG igual a 1. 
Note que uma flag de tempo de compilação pode ser escrita em maiúsculo ou minúsculo, 
como também ajustar as variáveis flag tempo-compilação para verdadeiro ou falso de forma que 
elas ajam como variáveis Boolean. Quando quer ajustar mais de uma flag de compilação condi-
cional, é necessário usar a seguinte sintaxe:
ALTER SESSION SET PLSQL_CCFLAGS = ‘name1:value1 [, 
name(n+1):value(n+1) ]’;
Os parâmetros de compilação condicional são armazenados como nome e pares de valor 
no parâmetro do banco de dados PLSQL_CCFLAG. O programa a seguir usa as diretrizes $IF, 
$THEN, $ELSE, $ELSIF, $ERROR e $END que criam um bloco de código de compilação 
condicional:
BEGIN
$IF $$DEBUG = 1 $THEN
dbms_output.put_line(‘Debug Level 1 Enabled.’);
$END
END;
/
Blocos de código condicional diferem dos códigos de blocos normais if-then-else (se-então-
outro). Mais notavelmente, a diretriz $END fecha o bloco ao invés de um END IF e ponto e 
vírgula como discutido no Capítulo 4. Note também que o símbolo $$ denota uma variável de 
compilação condicional PL/SQL.
As regras que governam compilação condicional são ajustadas pelo programa analisador 
SQL. Não é possível usar compilação condicional em objetos tipos SQL. Esta limitação também 
se aplica a tabelas alojadas e VARRAYs (tabelas escalares). 
Compilação condicional difere em funções e procedimentos. O comportamento muda se a 
função ou procedimento tiver uma lista de parâmetro formal. A compilação condicional pode 
ser usada após a abertura de parênteses de uma lista parâmetro formal, como
CREATE OR REPLACE FUNCTION conditional_type
(magic_number $IF $$DEBUG = 1 $THEN SIMPLE_NUMBER $ELSE NUMBER $END)
RETURN NUMBER IS
BEGIN
RETURN magic_number;
END;
/
Como alternativa, pode usá-las após a palavra-chave AS ou IS nas funções parâmetros ou 
procedimentos, que também podem ser usados dentro da lista de parâmetros formais e após o 
AS ou IS nas funções parâmetro ou procedimentos. 
Compilação condicional pode ocorrer apenas após a palavra-chave BEGIN nos triggers e nas 
unidades de programa de bloco anônimas. 
Capítulo 1: Visão Geral do Oracle Pl/SQL 13
Por favor note que não pode encapsular um placeholder ou uma variável bind dentro de um 
bloco de compilação condicional.
O Capítulo 4 contém exemplos usando técnicas de compilação condicional.
Comportamento do Número Datatipo
Iniciando com Oracle 10g Release 1, o banco de dados agora usa máquina aritmética para BI-
NARY_INTEGER, INTEGER, INT, NATURAL, NATURALN, PLS_INTEGER, POSTIVE, POSITIVEN 
e SIGNTYPE. Isto significa que eles agora usam a mesma resolução que o datatipo BINARY_IN-
TEGER.
Em versões anteriores do banco de dados, estes trabalhavam como o NUMBER datatipo e 
usavam a mesma biblioteca matemática C. 
As novas versões destes datatipos podem ser comparadas contra o infinito ou NaN (não um 
número). 
O lado ruim desta mudança é que agora eles usam precisão numérica e não decimal. As 
aplicações do financial devem continuar a usar o datatipo NUMBER por esta razão. 
Uma precisão simples BINARY_FLOAT e precisão dupla BINARY_DOUBLE são também for-
necidas no Oracle 10g Database. Elas são ideais para computações matemáticas ou científicas.
Compilador PL/SQL Otimizado
Começando com Oracle 10g Release 1, o banco de dados agora otimiza sua compilação PL/
SQL. Isto é ajustado de forma padrão e se aplica tanto ao código-p interpretado quanto ao 
código PL/SQL compilado nativamente. Você pode modificar isto ou cancelar a agressividade 
do otimizador reajustando o parâmetro PLSQL_OPTIMIZE_LEVEL. A Tabela 1-1 qualifica os três 
valores possíveis para o parâmetro. É possível desativar a sessão otimização usando: 
ALTER SESSION SET plsql_optimize_level = 0;
Também pode ajustar o nível de otimização para um procedimento. 
O protótipo é: 
ALTER PROCEDURE some_procedure COMPILE plsql_optimize_level = 1;
Após ter ajustado o nível de otimização, poderá usar a cláusula REUSE SETTINGS para utili-
sar novamente o ajuste anterior, como o 
ALTER PROCEDURE some_procedure COMPILE REUSE SETTINGS;
Embora seja informativo, deve geralmente deixá-lo no default (padrão). 
O código otimizado sempre funciona mais rápido que um código não otimizado.
NOTA
O PLSQL_OPTIMIZE_LEVEL deve estar ajustado em 2 ou mais 
alto para inling subprograma automático para ocorrer no Ora-
cle 10g ou no 11g Database.
14 Oracle Database 11g PL/SQL Programação
 
Nível de Otimização Significado da otimização
0 Sem otimização.
1 Otimização Moderada, pode eliminar códigos 
sem uso ou exceções.
2 (default) Otimização agressiva, pode rearranjar a fonte 
do fluxo do código.
 
TABELA 1-1 Valores PLSQL_OPTIMIZE_LEVEL Disponíveis
Expressões Regulares
Iniciando com Oracle 10g Release 1, o banco de dados agora suporta um ajuste de funções de 
expressões regulares. O acesso é feito do mesmo modo que nos statements SQL ou nas unidades 
de programa PL/SQL. Elas são:
REGEXP_LIKE ■ Busca uma string para um padrão compatível de expressão 
regular.
REGEXP_INSTR ■ Busca a posição inicial de um padrão compatível de uma 
expressão regular.
REGEXP_SUBSTR ■ Busca uma substring usando um padrão compatível de 
expressão regular.
REGEXP_REPLACE ■ Substitui uma substring usando um padrão compatível de 
expressões regulares.
Estas são funções ponderosas. O Apêndice E discute, analisa e demonstra expressões regula-
res usando as funções de expressões regulares do Oracle 11g Database.
Citação Alternativa
Começando com o Oracle 10g Release 1, o banco de dados agora permite substituir a familiar 
aspas por outro símbolo de citação. Isto é útil quando tiver uma quantidade de apóstrofes em 
uma string que exige, individualmente, citação anterior com outra citação simples. O velho jeito 
seria o seguinte:
SELECT ‘It’’s a bird, no plane, no it can’’t be ice cream!’ AS phrase
FROM dual;
A nova maneira é
SELECT q’(It’s a bird, no plane, no it can’t be ice cream!)’ AS phrase
FROM dual;
Ambos produzem o seguinte output:
PHRASE 
------------------------------------------------
It’s a bird, no plane, no it can’t be ice cream!
[N.T.: Ele é um pássaro, não um avião, nem pode ser um sorvete!]
Capítulo 1: Visão Geral do Oracle Pl/SQL 15
Existem oportunidades para usar a sintaxe mais nova e economizar tempo, mas a maneira 
antiga também continua funcionando, pois é mais facilmente compreendido e portátil.
Conjunto de Operadores
Começando com o Oracle 10g, Release 1, o banco de dados agora suporta conjunto de opera-
dores para tabelas alojadas que incluem os operadores MULTISET EXCEPT, MULTISET INTER-
SECT, MULTISET UNION e MULTISET UNION DISTINCT. 
MULTISET UNION comporta-se como o operador familiar UNION ALL. Ele retorna duas 
cópias de tudo, na interseção entre dois sets e uma cópia dos complementos relativos. 
O MULTISET UNION DISTINCT funciona como o operador UNION. Ele retorna uma cópia 
de tudo, executando um tipo de operação incremental. O capítulo 7 discute estes operadores 
como também as coleções.
Stack Tracing de Erros
Iniciando com o Oracle 10g Release 1, é possível finalmente formatar stack traces, que produ-
zem uma lista de erros de chamada inicial para o local onde o erro foi lançado. A função usada 
é: DBMS_UTILITY.FORMAT_ERROR_BACKTRACE para produzir um stack trace. Também pode 
acionar o FORMAT_CALL_STACK ou FORMAT_ERROR_STACK do mesmo pacote para traba-
lhar com as exceções lançadas. A seguir temos um exemplo simples:
DECLARE
local_exception EXCEPTION;
FUNCTION nested_local_function
RETURN BOOLEAN IS
retval BOOLEAN := FALSE;
BEGIN
RAISE local_exception;
RETURN retval;
END;
BEGIN
IF nested_local_function THEN
dbms_output.put_line(‘Noraised exception’);
END IF;
EXCEPTION
WHEN others THEN
dbms_output.put_line(‘DBMS_UTILITY.FORMAT_CALL_STACK’);
dbms_output.put_line(‘------------------------------’);
dbms_output.put_line(dbms_utility.format_call_stack);
dbms_output.put_line(‘DBMS_UTILITY.FORMAT_ERROR_BACKTRACE’);
dbms_output.put_line(‘-----------------------------------’);
dbms_output.put_line(dbms_utility.format_error_backtrace);
dbms_output.put_line(‘DBMS_UTILITY.FORMAT_ERROR_STACK’);
dbms_output.put_line(‘-------------------------------’);
dbms_output.put_line(dbms_utility.format_error_stack);
END;
/
16 Oracle Database 11g PL/SQL Programação
Este script produz o seguinte output:
DBMS_UTILITY.FORMAT_CALL_STACK
------------------------------
----- PL/SQL Call Stack -----
object line object
handle number
name
20909240 18 anonymous block
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
-----------------------------------
ORA-06512: at line 7
ORA-06512: at line 11
DBMS_UTILITY.FORMAT_ERROR_STACK
-------------------------------
ORA-06510: PL/SQL: unhandled user-defined exception
Provavelmente achará FORMAT_ERROR_BACKTRACE o mais útil. Ele captura a linha onde 
o primeiro erro ocorreu no topo e, depois, move para trás através de ligações até chegar ligação 
inicial. Números de linha e nomes de programas são exibidos juntos quando blocos nomeados 
estão envolvidos no mesmo evento stack. 
O Capítulo 5 contém mais sobre gerenciamento de erro.
Wrapping Programas PL/SQL Armazenados
Começando com o Oracle 10g Release 2, o banco de dados agora suporta a habilidade de 
wrap ou ofuscar seus programas PL/SQL armazenados. 
Isto é feito usando o pacote DBMS_DDL do procedimento CREATE_WRAPPED. Isto pé usa-
do da seguinte forma:
BEGIN
dbms_ddl.create_wrapped(
‘CREATE OR REPLACE FUNCTION hello_world RETURN STRING AS ‘
||’BEGIN ‘
||’ RETURN ‘’Hello World!’’; ‘
||’END;’);
END;
/
Após criar a função, você pode solicitar isso usando a seguinte formatação de coluna 
SQL*Plus e query:
SQL> COLUMN message FORMAT A20 HEADING “Message”
SQL> SELECT hello_world AS message FROM dual;
Message
--------------------
Hello World!
É possível descrever a função para inspecionar sua assinatura e tipo retorno:
SQL> DESCRIBE hello_world
FUNCTION hello_world RETURNS VARCHAR2
Capítulo 1: Visão Geral do Oracle Pl/SQL 17
Qualquer tentativa de inspecionar suas operações detalhadas irá produzir um efeito ofus-
cado. Teste isto através de querying a função implementada, armazenada na coluna TEXT da 
tabela USER_SOURCE, como a seguir:
SQL> COLUMN text FORMAT A80 HEADING “Source Text”
SQL> SET PAGESIZE 49999
SQL> SELECT text FROM user_source WHERE name = ‘HELLO_WORLD’;
O seguinte output é retornado:
FUNCTION hello_world wrapped
a000000
369
abcd
. . . et cetera . . .
Esta é uma ferramenta muito útil para esconder os detalhes da implementação de olhos 
curiosos. Nós voltaremos a este tópico no Apêndice F.
Oracle 11g Novas Características
Novas características PL/SQL introduzidas no Oracle 11g incluem:
 ■ Inlining subprograma automático
Uma declaração continue ■
Uma função cross-session PL/SQL result cache ■
 ■ Melhoramentos no Dynamic SQL
Mixado, nomeado e ■ notação posicional SQL calls
Um conjunto de conexões multiprocesso ■
Um PL/SQL Hierarchical Profiler ■
O PL/SQL Native Compiler agora gera código nativo ■
 ■ PL/Scope
Melhoramentos nas expressões regulares ■
A ■ SIMPLE_INTEGER datatipo
Chamadas de seqüência direta nos statements SQL ■
Estes melhoramentos são brevemente discutidos nas seguintes subseções: o Capítulo 3 cobre 
o datatipo SIMPLE_INTEGER. 
O Capítulo 4 discute o statement contínuo. 
O capítulo 6 demonstra a função cross-sessão PL/SQL result cache e as chamadas de nota-
ção posicional mixadas e nomeadas. 
Inlining de subprograma automatic e o PL/SQL Native Compiler são vistos no Capítulo 9. 
O Capítulo 16 discute o desenvolvimento de aplicações na web e o conjunto de conexão de 
multiprocesso. Encontrará também mais informação sobre a Expressão Regular, PL/SQL Hierar-
chical Profiler e PL/Scope nos Apêndices E, G e H, respectivamente.
18 Oracle Database 11g PL/SQL Programação
Inlining Subprograma Automático
Inlining um subprograma substitui a chamada para subprogramas externos com uma cópia do 
subprograma. Isto quase sempre melhora o desempenho do programa. 
Você poderia instruir o compilador para inline subprogramas usando a diretriz compilado-
ra PRAGMA INLINE no PL/SQL começando com o Oracle 11g Database. Você deve ajustar o 
PRAGMA quando tiver o parâmetro PLSQL_OPTIMIZE_LEVEL ajustado para 2. 
Vamos dizer que tenha uma função ADD_NUMBERS armazenada em um esquema; 
então instrua uma unidade programa PL/SQL para inline uma chamada para a função ADD_
NUMBERS. 
Isto seria muito útil quando chama a função ADD_NUMBERS em um loop, como neste 
exemplo:
CREATE OR REPLACE PROCEDURE inline_demo
(a NUMBER 
, b NUMBER ) IS
PRAGMA INLINE (add_numbers,’YES’);
BEGIN
FOR i IN 1..10000 LOOP
dbms_output.put_line(add_function(8,3));
END LOOP;
END;
/
O banco de dados automatiza escolhas inlining quando ajusta o parâmetro PLSQL_OPTIMI-
ZE_LEVEL para 3. Isto geralmente o livra de identificar quando ele é apropriado para inline uma 
função de chamadas. 
Todavia, estas são apenas recomendações para o compilador. É recomendado que deixe o 
motor otimizar seu código durante a compilação.
Declaração Continue
O declaração CONTINUE foi finalmente adicionado à linguagem PL/SQL. Alguns podem ter 
emoções mistas. Existem opiniões de que a declaração contínuo leva a uma programação menos 
que optimal, mas geralmente ele simplifica as estruturas loop. 
A declaração CONTINUE sinaliza um final imediato para uma interação loop e retorna para 
a primeira declaração no loop. O bloco anônimo a seguir ilustra isto usando um declaração 
contínue quando o índice loop é um número par:
BEGIN
FOR i IN 1..5 LOOP
dbms_output.put_line(‘First statement, index is [‘||i||’].’);
IF MOD(i,2) = 0 THEN
CONTINUE;
END IF;
dbms_output.put_line(‘Second statement, index is [‘||i||’].’);
END LOOP;
END;
/
A função MOD retorna um zero ao dividir qualquer número par, então, a segunda linha 
nunca é impressa porque a declaração CONTINUE aborta o resto do loop. 
Capítulo 1: Visão Geral do Oracle Pl/SQL 19
Mais informação sobre o uso deste comando está no Capítulo 4. 
O Apêndice J descreve a função MOD.
A Função Cross-Session PL/SQL Result Cache
A função cross-session PL/SQL result cache [resultado escondido] é um mecanismo para repartir 
funções freqüentemente acessadas no SGA entre sessões. 
Antes do Oracle 11g Database, cada chamada para uma função com os mesmos parâmetros 
reais ou valores run time era cache uma vez por sessão. O único trabalho a mais para esta fun-
cionalidade exigiria que codificasse os métodos de acesso. Você designa um dos seguintes result 
cache [resultados escondidos]:
RESULT_CACHE clause
ou
RESULT_CACHE RELIES_ON(table_name)
A cláusula RELIES_ON coloca uma limitação no result cache. Qualquer mudança para a tabela 
mencionada invalida a função, como também quaisquer funções, procedimentos ou vistas que 
dependem da função. 
O resultado quando chama a função pela primeira vez não é diferente do resultado non-
cache. Da mesma forma, o cache irá envelhecer fora do SGA quando não mais for chamado 
ativamente pelas sessões.
Melhoramentos SQL Dinâmico
SQL dinâmico ainda tem duas variedades no Oracle 11g Database. 
Também tem o Native Dynamic SQL, conhecido como NDS e o pacote DBMS_SQL embutido. 
Ambos foram aperfeiçoados nesta nova versão.
SQL Dinâmico Nativo
No Oracle 11g, SQL dinâmico native agora suporta statements dinâmicos maiores que 32KB 
pela aceitação de CLOB, que é acessado depois, em lugar de um statement SQL usando a 
seguinte sintaxe:
OPEN cursor_name FOR dynamic_string;
A stringdinâmica pode ser uma CHAR, VARCHAR2 ou CLOB. Ela não pode ser um Unicode 
NCHAR ou NVARCHAR2. Isto remove a restrição anterior que limitava o tamanho das strings 
construídas dinamicamente.
O Pacote DBMS_SQL Embutido (Built-in)
Diversas alterações têm melhorado a utilidade do pacote DBMS_SQL. Começando com o 
Oracle 11g, agora é possível usar todos datatipos NDS-suportáveis, como também o procedu-
re PARSE para trabalhar com statements maiores que 32KB. Isto é feito usando-se um datatipo 
CLOB, que substituiu o trabalho anterior, onde era usado uma tabela de datatipos VARCHAR2 
(tipicamente VARCHAR2A ou VARCHAR2S). 
Felizmente o pacote DBMS_SQL continua a suportar o contorno, mas deve refletir sobre 
mover adiante e encontrar uma solução melhor. 
20 Oracle Database 11g PL/SQL Programação
DBMS_SQL adicionou duas novas funções: as funções TO_REFCURSOR e TO_CURSOR_
NUMBER, que permitem transferir cursor referência para cursores e vice versa. 
Existem naturalmente palavras de sabedoria sobre o uso destes. Abra o cursor ou o sistema 
de referência cursor antes de usá-los e, após executá-los, não poderá acessar as velhas estrutu-
ras. 
Basicamente, o código reinstala a referência interativa a partir do cursor para o sistema de 
referência cursor ou a partir dos sistemas de referência cursor para o cursor. 
Por último, mas certamente não menos importante, é possível, agora, executar operações 
biding bulk frente a tipos de coleção definidas pelo usuário. 
Tipos de coleção podem ser tabelas escalares. Anteriormente estava restrito aos tipos defini-
dos pelas especificações dos pacotes DBMS_SQL.
Nome Mixado e Notação de Posição de Chamadas
O Oracle 11g Database traz mudanças na maneira de nomear e no trabalho de notação posicio-
nal em ambos SQL e PL/SQL. 
Na verdade, eles agora trabalham da mesma maneira tanto no SQL quanto no PL/SQL. Isto corri-
ge este antigo costume no banco de dados no Oracle.
PL/SQL Chamadas
Antes você tinha duas escolhas: poderia listar todos os parâmetros em suas ordens posicionais 
ou endereçar alguns para todos os parâmetros pela referência nomeada. Agora pode usar 
referência posicional, a referência nomeada ou uma mistura de ambos. 
A função a seguir permitirá experimentar os diferentes enfoques. A função aceita três parâ-
metros opcionais e retorna a soma dos três números.
CREATE OR REPLACE FUNCTION add_three_numbers
( a NUMBER := 0, b NUMBER := 0, c NUMBER := 0 ) RETURN NUMBER IS
BEGIN
RETURN a + b + c;
END;
/
As primeiras três subseções mostram como chamar usando notação posicional, nomeada e 
mixada. Nelas você determina os parâmetros reais para cada um dos parâmetros formais defini-
dos pela assinatura da função.
Também pode excluir um ou mais valores porque todos os parâmetros formais estão defini-
dos como opcionais, o que significa que têm valores padrão. Isto é feito na subseção “Notação 
Excludente.”
Notação Posicional
É possível chamar a função usando notação posicional através de
BEGIN
dbms_output.put_line(add_three_numbers(3,4,5));
END;
/
Notação Nomeada
Chame a função usando notação nomeada através de
Capítulo 1: Visão Geral do Oracle Pl/SQL 21
BEGIN
dbms_output.put_line(add_three_numbers(c => 4,b => 5,c => 3));
END;
/
Notação Mixada
Chame a função usando uma mistura de ambas as notações posicionada e nomeada através de
BEGIN
dbms_output.put_line(add_three_numbers(3,c => 4,b => 5));
END;
/
Existe uma restrição quanto à notação mixada. Todas as notações posicionais de parâmetros 
reais devem ocorrer primeiro e na mesma ordem em que foram definidas pela função assinatura. 
Não é possível determinar um valor posicional após um valor nomeado.
Notação Excludente
Como foi mencionado, também pode excluir um ou mais Parâmetros reais quando os parâme-
tros formais estão definidos como opcional. Todos os parâmetros na função ADD_THREE_NUM-
BERS são opcionais. O exemplo a seguir passa um valor para o primeiro parâmetro pela referên-
cia posicional e o terceiro parâmetro pela referência nomeada:
BEGIN
dbms_output.put_line(add_three_numbers(3,c => 4));
END;
/
Quando optar por não fornecer um parâmetro real, ela age como se estivesse passando um 
valor nulo. Isto é conhecido como notação excludente. Esta tem sido a recomendação de anos 
para listar as variáveis últimas na função e assinaturas do procedimento. 
Agora, pode excluir um ou alguns, mas não todos os parâmetros opcionais. Este é um grande 
avanço, mas tenha cuidado na forma como irá explorá-lo.
Notação Chamada SQL
Antes havia apenas uma escolha, onde era preciso listar todos os parâmetros em suas ordens 
posicionais, pois não poderia usar referência nomeada no SQL. Isto foi corrigido no Oracle 11g; 
agora é possível chamá-los do mesmo jeito que faz de um bloco PL/SQL. 
O exemplo a seguir demonstra notação mixada em uma chamada SQL:
SELECT add_three_numbers(3,c => 4,b => 5) FROM dual;
Como nas versões anteriores, apenas podia chamar funções que tinham somente variáveis 
no modo IN dos statements SQL. Não é possível chamar uma função do SQL quando qualquer 
um de seus parâmetros formais tiver definido apenas como variáveis no modo IN OUT ou OUT. 
Isto porque deve passar uma referência variável quando um parâmetro estiver em um modo 
OUT. Funções retornam uma referência para variáveis do modo OUT passadas como 
parâmetros reais.
22 Oracle Database 11g PL/SQL Programação
Conjunto de Conexão de Multiprocess
Enterprise JavaBeans (EJBs) ficou melhor com a liberação do conjunto de conexão de multi-
process no Oracle 11g Database. Ele é, oficialmente, Database Resident Connection Pooling 
(DRCP). Esta característica permite gerenciar mais conjuntos de conexão capazes de serem 
escalas pelo provedor. Antes disso precisava alavancar conjunto de conexão repartido ou Java 
Servlet de múltiplos threads. 
O conjunto de conexão multiprocess reduziu significantemente a memória de pegada na 
fileira do banco de dados e ela melhora o aumento da capacidade de execução de um computa-
dor de ambas as fileiras do meio e do banco de dados. Uma conexão de banco de dados padrão 
requer 4.4MB de memória real; 4MB é alocada para a conexão física e 400KB para a sessão 
usuário. 
Portanto, 500 conexões concorrentes dedicadas exigiriam aproximadamente 2.2GB de me-
mória real. Um modelo provedor-dividido é mais escalável e exige apenas 600MB de memória 
real para o mesmo número de usuários concorrentes. Oitenta por cento desta memória seria ge-
renciada no Shared Global Area (SGA) do Oracle. Database Resident Connection Pooling escala 
melhor e exigiria apenas 400MB de memória real. Certamente, para aplicação na web, DRCP é 
a solução preferida, especialmente quando usar conexões persistentes OCI8.
Os comportamentos destes modelos ditam suas respectivas escalabilidades. A Figura 1-2 
demonstra graficamente o uso de memória para os três modelos de 500 a 2 mil usuários 
concorrentes.
FIGURA 1-2 Escalabilidade de Conexão
A nova característica é entregue pelo novo pacote embutido DBMS_CONNECTION_POOL. 
Este pacote permite iniciar, parar e configurar parâmetros pooling como, por exemplo, tamanho 
e limite de tempo. Começe a conexão pool como o usuário SYS através do comando a seguir:
SQL> EXECUTE DBMS_CONNECTION_POOL.START_POOL();
Ative seus arquivos tnsnames.ora para suportar a conexão de um poll repartido. O seguinte 
permite conectar um descriptor de conexão pool dividido, desde que substitua um hostname 
correto, um domain, e um Oracle listener port number:
Capítulo 1: Visão Geral do Oracle Pl/SQL 23
ORCLCP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)
 (HOST = hostname.domain)
 (PORT = port_number)
)
(CONNECT_DATA = (SERVER = POOLED)
 (SERVICE_NAME = orcl)
)
)
A chave SERVER no descriptor connect direciona as conexões para o service de conexão 
pooling . É possível apenas usar o poolconexão quando tiver um Oracle 11g Database apoiado 
ou Oracle 11g Client, embora esta característica possa ser backport subsequentemente. O erro a 
seguir acontece quando se tenta conectar com uma versão mais velha ou biblioteca do provedor:
ERROR:
ORA-56606: DRCP: Client version does not support the feature.
A mensagem é sinalizada, vinda do provedor, quando ela falha em criar uma tomada apropria-
da; ela indica que está caindo o pool (conjunto) de requisição de conexão remota.
A Tabela 1-2 lista o dicionário da exibição de dados para conferência dos pools de conexão. 
Você pode usá-los para monitorar conexões ou características de desempenho. 
Para parar o pool de conexão como o usuário SYS use o seguinte comando:
SQL> EXECUTE DBMS_CONNECTION_POOL.STOP_POOL();
Exibição Descrição
DBA_CPOOL_INFO O status, conexões máxima e mínima e o idle timeouts [tempo 
desocupado] estão disponíveis nesta exibição para cada pool de 
conexão.
V$CPOOL_STAT O número de sessões solicitadas, o número de vezes em que uma 
sessão corresponde a uma solicitação encontrada no pool e o tempo 
total de espera por sessão de solicitação estão disponíveis nesta 
exibição.
V$CPOOL_CC_STATS As estatísticas de conexão de nível de classe para cada exemplo do 
pool de conexão.
 
TABELA 1-2 Dicionário das Exibições dos Dados do Pool de Conexão
Parece que a liberação inicial irá suportar apenas um pool de conexão único. O nome do 
pool de conexão é SYS_DEFAULT_CONNECTION_POOL. Também existem três outros procedi-
mentos no pacote DBMS_CONNECTION_POOL para gerenciar o pool de conexão: os procedi-
mentos ALTER_PARAM(), CONFIGURE_POOL() e RESTORE_DEFAULTS(). 
Para alterar um único parâmetro pool de conexão use o procedimento ALTER_PARAM(). 
Quando quiser alterar mais de um para todos parâmetros, use o procedimento CONFIGURE_
POOL(). O procedimento RESTORE_DEFAULTS() reajusta todos os parâmetros pool de conexões 
para seus defaults (padrões). 
Esta nova característica do Oracle 11g Database certamente melhora a escalabilidade [au-
mento da capacidade de execução de um computador] das aplicações da web. É uma caracterís-
24 Oracle Database 11g PL/SQL Programação
tica importante que autoriza a característica de conexão persistente introduzida nas bibliotecas 
OC18 no banco de dados Oracle 10g Release 2.
PL/SQL Profiler Hierárquico 
O profiler hierárquico entregue no Oracle 11g Database permite ver como todos componentes 
em uma aplicação atuam. Isto difere de um profiler não hierárquico (flat) que simplesmente gra-
va o tempo gasto em cada módulo. Os profilers hierárquicos seguem o ciclo de execução vindos 
do programa contido até o mais baixo subprograma.
O PL/SQL Hierarchical Profiler faz o seguinte: ■
Ele informa o perfil de execução dinâmica de seu programa PL/SQL, que está ■
organizado por chamadas de subprogramas.
Ele divide os tempos de execução SQLe PL/SQL e informa-os separadamente. ■
Ele não exige nenhum tipo especial de fonte ou preparação de tempo de compi- ■
lação, como o PRAGMA que é exigido para recomendação inlining .
Ele armazena resultados em um conjunto de tabelas de bancos de dados, os ■
quais pode usar para desenvolver ferramentas de informação ou usar como 
alternativa a ferramenta linha-comando plshprof para gerar apresentações 
HTML simples.
O sumário do nível dos subprogramas inclui informação sobre o número de chamadas de 
subprogramas, tempo gasto em subprogramas ou em suas sbtrees [sub árvores] e informações 
detalhadas entre os módulos. 
O Apêndice G discute como ler e usar o PL/SQL Hierarchical Profiler.
PL/SQL Compilador Nativo Gera Código Nativo
A compilação PL/SQL Native muda no Oracle 11g Database. É diferente das versões anteriores 
onde o PL/SQL foi traduzido primeiro para código C e então compilado; agora é possível compi-
lar diretamente.
O tempo de execução do código final, em alguns casos, é duas vezes mais rápido ou uma 
ordem de magnitude maior. 
O Oracle recomenda que execute todo PL/SQL em modo NATIVE ou INTERPRETED, que 
é o padrão do banco de dados, e módulos PL/SQL são armazenados como texto claro ou texto 
wrapped. 
É possível ver programas armazenados através de querying todos ALL_SOURCE, DBA_
SOURCE ou tabelas de dicionários de dados USER_SOURCE . 
O código modo NATIVE é compilado dentro de uma forma intermediária antes de ser 
reduzido a um código específico da máquina. Uma cópia do código é também armazenada no 
dicionário de dados, enquanto a biblioteca é posicionada em um diretório externo. Você mapeia 
o diretório físico para o diretório virtual definido pelo parâmetro virtual PLSQL_NATIVE_LIBRA-
RY_DIR. 
O código nativamente compilado é vantajoso quando o código de tempo de execução [run 
time] PL/SQL é lento. Isto pode acontecer com o código de intensivo-cumputar, mas geralmente, 
os atrasos na execução são causados pelo processamento de statement SQl. 
O novo PL/SQL Hierarchical Profiler deve ser usado para determinar se existe uma vantagem 
significante que justifique seu esforço de conversão.