Jump to content
carlossorocaba82

"Collection" de Bind´s variable

Recommended Posts

Boa tarde.

  Alguém sabe uma maneira de passar uma "lista/collection" de bind variable ao trabalhar com sql dinâmico utilizando cursor?
  
  Exemplificando meu problema, tenho a procedure abaixo, onde há comentários para exemplificar e questionar melhor o problema

create or replace procedure pr_teste(
  pAtivo  number := 1,
  pCampo2 number,
  pCampo3 varchar2,
  pCampo4 varchar2,
  pCursor in out SYS_REFCURSOR,
) is
  vQuery varchar2(1000);
  vWhere varchar2(1000) := '';
begin

  vQuery := 'select * from clientes where ativo = :ativo ';

  if pCampo2 > 0 then
    vWhere := vWhere || ' and campo2 = :campo2 ';
  end if;

  if pCampo3 is not null then
    vWhere := vWhere || ' and campo3 = :campo3 ';
  end if;

  if pCampo4 is not null then
    vWhere := vWhere || ' and campo4 = :campo4 ';
  end if;
  
  -- Se nenhum parâmetro for informado, com exceção do primeiro, que é obrigatório, eu tenho o seguinte comando para me retornar o cursor:
  if pCampo2 = 0 and  pCampo3 is null and pCampo4 is null then
    open pCursor for vQuery || vWhere
      using pAtivo;
  -- Se APENAS o campo2 for informado, o comando para retornar o cursor precisa de 2 "itens" no "using":
  elsif pCampo2 > 0 and  pCampo3 is null and pCampo4 is null then
    open pCursor for vQuery || vWhere
      using pAtivo, pCampo2;
  -- Se o campo2 + campo3 forem informados, o comando para retornar o cursor precisa de 3 "itens" no "using":
  elsif pCampo2 > 0 and  pCampo3 is not null and pCampo4 is null then
    open pCursor for vQuery || vWhere
      using pAtivo, pCampo2, pCampo3;
  -- Se o campo2 + campo3 + campo 4 forem informados, o comando para retornar o cursor precisa de 4 "itens" no "using":
  elsif pCampo2 > 0 and  pCampo3 is not null and pCampo4 not is null then
    open pCursor for vQuery || vWhere
      using pAtivo, pCampo2, pCampo3 , pCampo4;
  -- continuando os IF´s, seria necessário combinar as outras possibilidades ainda, como, "pcampo2 = 0 and pcampo3 is not null and pcampo 4 is null" OU
  -- "pcampo2 = 0 and pcampo3 is not null and pcampo 4 is not null" OU "pcampo2 = 0 and pcampo3 is null and pcampo 4 is not null"
  end if;
      
  -- ... agora, o meu caso real, eu tenho OITO parâmetros opcionais que podem ser combinados. é inviável trabalhar com tantas condições.
  -- ... Gostaria de alguma opção que eu pudesse ir adicionado os possíveis "binds" já em uma collection e depois, ao abrir o cursor, usar esta collection na clausula using.
  -- ... algo tipo:

  if pCampo2 > 0 then
    vWhere := vWhere || ' and campo2 = :campo2 ';
    <<collectionComOsParametrosQuePossuemValores>> := pCampo2;
  end if;

  if pCampo3 is not null then
    vWhere := vWhere || ' and campo3 = :campo3 ';
    <<collectionComOsParametrosQuePossuemValores>> := pCampo3;
  end if;

  if pCampo4 is not null then
    vWhere := vWhere || ' and campo4 = :campo4 ';
    <<collectionComOsParametrosQuePossuemValores>> := pCampo4;
  end if;

  open  pCursor for vQuery || vWhere
      using <<collectionComOsParametrosQuePossuemValores>>

end;
/

  Pesquisei muito a respeito e não encontrei nada.
  
  Agradeço a ajuda.
  
abs,

Share this post


Link to post
Share on other sites

Bom dia,

 

sem responder diretamente a sua pergunta em relação à collections, a linha que você está iniciando é boa.

Pelo meno eu uso dessa forma quando necessário.

 

