Code Generation Facility

From DBArtisan
Jump to: navigation, search

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.