Error when insert into table with a varray column through package procedure

Question

PROBLEM IS HOW TO INSERT INTO VARRAY- I HAVE A PROCEDURE WHERE I AM DECLARE IN PARAMETER AS TABLE DATATYPE IN PROCEDURE I USE INSERT STATEMENT TO INSERT INTO TABLE. I AM FACING PROBLEM TO INSERT INTO VARRAY ALREADY I AM A DECLARING A VARY TYPE IN PACKAGE SPECIFICATION AND USING IN BODY BT ERROR SHOWS:-Error(61,17): PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got SCOTT.SYS_PLSQL_75329_19_1

create or replace PACKAGE BODY CBIS_LOAN_PROD_PACKAGE AS 
    PROCEDURE LOAN_PRODUCT_INSERT_PROCEDURE
 (P_PRODUCT_TITLE LOAN_PROD_TAB.PRODUCT_TITLE%TYPE,
  P_PRODUCT_SUMMERY LOAN_PROD_TAB.PRODUCT_SUMMERY%TYPE,
  P_INTEREST_TYPE LOAN_PROD_TAB.INTEREST_TYPE%TYPE,
   P_INTEREST_RATE LOAN_PROD_TAB.INTEREST_RATE%TYPE,
   P_SECURITY_REQD LOAN_PROD_TAB.SECURITY_REQD%TYPE,
   P_MIN_LOAN_AMT LOAN_PROD_TAB.MIN_LOAN_AMT%TYPE,
   P_MAX_LOAN_AMT LOAN_PROD_TAB.PRODUCT_TITLE%TYPE,
   P_TERM_MIN LOAN_PROD_TAB.TERM_MIN%TYPE,
   P_TERM_MAX LOAN_PROD_TAB.TERM_MAX%TYPE,
   P_REPAYMENT_FREQUENCY LOAN_PROD_TAB.REPAYMENT_FREQUENCY%TYPE,
   P_REPAYMENT_AMT LOAN_PROD_TAB.REPAYMENT_AMT%TYPE,
   P_EARLY_REPAY_ALLOWED LOAN_PROD_TAB.EARLY_REPAY_ALLOWED%TYPE,
   P_MIN_AGE_LIMIT LOAN_PROD_TAB.MIN_AGE_LIMIT%TYPE,
   P_MAX_AGE_LIMIT LOAN_PROD_TAB.MAX_AGE_LIMIT%TYPE,
   V_1 VARCHAR2,
   V_2 VARCHAR2,
   V_3 VARCHAR2,
   V_4 VARCHAR2,
   V_5 VARCHAR2,                                    
   P_PROD_START_DT LOAN_PROD_TAB.PROD_START_DT%TYPE,
   P_PROD_END_DT LOAN_PROD_TAB.PROD_END_DT%TYPE,
   P_PROD_STATUS LOAN_PROD_TAB.PROD_STATUS%TYPE)
  IS  
  V_T RESIDENT_VARRAY:=RESIDENT_VARRAY('V_1','V_2','V_3','V_4','V_5');
BEGIN
  INSERT INTO LOAN_PROD_TAB
(
    PRODUCT_TITLE,
    PRODUCT_SUMMERY,
    INTEREST_TYPE,
    INTEREST_RATE,
    SECURITY_REQD,
    MIN_LOAN_AMT,
    MAX_LOAN_AMT,
   TERM_MIN,
   TERM_MAX,
   REPAYMENT_FREQUENCY,
 REPAYMENT_AMT,
 EARLY_REPAY_ALLOWED,
  MIN_AGE_LIMIT,
 MAX_AGE_LIMIT,
  RESIDENT,
  PROD_START_DT,
   PROD_END_DT,
   PROD_STATUS)
    VALUES(P_PRODUCT_TITLE,
    P_PRODUCT_SUMMERY,
    P_INTEREST_TYPE,
    P_INTEREST_RATE,
    P_SECURITY_REQD,
    P_MIN_LOAN_AMT,
    P_MAX_LOAN_AMT,
     P_TERM_MIN,
    P_TERM_MAX,
    P_REPAYMENT_FREQUENCY,
    P_REPAYMENT_AMT,
    P_EARLY_REPAY_ALLOWED,
    P_MIN_AGE_LIMIT,
    P_MAX_AGE_LIMIT,
    V_T,/*PROBLEM IS HERE-Error(61,17): PL/SQL: ORA-00932: inconsistent 
          datatypes: expected NUMBER got SCOTT.SYS_PLSQL_75329_19_1*/
     P_PROD_START_DT,
     P_PROD_END_DT,
  P_PROD_STATUS
   );
          END LOAN_PRODUCT_INSERT_PROCEDURE; 


               END;

Please have a look at [mcve]. It will help you to impove your question and get good answers — Aleksej, Feb 01 '18 at 12:38
What datatype is LOAN_PROD_TAB.V_T column? Do all the parameters V_1, V_2, V_3, V_4, V_5 have values ? — alexs, Feb 01 '18 at 12:46
LOAN_PROD_TAB HAS A RESIDENT COLUMN WHICH DATATYPE IS RESIDENT_VARRAY(VARRAY DATATYPE :VARRAY(5) OF VARCHAR2(15)) — Saumya Ranjan, Feb 01 '18 at 13:19
what is the datatype of the loan_prod_type.resident column? Going from the error, I assume it's some form of NUMBER? — Boneist, Feb 01 '18 at 14:15

score 0 · Answer 1 · answered Feb 02 '18 at 10:47

The error you encounter occurs because a SQL type is not the same as a PL/SQL type.

The type of LOAN_PROD_TAB.RESIDENT column which is of a type you declared at schema level, is not recognized as the same as the type called RESIDENT_VARRAY you declared in the CBIS_LOAN_PROD_PACKAGE package spec.

To make it work, when you declare (and instantiate) the variable V_T, you have to use the same type you used when you declared the LOAN_PROD_TAB.RESIDENT column.

Below is a full example to verify my solution:

CREATE OR REPLACE TYPE mem_type IS VARRAY(5) of VARCHAR2(15);

CREATE TABLE test_va (
  va_Name VARCHAR2(10),
  va_Address VARCHAR2(20),
  va_City VARCHAR2(20),
  va_Phone VARCHAR2(8),
  va_Members mem_type);

create or replace package test_va_pkg as
  TYPE p_mem_type IS VARRAY(5) of VARCHAR2(15); -- cannot be used to insert into test_va.va_Members column!!!!
  procedure p_test_va(
    p_name VARCHAR2,
    p_Address VARCHAR2,
    p_City VARCHAR2,
    p_Phone VARCHAR2
  );
end test_va_pkg;
/

create or replace package body test_va_pkg as
  procedure p_test_va(
    p_name VARCHAR2,
    p_Address VARCHAR2,
    p_City VARCHAR2,
    p_Phone VARCHAR2
  ) is 
--    v_members_va p_mem_type := p_mem_type('V_1','V_2','V_3','V_4','V_5'); -- doesn't work!
    v_members_va mem_type := mem_type('V_1','V_2','V_3','V_4','V_5');
  begin
    insert into test_va
    values (
      p_name,
      p_Address,
      p_City,
      p_Phone,
      v_members_va
    );

    commit;
  exception 
    when others then
      rollback;

      raise;

  end p_test_va;
end test_va_pkg;
/

begin
  test_va_pkg.p_test_va('Eric Smith', 'N/A', 'London', '12345679');
end;
/

select * from test_va;

Hope it helps!

Error when insert into table with a varray column through package procedure

1 Answers1