Announcement Announcement Module
Collapse
No announcement yet.
Oracle Function returning a REF CURSOR Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Oracle Function returning a REF CURSOR

    Hi, I found this example (http://forum.springframework.org/viewtopic.php?t=514) of calling a Postgresql function that returns a ref cursor and people have said it works for Oracle too. However, I have not been able to get it to work because there seems to be differences.

    For example, unless I use #setFunction(true); before compiling the StoredProcedure, Oracle won't even find the function.

    Also, I am using Springs DriverManagerDataSource for testing which does not support #setDefaultAutoCommit(). There must be another way to do this but I haven't found it yet.

    Is anybody else using Spring to access REF CURSORs from an Oracle Stored Procedure or an Oracle Function?

    Also, any ideas on how I can go about attacking this problem would be gratefully appreciated.

    Thanks!

    - Jonathan

  • #2
    Oracle + Spring + Funcs and Procs returning REF CURSOR

    This solution is far more straightforward than the PostgreSQL solution I mentioned in the previous post. It really threw me off there for a bit:

    Here is a basic function that returns sysdate in a REF CURSOR:
    Code:
    FUNCTION MY_DEMO_FNC
    	RETURN	MY_REFCURSOR_PKG.RefCursor
    AS	
    	return_date MY_REFCURSOR_PKG.RefCursor;
    BEGIN
    	OPEN return_date FOR 'select  sysdate from dual';
    	return return_date;
    END MY_DEMO_FNC;
    Here is the class that accesses it:
    Code:
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.HashMap;
    import java.util.Map;
    import javax.sql.DataSource;
    import oracle.jdbc.OracleTypes;
    import org.springframework.jdbc.core.RowCallbackHandler;
    import org.springframework.jdbc.core.SqlOutParameter;
    import org.springframework.jdbc.datasource.DriverManagerDataSource;
    import org.springframework.jdbc.object.StoredProcedure;
    
    public class RefCursorTestDao 
    {
      public static void main(String[] args) throws Exception 
      {
        new RefCursorTestDao().execute();
      }
    
      public void execute() throws Exception 
      {
        DataSource ds = new DriverManagerDataSource(
            "oracle.jdbc.driver.OracleDriver",
            "jdbc:oracle:thin:@localhost:1521:SID1", 
            "user", "password");
    
        DemoStoredProcedure proc = new DemoStoredProcedure(ds);
        Map params = new HashMap();
        proc.execute(params);
      }
    
      private class DemoStoredProcedure extends StoredProcedure 
      {
        public static final String SQL = "MY_TEST_PKG.MY_DEMO_FNC";
    
        public DemoStoredProcedure(DataSource ds) 
        {
          setDataSource(ds);
          setSql(SQL);
          setFunction(true);
          declareParameter(
             new SqlOutParameter(
                 "whatever", OracleTypes.CURSOR,  new DemoRowMapper()));
          compile();
        }
      }
    
      private class DemoRowMapper implements RowCallbackHandler 
      {
        public void processRow(ResultSet rs) throws SQLException 
        {
          System.out.println(rs.getTimestamp(1));
        }
      }
      
    }
    The process is the same for a real procedure with the REF CURSOR as an OUT parameter, but #setFunction should be false instead of true.

    Comment


    • #3
      I believe the link in the first post should be http://forum.springframework.org/arc...p/t-10054.html

      Comment


      • #4
        Using WebLogic connection pool based DataSource

        Instead of using DriverManagerDataSource, can I use WebLogic connection pool DataSource, and how?

        Thanks,

        Comment


        • #5
          thanks for the info guys. it'll really help me.

          Comment


          • #6
            Please, move your question to the StackOverflow - we are going to close this forum soon and rely on SO.
            We need to clean this forum (old unanswered question) before close it.

            Thanks for understanding

            Comment

            Working...
            X