Unit tests for PL/SQL - new tool

It's been a long time I don't post on this blog.
I've been creating a tool for unit tests called: Unit Test Tool for PL/SQL.

Please find it on the link below with installing manual:

http://utt-plsql.blogspot.com

Enjoy it!!

Pipelined Functions

Who never needed to use a memory table in a query instead of using a loop to get the values?

There is a kind of functions that allow you to do it.
The pipelined functions doesn't have the "return" statement.
Here is a example of a package that contains a record, a table for this record and a pipelined fuction:

I'm using Oracle XE 11G r2, it's available for version 10G too.
Before starting, let's create some test tables and populate it:

create table users (id number, name varchar2(40));
alter table users add constraint pk_users primary key (id);

create table permissions (id number, descr varchar2(40));
alter table permissions add constraint pk_permissions primary key (id);

create table user_permissions (id number, user_id number, id_permission number);
alter table user_permissions add constraint pk_user_permissions primary key (id);
alter table user_permissions add constraint fk_user_permissions_user foreign key (user_id) references users(id);
alter table user_permissions add constraint fk_user_permissions_perm foreign key (id_permission) references permissions(id);

insert into users (id, name) values (1,'Marcelo');
insert into users (id, name) values (2,'Seth');

insert into permissions (id,descr) values (1, 'Developer');
insert into permissions (id,descr) values (2, 'Analyst');
insert into permissions (id,descr) values (3, 'Tester');
insert into permissions (id,descr) values (4, 'Manager');

insert into user_permissions (id, user_id, id_permission) values (1,1,1);
insert into user_permissions (id, user_id, id_permission) values (2,1,2);
insert into user_permissions (id, user_id, id_permission) values (3,2,4);
insert into user_permissions (id, user_id, id_permission) values (4,2,2);
insert into user_permissions (id, user_id, id_permission) values (5,2,1);

Now we are ready to start:

create or replace package examples is

-- Author : Marcelo Cure
-- Created : 25/10/2012 11:30:49

--declare a simple record
type r_permissions is record (id number,
descr varchar2(40));
--declare a table of the record type just declared above type tb_permissions is table of r_permissions;

function get_user_permissions (p_user_id in number) return tb_permissions
pipelined;
end examples;
/
create or replace package body examples is
--returns a table of a specific record + pipelined option
function get_user_permissions (p_user_id in number) return tb_permissions pipelined is
--get user permissions
cursor c_permissions is
select b.id, b.descr
from user_permissions a,
permissions b
where a.user_id = p_user_id
and a.id_permission = b.id;

--declare the pipelined table
v_tb_users r_permissions;
v_id number;
v_descr varchar2(40);
begin

open c_permissions;
loop
fetch c_permissions into v_id, v_descr;
exit when c_permissions%notfound;

v_tb_users.id := v_id;
v_tb_users.descr := v_descr;
--inserts a row into the pipelined table
pipe row (v_tb_users);

end loop;
end get_user_permissions;
end examples;
/

Finally to test it, you can use try the following code:

select *
from table(examples.get_user_permissions(1));--passing user_id 1 as parameter

UTL_FILE

Então aqui estou novamente, desta vez vou escrever sobre a package UTL_FILE de uma forma simples e prática.

Esta package é responsável pela manipulação de arquivos, pode ser comparada, de certa forma, com a TEXT_IO do Forms/Reports.
Vou dar exemplos de criação de arquivos, leitura, manipulação, renomear, fazer cópias, etc.

Inicialmente, devemos ter um diretório criado no banco. No meu caso, estou desenvolvendo no Oracle XE configurado na minha máquina. Segue um script para setar um diretório.

create or replace directory my_dir
as 'C:\oraclexe\app\oracle\product\10.2.0\server\cure';

Após ter o seu diretório criado e configurado, podemos começar a utilizar o UTL_FILE. Segue alguns exemplos.

Criar um arquivo populando ele com duas linhas.

declare
  myfile utl_file.file_type;
begin
  myfile := utl_file.fopen('my_dir','teste.txt','W',32000); --este último parâmetro seta o tamanho da linha
  utl_file.put_line(myfile,'conteúdo linha 1',true);
  utl_file.put_line(myfile,'conteúdo linha 2',true);
  utl_file.fclose(myfile);
