CreatePLSQLCustomDynaset Method

Applies To

OraDatabase Object

Description

Create a Dynaset from a PLSQL cursor using custom cache and fetch parameters. SQL statement should be a stored procedure or anonymous block. Resulting Dynaset is read-only. Attempting to set SQL property results in error. Dynaset can be refreshed with new parameters as normal.

Usage

set OraDynaset = CreatePlsqlCustomDynaset(SQLStatement, CursorName , options slicesize, perblock, blocks, FetchLimit, Fetchsize)

Arguments	Description
SQLStatement	Any valid Oracle PLSQL stored procedure or anonymous block.
CursorName	Name of the cursor created in the PLSQL stored procedure.
Options	A bit flag indicating the status of any optional states of the dynaset. You can combine one or more options by adding their respective values.
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

The options flag values are:

Options Flag Values

Constant	Value	Description
ORADYN_DEFAULT	&H0&	Accept the default behavior.
ORADYN_NO_AUTOBIND	&H1&	Do not perform automatic binding of database parameters.
ORADYN_NO_BLANKSTRIP	&H2&	Do not strip trailing blanks from character string data retrieved from the database.
ORADYN_NOCACHE	&H8&	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.
ORADYN_NO_MOVEFIRST	&H40&	Does not force a MoveFirst on dynaset creation. BOF and EOF are both TRUE.

These values can be found in the file oraconst.txt.

Remarks

The SQL statement must be a PL/SQL 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 match the cursor created inside the stored procedure or anonymous PL/SQL block; otherwise error is returned. Cursor created inside the stored procedure should represent a valid SELECT SQL.

You do not need to bind the PL/SQL cursor variable using OraParameters Addif the stored procedure returns cursor as a output parameter. You can still use PL/SQL bind variables in conjunction with the OraParameters collection.

This method automatically moves to the first row of the created dynaset.

Specifying ORADYN_READONLY, ORADYN_ORAMODE, ORADYN_NO_REFETCH, ORADYN_DIRTY_WRITE options have no effect on the Dynaset creation.