What Are the Names of a Database's Stored Procedures? in JDBC

In a relational database management system such as Oracle, a stored procedure is a precompiled set of SQL statements and queries that can be shared by a number of programs. It is stored under a name as an executable unit. A stored function is similar to a function (like in Java and C/C++); it accepts zero, one, or more parameters and returns a single result. Stored procedures and functions are helpful in the following ways:

  • Controlling access to data: They can restrict client programs to data accessible only through the stored procedure.
  • Preserving data integrity: They ensure that information is entered in a consistent manner.
  • Improving productivity: You need to write a stored procedure only once.

Oracle, Microsoft SQL Server 2000, and Sybase Adaptive Server support stored procedures, but MySQL does not (stored procedures and views will be supported in MySQL 5.0.1, however). In general, you can use stored procedures to maximize security and increase data access efficiency. Because stored procedures execute in the database server, they minimize the network traffic between applications and the database, increasing application and system performance. Most of the time, stored procedures run faster than SQL. They also allow you to isolate your SQL code from your application.

Using Oracle9i database, consider the following table:

SQL> describe zemps;
Name Null? Type
---------------------------------- -------- -------------
ID NOT NULL NUMBER(38)
FIRSTNAME NOT NULL VARCHAR2(32)
LASTNAME NOT NULL VARCHAR2(32)
DEPT NOT NULL VARCHAR2(32)
TITLE VARCHAR2(32)
SALARY NUMBER(38)
EMAIL VARCHAR2(64)
COUNTRY VARCHAR2(32)

Next, try a basic query of the zemps table:

SQL> select id, firstName, lastName from zemps;
ID FIRSTNAME LASTNAME
---------- ---------- -----------
4401 Donald Knuth
4402 Charles Barkeley
4403 Alex Badame
4404 Jeff Torrango
4405 Mary Smith
4406 Alex Sitraka
4408 Jessica Clinton
4409 Betty Dillon
5501 Troy Briggs
5502 Barb Tayloy
6601 Pedro Hayward
6602 Chris Appleseed
6603 Tao Yang
6604 Kelvin Liu
14 rows selected.

The following stored procedure, getEmpCount, returns the number of records in the zemps table:

SQL> CREATE OR REPLACE function getEmpCount return int is
2 empCount int;
3 BEGIN
4 SELECT count(*) INTO empCount FROM zEmps;
5 RETURN empCount;
6 END getEmpCount;
7
8
9 /
Function created.

In order to make sure that getEmpCount is created correctly, you can execute it as follows,without passing any parameters:

SQL> var empCount number;
SQL> exec :empCount := getEmpCount;
PL/SQL procedure successfully completed.
SQL> print empCount;
EMPCOUNT
----------
14

The output proves that the getEmpCount performed correctly because it returned 14, which is the total number of records in the zemps table.

Overloading Stored Procedures

Oracle’s PL/SQL allows two or more packaged subprograms to have the same name. A package is a set of logically related functions and procedures, also known as a stored procedure. When stored procedures have the same name but different parameters, this is called overloading. This option is useful when you want a subprogram or function to accept parameters that have different data types. Be very cautious when you call overloaded subprogram or functions. You must make sure that you are passing the expected number of arguments and data types. For example, in Oracle 9i, the following package defines two functions named empPackage.

Oracle’s package specification is as follows:

CREATE or REPLACE PACKAGE empPackage AS
FUNCTION getEmployeeID(eFirstName VARCHAR2) return INT;
FUNCTION getEmployeeID(eFirstName VARCHAR2, eLastName VARCHAR2) return INT;
END empPackage;

Oracle’s package implementation is as follows:

CREATE or REPLACE PACKAGE BODY empPackage AS
FUNCTION getEmployeeID (eFirstName VARCHAR2) return INT is
empID INT;
BEGIN
SELECT id INTO empID FROM zEmps where firstName = eFirstName;
RETURN empID;
END getEmployeeID;
FUNCTION getEmployeeID (eFirstName VARCHAR2, eLastName VARCHAR2) return INT is
empID INT;
BEGIN
SELECT id INTO empID FROM zEmps
where firstName = eFirstName and lastName = eLastName;
RETURN empID;
END getEmployeeID;
END empPackage;
Here’s the empPackage description from the database:
SQL> describe empPackage;
FUNCTION GETEMPLOYEEID RETURNS NUMBER(38)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
EFIRSTNAME VARCHAR2 IN
FUNCTION GETEMPLOYEEID RETURNS NUMBER(38)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
EFIRSTNAME VARCHAR2 IN
ELASTNAME VARCHAR2 IN
Now execute these two functions:
SQL> var id1 NUMBER;
SQL> exec :id1:= empPackage.getEmployeeID('Donald');
PL/SQL procedure successfully completed.
SQL> print id1;
ID1
----------
4401
SQL> var id2 NUMBER;
SQL> exec :id2:= empPackage.getEmployeeID('Betty', 'Dillon');
PL/SQL procedure successfully completed.
SQL> print id2;
ID2
----------
4409

Note You may be wondering what this discussion has to do with getting the names of the stored procedures. This is because stored procedure names can be overloaded, and so you must be very careful in selecting the stored procedure names and their associated input parameter types.

How Can You Find the Package Code in Oracle?

The following SQL statement provides a way to see the Oracle package code:

select LINE, TEXT
from USER_SOURCE
where NAME ='&PKG' and TYPE = '&PACKAGE_TYPE'

where:

  • PKG refers to the package name.
  • PACKAGE_TYPE is the PACKAGE for the package specification.
  • PACKAGE BODY displays the body.

What Is the user_source Table?

The user_source table, which is a property of Oracle’s SYS user, is as follows. The output has been modified to include a description column.

