Hi, all java and db experts,
I need to write a tool to generate java file which will be used to hold the resultset of a stored procedure of Oracle. Is there any API call or tools to connect to db and then get the meta data of the return cursor instead of reading stored procedure definition on my own?
Please help, thanks a lot.
Hanna
Not sure what you mean by "metadata of the return cursor," but you might look into
[url http://java.sun.com/j2se/1.4.2/docs/api/java/sql/ResultSet.html#getMetaData()]ResultSet.getMetaData[/url]
and
[url http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Connection.html#getMetaData()]Connection.getMetaData[/url]
If i have not misunderstood your Q thn following code snippet should solve your issue :
try {
// Getting database metadata
DatabaseMetaData databasemd = connection.getMetaData();
// Getting all stored procedures in the database
ResultSet rs = databasemd .getProcedures(null, null, "%");
// Getting procedure names from resultset
while (rs.next()) {
String spNames = resultSet.getString(3);
}
} catch (DBSQLException e) { }
Cheers !
if i execute a Oracle stored procedure, the resultset of a cursor is returned. It's easy to know the meta data at the runtime.
However, could i get the meta data about the resultset of a cursor before runtime? Such as by connecting to the database and ask it about meta data of a specified stored procedure?
Is it feasible?
DatabaseMetaData dbmd = conn.getMetaData();
ResultSet rs = dbmd.getProcedureColumns("", "%", "SP_NAME", "%");
while (rs.next()) {
String colName = rs.getString(4);
int colType = rs.getInt(5);
int colDataType = rs.getInt(6);
int colPrecision = rs.getInt(8);
int colLen = rs.getInt(9);
int colScale = rs.getInt(10);
long defaultValue = rs.getLong(11);
....
}
But what i get is a list of stored procedure parameters. In oracle, cursor is IN OUT parameter . How can i get the meta data about the resultset of cursor?
