I found the easiest way to achieve this using Clojure. Here is a sample code:
(ns example
(:require [clojure.contrib.sql :as sql])
(:import [java.sql Types]))
(def devdb {:classname "oracle.jdbc.driver.OracleDriver"
:subprotocol "oracle"
:subname "thin:username/password@localhost:1509:devdb"
:create true})
(defn exec-ora-stored-proc [input-param db callback]
(sql/with-connection db
(with-open [stmt (.prepareCall (sql/connection) "{call some_schema.some_package.test_proc(?, ?, ?)}")]
(doto stmt
(.setInt 1 input-param)
(.registerOutParameter 2 Types/INTEGER)
(.registerOutParameter 3 oracle.jdbc.driver.OracleTypes/CURSOR)
(.execute))
(callback (. stmt getInt 2) (. stmt getObject 3)))))
(exec-ora-stored-proc
123 ;;input param value
devdb
(fn [err-code res-cursor]
(println (str "ret_code: " err-code))
;; prints returned refcursor rows
(let [resultset (resultset-seq res-cursor)]
(doseq [rec resultset]
(println rec)))))