How Do You Get ParameterMetadata from Stored Procedures (Oracle)? in JDBC
Interview Questions
Practice Test
Pragnyameter Exam
All Tutorials
Job SearchWhat is a “stored procedure”? A stored procedure or function is a program running in the database server that can take actions based on the input parameters. A stored procedure can have input, output, and input/output parameters. In JDBC, you may use a CallableStatement to invoke a stored procedure. Since CallableStatement extends PreparedStatement, you can invoke CallableStatement.getParameterMetaData() to get metadata information on a stored procedure’s parameters. In general, using a stored procedure is faster than doing the same work on a client, because the program runs right inside the database server. Stored procedures are normally written in SQL, Java, or combination of other languages such as PL/SQL (in Oracle).
According toWikipedia, a stored procedure is “a program (or procedure) which is physically stored within a database. They are usually written in a proprietary database language like PL/SQL for Oracle database. The advantage of a stored procedure is that when it is run, in response to a user request, it is run directly by the database engine, which usually runs on a separate database server. As such, it has direct access to the data it needs to manipulate and only needs to send its results back to the user.”
Next, let’s set up a stored procedure (called proc3), and invoke it using a CallableStatement. Then, we’ll invoke CallableStatement.getParameterMetaData() to get the stored procedure’s parameters metadata.
First, define a stored procedure in Oracle that has three parameters:
$ sqlplus scott/tigerSQL*Plus: Release 10.2.0.1.0 - Production on Sun Sep 18 13:30:53 2005
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
SQL> create procedure proc3(
2 p1 in varchar2,
3 p2 out varchar2,
4 p3 in out number
5 )
6 is
7 begin
8 -- body-of-stored-procedure-proc3
9 p2 := p1;
10 p3 := p3 + 10;
11 end;
12 /
Procedure created.
SQL> desc proc3;
PROCEDURE proc3
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P1 VARCHAR2 IN
P2 VARCHAR2 OUT
P3 NUMBER IN/OUT
SQL> var arg1 varchar2(12);
SQL> var arg2 varchar2(12);
SQL> var arg3 number;
SQL> begin :arg1:='abcd'; :arg3 := 6; proc3(:arg1, :arg2, :arg3); end;
2 .
SQL> run
1* begin :arg1:='abcd'; :arg3 := 6; proc3(:arg1, :arg2, :arg3); end;
PL/SQL procedure successfully completed.
SQL> print arg1 arg2 arg3
ARG1
--------------------------------
abcd
ARG2
--------------------------------
abcd
ARG3
----------
16
import java.sql.*;
import jcb.util.DatabaseUtil;
public class CallProc3 {
public static Connection getConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:goofy";
String username = "scott";
String password = "tiger";
Class.forName(driver); // load Oracle driver
return DriverManager.getConnection(url, username, password);
}
public static void main(String[] args) {
Connection conn = null;
try {
//
// Step-1: get a database connection
//
conn = getConnection();
System.out.println("conn="+conn);
//
// Step-2: identify the stored procedure
//
String proc3StoredProcedure = "{ call proc3(?, ?, ?) }";
//
// Step-3: prepare the callable statement
//
CallableStatement cs = conn.prepareCall(proc3StoredProcedure);
//
// Step-4: set input parameters ...
//
cs.setString(1, "abcd"); // first input argument
cs.setInt(3, 10); // third input argument
//
// Step-5: register output parameters ...
//
cs.registerOutParameter(2, java.sql.Types.VARCHAR);
cs.registerOutParameter(3, java.sql.Types.INTEGER);
//
// Step-6: execute the stored procedures: proc3
//
cs.execute();
//
// Step-7: extract the output parameters
//
String param2 = cs.getString(2); // get parameter 2 as output
int param3 = cs.getInt(3); // get parameter 3 as output
System.out.println("param2="+param2);
System.out.println("param3="+param3);
System.out.println("------------------------------------");
}
catch(Exception e){
e.printStackTrace();
System.exit(1);
}
finally {
DatabaseUtil.close(conn);
}
}
}
$ javac CallProc3.java
$ java CallProc3
[email protected]
param2=abcd
param3=20
------------------------------------
Now, we can check to see if a CallableStatement supports ParameterMetaData:
//// Step-5.5: get ParameterMetaData
//
ParameterMetaData pmeta = cs.getParameterMetaData();
if (pmeta == null) {
System.out.println("Vendor does not support ParameterMetaData");
}
else {
System.out.println(pmeta.getParameterType(1));
System.out.println(pmeta.getParameterType(2));
System.out.println(pmeta.getParameterType(3));
}
If you add these lines to the CallProc3 class
(call the new class CallProc3WithMetadata) and run the program again, you will get the following error (this means that Oracle does not support the CallableStatement.getParameterMetaData() method):
$ java CallProc3WithMetadata
[email protected]
java.sql.SQLException: Unsupported feature
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:227)
at oracle.jdbc.driver.DatabaseError.throwUnsupportedFeatureSqlException(
DatabaseError.java:537)
at oracle.jdbc.driver.OraclePreparedStatement.getParameterMetaData(Oracle
PreparedStatement.java:9086)
at CallProc3WithMetadata.main(CallProc3WithMetadata.java:49)
There is an alternative way that you can get the metadata information on a stored procedure’s parameters: you can use Connection.getMetaData(), which will give you a DatabaseMetaData object, and then using that DatabaseMetaData object, you can get the signature of all stored procedures by using the following methods:
// retrieves a description of the stored procedures// available in the given catalog.
ResultSet getProcedures(String catalog,
String schemaPattern,
String procedureNamePattern)
// retrieves a description of the given catalog's
// stored procedure parameter and result columns.
ResultSet getProcedureColumns(String catalog,
String schemaPattern,
String procedureNamePattern,
String columnNamePattern)
