Menu de navegação

[Joao HenriqueParticipante]

Bom dia Pessoal.

Estou utilizando um cursor para retornar mais de um registro no select e atribui o resultado em uma variável do tipo varchar(2000),  estou tentando imprimir o resultado com o pacote dbms_output.put_line(variável),  como o meu bloco possui uma exception para trata os erros o banco retorna o código 6502 utilizando a função SQL_CODE. Alguém pode me ajudar a retornar esta variável com os dados por favor. Desde já agradeço!

Segue a abaixo o código.

DECLARE

MSG        VARCHAR(2000);
err_msg VARCHAR(2000);

CURSOR C1
IS SELECT
tp.ds_tip_presc
FROM pre_med pm inner join itpre_med itm ON(pm.cd_pre_med = itm.cd_pre_med)
inner join tip_presc tp ON (itm.cd_tip_presc = tp.cd_tip_presc)
inner JOIN atendime a ON(a.cd_atendimento = pm.cd_atendimento)
WHERE itm.cd_tip_esq IN(‘PMA’,’PRO’)
AND pm.fl_impresso = ‘N’
AND pm.cd_atendimento = 4906856;

RC1 C1%ROWTYPE;
BEGIN
OPEN C1;
MSG:= ‘ITENS PRESCRITOS: ‘;
FETCH C1 INTO RC1;
WHILE C1%FOUND LOOP
— EXIT WHEN C1%ROWCOUNT > 0;
MSG := MSG ||RC1.DS_TIP_PRESC || ‘, ‘;
END LOOP;
Dbms_Output.PUT_LINE(MSG);

EXCEPTION
WHEN OTHERS THEN
err_msg := SQLCODE;
Dbms_Output.put_line(err_msg);
END;
/

[José Laurindo ChiappaModerador]

Tudo Blz ? Espero que sim… Então, antes de te responder, deixa eu te fazer uma Obs essa idéia de concatenar dados de N registros e depois retornar uma string com esses N dados concatenados é ** PÉSSIMA ** , muito Ruim mesmo – o problema principal é que ela é INSEGURA – afinal, HOJE alguém até pode ter Assegurado que os dados concatenados todos não ultrapassam digamos os 2000 bytes que vc está reservando , mas E SE amanhã o usuário enfiar mais dados na tabela ?? Não tem jeito, por MAIS que neguim te jure de pé juntos que os dados obedecem a um limite X, pode ter CERTEZA que cedo ou tarde eles VÃO crescer e ultrapassar esse limite, isso é um fato da vida…. Assim sendo, o SEGURO, Correto e RECOMENDADO seria vc retornar um ARRAY, ie, um RESULTSET composto por N linhas e X colunas, de tamanho variável : https://stackoverflow.com/questions/2153053/how-to-return-a-resultset-cursor-from-a-oracle-pl-sql-anonymous-block-that-exe é uma demonstração de como se faz isso no Oracle tradicionalmente, que é o chamado REF CURSOR…. Outra opção poderia ser vc transformar o array numa TABELA, aí vc pode fazer um SELECT dele como se fosse uma tabela : https://sqljana.wordpress.com/2017/01/22/oracle-return-select-statement-results-like-sql-server-sps-using-pipelined-functions/ exemplifica isso com o uso de uma PIPELINED FUNCTION…. Ou ainda (a ÚLTIMA opção, mas pra constar) vc poderia retornar uma string gigante (um CLOB no linguajar Oracle) ao inves de uma string escalar, https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1464712373163 exemplifica… Essa última opção pode NÂO SER VIÁVEL porque nem todos os front-ends/linguagens de programação são capazes de entender e trabalhar com LOBs, mas fica a possibilidade…

Aí sim, respondendo : ORA-06502 (cfrme amplamente documentado e citado em muitos sites, https://www.techonthenet.com/oracle/errors/ora06502.php é apenas um deles) é um erro de valor , ou seja : vc tentou enfiar uma string num número, tentou colocar mais dados do que a variável permite, tentou inserir uma data inválida, enfim, tentou colocar numa coluna de tabela ou numa variável PL/SQL um dado inapropriado…
Olhando o teu código, a PRIMEIRA coisa que salta aos olhos é que (cfrme documentado, https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/errors.htm é a documentação do Oracle 10g mas isso não muda em qquer que seja a versão) a função SQLCODE retorna um ** NÚMERO **, que é o Código de ERRO, e vc tá usando uma variável err_msg do datatype STRING…. Dá uma lida nesse trecho do manual que indiquei (no trecho “Example 10-11 Displaying SQLCODE and SQLERRM” mais especificamente) e veja que NÃO É ESSE o procedimento correto : normalmente isso NÂO deveria dar erro (o Oracle via de regra CONSEGUE converter implicitamente números em string), mas pode ser algo nesse sentido – INCLUSIVE, uma sugestão de debug seria vc executar temporariamente SEM o EXCEPTION, só pra receber o erro inteiro original….

Abraços,

Chiappa

[José Laurindo ChiappaModerador]

Só um detalhe adicional : como eu disse, melhor seria vc retornar um resultset MAS se vc REALMENTE QUISER retornar uma lista de dados separada por vírgula vc Não É Obrigado a construir a sua própria função – desde há MUITO tempo (pelo menos desde a 11g) nós JÁ TEMOS UMA FUNÇÃO NATIVA pra isso…
Veja os dados abaixo :

scott@TESTE:DESENV:SQL> select d.deptno, e.empno, e.ename
  2  from dept d, emp e
  3  where d.deptno = e.deptno order by 1, 3;

DEPTNO      EMPNO ENAME
    10       7782 CLARK
    10       7839 KING
    10       7934 MILLER
    20       7876 ADAMS
    20       7902 FORD
    20       7566 JONES
    20       7788 SCOTT
    20       7369 SMITH
    30       7499 ALLEN
    30       7698 BLAKE
    30       7900 JAMES
    30       7654 MARTIN
    30       7844 TURNER
    30       7521 WARD

14 linhas selecionadas.

scott@TESTE:DESENV:SQL>

==> Digamos que em cima do exemplo acima, eu quero ter para CADA DEPARTAMENTO a lista de empregados dentro dele – como NÂO quero construir a minha própria função que retorna os dados agrupados, eu VOU usar a built-in :

scott@TESTE:DESENV:SQL> ed
Gravou file afiedt.buf

1  select d.deptno, 'Empregados=' || listagg(e.ename, ',') empregados_do_depto
  2  from dept d, emp e
  3  where d.deptno = e.deptno
  4  group by d.deptno
  5* order by 1
scott@TESTE:DESENV:SQL> /

DEPTNO EMPREGADOS_DO_DEPTO
    10 Empregados=CLARK,KING,MILLER
    20 Empregados=SMITH,JONES,SCOTT,ADAMS,FORD
    30 Empregados=ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

3 linhas selecionadas.

scott@TESTE:DESENV:SQL>

certo ?? Eu ** IMAGINO ** que é algo do tipo que vc queria quando escreveu essa sua função…. OBVIAMENTE, há um LIMITE de comprimento na string que uma função built-in pode retornar (4000 bytes geralmente, MAS na versão 12c do banco pode ir até 32 kb SE o banco estiver configurado pra isso) , mas fica a Sugestão….

[Joao HenriqueParticipante]

Boa tarde Jose, apliquei a função listagg e deu certo aqui. Obrigado pelas discas.

Abraço.

 

[José Laurindo ChiappaModerador]

Blz… Fique ATENTO aos limites de coluna string no Oracle (o que a LISTAGG retorna é uma STRING) mas ok…

[]s

Chiappa

[Autor]

Posts