Menu de navegação

[rwarstatParticipante]

Pessoal,

Estou fazendo uma procedure onde preciso passar um parâmetro que poderá conter um ou mais valores.

CREATE OR REPLACE PROCEDURE pr_teste (p_cd_paciente IN VARCHAR2)
AS
v_result NUMBER (1);
BEGIN
SELECT 1
INTO v_result
FROM sol_exame se
WHERE se.cd_paciente IN (p_cd_paciente);
END;

O parâmetro eu defini como sendo varchar2, mas o campo na tabela é um number.
Defini o parâmetro como varchar2 pensando em pode passar uma lsita de códigos, separados por vírgula. Mas quando excuto dá um erro de “invalid number”.

Alguém tem alguma sugestão de como poderia fazer isso?

Grato,
Roberto

[Leonardo LitzParticipante]

Olá, sugiro que você crie o parametro como uma lista de valores numericos:

1º Crie um type publico:


CREATE OR REPLACE TYPE TYP_TB_OBJ_NUMBER IS TABLE OF NUMBER;


2º Altere os parametros de suas procedure para ser do mesmo tipo do type criado acima:


CREATE OR REPLACE PROCEDURE pr_teste (p_cd_paciente IN TYP_TB_OBJ_NUMBER )


3º Agora, altere o select de sua procedure para buscar todos os valores da lista:

SELECT 1
INTO v_result
FROM sol_exame se
WHERE se.cd_paciente IN (SELECT *
FROM TABLE(CAST(p_cd_paciente AS TYP_TB_OBJ_NUMBER)));


Sua procedure dever ficar assim:

CREATE OR REPLACE PROCEDURE pr_teste (p_cd_paciente IN TYP_TB_OBJ_NUMBER)
AS
v_result NUMBER (1);
BEGIN
SELECT 1
INTO v_result
FROM sol_exame se
WHERE se.cd_paciente IN (SELECT *
FROM TABLE(CAST(p_cd_paciente AS TYP_TB_OBJ_NUMBER)));
END;

Para efetuar o teste, execute o seguinte comando:


DECLARE
p typ_tb_obj_number := typ_tb_obj_number();
BEGIN

P.EXTEND;
---Coloque aqui a lista de valores que vc deseja
P(1) := 1;
P(2) := 2;
pr_teste(p => p);
END;


Vlw Leonardo Litz

[rwarstatParticipante]

Valeu Litz pela ajuda.

Estou trabalhando também com a seguinte solução:

1º Crio um type público, mas do tipo varchar:

create or replace type myTableType as table
of varchar2 (255);


2º Crio uma função que vai retorna o meu parâmetro varchar no type criado anteriormente:

create or replace
function in_list( p_string in varchar2 ) return myTableType
as
l_string long default p_string || ',';
l_data myTableType := myTableType();
n number;
begin
loop
exit when l_string is null;
n := instr( l_string, ',' );
l_data.extend;
l_data(l_data.count) :=
ltrim( rtrim( substr( l_string, 1, n-1 ) ) );
l_string := substr( l_string, n+1 );
end loop;

return l_data;

end;


3º Altero a clausula where do select para conter a chamada da função:

SELECT 1
INTO v_result
FROM sol_exame se
WHERE se.cd_paciente IN
(SELECT *
FROM THE (SELECT CAST (in_list (p_cd_paciente) AS mytableType) FROM DUAL) a)

A minha procedure vai ficar da seguinte maneira:

CREATE OR REPLACE PROCEDURE pr_teste (p_cd_paciente IN TYP_TB_OBJ_NUMBER)
AS
v_result NUMBER (1);
BEGIN
SELECT 1
INTO v_result
FROM sol_exame se
WHERE se.cd_paciente IN
(SELECT *
FROM THE (SELECT CAST (in_list (p_cd_paciente) AS mytableType) FROM DUAL) a);
END;

4º A chamada da função ficou assim:

DECLARE
P_CD_PACIENTE VARCHAR2(32767);

