Announcement Announcement Module
Collapse
No announcement yet.
Passing an ARRAY to Oracle Procedure Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Passing an ARRAY to Oracle Procedure

    I've seen some examples on how to do this and got to the following code,

    Code:
    public class ServicePlanCreate extends StoredProcedure {
        
        public ServicePlanCreate (DataSource ds, String spName) {
            super(ds, spName);
                   
            declareParameter(new SqlParameter("IN_SERVICE_PLAN_NAME", Types.VARCHAR));
            declareParameter(new SqlParameter("IN_PROPERTIES", Types.ARRAY,"VARRAY_VARCHAR_TYPE"));
    
            declareParameter(new SqlOutParameter("OUT_SERVICE_PLAN_ID", Types.NUMERIC));
            declareParameter(new SqlOutParameter("OUT_ERRORCODE", Types.NUMERIC));
            declareParameter(new SqlOutParameter("OUT_ERROR", Types.VARCHAR));
            compile();
        }
        
        public Map servicePlanCreate (String servicePlanName, String[] properties) {
            Map inParameters = new HashMap();                 
    
            final String[] temp = properties;
            inParameters.put("IN_SERVICE_PLAN_NAME", servicePlanName);
            inParameters.put("IN_PROPERTIES", new AbstractSqlTypeValue() {
                public Object createTypeValue(Connection con, int type, String typeName) throws SQLException {
                    ArrayDescriptor desc = new ArrayDescriptor(typeName, con);
                    return new ARRAY(desc, con, temp);
                }
            });
            
            Map out = execute(inParameters);
            return out;
        }
    }
    but this isnt working im getting the following error
    Code:
    java.lang.ClassCastException
    	at oracle.jdbc.driver.OracleConnection.physicalConnectionWithin(OracleConnection.java:5126)
    	at oracle.sql.TypeDescriptor.setPhysicalConnectionOf(TypeDescriptor.java:494)
    	at oracle.sql.TypeDescriptor.(TypeDescriptor.java:98)
    	at oracle.sql.ArrayDescriptor.(ArrayDescriptor.java:147)
    	at model.service.sp.ServicePlanCreate$1.createTypeValue(ServicePlanCreate.java:49)
    am i doing something wrong, or am i missing something?
    TIA

  • #2
    Since you are using Oracle specific calls on the Connection you need to set the nativeJdbcExtractor property on your JdbcTemplate:

    Code:
    <bean id="template" class="org.springframework.jdbc.core.JdbcTemplate">
      <constructor-arg><ref bean="dataSource"/></constructor-arg>
      <property name="nativeJdbcExtractor"><bean class="org.springframework.jdbc.support.nativejdbc.SimpleNativeJdbcExtractor"/></property>
    </bean>
    This will make sure that createTypeValue gets passed an actualy Oracle connection, not one of the Spring wrapped ones.

    Comment


    • #3
      More on this issue ...

      Hi!

      I have a slightly different problem. I have used the approach described in the "Professional Java Development With the Spring Framework" book, page 210 and 211 outlining how to pass an ARRAY to an Oracle stored proc. We are making a webapplication for handling Acegi Security and the problem is related to updating a USER table related to a USER_ROLE mapping table

      Here is my declared Oracle array:

      Code:
      create or replace type ROLEIDS as TABLE OF NUMBER
      My procedure:
      Code:
      create or replace package body PCK_CS is
      
        /* ------------ create user ------------*/
        PROCEDURE SP_CREATE_USER(user_id      in varchar2,
                                 user_fname   in varchar2,
                                 user_lname   in varchar2,
                                 user_pwd     in varchar2,
                                 user_mobile  in varchar2,
                                 user_phone   in varchar2,
                                 user_title   in varchar2,
                                 user_chby    in varchar2,
                                 user_email   in varchar2,
                                 user_address in varchar2,
                                 user_role_ids in roleids) is
          -- internal variables
          gen_usr_id      number;
          gen_usr_role_id number;
        
        begin
        
          select SEQ_CS_USERS.nextval into gen_usr_id from dual;
          select SEQ_CS_USERS_ROLE.nextval into gen_usr_role_id from dual;
        
          insert into CS_USERS
            (ID,
             USERID,
             FIRST_NAME,
             LAST_NAME,
             PASSWORD,
             MOBILE,
             PHONE,
             TITLE,
             TIMESTAMP,
             CHANGEDBY,
             EMAIL,
             ADDRESS)
          values
            (gen_usr_id,
             user_id,
             user_fname,
             user_lname,
             user_pwd,
             user_mobile,
             user_phone,
             user_title,
             sysdate,
             user_chby,
             user_email,
             user_address);
        
          -- updating mapping table (CS_USERS_ROLE)
          for i in 1..user_role_ids.count loop
          insert into CS_USERS_ROLE
            (ID, USER_ID, ROLE_ID, TIMESTAMP, CHANGEDBY)
          values
            (gen_usr_role_id, gen_usr_id, user_role_ids(i), sysdate, user_chby);
          end loop;
          
        end SP_CREATE_USER;
      end PCK_CS;
      The class that extends StoredProcedure has this execute method looks like this:
      Code:
      public Map execute(
      		final String userId,
      		final String userFname,
      		final String userLname,
      		final String passwd,
      		final String userMobile,
      		final String userPhone,
      		final String userTitle,
      		final String userChby,
      		final String userEmail,
      		final String useraddr,
      		final Integer[] arrRoleId)
      	{
      		return execute(new ParameterMapper()
      		{
      			public Map createMap(Connection conn) throws SQLException
      			{
      
      				HashMap inParams = new HashMap(11);
      				inParams.put("user_id", userId);
      				inParams.put("user_fname", userFname);
      				inParams.put("user_lname", userLname);
      				inParams.put("user_pwd", passwd);
      				inParams.put("user_mobile", userMobile);
      				inParams.put("user_phone", userPhone);
      				inParams.put("user_title", userTitle);
      				inParams.put("user_chby", userChby);
      				inParams.put("user_email", userEmail);
      				inParams.put("user_address", useraddr);
      				// Creating an oracle array input parameter for the procedure
      
      				ArrayDescriptor intDesc = new ArrayDescriptor("ROLEIDS", conn);
      				ARRAY roleIds = new ARRAY(intDesc, conn, arrRoleId);
      				inParams.put("user_role_ids", arrRoleId);
      				return inParams;
      			}
      		});
      	}
      When this code executes I end up with an SQLException with this detail message:
      Code:
      ORA-06550: line 1, column 7:
      PLS-00306: wrong number or types of arguments in call to 'SP_CREATE_USER'
      ORA-06550: line 1, column 7:
      PL/SQL: Statement ignored
      This occurs in line 691 in the OraclePreparedStatement class. We are using Oracle 9.2.0.4 and the ojdbc14.zip JDBC driver.

      It seems to me that there is some mismatch between the Java array and the Oracle array. As I said; I think my code is equal to the example in the book from Rod Johnson & company mentioned above.

      Any help would be greatly appreciated!

      regards,
      -jørgen, NORWAY

      Comment


      • #4
        Shouldn't this line:

        inParams.put("user_role_ids", arrRoleId);

        be:

        inParams.put("user_role_ids", roleIds);

        since you do want to use the new ARRAY class you just created.

        Comment


        • #5
          Errrm ... You are quite right! But unfortunately it was a typing mistake in the post and not in the code ... so the code was right:
          Code:
          ARRAY roleIds = new ARRAY(intDesc, conn, arrRoleId);
          inParams.put("user_role_ids", roleIds);
          I have now tested to create the array in the method instead of sending it in as a parameter (exactly like the example in the book) - but still no luck.

          I am getting really stuck on this now - any suggestions??

          Btw, thanks for the quick reply!

          -jørgen

          Comment


          • #6
            Could you post the code where the declareParameter calls are - probably the constructor of your StoredProcedure class.

            Comment


            • #7
              Hi!

              I'm sorry for the delay on this matter - but I have now sorted it out (kind of ;-)

              I rewrote the execute method in the procedure class so it is similar to the one on top of this thread. This way I omit the use of the ParameterMapper class and then things work out:

              Code:
              public class CallCreateUser extends StoredProcedure
              {
              	private static final String SP_CREATE_USER = "PCK_CS.SP_CREATE_USER";
              
              	public CallCreateUser(JdbcTemplate dbHelper)
              	{
              		super(dbHelper, SP_CREATE_USER);
              		declareParameter(new SqlParameter("user_id", Types.VARCHAR));
              		declareParameter(new SqlParameter("user_fname", Types.VARCHAR));
              		declareParameter(new SqlParameter("user_lname", Types.VARCHAR));
              		declareParameter(new SqlParameter("user_pwd", Types.VARCHAR));
              		declareParameter(new SqlParameter("user_mobile", Types.VARCHAR));
              		declareParameter(new SqlParameter("user_phone", Types.VARCHAR));
              		declareParameter(new SqlParameter("user_title", Types.VARCHAR));
              		declareParameter(new SqlParameter("user_chby", Types.VARCHAR));
              		declareParameter(new SqlParameter("user_email", Types.VARCHAR));
              		declareParameter(new SqlParameter("user_address", Types.VARCHAR));
              		declareParameter(new SqlParameter("user_role_ids", Types.ARRAY, "ROLEIDS"));
              		compile();
              	}
              	
              	public Map executeArray(
              		final String userId,
              		final String userFname,
              		final String userLname,
              		final String passwd,
              		final String userMobile,
              		final String userPhone,
              		final String userTitle,
              		final String userChby,
              		final String userEmail,
              		final String useraddr,
              		final Integer[] arrRoleId)
              	{
              		Map inParams = new HashMap(11);
              		inParams.put("user_id", userId);
              		inParams.put("user_fname", userFname);
              		inParams.put("user_lname", userLname);
              		inParams.put("user_pwd", passwd);
              		inParams.put("user_mobile", userMobile);
              		inParams.put("user_phone", userPhone);
              		inParams.put("user_title", userTitle);
              		inParams.put("user_chby", userChby);
              		inParams.put("user_email", userEmail);
              		inParams.put("user_address", useraddr);
              		inParams.put("user_role_ids", new AbstractSqlTypeValue()
              		{
              			public Object createTypeValue(Connection conn, int type, String typeName) throws SQLException
              			{
              				ArrayDescriptor desc = new ArrayDescriptor(typeName, conn);
              				return new ARRAY(desc, conn, arrRoleId);
              			}
              		});
              
              		Map out = execute(inParams);
              		return out;
              	}
              
              }
              I don't know exactly what to get out of this other than maybe there is a bug in the ParameterMapper class?

              Anyway, this is interesting, and if you want to discuss this further I will gladly do so. I would like to see an (fully working) example passing a java array to an PL/SQL procedure using Oracle defined arrays and the ParameterMapper class.

              regards,
              -jørgen

              Comment


              • #8
                I'm also having problems with accessing a stored procedure via Spring.

                Error message says:

                Code:
                org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call TEST_PKG.doTest(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}]; SQL state [null]; error code [17059]; Konvertierung zu interner Darstellung nicht erfolgreich: [Ljava.lang.String;@1815338; nested exception is java.sql.SQLException: Konvertierung zu interner Darstellung nicht erfolgreich: [Ljava.lang.String;@1815338
                java.sql.SQLException: Konvertierung zu interner Darstellung nicht erfolgreich: [Ljava.lang.String;@1815338
                	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
                	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
                	at oracle.sql.ARRAY.toARRAY(ARRAY.java:238)
                	at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:9152)
                	at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8739)
                	at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:9229)
                	at oracle.jdbc.driver.OracleCallableStatement.setObject(OracleCallableStatement.java:4691)
                	at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:162)
                	at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:191)
                	at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:68)
                	at org.springframework.jdbc.core.CallableStatementCreatorFactory$CallableStatementCreatorImpl.createCallableStatement(CallableStatementCreatorFactory.java:196)
                	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:818)
                	at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:857)
                	at org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:102)
                There are to "STRING_ARRAY"s defined in the stored procedure. Maybe this cause the problems.

                I'm using the Spring StoredProcedure class - declaration of parameters:

                Code:
                 declareParameter(new SqlParameter("keyList", Types.ARRAY,
                                "TEST.STRING_ARRAY"));
                 declareParameter(new SqlParameter("valueList", Types.ARRAY,
                                "TEST.STRING_ARRAY"));

                The plain Java way does work:

                Code:
                 ArrayDescriptor desc = ArrayDescriptor.createDescriptor(
                                    "TEST.STRING_ARRAY", dbConnection);
                
                            ARRAY keyArray = new ARRAY(desc, dbConnection, keyList);
                            cs.setArray(12, keyArray);
                            ARRAY valueArray = new ARRAY(desc, dbConnection, valueList);
                            cs.setArray(13, valueArray);

                But I want to use Spring for accessing the Stored Procedure.

                Any idea?

                Thanks in advance.

                Pieper

                Comment


                • #9
                  Do you have the stored procedure declaration? Are you sure the arrays are of the same type?

                  Comment


                  • #10
                    No, unfortunately I haven't got the declaration of the stored procedure.

                    But the plain java example IS working, so the declarations should work. (call the same arrays in the java example)

                    Maybe I cannot use Types.ARRAY?

                    Pieper

                    Comment


                    • #11
                      btw -
                      java.sql.SQLException: Konvertierung zu interner Darstellung nicht erfolgreich

                      means

                      java.sql.SQLException: Fail to convert to internal representation


                      Pieper

                      Comment


                      • #12
                        Hi all,

                        anyone, who has an idea what causes problem?

                        Did I forget to add something?

                        Pieper
                        Last edited by Pieper; Feb 21st, 2006, 04:04 AM.

                        Comment


                        • #13
                          move to top of forum.

                          I haven't found a solution using Spring. I've now have developed a class which uses plain JDBC to access the stored procedure.

                          Does anyone know how to solve it with Spring support?

                          Pieper

                          Comment


                          • #14
                            Can you post the code for your Spring StoredProcedure implementation that is not working - maybe we can spot the problem if you provide the code.

                            Comment

                            Working...
                            X