Menu de navegação

[marcelo_rhParticipante]

Boa tarde,

Estou com um problema muito específico.

Tenho um select em utilizando SQL dinâmico e variáveis Bind,
exemplo: v_sql := ‘select c1,c2 from tabela where c1=:1’||case when condition ‘c2=:2’ else ” end;

Desta forma, vou abrir um cursor em seguida:

open cursor for v_sql using v1,v2;

O problema é o seguinte:
Como vocês podem ver, existem casos em que terei apenas uma variável bind no select, desta forma, ao executar o comando “open”, na cláusula “using” não será possível passar os dois valores, pois o cursor aguarda apenas por 1 valor para substituição.

Estou trabalhando desta forma para melhoria de performance, poderia muito bem utilizar as variáveis “v1” e “v2” diretamente na montagem do SQL, porém isto me causaria a perda de performance com o parse que o banco irá efetuar a cada chamada da procedure.

Soluções alternativas utilizadas:

1 – Utilizando um if para executar o comando de open:
if “condition do case no select” then
open cursor for v_sql using v1,v2;
else
open cursor for v_sql using v1;
end if;
–Esta não é uma alternativa boa no meu caso, pois chego a ter 6 variações de combinações possíveis para o “using”, criando a necessidade de 64 if’s consecutivos para solucionar a questão.

2 – Executar o sql e o open através de “execute immediate”.
Neste caso, eu precisaria fazer todas as declarações de variáveis dentro deste execute immediate, o que posso fazer sem problemas.
Porém a minha procedure tem uma série de parâmetros que influenciam nos cases que geram a diferenciação da quantidade de variáveis do comando “using”. Sendo assim, através do execute immediate não é possível utilizar os parâmetros que são declarados na procedure, pois este não os ‘enxergará”.

Se puderem me ajudar, agradeço!!

Qualquer informação adicional que for necessária, eu posso informar, obrigado.

[rmanParticipante]

@marcelo_rh

Eu iria pelo solução 1, só não entendi porque 6 variações e 64 IFs.

Posta o código que você já tem, pelo exemplo genérico fica difícil de ajudar…

[marcelo_rhParticipante]

@rman,
obrigado pela resposta…. é o seguinte:

eu tenho uma tabela de movimentos, por exemplo:
MOVIMENTO ( movimento, campo1, campo2, campo3, campo4, campo5, campo6).

Na minha procedure, eu recebo por parâmetro os 6 campos, que podem vir com informação ou nulos.

Quando estiver nulo, estes não são adicionados à cláusula where. Exemplo de código:

v_sql:=’select movimento from MOVIMENTO
where movimento > :p_mov’ –este é o único parâmetro fixo
||case when p_campo1 is not null then ‘ and campo1 = :p_campo1’ else ” end
…
||case when p_campo6 is not null then ‘ and campo6 = :p_campo6’ else ” end

Bom, agora eu tenho então 64 possibilidades de parâmetros no meu comando SQL: pois são 4 variaveis que podem ser nulas(não entrando no comando) ou podem ter valor e serem filtradas.

Desta forma, ao abrir o meu cursor:
open c_mov for v_sql
using …

Não consigo saber quantos e quais campos terei que adicionar na clásula “using”.

Ficou mais claro?

Obrigado.

[diegolenhardtParticipante]

tenta ver o ref cursor, (cursor dinamico)

http://www.google.com.br/#sclient=psy-a … 40&bih=767

[rmanParticipante]

@marcelo_rh

Sim, agora ficou mais claro, o problema é SQL dinamico + variáveis BIND.

Segue uma solução utilizando o pacote DBMS_SQL:


l_sql := 'INSERT INTO SEARCH_RESULTS SELECT book_id FROM BOOK WHERE 1=1';
IF ( p_title IS NOT NULL ) THEN
l_parm_index := l_parm_index+1;
l_parms(l_parm_index) := p_title;
l_sql := l_sql || ' AND title = :parm' || l_parm_index;
END IF;
l_cursor := dbms_sql.open_cursor;
dbms_sql.parse( l_cursor, l_sql, dbms_sql.native );
IF ( l_parms.COUNT > 0 ) THEN
FOR l_index IN l_parms.FIRST .. l_parms.LAST LOOP
dbms_sql.bind_variable( l_cursor, ':parm' || l_index, l_parms(l_index) );
END LOOP;
END IF;
l_result := dbms_sql.execute( l_cursor );
dbms_sql.close_cursor( l_cursor );


Não tive oportunidade de testar, post depois se deu certo.

[marcelo_rhParticipante]

Me ajudou!!
Muito obrigado..

Tive uma outra solução também encontrada em um livro do ORACLE, assim que puder eu posto aqui também.

[marcelo_rhParticipante]

A solução encontrada no livro do ORACLE não é a melhor a ser seguida, mas funciona perfeitamente:

utilizar sempre todas as variáveis BIND quando colocado o “using” e adicionar ao select, alteranativas verdadeiras quando não deveria utilizar a variável.

Segue exemplo:
v_sql:=’select movimento from MOVIMENTO
where movimento > :p_mov’ –este é o único parâmetro fixo
||case when p_campo1 is not null
then ‘ and campo1 = :p_campo1’
else ‘ and (1=1 or :p_campo1 is null)’
end
…
||case when p_campo6 is not null
then ‘ and campo6 = :p_campo6’
else ‘ and (1=1 or :p_campo6 is null)’
end

Obrigado novamente.

[rmanParticipante]

@marcelo_rh

Analisando melhor, não precisa do 1=1


v_sql:='select movimento from MOVIMENTO
where movimento > :p_mov' --este é o único parâmetro fixo
||case when p_campo1 is not null
then ' and campo1 = :p_campo1'
else ' and :p_campo1 is null'
end
...
||case when p_campo6 is not null
then ' and campo6 = :p_campo6'
else ' and :p_campo6 is null'
end


[Autor]

Posts