SQL> describe user_source;
Name Type Description
---- --------------- ----------------------------
NAME VARCHAR2(30) Name of the object
TYPE VARCHAR2(12) Type of the object: "TYPE", "TYPE BODY",
"PROCEDURE", "FUNCTION", "PACKAGE",
"PACKAGE BODY" or "JAVA SOURCE"'
LINE NUMBER Line number of this line of source
TEXT VARCHAR2(4000) Source text
SQL> select name, type, line from user_source;
NAME TYPE LINE
------------------------------ ------------ ----------
EMPPACKAGE PACKAGE 1
EMPPACKAGE PACKAGE 2
EMPPACKAGE PACKAGE 3
EMPPACKAGE PACKAGE 4
EMPPACKAGE PACKAGE BODY 1
EMPPACKAGE PACKAGE BODY 2
EMPPACKAGE PACKAGE BODY 3
EMPPACKAGE PACKAGE BODY 4
EMPPACKAGE PACKAGE BODY 5
EMPPACKAGE PACKAGE BODY 6
EMPPACKAGE PACKAGE BODY 7
EMPPACKAGE PACKAGE BODY 8
EMPPACKAGE PACKAGE BODY 9
EMPPACKAGE PACKAGE BODY 10
EMPPACKAGE PACKAGE BODY 11
EMPPACKAGE PACKAGE BODY 12
EMPPACKAGE PACKAGE BODY 13
EMPPACKAGE PACKAGE BODY 14
EMPPACKAGE PACKAGE BODY 15
GETEMPCOUNT FUNCTION 1
GETEMPCOUNT FUNCTION 2
GETEMPCOUNT FUNCTION 3
GETEMPCOUNT FUNCTION 4
GETEMPCOUNT FUNCTION 5
GETEMPCOUNT FUNCTION 6
GETEMPCOUNT FUNCTION 7
26 rows selected.

What Are the Names of a Database’s Stored Procedures?

In the JDBC API, you can use the DatabaseMetaData.getProcedures() method to get the names of a database’s stored procedures and functions. However, this is not sufficient for very large databases. For example, in an Oracle database, atabaseMetadata.getProcedures() can return hundreds of stored procedures; most are system stored procedures, which most likely you do not need to retrieve. When you call this method, be as specific as possible when you provide names and patterns.

JDBC Solution: getProcedures()

Using JDBC, you can use DatabaseMetaData.getProcedures() to retrieve stored procedure names: To have a better performance, try to pass as much as information you can and avoid passing empty and null values to the DatabaseMetaData.getProcedures() method. Passing empty and null values might have a poor performance, and this is due to the fact that it might search all database catalogs and schemas. Therefore, it is best to pass as much as information (actual parameter values) to the DatabaseMetaData.getProcedures() method.

/**
* Get the stored procedures names.
* @param conn the Connection object
* @return a table of stored procedures names
* as an XML document (represented as a String object).
* Each element of XML document will have the name and
* type of a stored procedure.
*
*/
public static String getStoredProcedureNames
(java.sql.Connection conn,
String catalog,
String schemaPattern,
String procedureNamePattern) throws Exception {
ResultSet rs = null;
try {
DatabaseMetaData meta = conn.getMetaData();
if (meta == null) {
return null;
}
rs = meta.getProcedures(catalog, schemaPattern, procedureNamePattern);
StringBuffer sb = new StringBuffer();
sb.append("<storedProcedures>");
while (rs.next()) {
String spName = rs.getString("PROCEDURE_NAME");
String spType = getStoredProcedureType(rs.getInt("PROCEDURE_TYPE"));
sb.append("<storedProcedure name=\"");
sb.append(spName);
sb.append("\" type=\"");
sb.append(spType);
sb.append("\"/>");
}
sb.append("</storedProcedures>");
return sb.toString();
}
finally {
DatabaseUtil.close(rs);
}
}
private static String getStoredProcedureType(int spType) {
if (spType == DatabaseMetaData.procedureReturnsResult) {
return STORED_PROCEDURE_RETURNS_RESULT;
}
else if (spType == DatabaseMetaData.procedureNoResult) {
return STORED_PROCEDURE_NO_RESULT;
}
else {
return STORED_PROCEDURE_RESULT_UNKNOWN;
}
}

A Client Program

Before invoking a client program, let’s add another stored function: the getEmployeeCount stored function returns the number of employees for a specific department.

SQL> create FUNCTION getEmployeeCount(dept INTEGER) RETURN INTEGER IS
2 empCount INTEGER;
3 BEGIN
4 SELECT count(*) INTO empCount FROM EMPLOYEE
5 WHERE deptNumber = dept;
6 RETURN empCount;
7 END getEmployeeCount;
8 /
Function created.
SQL> describe getEmployeeCount;
FUNCTION getEmployeeCount RETURNS NUMBER(38)
Argument Name Type In/Out Default?
--------------------------- -------------------- ------ --------
DEPT NUMBER(38) IN
SQL> var empCount number;
SQL> exec :empCount := getEmployeeCount(23)
PL/SQL procedure successfully completed.
SQL> print empCount;
EMPCOUNT
----------
3

A Client Program

String spNames = DatabaseMetaDataTool.getStoredProcedureNames
(conn,
"",
"OCTOPUS",
"%");
System.out.println("-------- getStoredProcedureNames -------------");
System.out.println(spNames);
System.out.println("------------------------------------");

Output of the Client Program

<storedProcedures>
<storedProcedure name="GETEMPLOYEECOUNT" type="procedureReturnsResult"/>
<storedProcedure name="RAISESALARY" type="procedureNoResult"/>
<storedProcedure name="SHOWUSERS" type="procedureNoResult"/>
</storedProcedures>


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

JDBC Topics