Ref cursor

fbarros300472 · Wed, 27 May 2009 3:45 pm

Environment information:
* Oracle version: 10g
* Operating system: Red Hat 4

Good afternoon!

I'm trying to do this procedure to return a value, but it gives an error when it runs. I did some tests and it seems to me that the error is in sys_refcursor. I researched, but I did not find something that could exclaim. I looked for an example on the net to set up this proxy. If anyone can help me, thank you.

Select all

SQL> execute nfiscal(334094); 
  
begin nfiscal(334094); end; 
  
ORA-06550: line 2, column 7: 
PLS-00306: wrong number or types of arguments in call to 'NFISCAL' 
ORA-06550: line 2, column 7: 
PL/SQL: Statement ignored

Select all

CREATE OR REPLACE PROCEDURE NFISCAL (MOV IN NUMBER, OBS OUT SYS_REFCURSOR) 
 
IS 
 
BEGIN 
OPEN OBS FOR SELECT (CASE WHEN(SELECT TMOV.CODTB5FLX 
        FROM TMOV 
        WHERE IDMOV=MOV 
              AND TMOV.CODFILIAL<=2)='01' AND (SELECT SUM(VALOR) FROM TTRBMOV 
                                      WHERE IDMOV=388670 
                                      AND CODTRB IN ('INSS','IRRF','ISS','ISSD'))=0 THEN 
               'LOCAÇÃO ISENTO DE IRRF E ISS' 
            WHEN (SELECT FCFO.RETENCAOISS 
                  FROM TMOV,FCFO 
                  WHERE TMOV.CODCFO=FCFO.CODCFO 
                        AND TMOV.IDMOV=MOV 
                        AND TMOV.CODTB5FLX <> 1 
                        AND TMOV.CODFILIAL=6)=1 THEN 
                  'Tomador do Serviço Responsável pelo Recolhimento de ISS, conforme Lei complementar nº 28 de 18/02/2003 
                   - Art. 199 - Parágrafo Único - Inciso II - Item 7.09.' 
             WHEN (SELECT FCFO.RETENCAOISS 
                  FROM TMOV,FCFO 
                  WHERE TMOV.CODCFO=FCFO.CODCFO 
                        AND TMOV.IDMOV=MOV 
                        AND TMOV.CODTB5FLX <> 1 
                        AND TMOV.CODFILIAL=5)=1 THEN 
                  'Tomador do Serviço Responsável pelo Recolhimento de ISS, conforme Lei Municipal nº 2.528/2003 - 
                  Art. 8º - Inciso II - Item 7.09.' 
              WHEN (SELECT FCFO.RETENCAOISS 
                  FROM TMOV,FCFO 
                  WHERE TMOV.CODCFO=FCFO.CODCFO 
                        AND TMOV.IDMOV=MOV 
                        AND TMOV.CODTB5FLX <> 1 
                        AND TMOV.CODFILIAL=4)=1 THEN 
                  'Tomador do Serviço Responsável pelo Recolhimento de ISS, conforme Lei Complementar nº 116/2003 
                   - Art. 6º - Parágrafo 2º - Inciso II - Item 7.09.' END)  
 
 
 
FROM DUAL; 
 
END;

gilbertoca · Wed, 27 May 2009 5:48 pm

FABROS300472 wrote: Environment information:
* Oracle version: 10g
* Operating system: Red Hat 4

Good afternoon!

I'm trying to do this procedure to return a value, but it gives an error when it runs. I did some tests and it seems to me that the error is in sys_refcursor. I researched, but I did not find something that could exclaim. I looked for an example on the net to set up this proxy. If anyone can help me, thank you.

Proven statistics: 80% of developers do not read the reference documentation
[url=http://download.oracle.com/docs/cd/B193 ... sthref2376]Passing Data with Cursor Variables

to use it implicitly you need to use the cursor keyword.
otherwise, you will have to define it in the procedure.
[url=http://download.oracle.com/docs/cd/B193 ... sthref1409]Declaring REF CURSOR Types and Cursor Variables

Gilberto

fbarros300472 · Thu, 28 May 2009 2:31 pm

Thanks, thank you. The links helped a lot.

fbarros300472 · Thu, 28 May 2009 3:48 pm

Gilberto, I managed to change and is no longer giving error. The problem that the message does not appear.

Select all

 
CREATE OR REPLACE PROCEDURE NFISCAL (MOV  NUMBER ) 
 
IS 
 
    MENS      SYS_REFCURSOR; 
    NF  VARCHAR(200); 
 
BEGIN 
OPEN MENS FOR SELECT ( 
CASE WHEN(SELECT TMOV.CODTB5FLX 
                  FROM TMOV 
                 WHERE  
                 IDMOV=MOV AND TMOV.CODFILIAL<=2)='01'  
                 AND  
               (SELECT SUM(VALOR)  
                FROM TTRBMOV 
                WHERE IDMOV=MOV  AND CODTRB IN ('INSS','IRRF','ISS','ISSD'))=0  
               THEN  'LOCAÇÃO ISENTO DE IRRF E ISS' 
        WHEN (SELECT FCFO.RETENCAOISS 
                   FROM TMOV,FCFO 
                  WHERE TMOV.CODCFO=FCFO.CODCFO 
                        AND TMOV.IDMOV=MOV 
                        AND TMOV.CODTB5FLX <> 1 
                        AND TMOV.CODFILIAL=6)=1  
                THEN  'Tomador do Serviço Responsável pelo Recolhimento de ISS, conforme Lei complementar nº 28 de 18/02/2003 
                   - Art. 199 - Parágrafo Único - Inciso II - Item 7.09.' 
         WHEN (SELECT FCFO.RETENCAOISS 
                    FROM TMOV,FCFO 
                    WHERE TMOV.CODCFO=FCFO.CODCFO 
                        AND TMOV.IDMOV=MOV 
                        AND TMOV.CODTB5FLX <> 1 
                        AND TMOV.CODFILIAL=5)=1  
              THEN 'Tomador do Serviço Responsável pelo Recolhimento de ISS, conforme Lei Municipal nº 2.528/2003 - 
                  Art. 8º - Inciso II - Item 7.09.' 
         WHEN (SELECT FCFO.RETENCAOISS 
                    FROM TMOV,FCFO 
                    WHERE TMOV.CODCFO=FCFO.CODCFO 
                        AND TMOV.IDMOV=MOV 
                        AND TMOV.CODTB5FLX <> 1 
                        AND TMOV.CODFILIAL=4)=1  
              THEN 'Tomador do Serviço Responsável pelo Recolhimento de ISS, conforme Lei Complementar nº 116/2003 
                   - Art. 6º - Parágrafo 2º - Inciso II - Item 7.09.' END)  
 
FROM DUAL;  LOOP 
        FETCH MENS INTO NF; 
        EXIT WHEN MENS%NOTFOUND; 
        DBMS_OUTPUT.PUT_LINE(NF); 
    END LOOP; 
    CLOSE MENS; 
END;

gilbertoca · Thu, 28 May 2009 4:19 pm

fbaros300472 wrote: Gilberto, I was able to change and is no longer giving error. The problem that the message does not appear.

Select all

 
CREATE OR REPLACE PROCEDURE NFISCAL (MOV  NUMBER ) 
 
IS 
 
    MENS      SYS_REFCURSOR; 
    NF  VARCHAR(200); 
 
BEGIN 
OPEN MENS FOR SELECT ( 
CASE WHEN(SELECT TMOV.CODTB5FLX 
                  FROM TMOV 
                 WHERE  
                 IDMOV=MOV AND TMOV.CODFILIAL<=2)='01'  
                 AND  
               (SELECT SUM(VALOR)  
                FROM TTRBMOV 
                WHERE IDMOV=MOV  AND CODTRB IN ('INSS','IRRF','ISS','ISSD'))=0  
               THEN  'LOCAÇÃO ISENTO DE IRRF E ISS' 
        WHEN (SELECT FCFO.RETENCAOISS 
                   FROM TMOV,FCFO 
                  WHERE TMOV.CODCFO=FCFO.CODCFO 
                        AND TMOV.IDMOV=MOV 
                        AND TMOV.CODTB5FLX <> 1 
                        AND TMOV.CODFILIAL=6)=1  
                THEN  'Tomador do Serviço Responsável pelo Recolhimento de ISS, conforme Lei complementar nº 28 de 18/02/2003 
                   - Art. 199 - Parágrafo Único - Inciso II - Item 7.09.' 
         WHEN (SELECT FCFO.RETENCAOISS 
                    FROM TMOV,FCFO 
                    WHERE TMOV.CODCFO=FCFO.CODCFO 
                        AND TMOV.IDMOV=MOV 
                        AND TMOV.CODTB5FLX <> 1 
                        AND TMOV.CODFILIAL=5)=1  
              THEN 'Tomador do Serviço Responsável pelo Recolhimento de ISS, conforme Lei Municipal nº 2.528/2003 - 
                  Art. 8º - Inciso II - Item 7.09.' 
         WHEN (SELECT FCFO.RETENCAOISS 
                    FROM TMOV,FCFO 
                    WHERE TMOV.CODCFO=FCFO.CODCFO 
                        AND TMOV.IDMOV=MOV 
                        AND TMOV.CODTB5FLX <> 1 
                        AND TMOV.CODFILIAL=4)=1  
              THEN 'Tomador do Serviço Responsável pelo Recolhimento de ISS, conforme Lei Complementar nº 116/2003 
                   - Art. 6º - Parágrafo 2º - Inciso II - Item 7.09.' END)  
 
FROM DUAL;  LOOP 
        FETCH MENS INTO NF; 
        EXIT WHEN MENS%NOTFOUND; 
        DBMS_OUTPUT.PUT_LINE(NF); 
    END LOOP; 
    CLOSE MENS; 
END;

I think you did not understand the use of the case: [url=http://download.oracle.com/docs/cd/B193 ... m#BABHGHGB]Using CASE Statements
From what I have seen in your code there is no need cursor.

Gilberto

Ref cursor

Who is online