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.
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
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 Type||New Functionality|
|Data Source Explorer—DB2 LUW 9.7|
|Data Source Explorer—Oracle 10g or 11g|
|Data Project Explorer—DB2 LUW 9.7|
|Data Project Explorer—Oracle 10g or 11g|
|SQL Results view|
|PL/SQL package editor|
|PL/SQL procedure editor|
|PL/SQL function editor|
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
DB2 CONNECT TO cdbname DB2 GET DB CFG | findstr compatibility Number compatibility = ON Varchar2 compatibility = ON Date compatibility = ON DB2 DISCONNECT cdbname
To create a database connection profile for the GSDB sample database that is included with DB2 LUW 9.7, do the following:
- From the Data Source Explorer,
click the New Connection Profile toolbar button:
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
Select DB2 for Linux, UNIX, and Windows for your database manager.
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.
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
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
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:
- Obtain the ojdbc6.jar file and copy it to your hard drive in a directory such as C:\jdbcDrivers\Oracle.
- In the Data Source Explorer, right-click on the Database Connections folder and select New... from the pop-up menu.
- The New Connection wizard appears, as shown in Figure 4.
Figure 4. Choose an Oracle thin JDBC driver
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.
- 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 ojdbc6.jar file listed by default is a place-holder; you need to specify its full path (for example, C:\jdbcDrivers\Oracle\ojdbc6.jar).
- Select the place-holder and click Edit JAR/Zip.... A file section dialog appears.
- Navigate to your copy of ojdbc6.jar, select it, and click Open. The path appears in the Edit Jar List dialog.
- Click OK.
- 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
- Click OK.
- 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
Connecting to Oracle using the DataDirect Connect JDBC driver
Follow these steps to connect to Oracle using the DataDirect Connect JDBC driver:
- Obtain the full DataDirect Connect JDBC driver or the Oracle-only DataDirect Connect 4.0 JDBC driver from DataDirect.
- If you have the full DataDirect Connect JDBC 3.7 driver:
- 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.
- 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).
- Unzip connectjdbc.jar into a new directory named installdd. This gives you a 15-day license.
- In ODS, open Window -> Preferences, and select Data Management -> Connectivity -> Driver Definitions.
- Click Add...
- 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.
Oracle 10 Oracle 11 Driver Type Filter Database Database Vendor Filter Oracle Oracle Available driver templates Other Driver / Oracle / 10 Other Driver / Oracle / 11 Driver name DataDirect Connect for Oracle 10 DataDirect Connect for Oracle 11 Driver type Other Driver Other Driver
Jar List Driver Files:
Full DataDirect Connect 3.7 Driver Oracle-Only (4.0) Driver C:\jdbcDrivers\Oracle\datadirect\lib\base.jar
Oracle 10 Oracle 11 Catalog ALL ALL Connection URL jdbc:datadirect:oracle://habu.svl .ibm.com:1521;SID=ora10g jdbc:datadirect:oracle://chex.svl .ibm.com:1521;SID=ORA11 Database Name oracle oracle Driver Class com.ddtek.jdbc.oracle.OracleDriver com.ddtek.jdbc.oracle.OracleDriver Password (optional) User ID (optional) CRIOLLO1 CRIOLLO1 Note: You can put the SID either on the connection URL, as above, or add it as an additional property on the Optional tab.
- 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:
- This view describes all objects that are accessible to you.
- This view describes all objects in the database.
- 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
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
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
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 Source tab for the procedure shows its
CREATE statement, as shown in Figure 12.
Figure 12. Browse the source of a stored procedure
As you can see in the above
CREATE statement, the
TEST_NUMERIC procedure receives a
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:
- Right-click on an empty part of the Data Project Explorer background to open the
Select New -> Data Development Project, as shown in Figure 13.
Figure 13. Launch a wizard 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.
- 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
- 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
- Click Finish. The project now appears in the Data Project Explorer.
Expand the project. Figure 16 shows an example of what an Oracle project looks like.
Figure 16. Expand the folders of your Oracle project
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:
- 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
For Oracle, PL/SQL is the only supported language, so the Language field is disabled.
- 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.
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.
Click Next > to show the Parameters page,
as shown in Figure 18.
Figure 18. Add parameters for a PL/SQL procedure for Oracle
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.
- When you are finished adding parameters, click Next > to go to the Summary page.
- 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
- Click Finish to generate the procedure, add it to your project, and open it in the routine editor.
- 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
VAR01to 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:
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
For Oracle, PL/SQL is the only supported language, so the Language field is disabled.
- 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
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.
- Click Next > to show the Return Data Type page,
as shown in Figure 22.
Figure 22. Specify the function return type for Oracle
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.
- Click Next > to show the Parameters page,
as shown in Figure 23.
Figure 23. parameters for a PL/SQL function for Oracle
Use Add... and the Parameters dialog to add parameters of any supported data type.
- When you are finished adding parameters, click Next > to go to the Summary page.
- 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
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.
Click Finish to generate the function, add it to your project, and open it in the routine editor.
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:
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
This wizard has only one page. Specify the target platform and the name of the PL/SQL package.
- Click Finish to generate the PL/SQL package, add it to your project, and open it in the PL/SQL package editor.
Examine the new PL/SQL package in the editor. It contains two routines,
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.
- Connect to your GSDB database for DB2 and expand the tables in its GOSALES schema to find the INVENTORY_LEVELS table.
- 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
- Right-click on the INVENTORY_LEVELS table. From the pop-up menu, select Generate DDL.... The Generate DDL wizard appears.
- 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
Click Next >. The Objects page appears.
- 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
- Click Next >. The Save and Run DDL page appears.
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)!
- Click Browse... to show the Folder Selection dialog.
- Select Oracle Development and click OK.
- 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.
- Click Next > to show the Summary page.
- Click Finish. The inventory_levels script appears in the SQL Scripts folder of your Oracle Development project.
- 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 NONEclause. 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)!
- 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
CREATEstatement 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.
- 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.
- 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
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.
- In your Oracle connection, right-click on the INVENTORY_LEVELS table. From the pop-up menu, select Data -> Load.... The Load Data wizard appears.
- 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.
- 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.
- 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.
- 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.
- Go to the Specification tab and
change the name of the package to
INVENTORYand replace the existing samples with the signature for a
QUERY_CLOSING_INVENTORYfunction, as follows:
CREATE OR REPLACE PACKAGE INVENTORY AS FUNCTION QUERY_CLOSING_INVENTORY( Y NUMBER, M NUMBER, W NUMBER, P NUMBER ) RETURN NUMBER; END INVENTORY;
- Go to the Body tab and change the name of the package to
INVENTORYand replace the existing samples with the implementation for the
QUERY_CLOSING_INVENTORYfunction, 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;
- 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
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
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
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
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
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
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
For DB2 LUW 9.7, you may add
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
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
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:
- 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
- Click Run. The Specify Parameter Values dialog appears.
- As shown in Figure 40, enter the following parameter values:
- Y =
- M =
- W =
- P =
Figure 40. Enter values for input parameters for running the QUERY_CLOSING_INVENTORY function
- Y =
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
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:
- 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.
- 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
- As you did when running the routine, enter the following parameter values:
- Y =
- M =
- W =
- P =
- Y =
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
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:
Icon Button name (shortcut) Function Resume (F8) Runs your routine to the next breakpoint or to completion. Terminate (Ctrl+F2) Terminates your routine and the debugging session without returning results. Step into (F5) Lets you debug the function referenced or the procedure called on the current statement. Step over (F6) Steps over the function referenced or procedure called on the current statement. Step 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.
- The stack trace appears in the Debug view. The toolbar for this view contains the following useful buttons:
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
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
Tip: When you edit an Oracle PL/SQL package or routine that you intend to debug,
CREATE and object keyword on the same line.
The Oracle server removes line breaks until after the object keyword (
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 DATAfor a parameter for DB2. Where DB2 supports synonyms for the data type, such as
INT, the wizard shows the preferred DB2 synonym (
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
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:
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:
- From the Data Source Explorer, expand your Oracle connection and find the PL/SQL package named INVENTORY.
- Adjust your workbench so that the Data Source Explorer and the Data Project Explorer are both visible.
- 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.)
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
- 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.
- 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_LEVELStable 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;
- 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;
- 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
CHARis 2000 For DB2, the default length for
- 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:
- 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
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.
- 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.
Click Finish. The selected objects are deployed sequentially in a background thread. Results are reported in the SQL Results view.
- 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.
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_INTEGERis stored on a DB2 database as
- You can also run a routine in a PL/SQL package in the Data Source Explorer or in the Data Project Explorer.
- 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
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
To pass NULL for a character-string value (to distinguish NULL from an empty string),
click Set to NULL. The pseudo value
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
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
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.
INT is stored on an Oracle database
NUMBER, so that the run dialog shows
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
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;
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 Format||Catalog Format|
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:
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
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:
- 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
TYPEdeclaration is not supported in a function, procedure, trigger, or anonymous block.
- For DB2, the
FOR EACH STATEMENToption 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
CREATE TRIGGERstatement 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
BIGFILEtable spaces; however, you can use the Open with SQL editor menu action to edit the generated DDL and add the key word
- 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)|
These are the data types that ODS 2.2 does not support.
|PL/SQL data types that are not supported for DB2||PL/SQL data types that are not supported for Oracle|
CURSORare supported only for
OUTparameters (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.
- When the
DB2_COMPATIBILITY_VECTORregistry variable is set for the
DATEis equivalent to
NCLOBdata 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
NCLOBdata type is mapped to a DB2
NUMBERis supported for SQL on DB2 when the number_compat database configuration parameter is set to
VARCHAR2is supported for SQL on DB2 when the
varchar2_compatdatabase configuration parameter set to
TIMESTAMP WITH LOCAL TIME ZONEis not supported as a UDF return type.
XMLTYPEare not supported for Oracle because ODS does not have access to the wrapper classes that the Oracle drivers require.
SIMPLE_INTEGERare subtypes of
NOT NULLconstraint. This prevents you from using them as
OUTparameters. ODS 2.2 does not list them for creating routine parameters, but you may use them as
IN OUTin 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
- XML types
- URI data types
- URIFactory package
- Spatial types
- Media types
Data type limitations:
- ODS 2.2 has no support for PL/SQL collection data types (
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
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
- The maximum length of a
CLOBon 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:
DECFLOATsupports a larger range than Oracle binary
TIMESTAMPsupports a larger precision (although it cannot be used because the
java.sql.Timestampis limited to a fractional-seconds precision of 9).
- Integrated Data Management Information Center
- " What's new and cool in Optim Development Studio 2.2; " includes information about pureQuery support for Oracle and other features.
- "Run Oracle applications on DB2 9.7 for Linux, UNIX and Windows" provides a good introduction to the Oracle compatibility features in DB2 9.7.
- Integrated Data Management solutions page .
- Optim Development Studio product page.
- Oracle Database PL/SQL Language Reference, 11g Release 1
- Oracle Database PL/SQL Packages and Types Reference, 11g Release 1
- Oracle Database SQL Language Reference, 11g Release 1
- Oracle Database Advanced Application Developer's Guide
- Oracle Database JDBC Developer's Guide and Reference, 11g Release 1
- Oracle Database 2 Day Developer's Guide, 11g Release 1 (11.1)
- Eclipse Platform
- Eclipse Java Development Tools
- Eclipse Debug Project
- Eclipse Data Tools Platform
- Browse the technology bookstore for books on these and other technical topics.
Get products and technologies
- Oracle JDBC Drivers download page
- Download the GSDB sample database for DB2.
- Download Optim Development Studio 2.2.
- Participate in the discussion forum.
- Check out the Integrated Data Management experts blog and get involved in the developerWorks IDM community space.
Dig deeper into Information management on developerWorks
Get samples, articles, product docs, and community resources to help build, deploy, and manage your cloud apps.
Experiment with new directions in software development.
Software development in the cloud. Register today to create a project.
Evaluate IBM software and solutions, and transform challenges into opportunities.