BEGIN
P_CD_PACIENTE := '2702, 2703';

INFOSAUDE.PR_TESTE ( P_CD_PACIENTE );
COMMIT;
END;

Essa forma de fazer consegui através de uma lista, onde me indicaram o seguinte link
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:210612357425#9304006450690

Havia pensado também em fazer usando tabela temporária, mas me parece que o custo para o banco seria maior em razão de ter que ir “cortando” a variável de parâmetro e fazer a inserção na tabela. Por isso, essa foi descartada.

Abraço,
Roberto

[Leonardo LitzParticipante]

Roberto,

Creio que o seu exemplo funcione igualmente ao exemplo que coloquei, você esta transformando uma string delimitada para uma lista.

Veja, se é realmente necessário você receber esta string delimitada, seria melhor vc receber a lista diretamente.

Sobre tabela temporaria, evite ao máximo utiliza-las… existem outros recursos no banco de dados que podem substitui-lás…

Vlw Leonardo Litz

[rwarstatParticipante]

Litz,

Qual seria a melhora em receber a lista diretamente, ao invés de uma lista delimitada? Seria por causa do overhead da função de transformar a lista delimitada?

Abraço,
Roberto

[Leonardo LitzParticipante]

Creio que o tempo de processamento para desmembrar uma string seja pifio…
Mas, eu penso assim, se vc já tem o dado em lista, vc vai ter que concatenar a informação delimitando para coloca-la em uma string, para depois desmembrar essa informação delimitada…. Não seria mais fácil já passar a lista diretamente????

Salvo, pelos casos de que vc não teria o controle da forma que a informação chega para vc (arquivos textos delimitdados…, ou outros…)

Vlw Leonardo Litz

[rwarstatParticipante]

Como essa informação ainda é uma incógnita para mim, vou guardar as duas maneiras de fazer. Até por que nunca se sabe quando irá ser necessário novamente.

Muito obrigado Litz.

Abraço,
Roberto

[burgaParticipante]

E aí Roberto:
Outro erro aí no seu primeiro código é que você está retornando uma lista na consulta, mas está tentando alocá-la em um número. Veja bem:

Se a sua consulta funcionasse da forma que você pensou:
SELECT 1
INTO v_result
FROM sol_exame se
WHERE se.cd_paciente IN (p_cd_paciente);

ela retornaria uma lista de 1’s, e você está tentando jogar isso em uma variável numérica:
v_result NUMBER (1);

Talvez o erro seja por causa disso…

Agora, se você quer fazer funcionar a consulta, jogando uma lista dinâmica de números criada com uma string e separadores ‘,’, você pode fazer a consulta da seguinte forma:

SELECT 1
FROM sol_exame se
WHERE se.cd_paciente IN (
SELECT TO_NUMBER(TRIM(token)) FROM (
SELECT
SUBSTR(t.separador || t.str || t.separador,
INSTR(t.separador || t.str || t.separador , t.separador, 1, level) + 1,
INSTR(t.separador || t.str || t.separador , t.separador, 1, level + 1) - INSTR(t.separador || t.str || t.separador , t.separador, 1, level) - 1) token
FROM (
SELECT
p_cd_paciente str,
',' separador
FROM dual) t
CONNECT BY LEVEL < LENGTH(t.separador || t.str) - LENGTH(REPLACE(t.separador || t.str, t.separador)) + 1) t2
WHERE TRIM(t2.token) IS NOT NULL);

Só lembre de alterar o p_cd_paciente nesta consulta para a sua lista de números separados por ‘,’… Outra coisa, o resultado deverá ser guardado em uma collection, a não ser que você use o distinct…

[rwarstatParticipante]

Opa Burga,

Não, o erro não é por causa daquilo ali. Se o erro estivesse ali daria um ORA-04122 – que em portugês é “a extração exata retorna mais do que o número solicitado de linhas”.

Essa tua solução é bem interessante também.
Um dia eu chego nesse nível de programação. hehehe

Abraço,
Roberto

[Autor]

Posts