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!

ORA-00947 executing procedure

Discussion in 'SQL PL/SQL' started by Nick2005, Feb 5, 2013.

  1. Nick2005

    Nick2005 Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Hi to All,
    The version is Oracle DB10g R2 and this is the scenario:
    Code (SQL):
    CREATE TABLE MYITEMS
    (
      GRP_CODE       VARCHAR2(10 BYTE),
      IT_CODE         VARCHAR2(25 BYTE)
    )
    ....................

    CREATE TABLE MYITEMSROW
    (
      GRP_CODE      VARCHAR2(10 BYTE),
      IT_CODE        VARCHAR2(25 BYTE),
      IT_QTA          NUMBER(16,4)
    )
    ..........................
    CREATE TABLE TBSUM
    (
      GRP_CODE     VARCHAR2(10 BYTE),
      IT_CODE       VARCHAR2(25 BYTE),
      IT_SUM         NUMBER(14,4)
    )
     
    In the first Table I have for example:
    Code (Text):
    COM 02.72432.0080
    COM 02.74422.0070
    COM 02.74422.0090
    and in the second Table
    Code (Text):
    COM 02.74422.0090  13
    COM 02.74422.0070   1
    COM 02.74422.0090  10
    COM 02.74422.0090 15
    COM 02.74422.0090  20
    COM 02.74422.0090  3
    Now I have created a procedure
    Code (SQL):
    CREATE OR REPLACE PROCEDURE INSERT_SUM
    AS
       vExist     VARCHAR2 (1 BYTE);
       vQta       NUMBER (16, 4);
       vItem      VARCHAR2 (25 BYTE);

       CURSOR curZero
       IS
            SELECT *
              FROM MYITEMS
          ORDER BY GRP_CODE, IT_CODE;

    BEGIN
       FOR vZero IN curZero
       LOOP
          vExist := 'Y';

          BEGIN
               SELECT MYITEMSROW.IT_CODE vArticol, SUM (NVL (IT_QTA, 0)) INTO vQta
                 FROM MYITEMSROW
                WHERE MYITEMSROW.IT_CODE = vZero.IT_CODE
             GROUP BY MYITEMSROW.IT_CODE;
          EXCEPTION
             WHEN NO_DATA_FOUND
             THEN
                vExist := NULL;
          END;

          IF vExist = 'S'
          THEN
             INSERT INTO TBSUM
                  VALUES (vZero.GRP_CODE, vZero.IT_CODE, vQta);
          END IF;
       END LOOP;
    END;
    /
    When I compile the procedure this error is returned
    I have searched for this error, but without a valid solution.
    I need from select the sum of qty grouped by Item.
    Can You help me?
    Thank You
    Gaetano
     
  2. a_kamalraj

    a_kamalraj Forum Advisor

    Messages:
    121
    Likes Received:
    23
    Trophy Points:
    280
    Hi Gaetano,

    Can you please do the changes mentioned and see

    CURSOR curZero
    IS
    SELECT * ---> Remove * and put the column names which you want from the tables and try
    FROM MYITEMS
    ORDER BY GRP_CODE, IT_CODE;

    Cheers
    Kamal (kamal.love@gmail.com)
     
  3. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi Nick

    The select statement inside the for loop is retrieving two columns but INTO clause having one variable. Add one more variable in the INTO clause.

    SELECT MYITEMSROW.IT_CODE vArticol, SUM (NVL (IT_QTA, 0)) INTO vQta
    FROM MYITEMSROW
    WHERE MYITEMSROW.IT_CODE = vZero.IT_CODE
    GROUP BY MYITEMSROW.IT_CODE;

    Regards
    Samba
     
  4. a_kamalraj

    a_kamalraj Forum Advisor

    Messages:
    121
    Likes Received:
    23
    Trophy Points:
    280
    Yes ...

    True Samba.. tats the point .. good catch man

    Cheers
    Kamal(kamal.love@gmail.com)
     
    sambuduk likes this.
  5. Nick2005

    Nick2005 Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Thank You,
    I have correct the SQL and all work fine.
    Code (SQL):
    SELECT MYITEMSROW.IT_CODE, SUM (NVL (IT_QTA, 0)) INTO vArticol, vQta
    FROM MYITEMSROW
    etc...
    Bye
    Gaetano