How Do You Get ParameterMetadata from Stored Procedures (Oracle)? JDBC

What 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/tiger
SQL*Plus: Release - Production on Sun Sep 18 13:30:53 2005
Connected to: Oracle Database 10g Enterprise Edition Release
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;
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
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
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();
// 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
// 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
catch(Exception e){
finally {
$ javac
$ java CallProc3

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 {

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):

$ javac
$ java CallProc3WithMetadata
java.sql.SQLException: Unsupported feature
at oracle.jdbc.driver.DatabaseError.throwSqlException(
at oracle.jdbc.driver.DatabaseError.throwSqlException(
at oracle.jdbc.driver.DatabaseError.throwSqlException(
at oracle.jdbc.driver.DatabaseError.throwUnsupportedFeatureSqlException(
at oracle.jdbc.driver.OraclePreparedStatement.getParameterMetaData(Oracle
at CallProc3WithMetadata.main(

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)

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

JDBC Topics