Friday, July 15, 2005
DBMS_SQL Introdução
Autor: Ana Claudia
A Dúvida.
Ana, tem dois dias que *QUERO* "falar um pouquinho" sobre dbms_sql, mas é difícil, principalmente sem um problema efetivo ou um pequeno estudo de caso para que se possa desenvolver algo.
Basicamente a Oracle dá duas opções para quem necessita trabalhar com setenças SQL dinâmicas seja DML ou DDL: o DBMS_SQL e o Native Dynamic SQL (mais conhecido como execute immediate). O entedimento da diferença entre os dois métodos é essencial no momento da escolha entre um ou outro.
Eu, particularmente, uso pouco o dbms_sql. Na maioria das vezes utilizo o execute immediate. Tenho usado dbms_sql nos casos onde não conheço o nome das colunas nem os datatypes, por exemplo quando tive que gerar arquivos diretamente de queries, então usei o dbms_sql para conhecer as colunas e fazer dump dos dados no arquivo.
A figura abaixo representa o fluxo de execução do DBMS_SQL.
Sobre as "variaveis" DEFINE_ARRAY e BIND_VARIABLE, na verdade elas não são variáveis e sim procedures do pacote DBMS_SQL.
BIND_VARIABLE procedure é usada para definir valores a uma(s) variavel(is), ou seja, da mesma forma e importância que utilizamos bind variable nas SQLs estáticas devemos utilizar em SQL dinâmicas. Para conceito e importância de bind variable, clique aqui para ler a respeito.
DEFINE_ARRAY procedure assim como DEFINE_COLUMN e DEFINE_COLUMN_LONG são usadas como o INTO do SQL estático, ou seja, elas preparam as variáveis para receber os valores vindos do cursor após a execução.
O exemplo abaixo mostra como funciona o DESCRIBE, porém se achar interessante outros exemplos, fique a vontadade para enviá-los. Na medida do possível tentarei desenvolvê-los.
Repare que na verdade o DESCRIBE serve para o cursor e não somente para uma tabela. Fiz uma mistura entre a tabela EMP e ALL_OBJECTS e a procedure me devolveu todas as colunas envolvidas. Agora depende de suas necessidades para sacar proveito do DBMS_SQL.
A Dúvida.
Oi Marcio
Gosto muito do seu Blog,acho bastante interessante. Estive procurando pelo pacote DBMS_SQL mas nao encontrei nada, entao eu queria te fazer este pedido: fala um pouquinho do pacote DBMS_SQL e das variaveis DEFINE_ARRAY e BIND_VARIABLE. Estou trabalhando em um projeto que tem este pacote e nao estou muito segura pra mexer com ele.
Obrigada
Ana Claudia
Ana, tem dois dias que *QUERO* "falar um pouquinho" sobre dbms_sql, mas é difícil, principalmente sem um problema efetivo ou um pequeno estudo de caso para que se possa desenvolver algo.
Basicamente a Oracle dá duas opções para quem necessita trabalhar com setenças SQL dinâmicas seja DML ou DDL: o DBMS_SQL e o Native Dynamic SQL (mais conhecido como execute immediate). O entedimento da diferença entre os dois métodos é essencial no momento da escolha entre um ou outro.
Eu, particularmente, uso pouco o dbms_sql. Na maioria das vezes utilizo o execute immediate. Tenho usado dbms_sql nos casos onde não conheço o nome das colunas nem os datatypes, por exemplo quando tive que gerar arquivos diretamente de queries, então usei o dbms_sql para conhecer as colunas e fazer dump dos dados no arquivo.
A figura abaixo representa o fluxo de execução do DBMS_SQL.
Sobre as "variaveis" DEFINE_ARRAY e BIND_VARIABLE, na verdade elas não são variáveis e sim procedures do pacote DBMS_SQL.
BIND_VARIABLE procedure é usada para definir valores a uma(s) variavel(is), ou seja, da mesma forma e importância que utilizamos bind variable nas SQLs estáticas devemos utilizar em SQL dinâmicas. Para conceito e importância de bind variable, clique aqui para ler a respeito.
DEFINE_ARRAY procedure assim como DEFINE_COLUMN e DEFINE_COLUMN_LONG são usadas como o INTO do SQL estático, ou seja, elas preparam as variáveis para receber os valores vindos do cursor após a execução.
O exemplo abaixo mostra como funciona o DESCRIBE, porém se achar interessante outros exemplos, fique a vontadade para enviá-los. Na medida do possível tentarei desenvolvê-los.
ops$marcio@ORA10G> create or replace procedure p ( p_stmt in varchar2 )
2 is
3 c number;
4 d number;
5 col_cnt integer;
6 rec_tab dbms_sql.desc_tab;
7 col_num number;
8 procedure print_rec(rec in dbms_sql.desc_rec) is
9 begin
10 dbms_output.put_line( rec.col_name);
11 end;
12 begin
13 c := dbms_sql.open_cursor;
14 dbms_sql.parse(c, p_stmt, dbms_sql.native);
15 d := dbms_sql.execute(c);
16 dbms_sql.describe_columns(c, col_cnt, rec_tab);
17
18 col_num := rec_tab.first;
19 if (col_num is not null) then
20 loop
21 print_rec(rec_tab(col_num));
22 col_num := rec_tab.next(col_num);
23 exit when (col_num is null);
24 end loop;
25 end if;
26
27 dbms_sql.close_cursor(c);
28 end;
29 /
Procedure created.
ops$marcio@ORA10G> show error
No errors.
ops$marcio@ORA10G>
ops$marcio@ORA10G> exec p( 'select * from emp, all_objects' )
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
OWNER
OBJECT_NAME
SUBOBJECT_NAME
OBJECT_ID
DATA_OBJECT_ID
OBJECT_TYPE
CREATED
LAST_DDL_TIME
TIMESTAMP
STATUS
TEMPORARY
GENERATED
SECONDARY
PL/SQL procedure successfully completed.
Repare que na verdade o DESCRIBE serve para o cursor e não somente para uma tabela. Fiz uma mistura entre a tabela EMP e ALL_OBJECTS e a procedure me devolveu todas as colunas envolvidas. Agora depende de suas necessidades para sacar proveito do DBMS_SQL.
