1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Help with procuderes

Discussion in 'SQL PL/SQL' started by mfteixeira_, Jan 15, 2013.

  1. mfteixeira_

    mfteixeira_ Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi, I´m trying to do a procedure in a db and I´m not getting success.


    I´m using SQLDeveloper Client and this is my sql:

    Code (Text):
    create or replace
    procedure filialTeste (idfilial         out int,
                                             idlocal          out int,
                                             idparceiro       out int,
                                             codigointegracao out int,
                                             matriz           out int)
     is
    BEGIN  
     
        SELECT  *
        INTO  
              idfilial,
              idlocal,
              idparceiro,
              codigointegracao,
              matriz
        FROM   filial;
     
    END filialTeste ;
    when I execute this I got this error: PLS-00222

    the sql I executed:
    Code (Text):
    declare  
     idfilial int;  
     idlocal int;  
     idparceiro int;  
     codigointegracao int;  
     matriz int;  
      begin  
    execute immediate filialTeste(idfilial, idlocal, idparceiro, codigointegracao, matriz);  
    end;

    someone can help me?

    Thanks
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yes. Your first problem is trying to use execute immediate to execute a procedure within a PL/SQL block -- that throws your PLS-00222 error. The second issue is you are trying to select everything from a table and put it into variables designed to hold one value each. Please look at the example below to see what will happen when you call this procedure correctly and how to write it so it will work properly:

    Code (SQL):
    SQL> CREATE TABLE filial(
      2          idfilial NUMBER,
      3          idlocal NUMBER,
      4          idparceiro NUMBER,
      5          codigointegracao NUMBER,
      6          matriz  NUMBER);
     
    TABLE created.
     
    SQL>
    SQL> BEGIN
      2          FOR i IN 1..1000 loop
      3                  INSERT INTO filial
      4                  VALUES(i,i,i,i,i);
      5          END loop;
      6
      7          commit;
      8
      9  END;
     10  /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> --
    SQL> -- Original procedure
    SQL> --
    SQL>
    SQL> CREATE OR REPLACE
      2  PROCEDURE filialTeste (idfilial         OUT INT,
      3                                           idlocal          OUT INT,
      4                                           idparceiro       OUT INT,
      5                                           codigointegracao OUT INT,
      6                                           matriz           OUT INT)
      7   IS
      8  BEGIN
      9
     10      SELECT  *
     11      INTO
     12            idfilial,
     13            idlocal,
     14            idparceiro,
     15            codigointegracao,
     16            matriz
     17      FROM   filial;
     18
     19  END filialTeste ;
     20  /
     
    PROCEDURE created.
     
    SQL>
    SQL> SHOW errors

    No errors.

    SQL>
    SQL> DECLARE
      2   idfilial INT;
      3   idlocal INT;
      4   idparceiro INT;
      5   codigointegracao INT;
      6   matriz INT;
      7    BEGIN
      8  EXECUTE immediate filialTeste(idfilial, idlocal, idparceiro, codigointegracao, matriz);
      9  dbms_output.put_line(idfilial||' '|| idlocal||' '||idparceiro||' '||codigointegracao||' '||matriz);
     10  END;
     11  /
    EXECUTE immediate filialTeste(idfilial, idlocal, idparceiro, codigointegracao, matriz);
                      *
    ERROR at line 8:
    ORA-06550: line 8, COLUMN 19:
    PLS-00222: no FUNCTION WITH name 'FILIALTESTE' EXISTS IN this scope
    ORA-06550: line 8, COLUMN 1:
    PL/SQL: Statement ignored

    SQL>
    SQL> DECLARE
      2   idfilial INT;
      3   idlocal INT;
      4   idparceiro INT;
      5   codigointegracao INT;
      6   matriz INT;
      7    BEGIN
      8          filialTeste(idfilial, idlocal, idparceiro, codigointegracao, matriz);
      9          dbms_output.put_line(idfilial||' '|| idlocal||' '||idparceiro||' '||codigointegracao||' '||matriz);
     10  END;
     11  /
    DECLARE
    *
    ERROR at line 1:
    ORA-01422: exact fetch RETURNS more than requested NUMBER OF ROWS
    ORA-06512: at "BING.FILIALTESTE", line 9
    ORA-06512: at line 8

    SQL>
    SQL> --
    SQL> -- New procedure
    SQL> --
    SQL>
    SQL> CREATE OR REPLACE
      2  PROCEDURE filialTeste (                  pidfilial         IN INT,
      3                                           pidlocal          OUT INT,
      4                                           pidparceiro       OUT INT,
      5                                           pcodigointegracao OUT INT,
      6                                           pmatriz           OUT INT)
      7   IS
      8  BEGIN
      9
     10      SELECT idlocal, idparceiro, codigointegracao, matriz
     11      INTO
     12            pidlocal,
     13            pidparceiro,
     14            pcodigointegracao,
     15            pmatriz
     16      FROM   filial
     17      WHERE idfilial = pidfilial;
     18
     19  END filialTeste ;
     20  /
     
    PROCEDURE created.
     
    SQL>
    SQL> SHOW errors

    No errors.

    SQL>
    SQL> DECLARE
      2   idfilial INT:=11;
      3   idlocal INT;
      4   idparceiro INT;
      5   codigointegracao INT;
      6   matriz INT;
      7    BEGIN
      8          filialTeste(idfilial, idlocal, idparceiro, codigointegracao, matriz);
      9          dbms_output.put_line(idfilial||' '|| idlocal||' '||idparceiro||' '||codigointegracao||' '||matriz);
     10  END;
     11  /
    11 11 11 11 11
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    You will need to pass IN a value to use as a limiter to return only one record from the table the way you have your procedure written. It can be written as a function to return all records:

    Code (SQL):
    SQL> CREATE OR REPLACE
      2  FUNCTION filialTestef
      3  RETURN sys_refcursor
      4   IS
      5          filial_cur sys_refcursor;
      6
      7  BEGIN
      8
      9      OPEN filial_cur FOR SELECT * FROM filial;
     10      RETURN filial_cur;
     11
     12  END filialTestef ;
     13  /
     
    FUNCTION created.
     
    SQL>
    SQL> SHOW errors

    No errors.

    SQL>
    SQL> SELECT filialTestef FROM dual;
     
    FILIALTESTEF
    --------------------
    CURSOR STATEMENT : 1
    CURSOR STATEMENT : 1
     
      IDFILIAI    IDLOCAL IDPARCEIRO CODIGOINTEGRACAO     MATRIZ
    ---------- ---------- ---------- ---------------- ----------
             1          1          1                1          1
             2          2          2                2          2
             3          3          3                3          3
             4          4          4                4          4
             5          5          5                5          5
             6          6          6                6          6
             7          7          7                7          7
             8          8          8                8          8
             9          9          9                9          9
            10         10         10               10         10
            11         11         11               11         11
     
      IDFILIAI    IDLOCAL IDPARCEIRO CODIGOINTEGRACAO     MATRIZ
    ---------- ---------- ---------- ---------------- ----------
            12         12         12               12         12
            13         13         13               13         13
            14         14         14               14         14
            15         15         15               15         15
            16         16         16               16         16
            17         17         17               17         17
            18         18         18               18         18
            19         19         19               19         19
            20         20         20               20         20
            21         21         21               21         21
            22         22         22               22         22

    ...
     
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Also please find this link to know how to deal with sys refcurcursor returned from the called procerdure.
     
  4. mfteixeira_

    mfteixeira_ Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hey man, it´s works, thanks a lot.
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Coool. Could you please post your working script here so that people facing the same issue can refer your script in future. That is what the this forum stands for.