java get table list and package arguments from oracle

static Connection conn;
 static Statement st;

public static void main(String args[]) throws Exception{
 DatabaseMetaData md = conn.getMetaData();
 ResultSet rs = md.getTables(null, null, "%", null);
 while (rs.next()) {
 System.out.println(rs.getString(3)); // list table name
 }

String procedureCat = "XXX0000"; // package name
 String procedureSchem = "USERNAMENAJA";
 String procedureName = "CALL";  // function

rs = md.getProcedureColumns(procedureCat, procedureSchem, procedureName, null);
  while (rs.next()) {
System.out.println("----");
System.out.println("column name: "+rs.getObject(4)); // column name
System.out.println("data type: "+new Integer(rs.getInt(6))); // data type
System.out.println("columnReturnTypeName "+rs.getString(7)); // column return type name
int dbColumnReturn = rs.getInt(5);
String procReturn = "";
System.out.println("column return type: "+dbColumnReturn); // column type
switch(dbColumnReturn)
{
case DatabaseMetaData.procedureColumnIn:
procReturn = "In";
break;
case DatabaseMetaData.procedureColumnOut:
procReturn = "Out";
break;
case DatabaseMetaData.procedureColumnInOut:
procReturn = "In/Out";
break;
case DatabaseMetaData.procedureColumnReturn:
procReturn = "return value";
break;
case DatabaseMetaData.procedureColumnResult:
procReturn = "return ResultSet";
default:
procReturn = "Unknown";
}
System.out.println("column return : " + procReturn);
}
 }

static {
 try {
 // Step 1: Load the JDBC driver.
 Class.forName("oracle.jdbc.driver.OracleDriver");
 System.out.println("Driver Loaded.");
 // Step 2: Establish the connection to the database.
 String url = "jdbc:oracle:thin:@192.168.XX.XX:1521:XE";

conn = DriverManager.getConnection(url, "username", "passwordnaja");
 System.out.println("Got Connection.");
<pre>st = conn.createStatement();
 } catch (Exception e) {
 System.err.println("Got an exception! ");
 e.printStackTrace();
 System.exit(0);
 }
 }

RESULT OF get function args

column name: null
data type: 1111
CLOB
column return type: 5
column return : return value
----
column name: IN_FUNC_NAME
data type: 12
VARCHAR2
column return type: 1
column return : In
----
column name: IN_PARAMS
data type: 12
VARCHAR2
column return type: 1
column return : In

NOTE :

1. data type(java.sql.Types)

1111 = Types.OTHER

2005 = Types.CLOB

12 = Types.VARCHAR

2. Reference for each col in getProcedureColumns() resultset

String procedureCatalog     = rs.getString(1);
String procedureSchema      = rs.getString(2);
String procedureName        = rs.getString(3);
String columnName           = rs.getString(4);
short  columnReturn         = rs.getShort(5);
int    columnDataType       = rs.getInt(6);
String columnReturnTypeName = rs.getString(7);
int    columnPrecision      = rs.getInt(8);
int    columnByteLength     = rs.getInt(9);
short  columnScale          = rs.getShort(10);
short  columnRadix          = rs.getShort(11);
short  columnNullable       = rs.getShort(12);
String columnRemarks        = rs.getString(13);

3. NOTE that, if package function return CLOB, java will return as Types.OTHER (1111 like above output ).

If we use Types.OTHER in ps.registerOutParameter(), we will face the following error.

org.seasar.framework.exception.SQLRuntimeException: [ESSR0072]SQLException(SQL=[], Message=[Invalid column type], ErrorCode=17004, SQLState=null)

We must pass Types.CLOB into ps.registerOutParameter(), not Types.OTHER.

ref : java2s, mkyong, jguruCodo

Advertisements