Creating a Data Block from a Procedure that Uses a Ref Cursor
To create a data block from a procedure that uses a ref cursor:
- Choose Tools | Data Block Wizard to invoke a
wizard that guides you through the process of building a data block.
Or,
- In the Object Navigator, click the Data Blocks
node.
- Click Create in the toolbar.
- In the New Data Block dialog box, choose Build
a New Data Block Manually and click OK.
- Double-click the Block object icon to display the Property Palette.
- Under the Database node:
- Set the Query Data Source Type property
to Procedure.
- Type the name of a procedure in the Query Data
Source Name text box.
- Type the name of a procedure in the Query Data
Source Name text box.
- Double-click the Query Data Source Columns
property and in the Query Data Source Columns
dialog, type in the column names, type, length, precision, and scale for
the result set returned from the ref cursor.
- Double-click the Query Data Source Arguments
property and in the Query Data Source Arguments
dialog, enter the names, data types, mode, and value for the arguments
passed to and from the procedure.
- Under the General node, click the Name property,
then type a name or accept the default name. You can use any name that
follows ORACLE naming conventions and is unique among blocks in the form.
The block name is an internal descriptor that is not displayed at runtime.
Creating a data block from a procedure that uses a ref cursor Restrictions
- If the query datasource of a block is a stored procedure returning a cursor
variable, and the block is a detail block in a relation, only the isolated
deletes option is supported. Non-isolated deletes and cascading deletes cannot
be supported, because the appropriate SQL statements cannot be constructed
(cursors are a single active set of records resulting from a single select
statement that is issued). To enforce non-isolated deletes and cascading deletes,
provide checks in the ON-DELETE
or PRE-DELETE triggers.
- Count Query Hits is disabled when using stored procedures as a datasource.
An error message occurs if you attempt to count query hits at runtime. Instead,
you can use the ON-COUNT
trigger to count the query hits.
- You cannot pass a WHERE or ORDER BY clause to a stored procedure.
Creating a data block from a procedure that uses a ref cursor Examples
This example is a stored procedure that returns a ref cursor as a block datasource.
A package is used to logically group the related data types and procedures.
PACKAGE cv_datasource IS
TYPE emprec is RECORD (empno emp.empno%TYPE,
ename emp.ename%TYPE,
job emp.job%TYPE,
mgr emp.mgr%TYPE,
sal emp.sal%TYPE,
comm emp.comm%TYPE,
deptno emp.deptno%TYPE);
TYPE empcur is REF CURSOR RETURN emprec;
PROCEDURE empquery(resultset IN OUT empcur,
p_deptno IN NUMBER);
END;
PACKAGE BODY cv_datasource IS
PROCEDURE empquery(resultset IN OUT empcur,
p_deptno IN NUMBER) IS;
BEGIN
OPEN resultset FOR
SELECT empno, ename, job, mgr, sal, comm, deptno
FROM emp
WHERE deptno = p_deptno
ORDER BY empno;
END;
END;