Developing with PL/SQL in IBM Data Studio 2.2 and Optim Development Studio 2.2

Create, debug, and deploy routines for DB2 and Oracle

This article describes how you can use IBM® Data Studio 2.2 and Optim™ Development Studio 2.2 to develop routines that use Oracle PL/SQL. It shows you how to create, edit, deploy, and debug PL/SQL packages, procedures, and functions for the following databases: DB2® for Linux®, UNIX®, and Windows® Version 9.7, Oracle 10g, and Oracle 11g.

Thomas Sharp (sharpt@us.ibm.com), Routine Tooling, Architect, IBM

Thomas Sharp photoThomas Sharp is a Native American with a Ph.D. in English from Stanford University. He has worked for IBM as a technical writer and software engineer for 25 years. He holds 18 patents in routine tooling and user-interface technologies.



20 August 2009

Also available in Vietnamese

Overview

This article provides information about using Optim Development Studio (ODS) to create, edit, deploy, and debug PL/SQL routines for DB2 Linux, UNIX, and Windows (LUW) Version 9.7, Oracle 10g, and Oracle 11g databases. It assumes you have basic knowledge of DB2 and Oracle, and are familiar with working in an Eclipse-based software development environment.

The ability to develop, debug, and deploy using PL/SQL is also available in IBM's no-charge offering, Data Studio 2.2. However, Data Studio only supports development and deployment against DB2 9.7 databases in compatibility mode. To develop and deploy against Oracle databases, you must use Optim Development Studio 2.2 or a later release. To try out the capabilities described in this article, see the Resources section to download a trial copy of Optim Development Studio or a full-use copy of Data Studio.

What is Optim Development Studio?

