Announcement Announcement Module
Collapse
No announcement yet.
Strugging with spring SimpleJdbcCall to call Oracle function Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Strugging with spring SimpleJdbcCall to call Oracle function

    I am struggling with the below code to make it work, searching documentation and forums and stucked.
    Finally I decided to ask you for help.
    What I have is package with TYPES, FUNCTION declarations and FUNCTION BODY declaration.
    In future I would like to use SYNONYM to MYPACKAGE (This is only mock - I will not have package and types declarations in my database, but use dblink to external database and Java code to run procedures / funtions, but now I don't have this dblink accessible) and MYPACKAGE will be something accessible through dblink:

    Code:
      create public synonym dblink_MYPACKAGE for SOME_SCHEMA.MYPACKAGE@dblink_externalDB
    ;
    and I will be using dblink_MYPACKAGE instead of MYPACKAGE in Java Code.
    (but this doesn't matter does it?) The external database is not ours, so we CAN'T change anything there...
    Code:
        public class TestClassSpringBased {
    
    	private DataSource dataSource;
    
    	private SimpleJdbcCall jdbcCall;
    
    	@Override
    	public void testMe(Integer id) {
    
    		int iid = 1;
    		SqlParameterSource in = new MapSqlParameterSource().addValue("IN_1", iid);
    
    		Map<String, Object> out = jdbcCall.execute(in);
      
    	}
    
    	public DataSource getDataSource() {
    		return dataSource;
    	}
    
    	public void setDataSource(DataSource dataSource) {
    		this.dataSource = dataSource;
    		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    		jdbcTemplate.setResultsMapCaseInsensitive(true);
    
    		this.jdbcCall = new SimpleJdbcCall(dataSource)
    				.withCatalogName("MYPACKAGE")
    				.withProcedureName("MYFUNCTION")
    				.withReturnValue()
    				.useInParameterNames("IN_1")
    				.declareParameters(
    						new SqlInOutParameter("IN_1", OracleTypes.NUMBER),
    						new SqlInOutParameter("OUT_1", OracleTypes.STRUCT, "MYPACKAGE.CUSTOMELEMENTSTYPE",
    								new SqlReturnType() {
    									public Object getTypeValue(CallableStatement cs, int colIndx, int sqlType,
    											String typeName) throws SQLException {
    
    										return null; //just let it work, the I will think what to write here
    									}
    								}));
    
    	}
    
        }
    
    
    
    
    
         create or replace 
         PACKAGE         MYPACKAGE IS 
     
    
           TYPE CUSTOMELEMENTSTYPE_R IS RECORD (
             C1 VARCHAR2(60),   
             C2    VARCHAR2(30)
             
          );
    
          TYPE CUSTOMELEMENTSTYPE IS TABLE OF CUSTOMELEMENTSTYPE_R 
          INDEX BY PLS_INTEGER;
     
     
     
         FUNCTION MYFUNCTION(
           IN_1 IN INTEGER, OUT_1 OUT CUSTOMELEMENTSTYPE )
         RETURN VARCHAR2;
      
      
           END;
    
    
    
        create or replace 
         PACKAGE BODY MYPACKAGE  IS
           
           FUNCTION MYFUNCTION(
             IN_1 IN INTEGER, OUT_1 OUT CUSTOMELEMENTSTYPE )
            RETURN VARCHAR2  IS
    
          BEGIN
        
         SELECT *  BULK COLLECT INTO OUT_1
            FROM  SOME_TABLE;
           RETURN 'return param';
         END MYFUNCTION; 
    
          END MYPACKAGE ;
    The ERROR is:
    org.springframework.jdbc.UncategorizedSQLException : CallableStatementCallback; uncategorized SQLException for SQL [{? = call MYPACKAGE.MYFUNCTION(?, ?)}]; SQL state [99999]; error code [17074]; invalid name pattern: MYPACKAGE.CUSTOMELEMENTSTYPE; nested exception is java.sql.SQLException: invalid name pattern: MYPACKAGE.CUSTOMELEMENTSTYPE

    The problem is only with OUT parameter, the same code works, when I dont pass OUT parameter and run it against another version of MYFUNCTION, that has not OUT parameter.

    I tried also with OracleTypes.ARRAY (invalid name pattern) and OracleTypes.OTHER (Caused by: java.sql.SQLException: wrong column type: 1111)

  • #2
    Hi, I don't think you can use type names declared inside a package. Try declaring it as a global type outside of a package.

    Comment


    • #3
      maybe you can try extending storedprocedure like :

      Code:
      FindDocumentsProcedure extends StoredProcedure
      
      public FindDocumentsProcedure(JdbcTemplate template) {
      		super(template, SPROC_NAME);
      		declareParameter(new SqlParameter(IN_1, Types.NUMERIC));
      		...
      				
      		declareParameter(new SqlReturnResultSet("rs",mapper));
      		
      		setUpdatableResults(false);
      		setReturnGeneratedKeys(false);
      		
      		compile();
      	}

      Comment

      Working...
      X