/*******************************************************************************
Created by Paco van der Linden in his spare time, used in Oracle JHeadstart and
several other products.
Notes :
Open Issues :
$revision_history$
04-aug-2011 Paco van der Linden
1.4 oracleReturnValue is now correctly implemented
02-dec-2010 Paco van der LInden
1.3 Added support for CLOB types.
22-nov-2010 Paco van der Linden
1.2 Added custom parameter types and Oracle return types
04-oct-2010 Steven Davelaar
1.1 Added caching of procedures created in define method
03-jun-2009 Paco van der Linden
1.0 initial creation
******************************************************************************/
package oracle.jheadstart.util;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Locale;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import static java.util.regex.Pattern.CASE_INSENSITIVE;
import static java.util.regex.Pattern.COMMENTS;
import static java.util.regex.Pattern.DOTALL;
import oracle.jbo.CSMessageBundle;
import oracle.jbo.SQLStmtException;
import oracle.jbo.domain.Array;
import oracle.jbo.domain.ClobDomain;
import oracle.jbo.domain.Date;
import oracle.jbo.domain.Number;
import oracle.jbo.server.DBTransaction;
import static oracle.jbo.server.DBTransaction.DEFAULT;
import oracle.jdbc.OracleCallableStatement;
import oracle.sql.ArrayDescriptor;
import oracle.sql.CLOB;
import oracle.sql.Datum;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;
import oracle.adf.share.logging.ADFLogger;
import weblogic.utils.wrapper.Wrapper;
/**
* DatabaseProcedure
can be used to call (PL/SQL) stored procedures through a
* JDBC connection in a very intuitive way.
*
*
connection
variable always points to a {@link java.sql.Connection}
object.
*
* Instead of a java.sql.Connection
it is also possible to use an
* {@link oracle.jbo.server.DBTransaction}
. This is especially useful when
* this class is used with ADF Business Components.
*
*
* Simple function call example
* To call a database function (hello_world
) and get the result of the call in
* a String variable, one only needs to use the following lines of
* code:
*
*
* String name = ...; * * DatabaseProcedure helloWorldProc = * DatabaseProcedure.define("function hello_world(p_name in varchar2) return varchar2"); * * String result = (String) helloWorldProc.call(connection, name).getReturnValue(); ** * Simple procedure call example
* Integer id = ...; * * DatabaseProcedure getEmpDetailsProc = * DatabaseProcedure.define("procedure get_employee_details * ( p_id in number * , p_name out varchar2 * , p_address out varchar2 * , p_age out number)"); * * DatabaseProcedure.Result result = getEmpDetailsProc.call(connection, id); * * String name = (String) result.getOutputValue("p_name"); * String address = (String) result.getOutputValue("p_address"); * Number age = (Number) result.getOutputValue("p_age"); ** * Calling a function/procedure inside a package
* DatabaseProcedure helloWorldProc = * DatabaseProcedure.define("function my_package.hello_world(p_name in varchar2) return varchar2"); ** *
DatabaseProcedure
object can be reused multiple times, comparable to {@link Pattern}.
* It is therefore best practice to store the DatabaseProcedure
object in a static variable
* (as a constant).
*
*
* Proxies
* A nice pattern is to wrap the call to a database procedure inside a stronly typed Java method. The
* hello world example shown earlier now looks like this:
*
* public class HelloWorldProxy * { * private static final DatabaseProcedure HELLO_WORLD = * DatabaseProcedure.define("function hello_world(p_name in varchar2) return varchar2"); * * public static String helloWorld(DBTransaction transaction, String name) * { * return (String) HELLO_WORLD.call(transaction, name).getReturnValue(); * } * } ** *
varchar2
* number
* date
* integer
* clob
* {@link #registerArrayType}
, {@link #registerCustomParamType}
and
* {@link #deregisterCustomParamType}
,
*
* @author Paco van der Linden
* @version 1.2
*/
public class DatabaseProcedure
{
private static final ADFLogger sLog = ADFLogger.createADFLogger(DatabaseProcedure.class);
private static final Pattern PROCEDURE_DEFINITION =
Pattern.compile("\\s* (FUNCTION|PROCEDURE) \\s+ ([\\w.$]+) \\s* (?:\\((.*?)\\))? \\s* (?:RETURN\\s+(\\w+))? \\s* ;? \\s*",
CASE_INSENSITIVE | COMMENTS | DOTALL);
private static final Pattern PARAM_DEFINITION =
Pattern.compile("\\s* (\\w+) \\s+ (?:(IN)\\s+)? (?:(OUT)\\s+)? (\\w+) \\s* ,? \\s*", CASE_INSENSITIVE | COMMENTS);
private static final Map* Provide a custom parameter type that can subsequently be used * in any DatabaseProcedure definition. * * @param name the name of the parameter as will be used inside the procedure definition * @param sqlTypeCode the {@link Types JDBC SQL Type} * @param dataFactory the ORADataFactory if available (for use with ADF BC Domains) * @param typeName the case-sensitive name with which this type is known in the database. * * @see Types * @see #registerArrayType */ public static void registerCustomParamType(String name, int sqlTypeCode, ORADataFactory dataFactory, String typeName) { String uName = name.toUpperCase(Locale.ENGLISH); PARAM_TYPES.put(uName, new ParamType(uName, sqlTypeCode, dataFactory, typeName)); } /** * Advanced: Not yet fully tested / documented. *
* Register an array type. The type can subsequently be used in any DatabaseProcedure * definition. * * @param name the case-sensitive name of this array type in the database. */ public static void registerArrayType(String name) { registerCustomParamType(name, Types.ARRAY, Array.getORADataFactory(), name); } /** * Advanced: Not yet fully tested / documented. *
* Unregister a custom type.
*
* @param name the name of the type
* @return true
iff a type was unregistered
*/
public static boolean deregisterCustomParamType(String name)
{
return PARAM_TYPES.remove(name.toUpperCase(Locale.ENGLISH)) != null;
}
/**
* Construct a DatabaseProcedure from its (original) PL/SQL declaration.
*
*
* * Function declaration: *
* * Procedure declaration: *function <name> [(<param>[, <param>,...])] return <return-type>
* * With the optionalprocedure <name> [(<param>[, <param>,...])]
<param>
as:
* * *<param-name> [in] [out] <param-type>
*
* Example of a function with one parameter returning a varchar2
:
*
* * Example of a procedure with one input and one output parameter: *function hr.hello_world(p_name in out varchar2) return varchar2
* *procedure hr.hello_world( p_name in varchar2, p_msg out varchar2)
*
* See {@link DatabaseProcedure class documentation} for the possible parametertypes.
*
* @param declaration the procedure declaration in PL/SQL style
* @return a DatabaseProcedure object
*/
public static DatabaseProcedure define(String declaration)
{
if (DEFINITION_CACHE.containsKey(declaration))
{
return DEFINITION_CACHE.get(declaration);
}
DatabaseProcedure proc;
Matcher m = PROCEDURE_DEFINITION.matcher(declaration);
if (!m.matches())
{
compileError("Declaration is not valid", declaration);
}
boolean function = m.group(1).equalsIgnoreCase("FUNCTION");
String name = m.group(2);
String params = m.group(3);
String returns = m.group(4);
ParamType returnType = null;
if (function)
{
if (returns == null)
{
compileError("A function needs a return type", declaration);
}
returnType = PARAM_TYPES.get(returns.toUpperCase(Locale.ENGLISH));
if (returnType == null)
{
compileError("Unsupported type (" + returns + ")", declaration);
}
}
else if (returns != null)
{
compileError("A procedure has no return type", declaration);
}
if (params != null)
{
ArrayListnull
.
*
* @param paramIndex a valid parameter index (zero-based)
* @return outputvalue of the parameter
*/
public Object getOutputValue(int paramIndex)
{
return paramValues[paramIndex];
}
/**
* The returned value of an out parameter as an Oracle object. Parameters that have
* not been marked as out parameters always return null
.
*
* @param paramIndex a valid parameter index (zero-based)
* @return outputvalue of the parameter as an Oracle object.
*/
public Object getOracleOutputValue(int paramIndex)
{
Object value = paramOracleValues[paramIndex];
if (value == null)
{
value = paramValues[paramIndex];
}
return value;
}
protected int getParamIndex(String paramName)
{
for (int i = 0; i < paramDefs.length; i++)
{
if (paramDefs[i].name.equalsIgnoreCase(paramName))
{
return i;
}
}
throw new IllegalArgumentException("Unknown parameter name");
}
/**
* The returned value of an out parameter. Parameters that have not been marked as out
* parameters always return null
.
*
* @param paramName a valid parameter name
* @return outputvalue of the parameter
* @throws IllegalArgumentException if paramName is unknown
*/
public Object getOutputValue(String paramName)
throws IllegalArgumentException
{
return getOutputValue(getParamIndex(paramName));
}
/**
* The returned value of an out parameter as an Oracle object. Parameters that have
* not been marked as out parameters always return null
.
*
* @param paramName a valid parameter name
* @return outputvalue of the parameter as an Oracle object.
* @throws IllegalArgumentException if paramName is unknown
*/
public Object getOracleOutputValue(String paramName)
{
return getOracleOutputValue(getParamIndex(paramName));
}
/**
* The returned value of a function. Returns null in case of procedures.
*
* @return returnvalue of a function or null in case of a procedure
*/
public Object getReturnValue()
{
return returnValue;
}
/**
* The returned value of a function as an Oracle type. Returns null in case of procedures.
*
* @return returnvalue of a function or null in case of a procedure
*/
public Object getOracleReturnValue()
{
if (oracleReturnValue != null)
{
return oracleReturnValue;
}
else
{
return returnValue;
}
}
}
public static class ParamType
{
private final String name;
private final int sqlTypeCode;
private final ORADataFactory dataFactory;
private final String typeName;
private ParamType(String name, int sqlTypeCode, ORADataFactory dataFactory, String typeName)
{
this.name = name;
this.sqlTypeCode = sqlTypeCode;
this.dataFactory = dataFactory;
this.typeName = typeName;
}
public int getSqlTypeCode()
{
return sqlTypeCode;
}
public ORADataFactory getORADataFactory()
{
return dataFactory;
}
public String getName()
{
return name;
}
public String getTypeName()
{
return typeName;
}
@Override
public String toString()
{
return getName();
}
}
private static final class ClobDomainFactory
implements ORADataFactory
{
public ORAData create(Datum datum, int i)
{
return datum != null? new ClobDomain((Clob) datum): null;
}
}
public static void main(String... args)
{
DatabaseProcedure.define("function hr.hello_world(p_name in out varchar2) return varchar2").dumpInfo();
DatabaseProcedure.define("procedure hr.hello_world( p_name varchar2" +
" , p_msg out varchar2)").dumpInfo();
DatabaseProcedure.registerCustomParamType("custom_type", Types.ARRAY, Array.getORADataFactory(), "custom_type_tab");
DatabaseProcedure.define("function test(p_in in varchar2) return custom_type").dumpInfo();
}
private void dumpInfo()
{
System.out.println("JDBC escape sequence: " + getEscapeSequence());
System.out.println("Type: " + (returnType == null? "PROCEDURE": "FUNCTION"));
if (returnType != null)
{
System.out.println("Returntype: " + returnType);
}
System.out.println();
for (ParamDef pd: paramDefs)
{
System.out.println("Param: " + pd.name.toUpperCase());
System.out.println("Type: " + pd.type);
System.out.println("In/out: " + (pd.in? "IN ": "") + (pd.out? "OUT": ""));
System.out.println();
}
}
}