title: ORA-06550: line 1, column 7 (PL/SQL: Statement ignored) Error
body: <p>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 ??</p>
  
  <p>Error :- </p>
  
  <pre><code>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
  </code></pre>
  
  <p>Stored Prodedure(<strong>SP_DIST_RETAILER_REMAP</strong>) :- </p>
  
  <pre><code>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;
  /
  </code></pre>
  
  <p>Java Code :- </p>
  
  <pre><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));
  
          }
  </code></pre>