ORA-06550 line 1 column 7 PL SQL Statement ignored Error

Question

I am getting following error for the stored procedure and not able to understand the issue  must be from db side  While googling  I found similar issues but couldn t get the solution  Can any one help me please find the error in PROCEDURE     Error  -   18 58 50 281 ERROR  STDERR  java sql SQLException  ORA-06550  line 1  column 7  PLS-00306  wrong number or types of arguments in call to  SP DIST RETAILER REMAP  ORA-06550  line 1  column 7  PL SQL  Statement ignored   Stored Prodedure SP DIST RETAILER REMAP   -   CREATE OR REPLACE PROCEDURE SMAPRD02 SP DIST RETAILER REMAP             i old dist code IN VARCHAR2        i new dist code IN VARCHAR2              i territory remapping IN NUMBER        i remapping reason IN VARCHAR2        i trans doneby rolename IN VARCHAR2        i trans doneby id IN NUMBER             i trans dist rolename IN VARCHAR2         i trans ret rolename IN VARCHAR2             i activity type IN VARCHAR2        i ret list IN V ARRAY         result OUT VARCHAR2        i o query OUT VARCHAR2         AS  --i ret codes OUT VARCHAR2   v dist count NUMBER  v ret count NUMBER  v ret codes VARCHAR2 10000          v flag VARCHAR2 10      true   v trans id NUMBER  v query VARCHAR2 10000    BEGIN  IF i territory remapping   1 then  SELECT count    into v dist count FROM tblemployee where EMPCODE   i new dist code and circle code    select emp circle code         from tblemployee emp where emp empcode   i old dist code  and upper user type  like upper   dist    and upper ACCESS TO  in   SALES   BOTH   and upper stage  not in  upper  InActive      ELSE  SELECT count    into v dist count FROM tblemployee where EMPCODE   i new dist code and circle code    select emp circle code from tblemployee emp    where emp empcode   i old dist code  and cluster code    select emp cluster code from tblemployee emp where emp empcode   i old dist code     and upper user type  like upper   dist    and upper ACCESS TO  in   SALES   BOTH   and upper stage  not in  upper  InActive      END IF    IF v dist count  0 THEN  result     invalid new dist code    v flag     false    ELSIF v dist count   1 THEN  SELECT count    into v ret count FROM tblretailer t where t DIST CODE   i old dist code and  upper t ACCESS TO     SALES  or upper t ACCESS TO     BOTH     --SELECT count    into v ret count FROM tblretailer t where t DIST CODE   i old dist code and upper t ACCESS TO     SALES  and upper t stage  in   APPROVED   INACTIVE     IF v ret count i ret list count THEN      IF i territory remapping   1 THEN     result     no ret left       v flag     false       END IF   ELSE     IF i territory remapping    1 THEN      result     ret left        v flag     false        END IF   END IF    END IF   IF i ret list is null or i ret list count   0 THEN      result     empty retailers list        v flag     false    END IF     FOR i IN i ret list FIRST    i ret list LAST       LOOP             IF v ret codes is null            THEN                       v ret codes          i ret list i                    ELSE               v ret codes     v ret codes         i ret list i                     END IF                       IF v ret codes is null            THEN                       v ret codes    i ret list i              ELSE               v ret codes     v ret codes       i ret list i               END IF              END LOOP   i ret codes    v ret codes   v flag     false   result     success      IF v flag    true  THEN  FOR i IN i ret list FIRST    i ret list LAST       LOOP            IF v ret codes is null            THEN                       v ret codes          i ret list i                    ELSE               v ret codes     v ret codes         i ret list i                     END IF                        END LOOP   --i ret codes    v ret codes    --update tblretailer set dist code i new dist code DIST ID to number i new dist code  cluster code  select cluster code from tblemployee where empcode   i new dist code  FOSID    FOS CODE    DSR ID    DSR CODE    LAST UPDATED DATE sysdate where retcode in  v ret codes    v query     update tblretailer set dist code    i new dist code    DIST ID to number    i new dist code     cluster code  select cluster code from tblemployee where empcode      i new dist code     FOSID      FOS CODE      DSR ID      DSR CODE      LAST UPDATED DATE sysdate where retcode in     v ret codes        execute immediate  v query    --i query    update tblretailer set dist code    i new dist code    DIST ID to number    i new dist code     cluster code  select cluster code from tblemployee where empcode      i new dist code     FOSID    FOS CODE    DSR ID    DSR CODE    LAST UPDATED DATE sysdate where retcode in     v ret codes         insert into TBL TRANSFER SUP MASTER MASTER ID TRANS ID TRANS DONEBY ROLENAME TRANS DONEBY ID TRANS FROM ROLENAME TRANS FROM TRANS TO ROLENAME TRANS TO ACTIVITY CODE TRANS DATE TRANSFER REASON LAST UPDATED DATE                   values SUP MASTER TRANS ID SEQ nextval SUP MASTER TRANS ID SEQ nextval i trans doneby rolename i trans doneby id i trans dist rolename i old dist code i trans dist rolename i new dist code  101  sysdate i remapping reason sysdate  return TRANS ID into v trans id    FOR i IN i ret list FIRST    i ret list LAST       LOOP        insert into TBL TRANSFER SUP DTLS DTLS ID TRANS ID TRANS ON ROLENAME TRANS ON ID LAST UPDATED DATE               values SUP DTLS ID SEQ nextval v trans id i trans ret rolename i ret list i  sysdate           END LOOP   IF SQL ROWCOUNT gt 0 THEN  result     success    ELSE  result     failure    END IF   --update tblstock set NEW DIST CODE REMAP i new dist code REMAP DATE sysdate LAST UPDATED DATE sysdate where  DIST CODE i old dist code or NEW DIST CODE REMAP i old dist code  and RET CODE in  v ret codes          v query     update tblstock set NEW DIST CODE REMAP    i new dist code    REMAP DATE sysdate LAST UPDATED DATE sysdate where  DIST CODE    i old dist code    or NEW DIST CODE REMAP    i old dist code     and RET CODE in     v ret codes             execute immediate  v query    i o query    v query   insert all into TBL ACTIVITY LOG  LOG ID TRANS ID ACTIVITY DONEBY ROLENAME ACTIVITY DONEBY ID ACTIVITY REFERENCE ID ACTIVITY CODE ACTIVITY DATE                  values ACTIVITY LOG TRANS ID SEQ NEXTVAL ACTIVITY LOG TRANS ID SEQ NEXTVAL i trans doneby rolename i trans doneby id v trans id                  act code sysdate  select log config ACTIVITY CODE act code from TBL ACTIVITY LOG CONFIG log config                  where upper log config ACTIVITY TYPE   upper i activity type    END IF   END      Java Code  -   try                          if ret list size   gt 0                  ret code   ret list toArray                 con   ConnectionManager getDirectConnection                 ArrayDescriptor descriptor   ArrayDescriptor createDescriptor PropertyLoader RET SECONDARY V ARRAY con                ARRAY array to pass   new ARRAY  descriptor con  ret code                 cstmt   con prepareCall    call SP DIST RETAILER REMAP                                           cstmt setString 1 old dist code trim                  cstmt setString 2 new dist code trim                  if territory remapping                           cstmt setInt 3 1                else              cstmt setInt 3 2                          cstmt setString 4 remapping reason                cstmt setString 5 userVO getRolename   trim                  cstmt setInt 6 userVO getEmpid                  cstmt setString 7 PropertyLoader DISTRIBUOTR ROLENAME                cstmt setString 8 PropertyLoader RETAILER ROLENAME                cstmt setString 9 PropertyLoader ACTIVITY TYPES RETAILER REMAPPING                cstmt setArray 10 array to pass                cstmt registerOutParameter 11 Types VARCHAR                cstmt registerOutParameter 12 Types VARCHAR                  cstmt registerOutParameter 13 Types VARCHAR                  cstmt execute                 status   cstmt getString 11                System out println  Remap Update Query    cstmt getString 12                   System out println cstmt getString 13

User · Answer

If the value stored in PropertyLoader RET SECONDARY V ARRAY is not  V ARRAY   then you are using different types  even if they are declared identically  e g  both are table of number  this will not work   You re hitting this data type compatibility restriction      You can assign a collection to a collection variable only if they have   the same data type  Having the same element type is not enough    You re trying to call the procedure with a parameter that is a different type to the one it s expecting  which is what the error message is telling you

[sql] ORA-06550: line 1, column 7 (PL/SQL: Statement ignored) Error

Examples related to sql

Examples related to database

Examples related to oracle

Examples related to stored-procedures

Examples related to procedure