end;

Ler o arquivo e retornar o conteudo de cada linha.

declare
  myfile utl_file.file_type;
  conteudo varchar2(32000);
  conteudo_aux varchar2(32000);
begin
  myfile := utl_file.fopen('my_dir', 'teste.txt', 'R', 32000);
  loop
    begin
      utl_file.get_line(myfile, conteudo_aux, 32000);
    exception
    when no_data_found then
      exit;
    end;
    conteudo := conteudo||conteudo_aux||chr(10);
  end loop;
  dbms_output.put_line(conteudo);
  utl_file.fclose(myfile);
end;

Renomear um arquivo, com a possibilidade de alterar o diretório.

begin
utl_file.frename(src_location => 'my_dir',
                                src_filename => 'teste.txt',
                                dest_location => 'my_dir',
                                dest_filename => 'teste_renamed.txt',
                                overwrite => true);
end;

Remove um arquivo.

begin
  utl_file.fremove('my_dir','teste.txt');
end;

Copia o conteúdo de um arquivo para um novo arquivo, pode ser delimitado as linhasa ser copiadas, por exemplo, da linha 1 até a linha 12, veja o exemplo:

begin
utl_file.fcopy(src_location => 'my_dir',
                            src_filename => 'teste_renamed.txt',
                            dest_location => 'my_dir',
                            dest_filename => 'new_file.txt',
                            start_line => 1,
                            end_line => 12);
end;

A linha de comando "utl_file.fflush(myfile);" faz a função de salvar os dados gravados pelo put_line no arquivo, funciona como se fosse o save de algum editor de texto. o fflush é utilizado para gravar os dados durante o processo que popula. Os dados sao gravados definitivamente quando o comando utl_file.fclose(myfile); é executado.

O comando utl_file.fseek(... serve para se posicionar em um lugar específico do arquivo, ele recebe três parametros:

file => myfile,
absolute_offset => posição a ser procurada,
relative_offset => o número de bytes(posições) que deve ser procurado para frente ou para trás, considerando que: se o número for positivo procura pra frente, se for negativo, procura pra trás)

...
utl_file.fseek(myfile,
                        10,
                        -12);
...

Se tiverem dúvidas, coloquem no comentário.

DBMS_RANDOM

O primeiro assunto a ser tratado é sobre a package dbms_random.
Assim como o nome sugere, é uma package utilizada para gerar valores aleatórios, tanto números quanto strings. Vou mostrar exemplos simples e práticos sobre a aplicação da package.

Verifique abaixo:


Busca um valor aleatório entre 1 e 10, por default, o intervalo é entre 0 e 38.

select dbms_random.value(1,10)
from dual;

Gera uma string de 20 posições apartir de um valor de entrada 'A'.

declare
   str_1 varchar2(20);
begin
   str_1 := dbms_random.string('A', 20);
   dbms_output.put_line('str_1: '||str_1);
end;

Busca valores aleatórios.

SELECT dbms_random.normal
FROM dual;

Busca um rownum aleatório pelo order by.

select object_name
from (select object_name
           from sys.all_objects
           order by dbms_random.value)
where rownum = 1;

O seed é um valor gerado para iniciar o algorítmo que gera números aleatórios, para resetar o seed, basta executar a seguinte linha de comando:

exec dbms_random.seed(681457802);

Busca valores aleatórios a partir de um número.

declare
   ret pls_integer;
   init number(30);
BEGIN
   init := to_char(sys_extract_utc(current_timestamp), 'hh24miss');
   dbms_random.initialize(init);
   for i in 1..10 loop
      ret := dbms_random.random;
      dbms_output.put_line(ret);
      init := ret;
   end loop;
   dbms_random.terminate;
end;

Primeiro post

Este blog tem como objetivo ensinar e dar exemplos de funções e packages da Oracle que não são muito utilizadas e conhecidas por uma boa parte dos desenvolvedores.

Tive a idéia de fazer este blog em 2009 quando procurava algum material sobre LDAP e não achei muitos exemplos e explicações claras.

Estou aberto a questões, sugestões e dicas de post.

Sejam bem vindos.