Code Generation Facility

Go Up to Tools

The Code Generation Facility offers a quick way to generate DML statements for tables and views:

• Packages and procedures for Oracle.
• Procedures for IBM DB2 LUW, Microsoft SQL Server, and Sybase ASE.

Note: The Code Generation Facility can generate procedures for IBM DB2 LUW that are based on tables but not views.

The user interface lets you select the items you need to generate a generic block of code such as the following:

CREATE OR REPLACE PROCEDURE ADDRESS_SEL
(
    city_in IN SYSTEM.ADDRESS.CITY%TYPE,
    addressline1_out OUT SYSTEM.ADDRESS.ADDRESSLINE1%TYPE,
    addressline2_out OUT SYSTEM.ADDRESS.ADDRESSLINE2%TYPE,
    city_out OUT SYSTEM.ADDRESS.CITY%TYPE,
    stateprovinceid_out OUT SYSTEM.ADDRESS.STATEPROVINCEID%TYPE,
    postalcode_out OUT SYSTEM.ADDRESS.POSTALCODE%TYPE
)
IS
BEGIN
    SELECT
    t.ADDRESSLINE1,
    t.ADDRESSLINE2,
    t.CITY,
    t.STATEPROVINCEID,
    t.POSTALCODE
    INTO
        addressline1_out,
        addressline2_out,
        city_out,
        stateprovinceid_out,
        postalcode_out
    FROM SYSTEM.ADDRESS t
    WHERE
     t.CITY = city_in;

END;

The procedure (or package) can be executed directly from the choices on the Code Generation Facility window or opened in the SQL Editor after generation. For more information, see Using the SQL Editor.

To generate a procedure or package using the Code Generation Facility

1. Select Tools > Code Generation Facility to open the IDERA Code Generator window.
2. Use the following table as a guide to understanding the user interface elements as you make the choices in generating a procedure or package.

Option	Description
Datasource/database/Owner lists	Let you qualify the table or views that you want to generate a procude ot package against. NOTE: If you select a currently unconnected datasource, a Connect button becomes available, letting you connect to the target datasource. The current datasource, and database and datasource user name where applicable, are preselected when you open Code Generation Facility.
Tables/Views buttons and list	Let you select the table or view that the SELECT/INSERT/UPDATE/DELETE statement is to be generated against.
Select 1 or More Where Clause Columns	Select the check boxes that correspond to the target where clauses. NOTE: Columns of primary keys are preselected.
Select 1 or More Output Columns	Select the check boxes that correspond to the target output columns.
Provide an Output File Name and Open	Lets you specify that the procedure or package is to be written to a file. Optionally, you can have the file opened after writing the file.
Execute Immediately	Select to execute the file immediately.
Generate	Specify whether you want to generate a procedure or package, and select one or more DML statement types.
Grant Execute to	Use these controls to grant execute privileges.

3. Click OK when ready to generate the procedure or package.