com.microdeveloper.data
Class OracleStoredProcedure

java.lang.Object
  com.microdeveloper.data.OracleStoredProcedure

public class OracleStoredProcedure
extends Object

A generic stored procedure execution class which encapsulates the procedure with the connection, variables, and results.

Variables are loaded to an argument stack then read like a resultSet. This is very similar to the JDBC call and only differs in the encapsulation.

Usage Instructions

Stored Procedures
Assume you want to execute a stored procedure that adds two varchar2 variables together like this:
addtxt(a IN VARCHAR2, b IN VARCHAR2, c OUT VARCHAR2)

In SQL*Plus this could be executed like this:
VARIABLE c VARCHAR2(255);
EXEC addtxt('Smith','Scott',:c);
PRINT c

To do the same with this class the actions would be:
// assign the arguments
addArg(1,Types.VARCHAR,IN,"Smith");
addArg(2,Types.VARCHAR,IN,"Scott");
addArg(3,Types.VARCHAR,OUT,null);

// execute the procedure
boolean result = doProcedure(Connection, 'addtxt');

//read the results
System.out.println((String) getArg(3));

Stored Functions

Assume you want to execute a stored function that adds two varchar2 variables together like this:
addtxt(a IN VARCHAR2, b IN VARCHAR2) RETURN VARCHAR2

In SQL*Plus this could be executed like this:
VARIABLE c VARCHAR2(255);
EXEC c:= addtxt('Smith','Scott');
PRINT c

To do the same with this class the actions would be:
// assign the arguments
addArg(1,Types.VARCHAR,IN,"Smith");
addArg(2,Types.VARCHAR,IN,"Scott");

// execute the function
object result = doFunction(Connection, 'addtxt');

// read the results
System.out.println((String) result);

Changing Argument Values

To change argument values call setArgVal([argument position], [new argument value])
There is no need to remove and re-add the argument.
Example: setArgVal(1, "Jones");
To see the current value assigned to an argument use getArgVal([argument position]);

Limitations

Overloaded procedures and functions are not supported in this version.

Since:

1.5

Version:

1.0

Author:

MicroDeveloper, Inc.

Field Summary

static int	DIR Internal DIR value
static int	FUNCTION Function index value
static int	IN IN argument type
static int	INOUT INOUT argument type
static int	OUT OUT argument type
static int	POS Internal POS value
static int	PROCEDURE Procedure index value
static int	TYPE Internal TYPE value
static int	VAL Internal VAL value

Constructor Summary

OracleStoredProcedure()
Create a new OracleStoredProcedure object

Method Summary

boolean	addArg(int pos, int type, int dir, Object val) Add an argument to the this object.
String	argsToString(int callType) Convert the arguments to a String.
boolean	clearArg(int pos) Remove a single argument from the this object.
boolean	clearArgs() Remove all arguments from the this object.
Object	doFunction(Connection conn, String procedure) Execute the given function.
boolean	doProcedure(Connection conn, String procedure) Executes the given stored procedure.
List	getArg(int pos) Get a single argument from this object.
Object	getArgVal(int pos) Get a single arguments value.
OracleStoredProcedureInterface	getOracleStoredProcedureInterface() Obtain a new OracleStoredProcedure interface
boolean	setArgVal(int pos, Object val) Set an arguments value
boolean	setOutValues(CallableStatement s) Establish the outbound parameters
boolean	setParameter(CallableStatement s, int pos) Establish the argument as a parameter.

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

IN

public static final int IN

IN argument type

See Also:

Constant Field Values

OUT

public static final int OUT

OUT argument type

See Also:

Constant Field Values

INOUT

public static final int INOUT

INOUT argument type

See Also:

Constant Field Values

POS

public static final int POS

Internal POS value

See Also:

Constant Field Values

TYPE

public static final int TYPE

Internal TYPE value

See Also:

Constant Field Values

DIR

public static final int DIR

Internal DIR value

See Also:

Constant Field Values

VAL

public static final int VAL

Internal VAL value

See Also:

Constant Field Values

FUNCTION

public static final int FUNCTION

Function index value

See Also:

Constant Field Values

PROCEDURE

public static final int PROCEDURE

Procedure index value

See Also:

Constant Field Values

Constructor Detail

OracleStoredProcedure

public OracleStoredProcedure()

Create a new OracleStoredProcedure object

Method Detail

getOracleStoredProcedureInterface

public OracleStoredProcedureInterface getOracleStoredProcedureInterface()

Obtain a new OracleStoredProcedure interface

Returns:

interface to the class

doFunction

public Object doFunction(Connection conn,
                         String procedure)
                  throws SQLException

Execute the given function.

Parameters:

conn - established connection object from a JDBC connection

procedure - name of the function to execute

Returns:

Object representing the result of the function

Throws:

SQLException - thrown if the function cannot be executed

See Also:

doProcedure(Connection,String), Connector, ConnectionPool

doProcedure

public boolean doProcedure(Connection conn,
                           String procedure)
                    throws SQLException

Executes the given stored procedure.

Parameters:

conn - established connection object from a JDBC connection

procedure - name of the procedure to execute

Returns:

result of the execution, true if successful, false otherwise

Throws:

SQLException - thrown if the procedure cannot be executed

See Also:

doFunction(Connection,String), Connector, ConnectionPool

addArg

public boolean addArg(int pos,
                      int type,
                      int dir,
                      Object val)

Add an argument to the this object. addArg accepts a position to identify the parameter, a direction of IN, OUT, or INOUT and, in the case of an IN or INOUT, an value.

Parameters:

pos - position in the procedures argument list

dir - argument direction in IN, OUT, or INOUT

val - the value of an IN or INOUT parameter

clearArgs

public boolean clearArgs()

Remove all arguments from the this object.

Returns:

true if operation was successful, otherwise false

clearArg

public boolean clearArg(int pos)

Remove a single argument from the this object.

Parameters:

pos - index of the element to remove

Returns:

true if operation was successful, otherwise false

getArg

public List getArg(int pos)

Get a single argument from this object. If the argument does not exist no error is thrown.

Parameters:

pos - the index of the argument to return

Returns:

returns a list item representing the argument

getArgVal

public Object getArgVal(int pos)

Get a single arguments value. If the argument does not exist null is returned.

Parameters:

pos - the index of the argument to return

Returns:

the value of the argument

setParameter

public boolean setParameter(CallableStatement s,
                            int pos)

Establish the argument as a parameter.

Parameters:

s - CallableStatement to set

pos - argument index to set

Returns:

true of the operation succeeds, otherwise false

argsToString

public String argsToString(int callType)

Convert the arguments to a String.

Parameters:

callType - a constant call type

Returns:

formatted argument string

setOutValues

public boolean setOutValues(CallableStatement s)

Establish the outbound parameters

Parameters:

s - CallableStatement to set

Returns:

true if the operation succeeded, otherwise false

setArgVal

public boolean setArgVal(int pos,
                         Object val)

Set an arguments value

Parameters:

pos - index of the argument to set

val - value of the argument

Returns:

true if the operation succeeded, otherwise false