JDBC for Oracle - Herong's Tutorial Examples - v3.13, by Herong Yang
getProcedures() - Listing Stored Procedures
This section describes how to get a list of stored procedures in the database server.
If you want to get a list of stored procedures through the JDBC interface, you can use the getProcedures() method on the DatabaseMetaData object as shown in the program program below:
/* OracleCallGetProcedures.java * Copyright (c) HerongYang.com. All Rights Reserved. */ import java.sql.*; public class OracleCallGetProcedures { public static void main(String [] args) { Connection con = null; try { oracle.jdbc.pool.OracleDataSource ds = new oracle.jdbc.pool.OracleDataSource(); ds.setDriverType("thin"); ds.setServerName("localhost"); ds.setPortNumber(1521); ds.setDatabaseName("XE"); ds.setUser("Herong"); ds.setPassword("TopSecret"); con = ds.getConnection(); DatabaseMetaData meta = con.getMetaData(); // Listing all stored procedures ResultSet res = meta.getProcedures(null, "HERONG", "%"); System.out.println("Stored procedures:"); while (res.next()) { System.out.println( " "+res.getString("PROCEDURE_CAT") + ", "+res.getString("PROCEDURE_SCHEM") + ", "+res.getString("PROCEDURE_NAME")); } res.close(); // Listing all tables res = meta.getTables(null, "HERONG", "%", null); System.out.println("Tables:"); while (res.next()) { System.out.println( " "+res.getString("TABLE_CAT") + ", "+res.getString("TABLE_SCHEM") + ", "+res.getString("TABLE_NAME")); } res.close(); con.close(); } catch (Exception e) { e.printStackTrace(); } } }
The output of the program looks good and clean with newer versions of JDK, Oracle Server and JDBC Driver:
herong> java -cp .;ojdbc11.jar OracleCallGetProcedures Stored procedures: null, HERONG, COUNTCHAR null, HERONG, UPDATEPOINT Tables: null, HERONG, PROFILE
But when running the same program with JDK 1.6, Oracle Server 10.2 and ojdbc14.jar, the output was a surprise to me:
herong> java -cp .;ojdbc11.jar OracleCallGetProcedures Stored procedures: null, HERONG, COUNTCHAR null, HERONG, UPDATEPOINT Tables: null, HERONG, BIN$RJZ2RPSgRIWblY7KVAPbaQ==$0 null, HERONG, BIN$gFli9YvMSzuaH8TNsZA/iw==$0 null, HERONG, PROFILE
Note that there were some tables named as "BIN$..." in the database. Those tables were copies of dropped tables stored in the recycle bin, just in case you want to restore them.
Table of Contents
JDBC (Java Database Connectivity) Introduction
Oracle Express Edition Installation on Windows
Oracle - Reference Implementation of JdbcRowSet
►Oracle - JBDC CallableStatement
Overview of CallableStatement Objects
"CREATE PROCEDURE" - Creating a Simple Procedure
Creating Procedures with IN and OUT Parameters
Creating CallableStatement Objects with prepareCall()
Creating CallableStatement Objects with Parameters
►getProcedures() - Listing Stored Procedures
Oracle CLOB (Character Large Object) - TEXT
Oracle BLOB (Binary Large Object) - BLOB