Ref Cursor

Tuning de Banco, Tuning de SQL, Ferramentas de tuning
  

Poston Wed, 27 May 2009 3:45 pm

Environmental information: * Oracle Version: 10 g * operating system: Red Hat 4 good afternoon everyone!

I'm trying to do this procedure to return a value, but gives an error at the time it is performed. I did some tests and it seems to me that the error is in SYS_REFCURSOR. I searched, but couldn't find anything that could me sort. I sought an example on the net to assemble this proceudre. If anyone can help me, I thank you.

Code: 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



Code: 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;
fbarros300472
Location: sp

Poston Wed, 27 May 2009 5:48 pm

[quote = " fbarros300472 "]Environmental information: * Oracle Version: 10 g * operating system: Red Hat 4 good afternoon everyone!

I'm trying to do this procedure to return a value, but gives an error at the time it is performed. I did some tests and it seems to me that the error is in SYS_REFCURSOR. I searched, but couldn't find anything that could me sort. I sought an example on the net to assemble this proceudre. If anyone can help me, I thank you.
[/quote]

Proven statistic: 80% of the developers do not read the reference documentation [url = http ://download . oracle . com/docs/cd/B19306_01/./b14261/102 appdev tuning . htm # sthref2376] Passing Data with Cursor Variables [/url] to use it implicitly you must use the keyword CURSOR.
Otherwise, you have to set it in the procedure.

[url = ://download http . . oracle com/docs/cd/B19306_01/./b14261/102 appdev sqloperations . htm # sthref1409]Declaring REF CURSOR Types and Cursor Variables [/url] Gilberto
gilbertoca
Location: Palmas - TO


Poston Thu, 28 May 2009 2:31 pm

Thanks, thanks a lot. The links have been very helpful.
fbarros300472
Location: sp

Poston Thu, 28 May 2009 3:48 pm

Gilberto, managed change and no longer giving error. The problem is that the message does not appear.
Code: 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;
fbarros300472
Location: sp

Poston Thu, 28 May 2009 4:19 pm

[quote = " fbarros300472 "]Gilberto, managed change and no longer giving error. The problem is that the message does not appear.
Code: 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;
[/quote] I think you misunderstand the use of the CASE: [url = ://download http . . oracle com/docs/cd/B19306_01/./b14261/102 appdev controlstructures . htm # BABHGHGB] Using CASE Statements [/url] from what I saw in your code there is no need for CURSOR.

Gilberto
gilbertoca
Location: Palmas - TO




Return to DBA Tuning

Who is online

Users browsing this forum: No registered users and 1 guest