Creating a Data Block from a Procedure that Uses a Table of Records
To create a data block from a procedure that uses a table of records:
- 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 database procedure in the Query Procedure Name text
box.
- Double-click the Query Data Source Columns property. Then in the Query
Data Source Columns dialog box, type in the column names, type, length,
precision, and scale for the result set returned from the table of records.
- Double-click the Query Data Source Arguments property. Then in the Query
Data Source Arguments dialog box, type in the name, data types, mode,
and value for the arguments passed to and from the procedure.
- Under the Advanced Database node:
- Set the DML Data Target Type property to Procedure.
If you are performing an INSERT from your procedure, type the name of
the procedure in the Insert Procedure Name text box, double-click the
Insert Procedure Result Set Columns property to provide the result set
information (name, type, length, precision, and scale), and double-click
the Insert Procedure Arguments property to provide the argument information
(name, data type, mode, value).
If you are performing an UPDATE from your procedure, type the name of
the procedure in the Update Procedure Name text box, double-click the
Update Procedure Result Set Columns property to provide the result set
information (name, type, length, precision, and scale), and double-click
the Update Procedure Arguments property to provide the argument information
(name, data type, mode, value).
If you are performing a DELETE from your procedure, type the name of the
procedure in the Delete Procedure Name text box, double-click the Delete
Procedure Result Set Columns property to provide the result set information
(name, type, length, precision, and scale), and double-click the Delete
Procedure Arguments property to provide the argument information (name,
data type, mode, value).
If you are performing a LOCK from your procedure, type the name of the
procedure in the Lock Procedure Name text box, double-click on the Lock
Procedure Result Set Columns property to provide the result set information
(name, type, length, precision, and scale), and double-click the Lock
Procedure Arguments property to provide the argument information (name,
data type, mode, value)
- 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 table of records Restrictions
In a block based on a stored procedure performing an UPDATE, always reference
the same columns in both the procedure and the block. Do not pass back values
from the procedure or function that you do not use as database items in your
form.
If the procedure or function returns more columns than those used in the block,
the procedure or function returns null values for the columns that are not used.
Because Update Changed Columns is not supported with procedures or functions
as block datasources, the procedure or function should always pass back all
the column values (Updated Changed Columns specifies that only columns whose
values have changed are included in the UPDATE statement).
It is impossible to determine whether a null value passed to procedure or
function is an unused column in the block or an intentional null value for a
column that is used. If you do not have a procedure or function that requires
you to pass back all the column values, you will not be able to set a column
to a null value in an UPDATE statement. 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.
Related topics
Delete Procedure Name property
DML Data Target Type property
Query Data Source Type property
Query Data Source Name property
Connecting to a database