Getting exception while passing List<Long> from hibernate to Stored Procedure

[Deeps Mistry]

Hi,

I need to pass List<Long> from hibernate to my stored procedure. Below is the code for the same -

Here is my query -

Query q = getSession().getNamedQuery("cloneSpecialClauses")
                .setLong("grpId",groupId)
                .setLong("maId",maId)
                .setLong("amdId",amdId)
                .setString("user",user)
                .setParameterList("deleted_spc_ids",deleted_spc_ids)
               .setParameterList("updated_spc_ids",updated_spc_ids);
              q.uniqueResult();

<sql-query name="cloneClauses" callable="true">
        { call clone_clause(?, :grpId, :maId, :amdId, :user, :deleted_spc_ids, :updated_spc_ids) }
    </sql-query>

My stored procedure looks like this -

CREATE OR REPLACE TYPE t_in_list AS TABLE OF NUMBER;
CREATE OR REPLACE PROCEDURE clone_clause (

   grpid   NUMBER,
   maid    NUMBER,
   amdid   NUMBER,
   cuser   VARCHAR2,
   deleted_spc_ids t_in_list,
   updated_spc_ids t_in_list
  
)

AS

BEGIN
 
   FOR elem in 1 .. deleted_spc_ids.count LOOP
  
   DELETE FROM  abc th
         WHERE th.ma_id IN (SELECT ma_id
                              FROM def
                             WHERE ma_id <> maid AND grp_id = grpid)
           AND th.amd_id = amdid
           AND th.spc_cla_id = deleted_spc_ids(elem);
    END LOOP;
    
    
    FOR elem1 in 1 .. updated_spc_ids.count LOOP
    
    UPDATE abc  c1
      SET is_applicable =
       (select is_applicable from abc where ma_id=maid 
       and spc_cla_id=updated_spc_ids(elem1)),
          date_modif = SYSDATE,
          user_modif = cuser
    WHERE ma_id IN (SELECT ma_id
                      FROM def
                     WHERE ma_id <> maid AND grp_id = grpid)
          AND amd_id = amdid
          AND spc_cla_id = updated_spc_ids(elem1);
          
     END LOOP;
     
     
              
END; 

The exception is get is -

org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: could not execute query; bad SQL grammar [{ call clone_special_clause(?, ?, ?, ?, ?, ?, ?, , ?, ?, ?) }]; nested exception is java.sql.SQLException: ORA-06550: line 1, column 56:
PLS-00103: Encountered the symbol "," when expecting one of the following:

   ( - + case mod new not null others <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> avg
   count current exists max min prior sql stddev sum variance
   execute forall merge time timestamp interval date
   <a string literal with character set specification>
   <a number> <a single-quoted SQL string> pipe
   <an alternatively-quoted string literal with character set specification>
   <an alternatively-q

Is there something wrong with my stored procedure or the way i am passing the List to my stored procedure?

Thanks.

[Bogdan Baraila]

I think that the problem my be in how you are using the NUMBER data type. If you are using oracle then you should use something like this: NUMBER(p) or INTEGER or LONG (BLOB). Here it is the description of NUMBER. As you can see if it's used like in your function then it will basically expect a list of floats.
The NUMBER datatype
Stores zero, positive, and negative numbers, fixed or floating-point numbers

Fixed-point NUMBER
NUMBER(p,s)
precision p = length of the number in digits
scale s = places after the decimal point, or (for negative scale values) significant places before the decimal point.

Integer NUMBER
NUMBER(p)
This is a fixed-point number with precision p and scale 0. Equivalent to NUMBER(p,0)

Floating-Point NUMBER
NUMBER
floating-point number with decimal precision 38