百度wwWtekuu杀毒,点回车了tekuucom怎么出现_百度知道
百度wwWtekuu杀毒,点回车了tekuucom怎么出现
我有更好的答案
腾讯电脑管家已建立起全球最大的恶意网址数据库，并通过云举报平台实时更新，因此能够实现QQ平台中更精准的网址安全检测，在防网络诈骗、反钓鱼等领域，已处于全球领先水平遇到挂马网站了么试试腾讯电脑管家保护上网环境，依托腾讯庞大的产品生态链和用户基础
采纳率：93%
来自团队：
为您推荐：
其他类似问题
换一换
回答问题，赢新手礼包
个人、企业类
违法有害信息,请在下方选择后提交
色情、暴力
我们会通过消息、邮箱等方式尽快将举报结果通知您。Developing with PL/SQL in IBM Data Studio 2.2 and Optim Development Studio 2.2
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
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
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 exceptionsA body that includes variable and cursor declarations, the bodies of its routines,
and an optional initialization sectionThe specification is a public decla 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 CREATE and ALTER statements in a SQL script in your project and then run the script.Summary of PL/SQL functionalityOptim 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 FunctionalityData Source Explorer—DB2 LUW 9.7Show 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 11gShow 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.7Create 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 11gCreate 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 viewView 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 editorEdit 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 editorEdit 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 editorEdit 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.7To 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 cdbnameFor 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
Varchar2 compatibility
Date compatibility
DB2 DISCONNECT cdbnameTo 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.7Select 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 ExplorerNotice 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 connectConnecting 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.
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 driverOracle 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
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 locationThe ojdbc6.jar file listed by default is a place-
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 confirmationClick 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 driverFollow 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).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.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.
Name/Type:Oracle 10Oracle 11Driver Type FilterDatabaseDatabaseVendor FilterOracleOracleAvailable driver templatesOther Driver / Oracle / 10Other Driver / Oracle / 11Driver nameDataDirect Connect for Oracle 10DataDirect Connect for Oracle 11Driver typeOther DriverOther DriverJar 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 11CatalogALLALLConnection URLjdbc:datadirect:oracle://habu.svl
.ibm.com:1521;SID=ora10gjdbc:datadirect:oracle://chex.svl
.ibm.com:1521;SID=ORA11Database NameoracleoracleDriver Classcom.ddtek.jdbc.oracle.OracleDrivercom.ddtek.jdbc.oracle.OracleDriverPassword (optional)User ID (optional)CRIOLLO1CRIOLLO1Note: 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 viewsWhen 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:AllThis view describes all objects that are accessible to you.DBAThis view describes all objects in the database.UserThis view describes objects that you own.Browsing your database and objectsAfter 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 schemaExpand 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 schemaYou 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 procedureFrom 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 procedureThe Source tab for the procedure shows its CREATE statement, as shown in Figure 12.Figure 12. Browse the source of a stored procedureAs 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 OracleThe 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 projectA 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
pop-up menu.
Select New -& Data Development Project, as shown in Figure 13.
Figure 13. Launch a wizard to create a new data development projectThis 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 projectFrom 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 projectClick 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 projectEach 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 OracleFollow 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 OracleFor 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 OracleUse
Add... and the Parameters dialog to add parameters of any supported data
type.For a list of supported data types, see .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 procedureClick 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 VAR01 to an output parameter
and to assign a value to it, as follows:
CREATE OR REPLACE PROCEDURE PROCEDURE1 ( VAR01 OUT INT )
VAR01 := 2020;
END;Creating a PL/SQL function for OracleFollow these steps to use the New User-Defined Function wizard to create a PL/SQL
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 OracleFor 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 functionFrom 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 OracleFor 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 OracleUse
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 functionODS 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
RETURN VAR01;
END;Creating a PL/SQL package for OracleFollow these steps to use the New PL/SQL Package wizard to create a PL/SQL
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 OracleThis 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, 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
/* PL/SQL package specification */
PROCEDURE ExampleP
FUNCTION ExampleFunction RETURN INT;
END PACKAGE1;The PL/SQL package body implements the procedure and function:CREATE OR REPLACE PACKAGE BODY PACKAGE1
/* PL/SQL package body */
PROCEDURE ExampleProcedure
X INT := 1;
X := X + 1;
END ExampleP
FUNCTION ExampleFunction
RETURN INT
RETURN (X + 1);
END ExampleF
END PACKAGE1;Moving a table from DB2 to OracleThis 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 tableClick 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 tableClick 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 SQL500 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 NONE
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 SQL500 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 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.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_LEVELSClick 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 OracleWhen 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 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 INVENTORY
and replace the existing samples
with the signature for a QUERY_CLOSING_INVENTORY function, as follows:
CREATE OR REPLACE PACKAGE INVENTORY
FUNCTION QUERY_CLOSING_INVENTORY(
RETURN NUMBER;
END INVENTORY;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
FUNCTION QUERY_CLOSING_INVENTORY(
RETURN NUMBER
X NUMBER(38,0);
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;
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 routineAfter 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 procedureYou 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_TIMESTAMPDeploying a PL/SQL package to your Oracle serverUnlike 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 packageWhen 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 deployThe 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 packagesYou 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
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 packagesThis 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 viewRunning a PL/SQL package on OracleYou 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 packageClick Run.
The Specify Parameter Values dialog appears.
As shown in Figure 40, enter the following parameter values:
Y = 2007M = 1W = 7P = 2110Figure 40. Enter values for input parameters for running the QUERY_CLOSING_INVENTORY functionClick 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 functionYou 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 OracleODS 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 = 2007M = 1W = 7P = 2110
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 perspectiveClick Yes.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)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 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 perspectiveIf 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 debuggingTip: 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.7To 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 .
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 DB2The 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_LEVELSAfter 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 projectAfter 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_LEVELS table on DB2,
in the Specification tab of the editor:
CREATE OR REPLACE PACKAGE INVENTORY
FUNCTION QUERY_CLOSING_INVENTORY(
Y SMALLINT,
M SMALLINT,
W INTEGER,
RETURN INTEGER;
END INVENTORY;Make corresponding changes in the Body tab:
CREATE OR REPLACE PACKAGE BODY INVENTORY
FUNCTION QUERY_CLOSING_INVENTORY(
Y SMALLINT,
M SMALLINT,
W INTEGER,
RETURN INTEGER
X INTEGER;
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;
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 CHAR is 2000
For DB2, the default length for CHAR is 1.
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 DB2Deploying, 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 DB2The 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
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_INTEGER is stored on a DB2 database as INTEGER.
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 valuesThe 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 valuesYou 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 parameterFor 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 parameterParameter 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 detailsUsing CURSOR types in PL/SQLA 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 )
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 flowThe 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 WHENIn 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
section.Also, you may declare custom exceptions and raise them programmatically:exception-name EXCEPTION;
IF condition THEN RAISE exception-Naming conventionsFor 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 FormatmyNameMYNAMEMYNAMEMYNAME"myName"myName"my.name"my.nameIn 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();LimitationsNot all PL/SQL functionality is supported by ODS 2.2:
See the table of data types in
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 ExpressDB2 Express-CDB2 Personal EditionDB2 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
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 incl 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 t 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 typesThe following table shows the data types that ODS 2.2 supports for DB2 LUW 9.7 and Oracle 10g
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)BIGINTBINARY_INTEGERBLOBCHAR, CHARACTERCHAR FOR BIT DATA, CHARACTER FOR BIT DATACLOB, CHARACTER LARGE OBJECT,
CHAR LARGE OBJECTCURSOR1DATE2DBCLOBDECFLOATDECIMAL, DECDOUBLE, DOUBLE PRECISIONFLOATGRAPHICINTEGER, INTLONG RAWNCHAR, NATIONAL CHARACTER, NATIONAL CHARNCLOB3NUMBER4NUMERIC, NUMNVARCHAR2, NATIONAL CHARACTER VARYING,
NATIONAL CHAR VARYING, NCHAR VARYINGPLS_INTEGERRAWREALSMALLINTSYS_REFCURSOR, REFCURSOR,
REF CURSOR1TIMETIMESTAMPVARCHAR, CHARACTER VARYING, CHAR VARYINGVARCHAR FOR BIT DATA, CHARACTER VARYING FOR BIT DATA,
CHAR VARYING FOR BIT DATAVARCHAR25VARGRAPHICBINARY_DOUBLEBINARY_FLOATBINARY_INTEGERCHAR, CHARACTERDATEDECIMAL, DECDOUBLE PRECISIONFLOATINT, INTEGERLONG RAWNATURALNUMBERNUMERICPLS_INTEGERPOSITIVERAWREALROWIDSIGNTYPESMALLINTSTRINGSYS_REFCURSOR, REFCURSOR,
REF CURSOR1TIMESTAMPTIMESTAMP WITH LOCAL TIME ZONE6TIMESTAMP WITH TIME ZONEVARCHAR2, CHARACTER VARYING,
CHAR VARYING, VARCHARThese 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 OracleBINARY_DOUBLEBINARY_FLOATBOOLEAN (JCC does not support BOOLEAN for PL/SQL)BFILEINTERVALINTERVAL YEAR TO MONTHINTERVAL DAY TO SECONDLONGNATURALNPOSITIVEPOSITIVENROWSIGNTYPESIMPLE_INTEGERSTRINGTABLETIMESTAMP WITH LOCAL TIME ZONETIMESTAMP WITH TIME ZONEVARRAYXMLTYPEBFILE7BLOB7BOOLEAN, PL/SQL BOOLEANCLOB7INTERVALINTERVAL YEAR TO MONTHINTERVAL DAY TO SECONDLONGNATURALN8NCHAR,NATIONAL
CHARACTER,NATIONAL CHARNCLOB7NVARCHAR2,NATIONAL CHARACTER VARYING,NATIONAL CHAR VARYING, NCHAR VARYINGPOSITIVEN8ROWSIMPLE_INTEGER8TABLEUROWIDVARRAYXMLTYPE7Notes:SYS_REFCURSOR and CURSOR are
supported only for OUT parameters (except where passed a value from another routine).
PL/SQL user-defined functions must therefore, these data types are not supported for
PL/SQL user-defined functions.When the DB2_COMPATIBILITY_VECTOR registry variable is set
for the DATE data type, DATE is equivalent
to TIMESTAMP (0).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.NUMBER is supported for SQL on DB2 when the number_compat database
configuration parameter is set to ON.VARCHAR2 is supported for SQL on DB2 when the varchar2_compat
database configuration parameter set to ON.TIMESTAMP WITH LOCAL TIME ZONE is not supported as a UDF return type.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.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 typesREF nested tablesAny types
ANYTYPEANYDATAANYDATASETXML types
URI data typesURIFactory packageSpatial types
SDO_GEOMETRYSDO_TOPO_GEOMETRYSDO_GEORASTERSI_TextureSI_FeatureListMedia types
ORDAudioORDImageORDImageSignatureORDVideoORDDocSI_StillImageSI_ColorSI_AverageColorSI_ColorHistogramSI_PositionalColorData 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 ZONETIMESTAMP WITH LOCAL TIME ZONETIMESTAMP (fractional-seconds-precision)RAWBFILEXMLINTERVAL (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 typesROW 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).
Downloadable resourcesRelated topics
"" includes information about pureQuery support for Oracle and other features.
"" provides a good introduction to the Oracle compatibility features in DB2 9.7.
product page.
Download .
Download .
to add and subscribe to comments.
Subscribe me to comment notifications
static.content.url=http://www.ibm.com/developerworks/js/artrating/SITE_ID=1Zone=Information ManagementArticleID=421939ArticleTitle=Developing with PL/SQL in IBM Data Studio 2.2 and Optim Development Studio 2.}