PlsqlOpen (ODynaset) Method

Applies To

Description

This method sets up a dynaset to access PLSQL cursor. The SQL statement should be a stored procedure or anonymous block. The Resulting dynaset is READONLY. Attempting to use SetSQL method results in error. The dynaset can be refreshed with new parameters as normal.

Usage

oresult PlsqlOpen(const ODatabase &odb, const char * sqlstmt, const char *CursorName,long options = ODYNASET_DEFAULT)

oresult PlsqlOpen(const ODatabase &odb, const char *sqlstmt, const char *CursorName, unsigned int slicesize, unsigned int perblock, unsigned int blocks, unsigned int fetchlimit, unsigned int fetchsize, long options = ODYNASET_DEFAULT)

Arguments	Description
odb	The database with which to open this dynaset.
sqlstmt	A valid PLSQL procedure.
CursorName	Name of the cursor created in the PLSQL stored procedure.
options	Options to be used to create the dynaset.
sqlstmt	A valid select SQL statement.
slicesize	Cache slice size.
perblock	Cache slices for each block.
blocks	Cache maximum number of blocks.
fetchlimit	Fetch array size.
fetchsize	Fetch array buffer size.

Remarks

The first method opens the ODynaset object from the PL/SQL cursor, creating an underlying dynaset object. The dynaset is formed on records retrieved from the database represented by odb and PLSQL cursor describing the SQL select statement. The ODynaset is automatically positioned at the first record after opening.

The SQL statement must be a PLSQL stored procedure with BEGIN and END around the call as if it were executed as an anonymous PL/SQL block .Otherwise an error is returned. CursorName should exactly matches the cursor created inside the stored procedure or anonymous PL/SQL block . Otherwise an error is returned. Cursor created inside the stored procedure should represents the valid SELECT sql statement or an error is returned.

You do not need to bind the PL/SQL cursor variable using OParameterCollection's Add Method, if stored procedure is having cursor as a output parameter. You can use PL/SQL bind variables in conjunction with the OParameterCollection.

The second Open method opens the ODynaset object from the PLSQL cursor using custom cache and fetch parameters.

The options argument can have the following values:

Open Method Options

Constant	Value	Description
ODYNASET_DEFAULT	0	Accept the following default behavior: Behave like the default mode for an ODatabase object: Field values not explicitly set are set to NULL, overriding server column defaults. Perform automatic binding of database parameters. Strip trailing blanks from character string data retrieved from the database. Create an updatable dynaset. Cache data on client. Force a MoveFirst on dynaset creation. Maintain read-consistency.
ODYNASET_NOBIND	1	Do not perform automatic binding of database parameters.
ODYNASET_KEEP_BLANKS	2	Do not strip trailing blanks from character string data retrieved from the database.
ODYNASET_NOCACHE	8	Do not create a local dynaset data cache. Without the local cache, previous rows within a dynaset are unavailable; however, increased performance results during retrieval of data from the database (move operations) and from the rows (field operations). Use this option in applications that make single passes through the rows of a dynaset for increased performance and decreased resource usage.
ODYNASET_N_MOVEFIRST	64	Does not force a MoveFirst on dynaset creation. BOF and EOF are both TRUE.

Options may be combined by adding their respective values. These values can be found in the file ORCL.H.

Example

This example demonstrates the use of PL/SQL cursor in the PlsqlOpen method. It creates a PLSQL cursor as a dynaset object for the different values of DEPTNO parameter. Make sure that corresponding stored procedure (found in EMPCUR.SQL) is available in the Oracle Server.

// first, open an Odatabase

ODatabase odb("ExampleDB", "scott", "tiger");

OParameterCollection params = odb.GetParameters();

params.Add("DEPTNO", 20, OPARAMETER_INVAR, OTYPE_NUMBER);

// create and open a dynaset

ODynaset odyn;

odyn.PlsqlOpen(odb, "Begin Employee.GetEmpData (:DEPTNO,:EmpCursor);

end;", "EmpCursor", 0L);