Optim Development Studio (IBM's new name for Data Studio Developer) provides an Eclipse-based integrated development environment to speed data-centric development targeting DB2, Informix, and Oracle databases.

In addition to basic database management capabilities and the ability to develop database routines, Optim Development Studio takes database development to the next level with its enhanced capabilities for developing and optimizing Java database applications for heterogeneous databases. For more information about Java development support, see the Optim Development Studio product page and the Integrated Data Management Information Center, both of which are linked to from the Resources section of this article.

In previous releases of Optim Development Studio, you could develop both client-side applications in Java and server-side business logic in Java or SQL using advanced wizards, editors, deployment tools, and debuggers. Version 2.2 also supports server-side business logic in PL/SQL. It lets you manage PL/SQL objects in the Data Source Explorer and develop PL/SQL packages, procedures, and functions in the Data Project Explorer.

Because Optim Development Studio 2.2 is based on Eclipse 3.4.2, you may install it in the same Eclipse "shell" with other products that are based on Eclipse 3.4.2.

What is PL/SQL?

PL/SQL is Oracle's procedural-language extension to SQL. Like native SQL for DB2, PL/SQL is a dialect of SQL that provides 3GL constructs for implementing logic that runs on the database server.

You can use PL/SQL for creating routines (procedures and functions) and for creating PL/SQL packages that contain data type definitions, variable declarations, procedures, and functions.

A PL/SQL routine may overload another routine of the same type (procedure or function) only if they are both in the same PL/SQL package. The contents of a PL/SQL package are edited together, deployed together, and debugged together. They are managed as a unit on the database server and are qualified by the name of the PL/SQL package and its schema.

A PL/SQL package has two parts:

  • A specification that specifies routines and declares exceptions
  • A body that includes variable and cursor declarations, the bodies of its routines, and an optional initialization section

The specification is a public declaration of interfaces; the body is its private implementation.

Disambiguating the term package

Do not confuse a PL/SQL package with a DB2 (SQL) package. A DB2 package is a database object. It is the result of binding an SQL application or routine to encapsulate static SQL statements and DB2's data access plan. A PL/SQL package is a programming construct. It is a container of PL/SQL statements.

When you deploy a PL/SQL package on DB2 LUW 9.7, DB2 compiles it into a DB2 module, which is a native SQL object similar to a PL/SQL package. Like a PL/SQL package, a DB2 module contains conditions, functions, procedures, user-defined types, and variables. Unlike a PL/SQL package, a DB2 module only logically contains its elements. It has no source file that specifies its contents. Instead, after you create a DB2 module, you write a sequence of ALTER statements to add elements to it.

To create a DB2 module, you can use the Data Object editor from the Data Source Explorer, or you can type the CREATE and ALTER statements in a SQL script in your project and then run the script.

Summary of PL/SQL functionality

Optim Development Studios (ODS) 2.2 supports DB2 modules and PL/SQL for DB2 LUW 9.7. It also supports PL/SQL for Oracle 10g and 11g. To work with PL/SQL, use the ODS Data perspective.

Table 1. New PL/SQL functionality
View—Connection TypeNew Functionality
Data Source Explorer—DB2 LUW 9.7
  • Show a PL/SQL package in the PL/SQL Packages folder of a schema. These do not expand to show their contents.
  • Show a PL/SQL procedure or function in its folder of a schema.
  • View a PL/SQL package, procedure, or function in the Properties view.
  • Compare two PL/SQL objects of the same type.
  • Run a PL/SQL procedure or function, whether it is stand-alone or in a PL/SQL package, and view its results in the SQL Results view.
Data Source Explorer—Oracle 10g or 11g
  • Show a PL/SQL package in the PL/SQL Packages folder of a schema. These do not expand to show their contents.
  • Show a PL/SQL procedure or function in its folder of a schema.
  • View a PL/SQL package, procedure, or function in the Properties view.
  • Compare two PL/SQL objects of the same type.
  • Run a PL/SQL procedure or function, whether it is stand-alone or in a PL/SQL package, and view its results in the SQL Results view.
Data Project Explorer—DB2 LUW 9.7
  • Create a PL/SQL package.
  • Create a stand-alone PL/SQL procedure.
  • Create a stand-alone PL/SQL function.
  • Deploy the PL/SQL objects in a folder.
  • Test a PL/SQL procedure or function by running it and viewing its results in the SQL Results view.
  • Debug a procedure or function in a PL/SQL package.
  • Debug a stand-alone PL/SQL procedure.
  • Drag or copy a PL/SQL package or routine from an Oracle or DB2 connection in the Data Source Explorer into your project.
  • Drag or copy a PL/SQL package or routine from one project into another.
Data Project Explorer—Oracle 10g or 11g
  • Create a PL/SQL package.
  • Create a stand-alone PL/SQL procedure.
  • Create a stand-alone PL/SQL function.
  • Deploy the PL/SQL objects in a folder.
  • Test a PL/SQL procedure or function by running it and viewing its results in the SQL Results view.
  • Debug a procedure or function in a PL/SQL package.
  • Debug a stand-alone PL/SQL procedure.
  • Drag or copy a PL/SQL package or routine from an Oracle connection in the Data Source Explorer into your project.
  • Drag or copy a PL/SQL package or routine from one project into another.
SQL Results view
  • View parameter inputs, parameter outputs, and return result sets after successfully running or debugging a PL/SQL routine.
  • View error messages after unsuccessfully deploying, running, debugging, or dropping a object from the database.
PL/SQL package editor
  • Edit the specification in the Specification tab.
  • Qualify or rename the PL/SQL package to update it in the Data Project Explorer.
  • Edit the body in the Body tab.
PL/SQL procedure editor
  • Edit the source in the Source tab.
  • Qualify or rename the procedure to update it in the Data Project Explorer.
  • Edit the model attributes in the Configuration tab.
PL/SQL function editor
  • Edit the source in the Source tab.
  • Qualify or rename the function to update it in the Data Project Explorer.
  • Edit the model attributes in the Configuration tab.

Connecting to a PL/SQL-compatible database in DB2 LUW 9.7

To create a database named cdbname with PL/SQL compatibility, enter the following statements in a DB2 command window:

DB2SET DB2_COMPATIBILITY_VECTOR=FFF
DB2STOP && DB2START
DB2 CREATE DATABASE cdbname

For the cdbname database, DB2 supports PL/SQL-specific data types. If you connect to the cdbname database in a DB2 command window and get the database configuration, you can confirm that the following compatibility settings are on by using the following commands (on Linux, use grep instead of findstr):

DB2 CONNECT TO cdbname
DB2 GET DB CFG | findstr compatibility
 Number compatibility                                    = ON
 Varchar2 compatibility                                  = ON
 Date compatibility                                      = ON
DB2 DISCONNECT cdbname

Pop-up menu keyboard shortcut

In most cases, you can use the Shift+F10 keyboard shortcut to see the pop-up menu for the current view or selection. This can be used as an alternative to a right-click.

To create a database connection profile for the GSDB sample database that is included with DB2 LUW 9.7, do the following:

  1. From the Data Source Explorer, click the New Connection Profile toolbar button:
    New Connection Profile toolbar button from the Data Source Explorer view. Its icon is two hands holding each other and a plus sign.
    Or, right-click on the Database Connections folder and select New... from the pop-up menu. Either of these actions causes the New Connection wizard to appear, as shown in Figure 1.
    Figure 1. Create a connection to GSDB on DB2 9.7
    The New Connection wizard is shown. The driver properties are also shown.
  2. Select DB2 for Linux, UNIX, and Windows for your database manager.

  3. Select the IBM Data Server Driver for JDBC and SQLJ (JDBC 4.0) Default driver and specify your database name, host server name, connection port number, user name, and user password. The driver definition for the IBM JDBC driver is predefined and its JAR files are bundled with ODS 2.2.

  4. Click Finish. The connection profile appears in the Data Source Explorer, as shown in Figure 2.

    Figure 2. Browse the new DB2-GSDB connection profile in the Data Source Explorer
    The new connection profile named DB2-GSDB appears in the Data Source Explorer tree under the Database Connections folder.

Notice that the DB2-GSDB connection profile shown in Figure 2 is followed by a connection with the name GSDB [DB2 Alias]. If you have installed a DB2 client and added a DB2 database alias by creating the database locally or using the Configuration Assistant, then the database alias appears in the Data Source Explorer automatically when you create or open a workspace.

Because the database version is hidden to users of DB2 aliases, an alias profile is assumed to be a connection to DB2 LUW 9.1. As such, ODS 2.2 does not show you PL/SQL folders or actions when you create a data development project associated with it. However, when you specify an authorized user ID and password for this profile and connect to the database, ODS 2.2 discovers the actual DB2 platform and version. If the actual platform and version are DB2 LUW and 9.7, then ODS 2.2 shows you PL/SQL folders and actions.

When you attempt to connect to a DB2 alias the first time, ODS 2.2 prompts you for a user name and password, as shown in Figure 3.

Figure 3. Enter a user name and password to connect
The Properties for GSDB dialog appears as a prompt for user name and password. The error message 'Enter a user name' is shown.

Connecting to Oracle 10g or 11g

ODS does not bundle a JDBC driver for connecting to an Oracle database server. Therefore, you must first obtain a JDBC driver that supports Oracle from Oracle or DataDirect. These JDBC drivers are licensed program products that you must pay for or use with permission.

As an Oracle developer, you can register on the Oracle Technology Network Web site, accept the OTN Development and Distribution License Agreement, and download an Oracle JDBC driver. See Resources for a link to the Oracle JDBC driver download page.

Oracle provides thin JDBC drivers and Oracle call interface (OCI) JDBC drivers. Thin drivers do not require an Oracle Database Client. OCI drivers do require an Oracle Database Client (or you can download the equivalent required libraries).

Connecting to Oracle using an Oracle thin JDBC driver

Oracle has delivered a new thin JDBC driver for various JRE levels since 1.1.1. You should always use the driver for the latest JRE that is available. Currently, the latest JRE is for 1.6 and its JAR is ojdbc6.jar. The version of Eclipse that ODS 2.2 extends is built on JRE 1.6.

Follow these steps to connect to Oracle using an Oracle thin JDBC driver:

  1. Obtain the ojdbc6.jar file and copy it to your hard drive in a directory such as C:\jdbcDrivers\Oracle.
  2. In the Data Source Explorer, right-click on the Database Connections folder and select New... from the pop-up menu.
  3. The New Connection wizard appears, as shown in Figure 4.
    Figure 4. Choose an Oracle thin JDBC driver
    The Connection Properties page of the New Connection wizard for a new Oracle connection.
    Here you must select Oracle for the database manager, and you may select one of the thin drivers, such as Oracle 11 - Oracle Thin Driver Default.

    Fill in the general properties to identify your Oracle database server.

  4. Click the Edit Jar List... icon to the right of the JDBC driver:

    The Edit Jar List dialog appears, as shown in Figure 5.

    Figure 5. Specify the ojdbc6.jar location
    The Edit Jar List dialog asks you to specify the jar/zip files required by the JDBC driver.

    The ojdbc6.jar file listed by default is a place-holder; you need to specify its full path (for example, C:\jdbcDrivers\Oracle\ojdbc6.jar).

  5. Select the place-holder and click Edit JAR/Zip.... A file section dialog appears.
  6. Navigate to your copy of ojdbc6.jar, select it, and click Open. The path appears in the Edit Jar List dialog.
  7. Click OK.
  8. Back in the New Connection wizard, click Test Connection. A confirmation message should appear, as shown in Figure 6.
    Figure 6. Accept the connection confirmation
    The confirmation dialog is titled 'Next Connection' and reads 'Connection succeeded.' It has one button: 'OK.'
  9. Click OK.
  10. Click Finish. The connection profile appears in the Data Source Explorer, as shown in Figure 7.
    Figure 7. Browse the new Oracle connection profile in the Data Source Explorer
    New Oracle connection ORA11 showing child folders: Roles, Schemas, Table Spaces, and Users.

Connecting to Oracle using the DataDirect Connect JDBC driver

Follow these steps to connect to Oracle using the DataDirect Connect JDBC driver:

  1. Obtain the full DataDirect Connect JDBC driver or the Oracle-only DataDirect Connect 4.0 JDBC driver from DataDirect.
  2. If you have the full DataDirect Connect JDBC 3.7 driver:
    1. Unzip connectjdbc.jar into a new directory named installdd. This gives you a 15-day license.

      Note: Do not attempt to renew the license using the LicenseTool.jar in the installation directory. Attempts to do so have been unsuccessful and destructive.

    2. From the installdd directory, run the Installer and choose a directory where you want to install the driver (for example, the C:\jdbcDrivers\Oracle\datadirect directory).
  3. If you have the Oracle-only DataDirect Connect JDBC driver, you may use oracle.jar "as is" depending on its terms of use according to your license.
  4. In ODS, open Window -> Preferences, and select Data Management -> Connectivity -> Driver Definitions.
  5. Click Add...
  6. The following tables provide details on how to fill in the fields on the New Driver Definition dialog depending on which Oracle database and driver you are using.

    Name/Type:

    Oracle 10Oracle 11
    Driver Type FilterDatabaseDatabase
    Vendor FilterOracleOracle
    Available driver templatesOther Driver / Oracle / 10Other Driver / Oracle / 11
    Driver nameDataDirect Connect for Oracle 10DataDirect Connect for Oracle 11
    Driver typeOther DriverOther Driver

    Jar List Driver Files:

    Full DataDirect Connect 3.7 DriverOracle-Only (4.0) Driver
    C:\jdbcDrivers\Oracle\datadirect\lib\base.jar
    C:\jdbcDrivers\Oracle\datadirect\lib\util.jar
    C:\jdbcDrivers\Oracle\datadirect\lib\oracle.jar
    C:\jdbcDrivers\Oracle\datadirect\oracle.jar

    Properties:

    Oracle 10Oracle 11
    CatalogALLALL
    Connection URLjdbc:datadirect:oracle://habu.svl .ibm.com:1521;SID=ora10gjdbc:datadirect:oracle://chex.svl .ibm.com:1521;SID=ORA11
    Database Nameoracleoracle
    Driver Classcom.ddtek.jdbc.oracle.OracleDrivercom.ddtek.jdbc.oracle.OracleDriver
    Password (optional)
    User ID (optional)CRIOLLO1CRIOLLO1
    Note: You can put the SID either on the connection URL, as above, or add it as an additional property on the Optional tab.
  7. After you OK everything, you can create a new connection profile for Oracle using this driver definition from the Data Source Explorer using the New Connection wizard.

Oracle catalog views

When you create an Oracle connection, you may select one of three catalog views that filter what you see in the Data Source Explorer by owner:

All
This view describes all objects that are accessible to you.
DBA
This view describes all objects in the database.
User
This view describes objects that you own.

Browsing your database and objects

After you create a database connection, its connection profile appears in the Data Source Explorer.

Expand the DB2 connection to GSDB to see its main folders. Most of the information that you are interested in is in the Schemas folder. Expand the Schemas folder, scroll to your default schema name, and expand that to see its folders, as shown in Figure 8.

Figure 8. Expand your DB2 connection to see folders in the GOSALES schema
Expanded DB2 schema 'GOSALES showing child folders and unexpanded schemas GOSALESCT, GOSALESDW, and GOSALESHR.

Expand your Oracle connection to see its main folders. Expand the Schemas folder, scroll to your default schema name, and expand that to see its folders, as shown in Figure 9.

Figure 9. Expand your Oracle connection to see folders in the CRIOLO1 schema
Expanded Oracle schema CRIOLLO1 showing child folders.

You can find existing objects under their folders. When you select an existing object, you can see its properties in the Properties view. The General tab in the Properties view identifies the object, as shown in Figure 10.

Figure 10. Browse the general properties of a stored procedure
The Properties view for the stored procedure TEST_NUMERIC. The General tab is selected.

From the Oracle point of view, the implementation language for this PL/SQL procedure is SQL, not PL/SQL. ODS 2.2 distinguishes between SQL and PL/SQL for development purposes.

The Parameters tab shows input and output parameters, as shown in Figure 11.

Figure 11. Browse the parameters of a stored procedure
The Properties view for the stored procedure TEST_NUMERIC. The Parameters tab is selected and shows a table of parameters.

The Source tab for the procedure shows its CREATE statement, as shown in Figure 12.

Figure 12. Browse the source of a stored procedure
The Properties view for the stored procedure TEST_NUMERIC. The Source tab is selected.

Working offline

The Data perspective is designed to help you work with your databases; however, you can still do some work when you are traveling or the network is down. This is called working offline.

To cache the database catalog information in your workspace for working offline, choose these actions from the pop-up menu by right-clicking on the connection profile:

  1. Connect
  2. Save Offline
  3. Disconnect
  4. Work Offline

You can tell if you are working offline when the pop-up menu on the connection profile lets you connect or disconnect, but does not let you work offline or save offline.

When you are working offline, you can:

  • Expand the database to view all objects in your schemas and their properties.
  • Create a data development project for the database connection.
  • Drag and drop (or copy and paste) PL/SQL packages and routines from the Data Source Explorer to your project in the Data Project Explorer.
  • Create new PL/SQL packages and routines in your project.
  • Edit objects in your project.

However, if you choose an action in your project that requires a connection, such as Run or Deploy, you may be prompted to connect. If you choose not to, then the action will not complete.

As you can see in the above CREATE statement, the TEST_NUMERIC procedure receives a DECIMAL and NUMERIC and returns these values incremented by 1.

By comparing the source with the parameters, you can also see that although the VAR01 parameter data type is defined as DECIMAL, the Oracle database server has normalized this to NUMBER.

An Oracle NUMBER can be an integral, floating-point, or decimal value depending on its optional precision and scale.


Developing PL/SQL objects for Oracle

The processes for developing PL/SQL packages and routines with Optim Development Studio are similar to the processes for developing DB2 packages and routines.

Because Optim Development Studio 2.2 supports PL/SQL for both Oracle and DB2, you may easily transfer work from Oracle to DB2 by dragging objects from an Oracle project in the Data Project Explorer, or from an Oracle database in the Data Source Explorer, to a DB2 project in the Data Project Explorer.

Creating an Oracle data development project

A data development project contains a set of SQL scripts, routines, and XML-related resources associated with one, and only one, database connection profile in the Data Source Explorer. A data development project associated with a DB2 LUW 9.7 connection profile is called a DB2 project, and a data development project associated with an Oracle 10g or 11g connection profile is called an Oracle project.

Follow these steps to create a data development project in the Data Project Explorer:

  1. Right-click on an empty part of the Data Project Explorer background to open the pop-up menu. Select New -> Data Development Project, as shown in Figure 13.
    Figure 13. Launch a wizard to create a new data development project
    Pop-up menu to create a new Data Development Project

    This menu appears only if you click in the white space while nothing in the view is selected.

  2. The Development Project page of the wizard appears. Enter a name for the project, as shown in Figure 14, and click Next >.
    Figure 14. Name your data development project
    The Data Development Project page of the New Data Development Project wizard
  3. From the Select Connection page, choose an existing Oracle connection, as shown in Figure 15, or create a new connection.
    Figure 15. Select a connection profile for your data development project
    The Select Connection page of the New Data Development wizard with the description
  4. Click Finish. The project now appears in the Data Project Explorer.
  5. Expand the project. Figure 16 shows an example of what an Oracle project looks like.

    Figure 16. Expand the folders of your Oracle project
    A data development project for Oracle with folders PL/SQL Packages, SQL Scripts, Stored Procedures, User-Defined Functions, and XML

Each folder contains resources of only one type. You can right-click on any folder and use the pop-up menu to invoke a wizard that lets you create a resource of that type to be contained in the folder.

Each New action on a folder in a data development project opens a wizard for creating an object of the type implied by the folder name.

Creating a PL/SQL procedure for Oracle

Follow these steps to use the New Stored Procedure wizard to create a PL/SQL procedure:

  1. Right-click on the Stored Procedures folder of your Oracle data development project. From the pop-up menu, select New -> Stored Procedure. The New Stored Procedure wizard appears, as shown in Figure 17.
    Figure 17. Name a new PL/SQL procedure for Oracle
    The Name and Language page of the New Stored Procedure wizard.

    For Oracle, PL/SQL is the only supported language, so the Language field is disabled.

  2. By default, the value for the Project field is the project that contains your selection. You may also choose a different target project for the new procedure or use the New... button to create a different target project.
  3. Enter a name for the procedure. You may qualify this name with an owner (for example, CRIOLLO1.TESTPROCEDURE). You may also delimit the procedure name to respect lower case characters and to permit spaces and special characters (for example, CRIOLLO1."Test.Procedure"). For Oracle, the name (without the owner) may be up to 30 characters long.
  4. Click Next > to show the Parameters page, as shown in Figure 18.
    Figure 18. Add parameters for a PL/SQL procedure for Oracle
    The Parameters page of the New Stored Procedure wizard. The Add... button has been clicked to show the 'Parameters dialog.

    Use Add... and the Parameters dialog to add parameters of any supported data type.

    For a list of supported data types, see Appendix A.

    For Oracle, you cannot specify the lengths, precisions, or scales for a parameterized data type. Oracle does not permit these in parameter declarations (or in the return type declaration for a function). Instead, Oracle permits the maximum lengths, precisions, or scales for the data types.

    Variables declared in a PL/SQL routine can include lengths, precisions, and scales. You can add these in the routine editor.

  5. When you are finished adding parameters, click Next > to go to the Summary page.
  6. Click Show SQL to see the PL/SQL code that will be generated, as shown in Figure 19.
    Figure 19. Check the generated PL/SQL code for an Oracle procedure
    The Summary page of the New Stored-Procedure wizard. The Show SQL button has been clicked to show the SQL Statement or Expression dialog.
  7. Click Finish to generate the procedure, add it to your project, and open it in the routine editor.
  8. Examine the new procedure in the routine editor. Before you can deploy this procedure, you need to add code to its body. A simple and testable enhancement is to change VAR01 to an output parameter and to assign a value to it, as follows:
    CREATE OR REPLACE PROCEDURE PROCEDURE1 ( VAR01 OUT INT )
    AS
    BEGIN
        VAR01 := 2020;
    END;

Creating a PL/SQL function for Oracle

Follow these steps to use the New User-Defined Function wizard to create a PL/SQL function:

  1. Right-click on the User-Defined Functions folder of your Oracle data development project. From the pop-up menu, select New -> User-Defined Function. The New User-Defined Function wizard appears, as shown in Figure 20.
    Figure 20. Name a new PL/SQL function for Oracle
    The Name and Language page of the New User-Defined Function wizard.

    For Oracle, PL/SQL is the only supported language, so the Language field is disabled.

  2. Click Next > to show the SQL Statement or Expression page, as shown in Figure 21.
    Figure 21. Specify a statement or expression for an Oracle function
    The SQL Statement or Expression page of the New User-Defined Function wizard.

    From this page, you may enter an expression to be returned by the function. Optionally, you may choose not to enter an expression here and change it in the routine editor later.

    For PL/SQL, ODS 2.2 does not support table functions. A PL/SQL function must have a scalar result.

  3. Click Next > to show the Return Data Type page, as shown in Figure 22.
    Figure 22. Specify the function return type for Oracle
    The Return Data Type page of the New User-Defined Function wizard.

    For Oracle, you cannot specify the length, precision, or scale for a return data type. Oracle does not permit these in return type declarations. Instead, Oracle permits the maximum length, precision, or scale for the data type to be returned.

  4. Click Next > to show the Parameters page, as shown in Figure 23.
    Figure 23. parameters for a PL/SQL function for Oracle
    The Parameters page of the New PL/SQL User-Defined Function wizard.

    Use Add... and the Parameters dialog to add parameters of any supported data type.

  5. When you are finished adding parameters, click Next > to go to the Summary page.
  6. Click Show SQL to see the PL/SQL code that will be generated, as shown in Figure 24.
    Figure 24. Check the generated PL/SQL code for a Oracle function
    The Summary page of the New PL/SQL User-Defined Function wizard. The Show SQL button has been clicked to show the SQL Statement or Expression dialog

    ODS 2.2 does not determine the data type of the expression and it cannot automatically determine the return type. The generated code will not work without modification if you change the SQL expression or return data type so that they do not correspond.

  7. Click Finish to generate the function, add it to your project, and open it in the routine editor.

  8. Examine the new function in the routine editor. For testing, you can return the value of the input parameter VAR01, as follows:

    CREATE OR REPLACE FUNCTION FUNCTION1 ( VAR01 INT )
    RETURN INT
    AS
    BEGIN
        RETURN VAR01;
    END;

Creating a PL/SQL package for Oracle

Follow these steps to use the New PL/SQL Package wizard to create a PL/SQL package:

  1. Right-click on the PL/SQL Packages folder of your Oracle data development project. From the pop-up menu, select New -> PL/SQL Package. The New PL/SQL Package wizard appears, as shown in Figure 25.
    Figure 25. Name a new PL/SQL package for Oracle
    The Name page of the New PL/SQL Package wizard.

    This wizard has only one page. Specify the target platform and the name of the PL/SQL package.

  2. Click Finish to generate the PL/SQL package, add it to your project, and open it in the PL/SQL package editor.
  3. Examine the new PL/SQL package in the editor. It contains two routines, ExampleProcedure and ExampleFunction, which you may deploy and test as they are, or modify or replace in the editor with your own PL/SQL package routines.

    The PL/SQL package specification gives the signature for a procedure and a function:

    CREATE OR REPLACE PACKAGE PACKAGE1
    AS
       /* PL/SQL package specification */
       PROCEDURE ExampleProcedure;
       FUNCTION ExampleFunction RETURN INT;
    END PACKAGE1;

    The PL/SQL package body implements the procedure and function:

    CREATE OR REPLACE PACKAGE BODY PACKAGE1
    AS
       /* PL/SQL package body */
       PROCEDURE ExampleProcedure  
       AS
       X INT := 1; 
       BEGIN 
          X := X + 1; 
       END ExampleProcedure; 
     
       FUNCTION ExampleFunction  
       RETURN INT 
       AS 
       X INT; 
       BEGIN 
          X := 1; 
          RETURN (X + 1); 
       END ExampleFunction; 
    
    END PACKAGE1;

Moving a table from DB2 to Oracle

This section shows you how to edit the default PL/SQL package that you generated so that it does some useful work. Later, you will learn how to copy the PL/SQL package from Oracle to DB2. Therefore, you want to have the same table on both DB2 and Oracle.

This article assumes that you have installed the GSDB sample database on your DB2 LUW 9.7 server, but it does not assume that your Oracle database has the same sample tables. Therefore, follow these steps to recreate a DB2 table and its data on Oracle.

  1. Connect to your GSDB database for DB2 and expand the tables in its GOSALES schema to find the INVENTORY_LEVELS table.
  2. Expand the table columns so that you can view the names and data types, as shown in Figure 26.
    Figure 26. View the columns of the GOSALES.INVENTORY_LEVELS table
    The Data Source Explorer window showing the column names and data types for the INVENTORY_LEVELS table.
  3. Right-click on the INVENTORY_LEVELS table. From the pop-up menu, select Generate DDL.... The Generate DDL wizard appears.
  4. Clear the COMMENT ON statements and IN TABLESPACE clause check boxes. Leave only CREATE statements selected, as shown in Figure 27.
    Figure 27. Set options for generating DDL to create the INVENTORY_LEVELS table
    Options page of the Generate DDL wizard
  5. Click Next >. The Objects page appears.

  6. Click Deselect All, then select the Primary key constraint and Tables check boxes, as shown in Figure 28.
    Figure 28. Select objects for generating DDL to create the INVENTORY_LEVELS table
    Objects page of Generate DDL wizard
  7. Click Next >. The Save and Run DDL page appears.
  8. Review the generated statements in the Preview DDL area:
    --<ScriptOptions statementTerminator="!">
    
    CREATE TABLE INVENTORY_LEVELS (
          INVENTORY_YEAR SMALLINT NOT NULL,
          INVENTORY_MONTH SMALLINT NOT NULL,
          WAREHOUSE_BRANCH_CODE INTEGER NOT NULL,
          PRODUCT_NUMBER INTEGER NOT NULL,
          OPENING_INVENTORY INTEGER,
          QUANTITY_SHIPPED INTEGER,
          ADDITIONS INTEGER,
          UNIT_COST DECIMAL(19 , 2),
          CLOSING_INVENTORY INTEGER NOT NULL,
          AVERAGE_UNIT_COST DECIMAL(19 , 2)
       )
       DATA CAPTURE NONE!
    
    ALTER TABLE INVENTORY_LEVELS ADD CONSTRAINT SQL090729130124500 PRIMARY KEY
       (INVENTORY_YEAR,
        INVENTORY_MONTH,
        WAREHOUSE_BRANCH_CODE,
        PRODUCT_NUMBER)!
  9. Click Browse... to show the Folder Selection dialog.
  10. Select Oracle Development and click OK.
  11. Change the value of the File name field to inventory_levels.sql, as shown in Figure 29.
    Figure 29. Specify the name for the file for the generated DDL.
    The Save and Run page of the Generate DDL wizard. The File name field has been changed to inventory_levels.sql.
  12. Click Next > to show the Summary page.
  13. Click Finish. The inventory_levels script appears in the SQL Scripts folder of your Oracle Development project.
  14. This script was generated for DB2, but because you want to run it for Oracle, you need to go to the SQL editor and remove the DATA CAPTURE NONE clause. The script should then look like the following:
    --<ScriptOptions statementTerminator="!">
    
    CREATE TABLE INVENTORY_LEVELS (
          INVENTORY_YEAR SMALLINT NOT NULL,
          INVENTORY_MONTH SMALLINT NOT NULL,
          WAREHOUSE_BRANCH_CODE INTEGER NOT NULL,
          PRODUCT_NUMBER INTEGER NOT NULL,
          OPENING_INVENTORY INTEGER,
          QUANTITY_SHIPPED INTEGER,
          ADDITIONS INTEGER,
          UNIT_COST DECIMAL(19 , 2),
          CLOSING_INVENTORY INTEGER NOT NULL,
          AVERAGE_UNIT_COST DECIMAL(19 , 2)
       )!
    
    ALTER TABLE INVENTORY_LEVELS ADD CONSTRAINT SQL090729130124500 PRIMARY KEY
       (INVENTORY_YEAR,
        INVENTORY_MONTH,
        WAREHOUSE_BRANCH_CODE,
        PRODUCT_NUMBER)!
  15. Right-click In the SQL editor. From the editor's pop-up menu, select Run SQL.

    When the script completes, you should see a "Succeeded" status for the operation in the SQL Results view. Because the table name in the CREATE statement is not qualified, the table is created in your Oracle database under your user ID.

    In the remaining steps of this section, you will transfer the data from the INVENTORY_LEVELS table in DB2 to the corresponding table in Oracle.

  16. In your DB2 connection to GSDB, right-click on the INVENTORY_LEVELS table. From the pop-up menu, select Data -> Extract.... The Extract Data wizard appears.
  17. Use the Output file field to identify the path and name of the file where you want to extract the data from the table to. Optionally, you can use Browse... to assist with this. For example, you might enter C:\temp\INVENTORY_LEVELS.data, as shown in Figure 30.
    Figure 30. Export the data from GOSALES.INVENTORY_LEVELS
    Extract Data wizard with C:\temp\INVENTORY_LEVELS.data entered in the Output file field.
  18. Click Finish. The operation starts in the SQL Results view. When it completes, the Message1 tab shows the following:

    Extracting "GOSALES"."INVENTORY_LEVELS"...
    Data extraction was successful.
    53730 row(s) extracted.
  19. In your Oracle connection, right-click on the INVENTORY_LEVELS table. From the pop-up menu, select Data -> Load.... The Load Data wizard appears.
  20. In the Input file field, enter the path and name of the output file where you extracted data to with the Extract Data wizard in step 17.
  21. Click Finish. Again, an operation starts in the SQL Results view. When the operation completes, the INVENTORY_LEVELS table in Oracle contains the same data as the table in DB2.
  22. To see a sample of that data, right-click on the INVENTORY_LEVELS table in your Oracle connection, and select Data -> Sample Contents from the pop-up menu.

    The Sample Contents operation displays the data in the Result1 tab of the SQL Results view.

Editing a PL/SQL package for Oracle

When you finish creating a new object in a wizard, ODS 2.2 opens the object in the appropriate editor.

If you have closed an editor instance for an object, you can re-open it by double-clicking the object in your data development project, or by right-clicking on it and selecting Open from the pop-up menu.

  1. Open the PL/SQL package editor for the PL/SQL package named PACKAGE1 that you created in your Oracle Development project.

    The PL/SQL package editor has two tabs, one for the specification and one for the body. The specification is the declaration of the public API for your PL/SQL package. The implementations are in the body.

    Both of these tabs show your source using the SQL editor; however, the Validate Statement Syntax option (shown in the pop-up menu for the editor) is unchecked by default because ODS 2.2 does not have a live PL/SQL parser capable of validating the syntax. If you check this option in the pop-up menu, then the SQL parser generates errors for PL/SQL syntax.

    The content of both tabs are fully editable. You may change the name of the PL/SQL package, add, modify, or remove routines, and (in the Body tab) implement the routine bodies.

    You may change the name of the PL/SQL package in the editor, but you must change it in four locations, at the beginning and at the end of both the specification and the body. When you save a name change, the name is updated in your project.

  2. Go to the Specification tab and change the name of the package to INVENTORY and replace the existing samples with the signature for a QUERY_CLOSING_INVENTORY function, as follows:
    CREATE OR REPLACE PACKAGE INVENTORY
    AS
       FUNCTION QUERY_CLOSING_INVENTORY(
           Y NUMBER,
           M NUMBER,
           W NUMBER,
           P NUMBER
           )
        RETURN NUMBER;
       
    END INVENTORY;
  3. Go to the Body tab and change the name of the package to INVENTORY and replace the existing samples with the implementation for the QUERY_CLOSING_INVENTORY function, as follows:
    CREATE OR REPLACE PACKAGE BODY INVENTORY
    AS
        FUNCTION QUERY_CLOSING_INVENTORY(
           Y NUMBER,
           M NUMBER,
           W NUMBER,
           P NUMBER
           )
        RETURN NUMBER
        AS
        X NUMBER(38,0);
        BEGIN
           SELECT CLOSING_INVENTORY INTO X
           FROM INVENTORY_LEVELS
              WHERE INVENTORY_YEAR = Y
              AND INVENTORY_MONTH = M
              AND WAREHOUSE_BRANCH_CODE = W
              AND PRODUCT_NUMBER = P;
           RETURN X;
        END;
    
    END INVENTORY;
  4. Save your changes by pressing Ctrl+S or by selecting File -> Save on the main menu or from the editor's pop-up menu. Both specification and body are saved and the new name appears in the PL/SQL Packages folder of your project.

    Later, you will see how to deploy and test this PL/SQL package.

Editing a PL/SQL routine

After creating a PL/SQL procedure or function, you typically want to edit it to add your own business logic. The editors for a PL/SQL procedure or function provide similar functionality to the editor for a PL/SQL package.

The PL/SQL routine editor has two tabs. The Configuration tab contains the attributes of the model. The Source tab contains the PL/SQL source.

The Configuration tab for a PL/SQL procedure for Oracle is shown in Figure 31:

Figure 31. Review the Configuration tab of the PL/SQL procedure editor for TEST_TIMESTAMP
Configuration tab of the PL/SQL routine editor showing attributes of the TEST_TIMESTAMP routine.

The most useful attribute on the Configuration tab is the one that indicates whether the routine is enabled for debugging. You must check this box and deploy this change before you can debug the procedure.

ODS 2.2 does not support debugging a stand-alone function (PL/SQL, SQL, or OLE DB).

The Deploy, Run, and Debug links to the right of the Deployment Configuration section work like buttons or menu items to invoke the normal actions for deploying and testing your work.

The Parameters and Options sections are expandable. The parameters in the table for a PL/SQL routine are not editable, as shown in Figure 32.

Figure 32. Review the parameters for the PL/SQL TEST_TIMESTAMP procedure
The expanded Parameters section of the PL/SQL routine editor for the TEST_TIMESTAMP routine.

You can edit the content of the Source tab. You may change the name of the routine, add, modify, or remove parameter names, modes, and data types, modify the return type of a function, and implement the body of the routine. The editor does not validate PL/SQL syntax as you enter it, but it does use color syntax highlighting as shown in Figure 33.

Figure 33. Edit the source of the PL/SQL procedure TEST_TIMESTAMP
Source tab of the PL/SQL routine editor showing color syntax highlighting of the PL/SQL source for the TEST_TIMESTAMP routine.

Deploying a PL/SQL package to your Oracle server

Unlike a Java application, which you can compile and run locally, you must deploy PL/SQL packages, procedures, and functions to your database server before you can run them. You cannot run them in your project, but you can deploy them and run them from your project.

When you select a PL/SQL package, procedure, or function in a data development project and then select the Deploy... action from the object's pop-up menu, and if you are connected to the database associated with the project, then the Deploy PL/SQL Package wizard appears. In this case, the wizard starts with the Deploy Options page, as shown in Figure 34.

Figure 34. Set deployment options for a PL/SQL package
Deploy Options page of the Deploy PL/SQL Package wizard.

When you select the object folder, or multiple objects in a single folder, and then select the Deploy... action from the pop-up menu, the Deploy PL/SQL Package wizard appears. In this case the wizard starts with the Selection page preceding the Deploy Options page as shown in Figure 35. From this page you can check individual objects, or click Select All.

Figure 35. Select PL/SQL packages to deploy
Selection page of Deploy PL/SQL Package wizard.

The Deploy wizard lets you deploy single or multiple objects of the same type.

When you deploy multiple PL/SQL procedures, you get additional options on the Deploy Options page, as shown in Figure 36.

Figure 36. Set options for deploying multiple PL/SQL packages
Deploy Options page of Deploy PL/SQL Package wizard.

You can deploy the selected objects to your current database, which is the database associated with your project, or you can select a different compatible database as your deployment target.

You can specify a target schema (or owner, for Oracle). For DB2, the target schema is the SQLID that will be used for unqualified objects.

For DB2, you can change the default path by adding or removing schemas from this comma-delimited list.

You can also determine how to handle duplicate objects. By default, ODS 2.2 drops duplicate objects before recreating them, but it can also stop and roll back changes if it encounters an error because the object already exists on the database.

When your PL/SQL package source contains CREATE OR REPLACE statements, the duplicate handling options are ignored unless you remove OR REPLACE. For DB2 LUW 9.7, you may add OR REPLACE to any CREATE PROCEDURE or CREATE FUNCTION statement, but for SQL and Java routines, the duplicate handling options are not ignored.

When you click Next >, the Deploy Options page or the PL/SQL Package Options page appears, as shown in Figure 37.

Figure 37. Set deploy options for PL/SQL packages
PL/SQL Package Options page of the Deploy PL/SQL Package wizard

This page lets you change the options for each object that you are deploying. For PL/SQL packages, the only option you have is whether to enable debugging. There can be more options for other object types and database targets.

Except for enabling debugging, options that you change here are not saved in your project. If the deployment is successful, they are saved only in your target database.

When you click Finish, the selected objects are deployed sequentially in a background thread. Results are reported in the SQL Results view.

When more than one object is deployed, operations for each are grouped together in the SQL Results view. To view the status of an individual object, expand the group and select the object, as shown in Figure 38.

Figure 38. Expand the deployment operation in the SQL Results view
The SQL Results view showing the successful status of the CRIOLLO1.PACKAGE1 object.

Running a PL/SQL package on Oracle

You can run a stand-alone routine (regardless of its implementation language) in the Data Source Explorer. In the Data Project Explorer, if you can deploy it, then you can also run it. When you select a PL/SQL package to run, first you are prompted to select a procedure or function in the PL/SQL package. If the routine declares input parameters, then you are also prompted to supply values for them.

Follow these steps to run the CRIOLLO1.INVENTORY.QUERY_CLOSING_INVENTORY function:

  1. Right-click on the PL/SQL package named INVENTORY. From the pop-up menu, select the Run.... The Run - CRIOLLO1.INVENTORY dialog appears, as shown in Figure 39.
    Figure 39. Run the QUERY_CLOSING_INVENTORY function in the PL/SQL package
    The Run - CRIOLLO1.INVENTORY dialog.
  2. Click Run. The Specify Parameter Values dialog appears.
  3. As shown in Figure 40, enter the following parameter values:
    • Y = 2007
    • M = 1
    • W = 7
    • P = 2110
    Figure 40. Enter values for input parameters for running the QUERY_CLOSING_INVENTORY function
    The Specify Parameter Values - QUERY_CLOSING_INVENTORY dialog.
  4. Click OK. The run operation appears in the SQL Results view. The Result1 tab for the operation shows the return value of the function, as shown in Figure 41.

    Figure 41. Check the result after running the QUERY_CLOSING_INVENTORY function
    The Result1 tab of the SQL Results view showing the return value of the run operation for CRIOLLO1.INVENTORY.QUERY_CLOSING_INVENTORY.

You can also use the Data Source Explorer or Data Project Explorer to run a routine in a PL/SQL package.

Debugging a PL/SQL package on Oracle

ODS 2.2 provides PL/SQL routine debuggers for both DB2 LUW 9.7 and (when using the Oracle JDBC thin driver) Oracle 10g or 11g.

Follow these steps to debug a PL/SQL package on Oracle:

  1. When you create, edit, or deploy a routine that can be debugged, select the Enable debugging check box:

    If you deploy a PL/SQL package or routine without selecting this check box, the server does not generate debug information for it. Setting and saving this option in the routine editor is not sufficient to enable debugging. You must also deploy the routine with this option selected.

    Deploy the PL/SQL package named INVENTORY. Be sure to select the Enable debugging check box on the PL/SQL Package Options page.

  2. Right-click on the INVENTORY package. From the pop-up menu, select Debug.... Similar to when you run the package, this causes the Run - CRIOLLO1.INVENTORY dialog to appear with the following function selected: QUERY_CLOSING_INVENTORY (NUMBER,NUMBER,NUMBER,NUMBER) RETURN NUMBER
  3. As you did when running the routine, enter the following parameter values:
    • Y = 2007
    • M = 1
    • W = 7
    • P = 2110
  4. Click OK.

    You are then asked to confirm that you want to switch to the Debug perspective, as shown in Figure 42.

    Figure 42. Confirm that you want to switch to the Debug perspective
    Prompt to confirm you want to switch to the Debug perspective.
  5. Click Yes.

  6. The Debug perspective gives you the views that you need for debugging a routine. You may arrange and size these to suit your own preferences:

    • The stack trace appears in the Debug view. The toolbar for this view contains the following useful buttons:
      IconButton name (shortcut)Function
      Resume (F8) debug tool Resume (F8)Runs your routine to the next breakpoint or to completion.
      Terminate (Ctrl+F2) debug toolTerminate (Ctrl+F2) Terminates your routine and the debugging session without returning results.
      Step into (F5) debug toolStep into (F5)Lets you debug the function referenced or the procedure called on the current statement.
      Step over (F6) debug toolStep over (F6)Steps over the function referenced or procedure called on the current statement.
      Step return (F7) debug toolStep return (F7)Returns from a function or procedure that you have stepped into.
    • Your breakpoints appear in the Breakpoints view.
    • The routine variables with their current values appear in the Variables view.
    • Your source appears in the routine editor.
  7. The debugger always stops at the first line of a routine, even if you have not set a breakpoint.

    To set a breakpoint, double-click in the left margin of the routine editor, or right-click in the margin to the left of the statement where you want the debugger to stop, and select Add Breakpoint from the pop-up menu.

    Figure 43 shows the debug perspective view after a breakpoint has been encountered on line 5 of PROCEDURE1.

    Figure 43. Set a breakpoint in the Debug perspective
    Debug perspective views as arranged by the author.
  8. If you run to completion, then the SQL Results view appears to show you the status and any results, as shown in Figure 44.

    Figure 44. Check status and the result after debugging
    Debug perspective with the SQL Results view as a temporary fast view popping up from the bottom of the window.

Tip: When you edit an Oracle PL/SQL package or routine that you intend to debug, keep the CREATE and object keyword on the same line. The Oracle server removes line breaks until after the object keyword (PACKAGE, PACKAGE BODY, PROCEDURE, or FUNCTION). So if you don't do this, the line numbers in your source will not match the line numbers known to the server.

One procedure may call another procedure or execute a function when evaluating an expression. The debugger lets you step into these nested calls and function references.

You can debug a PL/SQL function if it is in a PL/SQL package, and you may step from a PL/SQL procedure to a PL/SQL function if it is in the same PL/SQL package. You can debug a PL/SQL procedure if it is in a PL/SQL package or if it is stand-alone, and you may step from a call in the body of one into the PL/SQL procedure that is called.

Unlike the SQL and Java routine debuggers in ODS 2.2, the PL/SQL debugger for DB2 does not require that you set up or start a debug session manager. The debug session manager is integrated with the debugger client and starts automatically.


Developing PL/SQL objects for DB2 LUW 9.7

To develop a PL/SQL package, procedure, or function for DB2, you perform the same tasks as you do when developing for Oracle. The differences between DB2's and Oracle's support for PL/SQL are minor:

  • When you name a PL/SQL package, procedure, or function for Oracle, the name (without the owner) is limited to 30 characters. For DB2, the name may be up to 128 characters long.
  • When you create a stand-alone procedure or function for DB2, you have a choice of languages other than PL/SQL (SQL and Java). Therefore, the Language field for selecting the implementation language in the wizard is not disabled and selects SQL by default.
  • When you are using a wizard to add a parameter to a PL/SQL procedure or function, or change the return type of a function, the list of data types for DB2 includes SQL data types as well as PL/SQL data types. You may use, for example, CHAR(254) FOR BIT DATA for a parameter for DB2. Where DB2 supports synonyms for the data type, such as INTEGER and INT, the wizard shows the preferred DB2 synonym (INTEGER).

    For a list of supported data types, see Appendix A.

  • For DB2, you specify the lengths, precisions, or scales for parameterized data types as usual, and these restrict the values of the parameters.
  • When you create a DB2 project in the Data Project Explorer, in addition to the folders for PL/SQL packages, SQL scripts, stored procedures, user-defined functions, and XML objects, you see a Web Services folder, as shown in Figure 45.
    Figure 45. See the Web Services folder in a data development project for DB2
    A data development project for DB2 named DB2 Development with folders PL/SQL Packages, SQL Scripts, Stored Procedures, User-Defined Functions, Web Services, and XML

    The actions associated with the Web Services folder help you generate Web services that run statements in a SQL script or call the stored procedures and user-defined functions.

    To create a Web service, right-click on the Web Services folder and select New Web Service... from the pop-up menu to launch the wizard. Then you can drag SQL scripts and stored procedures into the new service.

    You cannot copy or drag a PL/SQL package into a Web service to create an operation to call a procedure in the PL/SQL package. To create a Web service operation that calls a procedure in a PL/SQL package, generate a dummy service for another procedure, then edit the operation to add the PL/SQL package name as the middle qualifier, for example:
    TEAM.INVENTORY.TOTAL_INVENTORY_LEVELS

After you create your DB2 data development project, you can start development using PL/SQL in a DB2 project by copying or dragging PL/SQL objects from your Oracle project to your DB2 project. Or, you can copy or drag objects from your Oracle database connection in the Data Source Explorer.

Dragging a PL/SQL object from Oracle to a DB2 project

After you have created a PL/SQL package or stand-alone procedure or function in a data development project for Oracle, you may copy or drag it into another Oracle project or into a data development project for DB2 LUW 9.7.

After you have deployed a PL/SQL package or stand-alone procedure or function to Oracle, you can find it in the Data Source explorer and copy or drag it to a data development project for Oracle or DB2 LUW 9.7.

Follow these steps to drag a PL/SQL object from Oracle to a DB2 project:

  1. From the Data Source Explorer, expand your Oracle connection and find the PL/SQL package named INVENTORY.
  2. Adjust your workbench so that the Data Source Explorer and the Data Project Explorer are both visible.
  3. Drag the PL/SQL package from the Data Source Explorer to the PL/SQL Packages folder of your DB2 project in the Data Project Explorer. (If the Data Source Explorer and Data Project Explorer are not both visible, you can alternately copy from the source and paste to the target project.)
  4. When the drag and drop operation completes, you are notified that an incompatibility can exist, as shown in Figure 46.

    Figure 46. Accept that incompatibilities can exist between projects
    'Incompatible Server Destination dialog.
    Click OK.
  5. Open the PL/SQL package named INVENTORY that you have moved to your DB2 project by double-clicking it or by right-clicking and selecting Open from the pop-up menu.
  6. DB2 does not like the precision and scale on the variable declaration X NUMBER(38,0). Change all the data types to match the types in the INVENTORY_LEVELS table on DB2, in the Specification tab of the editor:
    CREATE OR REPLACE PACKAGE INVENTORY
    AS
       FUNCTION QUERY_CLOSING_INVENTORY(
           Y SMALLINT,
           M SMALLINT,
           W INTEGER,
           P INTEGER
           )
        RETURN INTEGER;
       
    END INVENTORY;
  7. Make corresponding changes in the Body tab:
    CREATE OR REPLACE PACKAGE BODY INVENTORY
    AS
       FUNCTION QUERY_CLOSING_INVENTORY(
           Y SMALLINT,
           M SMALLINT,
           W INTEGER,
           P INTEGER
           )
        RETURN INTEGER
        AS
        X INTEGER;
        BEGIN
           SELECT CLOSING_INVENTORY INTO X
           FROM INVENTORY_LEVELS
              WHERE INVENTORY_YEAR = Y
              AND INVENTORY_MONTH = M
              AND WAREHOUSE_BRANCH_CODE = W
              AND PRODUCT_NUMBER = P;
           RETURN X;
        END;
    
    END INVENTORY;
  8. If you were dragging a PL/SQL object that included a character data type, then you need to be sure to add the maximum string lengths when you edit the data types. For Oracle, the default length for CHAR is 2000 For DB2, the default length for CHAR is 1.
  9. Save your changes by pressing Ctrl+S or by selecting File -> Save on the main menu or from the editor's pop-up menu. Both specification and body are saved.

Deploying, running, and debugging on DB2

Deploying, running, and debugging PL/SQL packages and routines on DB2 is similar to performing these tasks on Oracle, or performing these tasks for SQL and Java routines on DB2.

Follow these steps to deploy, run, and debug a PL/SQL Package wizard to create a PL/SQL package:

  1. When you select a PL/SQL package, procedure, or function in a data development project and then select the Deploy... action from the object's pop-up menu, and if you are connected to the database associated with the project, then the Deploy PL/SQL Package wizard appears, as shown in Figure 47.
    Figure 47. Select options for PL/SQL packages for deployment to DB2
    Deploy Options page of the Deploy PL/SQL Package wizard

    The Default path field appears in the second group (Target schema...) for DB2 only. The field does not appear for Oracle.

    The third group (Error handling) appears on this page only when you have selected more than one PL/SQL package to deploy with this action.

  2. When developing, it is a good practice to select the Enable debugging check box on the PL/SQL Package Options page of the Deploy wizard so that DB2 compiles the object with debugging information.
  3. Click Finish. The selected objects are deployed sequentially in a background thread. Results are reported in the SQL Results view.

  4. You can run a stand-alone routine (regardless of its implementation language) in the Data Source Explorer. In the Data Project Explorer, if you can deploy it, then you can also run it. If the routine declares input parameters, then you are prompted to supply values for them.
  5. Parameter data types shown in the run dialog are normalized by the database server. DB2 servers normalize PL/SQL-specific data types to their equivalent SQL types. For example, BINARY_INTEGER is stored on a DB2 database as INTEGER.

  6. You can also run a routine in a PL/SQL package in the Data Source Explorer or in the Data Project Explorer.
  7. ODS 2.2 provides a PL/SQL routine debugger for DB2 LUW 9.7. Unlike the SQL and Java routine debuggers in ODS 2.2, the PL/SQL debugger for DB2 does not require you to set up or start a debug session manager. The debug session manager is integrated with the debugger client and starts automatically.

Specifying long or binary input values

The Specify Parameter Values dialog has additional functionality for specifying and importing large character-string, binary, and XML values. You can edit a cell in a secondary dialog by clicking the ellipsis button in the right most column for the parameter, as shown in Figure 48.

Figure 48. Click the ellipsis buttons for specifying long values
The Specify Parameter Values dialog for TEST_PARAMETERS. A table shows five parameters with the fifth one selected.

You can type a numeric or character-string value in the table cell. For a character-string value, you do not enter string delimiters. (For example, you would enter my value, not 'my value'.

To pass NULL for a character-string value (to distinguish NULL from an empty string), click Set to NULL. The pseudo value *NULL* is shown and a NULL is passed to the routine.

If the ellipsis button appears, you may click on it to get a larger dialog for editing the value.

For an XML data type, click Browse... to pick an XML file, and view it in the XML Tree tab, as shown in Figure 49.

Figure 49. Select an XML document for an input parameter
The Specify Value dialog for the parameter S, which shows an XML tree for a database descriptor.

For a binary data type, you must enter a hexadecimal string with no delimiters. Any file that you pick with Browse... is converted to hexadecimal, as shown in Figure 50.

Figure 50. Select a binary file for an input parameter
The Edit dialog for the parameter B showing a binary data type with a value in hexadecimal.

Parameter data types shown in the run dialog are normalized by the database server. DB2 servers normalize PL/SQL-specific data types to their equivalent SQL types. Oracle servers normalize subtypes to their base types. For example, INT is stored on an Oracle database as NUMBER, so that the run dialog shows NUMBER instead of INT.


Programming details

Using CURSOR types in PL/SQL

A PL/SQL procedure does not implicitly return all open cursors, as do native SQL procedures in DB2. Instead, you need to declare an OUT parameter of type CURSOR or REF CURSOR (such as SYS_REFCURSOR) and open it with a database query. This output value is received by a JDBC application as a result set.

For example, for a PL/SQL procedure on Oracle:

CREATE OR REPLACE PROCEDURE TEST_SYSREFCURSOR  (
      cursor_1 OUT SYS_REFCURSOR )
AS
BEGIN
    OPEN cursor_1 FOR SELECT OBJECT_NAME
        FROM USER_PROCEDURES
        WHERE OBJECT_NAME LIKE 'SP0%';
END;

To allow the calling program to process the cursor, you must leave it open.

Controlling the flow

The body of a PL/SQL routine is contained in a PL/SQL block, which starts with BEGIN and ends with END optionally followed by the name of the routine.

In the body of a PL/SQL routine, you can use control structures for sequence, iteration, and selection.

IF (condition) THEN...[ELSEIF (condition) THEN...][ELSE...] END IF;

CASE condition WHEN value THEN...[WHEN value THEN...] ELSE...END CASE;

FOR counter IN range LOOP...END LOOP;

WHILE condition LOOP...END LOOP;

WHILE...EXIT WHEN condition;

In addition, PL/SQL lets you code an exception handler at the end of a block:

EXCEPTION
    WHEN exception-name THEN ...
    [WHEN exception-name THEN ...]

See "Handling Errors and Exceptions" in the Oracle Database 2 Day Developer's Guide for a list of common exception names. There is a link to the guide in the Resources section.

Also, you may declare custom exceptions and raise them programmatically:

exception-name EXCEPTION;

IF condition THEN RAISE exception-name;

Naming conventions

For PL/SQL, you can basically follow the SQL standard in regard to naming. All names for Oracle objects have a maximum length of 30, except for a database name, which has a maximum length of 8.

In a SQL or PL/SQL statement, an ordinary SQL identifier can have no spaces or special characters. These names are folded to upper case by the database server, essentially making these names insensitive to case.

Names in SQL or PL/SQL with spaces, special characters, or lower case must be delimited with double quotation marks. These delimiters are not stored in the database catalog.

This scheme gives us two name formats:

SQL FormatCatalog Format
myNameMYNAME
MYNAMEMYNAME
"myName"myName
"my.name"my.name

In ODS 2.2, you see SQL names in the catalog format in the Data Source Explorer, and you see SQL names in the SQL format in the Data Project Explorer.

Names of PL/SQL packages and stand-alone routines can be qualified. In DB2, the qualifier is a schema name, which is also a SQL identifier. In Oracle, the qualifier is an owner name. This qualifier precedes the object's simple name and is separated from it with a dot:

"mySchema"."myName"

PL/SQL packages introduce another wrinkle, because the package is a member of a database schema, not the routines in the package. To test a routine in a package, you can qualify the routine name with the package name in the CALL statement:

CALL "mySchema"."myPackage"."myName"();

Oracle introduces yet another naming format, which ODS 2.2 does not support: remote names. A remote name has the name of another database following the simple name, separated with an at-sign:

CALL "mySchema"."myPackage"."myName"@otherDB();

Limitations

  • Not all PL/SQL functionality is supported by ODS 2.2:
    • See the table of data types in Appendix A for unsupported data types.
    • For DB2, you must declare the lengths, precisions, and scales of parameterized data types for routine parameters.
    • IBM does not support PL/SQL statement compilation and execution for the following product editions:
      • DB2 Express
      • DB2 Express-C
      • DB2 Personal Edition
    • DB2 does not let you create PL/SQL functions and triggers in a partitioned database environment.
    • For DB2, a TYPE declaration is not supported in a function, procedure, trigger, or anonymous block.
    • For DB2, the FOR EACH STATEMENT option is not supported for PL/SQL triggers.
  • Not all functionality supported for SQL and Java routines for DB2 is supported for PL/SQL:
    • There is no export functionality or command-line deployment for PL/SQL objects.
    • There is no generated error handling code as for DB2 SQL procedures.
    • You cannot add an SQL statement in the wizard when creating a PL/SQL stored procedure. The code that the wizard generates is not based on your specified database access, but is limited to declarations of your parameters.
    • There is no content assistance in the SQL editor or routine editor for PL/SQL.
    • There is no support for Run Settings.
    • There is no import wizard provided for PL/SQL packages, procedures, or functions.
    • You cannot copy or drag a PL/SQL package into a Web service to create an operation to call a procedure in the PL/SQL package.
  • Not all DB2 or Oracle functionality is supported by ODS 2.2:
    • No development of Java functions in a data development project for Oracle.
    • No development of triggers in a data development project, except that you can run a CREATE TRIGGER statement in an SQL script in the SQL editor.
    • No support in data development projects for DB2 modules.
    • No support for remote names on Oracle.
  • ODS 2.2 does not use the Outline view to show routines in a PL/SQL package.
  • You cannot copy or drag a stand-alone routine from a project or database into a PL/SQL package in a project. You cannot drag a routine in a PL/SQL package (either from a project or from a database) into a Stored Procedures or User-Defined Function folder.
  • You cannot necessarily copy objects between Oracle and DB2 databases without editing them because of differences in functionality. If a particular constraint, for example, is not supported by the engine, but the constraint can still be created, it will be. A warning will be posted to the SQL Results view. If the constraint cannot be created at all, an error is posted.
  • ODS 2.2 copies PL/SQL objects without translating them for the target project. If an Oracle PL/SQL package, procedure, or function contains syntax or functionality not supported on DB2, ODS 2.2 cannot deploy the copied object successfully. You would have to correct the issue before you can deploy the PL/SQL object successfully.
  • You cannot use the Data Object editor to create BIGFILE table spaces; however, you can use the Open with SQL editor menu action to edit the generated DDL and add the key word BIGFILE.
  • The existing SQL parsers do not support PL/SQL. In ODS 2.2, parsers are used for live evaluation of your source in the editors, for import, and for interpretation of SQL statements in the wizards. A PL/SQL parser is not included in this release; therefore, the editors have the following limitations:
    • The PL/SQL editors do not provide content assistance or mark errors. For a routine editor, syntax validation is turned off by default; however, for the SQL script editor, you should clear the Validate Statement Syntax check box in the editor menu when you edit a script containing PL/SQL.
    • The PL/SQL package editor does not synchronize changes in between the specification and body.
    • To delete a routine in a PL/SQL package, you need to edit the PL/SQL package and remove the routine from both the specification and body.
    • You cannot update parameters of a stand-alone PL/SQL routine in the Configuration tab of the editor.
    • The PL/SQL procedure wizard does not include the SQL statements page.
    • Even though there is a SQL statements page in the PL/SQL function wizard, it does not determine the return type from your SQL statement or expression, and it does not provide Import, Validate, or Create SQL buttons.
  • Debugging PL/SQL routines has these limitations:
    • Debugging PL/SQL routines requires a connection using an Oracle Thin JDBC driver.
    • You may debug a PL/SQL function only if it is in a PL/SQL package. You cannot debug any stand-alone function, regardless of its implementation language.
    • You cannot update a LOB element (LOB variable, LOB field within a row variable, or LOB element within an array variable) while debugging.
    • The debugger displays only the first 32 K bytes of a LOB value.
    • The debugger does not support an array of rows.

Appendix A. Supported data types

The following table shows the data types that ODS 2.2 supports for DB2 LUW 9.7 and Oracle 10g and 11g. If the data type is supported, it means that you may choose the first synonym in a wizard for the data type of a parameter or return type, and you may type in any of its synonyms in the routine editor.

PL/SQL and DB2 data types from the DB2 9.7 database definition that are supported by ODS 2.2 (bold indicates the type is unique to DB2)PL/SQL data types from the Oracle database definitions that are supported by ODS 2.2 (bold indicates the type is unique to PL/SQL)
  • BIGINT
  • BINARY_INTEGER
  • BLOB
  • CHAR, CHARACTER
  • CHAR FOR BIT DATA, CHARACTER FOR BIT DATA
  • CLOB, CHARACTER LARGE OBJECT, CHAR LARGE OBJECT
  • CURSOR1
  • DATE2
  • DBCLOB
  • DECFLOAT
  • DECIMAL, DEC
  • DOUBLE, DOUBLE PRECISION
  • FLOAT
  • GRAPHIC
  • INTEGER, INT
  • LONG RAW
  • NCHAR, NATIONAL CHARACTER, NATIONAL CHAR
  • NCLOB3
  • NUMBER4
  • NUMERIC, NUM
  • NVARCHAR2, NATIONAL CHARACTER VARYING, NATIONAL CHAR VARYING, NCHAR VARYING
  • PLS_INTEGER
  • RAW
  • REAL
  • SMALLINT
  • SYS_REFCURSOR, REFCURSOR, REF CURSOR1
  • TIME
  • TIMESTAMP
  • VARCHAR, CHARACTER VARYING, CHAR VARYING
  • VARCHAR FOR BIT DATA, CHARACTER VARYING FOR BIT DATA, CHAR VARYING FOR BIT DATA
  • VARCHAR25
  • VARGRAPHIC
  • BINARY_DOUBLE
  • BINARY_FLOAT
  • BINARY_INTEGER
  • CHAR, CHARACTER
  • DATE
  • DECIMAL, DEC
  • DOUBLE PRECISION
  • FLOAT
  • INT, INTEGER
  • LONG RAW
  • NATURAL
  • NUMBER
  • NUMERIC
  • PLS_INTEGER
  • POSITIVE
  • RAW
  • REAL
  • ROWID
  • SIGNTYPE
  • SMALLINT
  • STRING
  • SYS_REFCURSOR, REFCURSOR, REF CURSOR1
  • TIMESTAMP
  • TIMESTAMP WITH LOCAL TIME ZONE6
  • TIMESTAMP WITH TIME ZONE
  • VARCHAR2, CHARACTER VARYING, CHAR VARYING, VARCHAR

These are the data types that ODS 2.2 does not support.

PL/SQL data types that are not supported for DB2PL/SQL data types that are not supported for Oracle
  • BINARY_DOUBLE
  • BINARY_FLOAT
  • BOOLEAN (JCC does not support BOOLEAN for PL/SQL)
  • BFILE
  • INTERVAL
  • INTERVAL YEAR TO MONTH
  • INTERVAL DAY TO SECOND
  • LONG
  • NATURALN
  • POSITIVE
  • POSITIVEN
  • ROW
  • SIGNTYPE
  • SIMPLE_INTEGER
  • STRING
  • TABLE
  • TIMESTAMP WITH LOCAL TIME ZONE
  • TIMESTAMP WITH TIME ZONE
  • VARRAY
  • XMLTYPE
  • BFILE7
  • BLOB7
  • BOOLEAN, PL/SQL BOOLEAN
  • CLOB7
  • INTERVAL
  • INTERVAL YEAR TO MONTH
  • INTERVAL DAY TO SECOND
  • LONG
  • NATURALN8
  • NCHAR,
    NATIONAL CHARACTER,
    NATIONAL CHAR
  • NCLOB7
  • NVARCHAR2,
    NATIONAL CHARACTER VARYING,
    NATIONAL CHAR VARYING, NCHAR VARYING
  • POSITIVEN8
  • ROW
  • SIMPLE_INTEGER8
  • TABLE
  • UROWID
  • VARRAY
  • XMLTYPE7

Notes:

  1. SYS_REFCURSOR and CURSOR are supported only for OUT parameters (except where passed a value from another routine). PL/SQL user-defined functions must have scalar returns; therefore, these data types are not supported for PL/SQL user-defined functions.
  2. When the DB2_COMPATIBILITY_VECTOR registry variable is set for the DATE data type, DATE is equivalent to TIMESTAMP (0).
  3. The NCLOB data type is not supported for use in PL/SQL statements or in PL/SQL contexts when the database is not defined as a Unicode database. In Unicode databases, the NCLOB data type is mapped to a DB2 DBCLOB data type.
  4. NUMBER is supported for SQL on DB2 when the number_compat database configuration parameter is set to ON.
  5. VARCHAR2 is supported for SQL on DB2 when the varchar2_compat database configuration parameter set to ON.
  6. TIMESTAMP WITH LOCAL TIME ZONE is not supported as a UDF return type.
  7. BFILE, SIMPLE_INTEGER, BLOB, CLOB, NCLOB and XMLTYPE are not supported for Oracle because ODS does not have access to the wrapper classes that the Oracle drivers require.
  8. NATURALN, POSITIVEN, and SIMPLE_INTEGER are subtypes of PLS_INTEGER with a NOT NULL constraint. This prevents you from using them as OUT parameters. ODS 2.2 does not list them for creating routine parameters, but you may use them as IN or IN OUT in the editor.

In addition to the limitations above, Data Studio does not support the following Oracle data types:

  • User-defined types
    • Object types
    • REF nested tables
  • Any types
    • ANYTYPE
    • ANYDATA
    • ANYDATASET
  • XML types
    • URI data types
    • URIFactory package
  • Spatial types
    • SDO_GEOMETRY
    • SDO_TOPO_GEOMETRY
    • SDO_GEORASTER
    • SI_Texture
    • SI_FeatureList
  • Media types
    • ORDAudio
    • ORDImage
    • ORDImageSignature
    • ORDVideo
    • ORDDoc
    • SI_StillImage
    • SI_Color
    • SI_AverageColor
    • SI_ColorHistogram
    • SI_PositionalColor

Data type limitations:

  • ODS 2.2 has no support for PL/SQL collection data types (ROW and VARRAY) other than CURSOR. You can declare only scalar return types for PL/SQL functions.
  • When copying data from Oracle to DB2, there are some limitations based on the types of the data. Data of the following Oracle types cannot be copied in this release:
    • TIMESTAMP WITH TIME ZONE
    • TIMESTAMP WITH LOCAL TIME ZONE
    • TIMESTAMP (fractional-seconds-precision)
    • RAW
    • BFILE
    • XML
    • INTERVAL (not supported on the DB2 server)
    • NUMBER (4, -1) [where the scale is negative]
    • NUMBER (5,10) [where the precision is less than the scale]
    • User defined types
    • ROW and ARRAY (composite types)
  • The maximum length of a BLOB or CLOB on Oracle can be 4 GB, but DB2 and ODS 2.2 can handle only up to 2 GB. Longer LOBs would get truncated.
  • When copying data from DB2 to Oracle, there may be some truncation:
    • DB2 DECFLOAT supports a larger range than Oracle binary FLOAT.
    • DB2 TIMESTAMP supports a larger precision (although it cannot be used because the java.sql.Timestamp is limited to a fractional-seconds precision of 9).

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=421939
ArticleTitle=Developing with PL/SQL in IBM Data Studio 2.2 and Optim Development Studio 2.2
publish-date=08202009