seguindo essa linha não resolveria, apesar de você ter 8 parâmetros?

 

create or replace procedure pr_teste(
  pAtivo  number,
  pCampo2 number,
  pCampo3 varchar2,
  pCampo4 varchar2,
  pCursor in out SYS_REFCURSOR
) is
--
  vQuery varchar2(1000);
  vWhere varchar2(1000) := '';
begin
  --
  vQuery := 'select * from clientes where ativo = '||to_char(nvl(pativo,1));
  --
  if nvl(pCampo2,0) > 0 then
    vWhere := vWhere || ' and campo2 = '||to_Char(pcampo2);
  end if;
  if pCampo3 is not null then
    vWhere := vWhere || ' and campo3 = '||''''||pcampo3||'''';
  end if;
  if pCampo4 is not null then
    vWhere := vWhere || ' and campo4 = '||''''||pcampo4||'''';
  end if;
  --
  if vWhere is not null then
     vQuery := vQuery||vWhere;
  end if;
  --
  open pCursor for vQuery;
   loop
    fetch pCursor into ...
 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Similar Content

    • By Viniciusr9
      Estou com um problema com esse esse script abaixo: 
      tenho um checkbox na minha página ( não em relatório, na página mesmo, um item de página) e gostaria que o mesmo ao ser clicado e pressionado um botão submit realizasse o procedimento do script, porém ele faz o processo mas não me retorna nada. acredito que o problema seja no LOOP do APEX_APPLICATION.G_F01 . Alguém consegue me ajudar com isso?
      DECLARE V_DS_COLAB VARCHAR2(50); BEGIN APEX_DEBUG.MESSAGE('CHECK:'|| vCHECK); FOR A IN 1 .. APEX_APPLICATION.G_F01.COUNT LOOP BEGIN SELECT C.DS_COLABORADOR INTO V_DS_COLAB FROM COLABORADOR C WHERE C.USER_APEX = V('APP_USER') ; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20001,'NENHUM REGISTRO ENCONTRADO!'); WHEN TOO_MANY_ROWS THEN RAISE_APPLICATION_ERROR(-20002,'MAIS QUE UM REGISTRO ENCONTRADO!'); WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20003,'ERRO NAO PREVISTO' || SQLERRM) ; END; :P12_SUPER := (V_DS_COLAB || ' - ' || TO_CHAR(SYSDATE,'DD/MM/RRRR HH24:MI')); UPDATE COMPETENCIA_COLABORADOR CC SET FINALIZADO_SN = 'S' WHERE CC.CD_EQUIPE = :P12_EQUIPE AND CC.CD_COMPETENCIA = (SELECT C.CD_COMPETENCIA FROM COMPETENCIA C WHERE TO_DATE(LPAD(C.MES_COMPETENCIA,2,'0') || '/' || C.ANO_COMPETENCIA,'MM/RRRR') = TO_DATE(:P12_COMPETENCIA,'MM/RRRR')); END LOOP; END;  
    • By Viniciusr9
      Boa tarde pessoal,
      Sei que tem varios tópicos sobre esse erro, porém analisei todos e nenhum foi aplicável ao meu caso ( a maioria era porquê o pessoal esquecia do Group By ao final das Querys) . 
      Se alguém puder ajudar, agradeço . Os campos sem função estão inseridos no group by, porém o erro persiste .
       
       

       
      SELECT * FROM( select LPAD(C.MES_COMPETENCIA,2,'0') ||'/'||C.ANO_COMPETENCIA AS PROJETO, E.DS_EQUIPE as EQUIPE, SUM(NVL((CC.QT_HORAS_CHEIA - SUM(AU.DT_FIM - AU.DT_INI)*24 ),CC.QT_HORAS_CHEIA)) as "ESFORÇO CALCULADO" from EQUIPE E, COLABORADOR C1, COMPETENCIA C, COMPETENCIA_COLABORADOR CC, AUSENCIAS AU where E.CD_EQUIPE=CC.CD_EQUIPE and C.CD_COMPETENCIA=CC.CD_COMPETENCIA and C1.CD_COLABORADOR=CC.CD_COLABORADOR and C1.STATUS = 1 AND C1.CD_GESTOR <> C1.CD_COLABORADOR AND AU.CD_COLABORADOR (+) = C1.CD_COLABORADOR GROUP BY E.DS_EQUIPE, LPAD(C.MES_COMPETENCIA,2,'0') ||'/'||C.ANO_COMPETENCIA ) VT WHERE VT.PROJETO = ((select to_char(sysdate, 'MM') from dual)||'/'||(select to_char(sysdate, 'RRRR') from dual))  
    • By Viniciusr9
      Script com retorno de dias uteis no mês ( desconsiderando finais de semana e feriados também ( os mesmos cadastrados em uma tabela )), ajuda!
       
      Boa tarde,
      alguém poderia me ajudar , tentei com alguns que vi pela net , fazendo alterações mas não consegui o que gostaria ainda. Preciso de um script que dado um valor (mês/ano) ele retorne a quantidade de dias uteis nesse mês, desconsiderando os sabados e domingos e os feriados listados na tabela de feriados, em Oracle Sql puro ou PL/SQL  . Agradeço pela ajuda!
    • By cfreis01
      Boa tarde galera
      Estou tentando importar um arquivo e esta dando o erro ORA-29282 ID de arquivo invalido
      Alguem pode me ajudar?
       
      Procedure File_Open(Pdiretorio Varchar2, Parquivo Varchar2, Pmodo Char) Is Msg Varchar2(200); BEGIN O erro esta dando neste UTL_Open abaixo, abaixo esta oq cada variavel esta carregando -- pdiretorio esta vindo o diretorio do arquivo, p arquivo esta vindo o nome do arquivo que quero importar o Pmodo esta retornando 'R' Utl_Open := Utl_File.Fopen(Pdiretorio, Parquivo, Pmodo,32713); Exception When Utl_File.Invalid_Path THEN Msg := 'Diretorio invalido: ' || Pdiretorio; Lib_Proc.Add_Log(Msg || Sqlerrm, 1); When Utl_File.Invalid_Operation THEN -- esta caindo nesta exception ********* Msg := 'Arquivo invalido: ' || Parquivo; -- Lib_Proc.Add_Log(Msg || Sqlerrm, 1); When Others THEN Msg := 'Erro na abertura do arquivo: ' || Parquivo; Lib_Proc.Add_Log(Msg || Sqlerrm, 1); INSERT INTO teste_c VALUES('88');COMMIT; End File_Open;  
    • By cfreis01
      Bom dia galera,
      Estou com um erro de execução na criação de uma procedure.
      Eu vou passar uma cidade como parametro e ela me retorna os funcionarios que trabalham nessa cidade se nao encontrar retorna nao encontrado.
      Tem que ser por dbms_output.
      Se eu executo o bloco fora de procedure ele executa, quando coloco o script numa procedure ela compila mas se eu executo:
       
      BEGIN
      EXECUTE FUNC_CIDADE_PROC('DALLAS');
      end;
      Da erro :PLS-00103: Encontrado o simbolo "EMP_CIDADE_PROC" quando um dos seguintes simbolos era esperado:
      := .(
       
      Mas se compilou e executou num bloco por que esta dando erro?
       
      CREATE OR REPLACE PROCEDURE EMP_CIDADE_PROC(p_cidade varchar2) IS contador number; begin begin select COUNT(EMPNO) into contador from emp emp ,dept dp where emp.deptno = dp.deptno and dp.loc = p_cidade GROUP BY LOC; EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line('Nenhum funcionário encontrado.'); end; IF(CONTADOR > 0)THEN FOR CUR_EMP IN( select EMP.EMPNO ,EMP.JOB ,EMP.SAL from emp emp ,dept dp where emp.deptno = dp.deptno and dp.loc = p_cidade)LOOP dbms_output.put_line('Código: '||cur_emp.empno||' | Cargo: '||cur_emp.job||' | Salário: '||cur_emp.sal); END LOOP; END IF; -- END;  
×

Important Information

Ao usar o fórum, você concorda com nossos Terms of Use.