Using OrindaBuild as an PL/SQL Developer Plugin

The OrindaBuild Plugin for PL/SQL Developer 7.0.

Topics

Installation
Using OrindaBuild from within PL/SQL Developer
Using The OrindaBuild Wizard
Next Steps
OrindaBuild / PL/SQL Developer Messages

1. Installation

There are two ways to install the OrindaBuild extension for PL/SQL Developer 7.0:

1.1 Use the OrindaBuild install program.

When run the OrindaBuild install program asks you whether you want to install the Pl/SQL Developer Plugin:

Installing the OrindaBuild Addin for PL/SQL Developer 7.0

1.2. Manually install the Plugin.

If you have already installed OrindaBuild you can copy the OrindaBuild Plugin from OrindaBuild's install directory to PL/SQL Developer's plugins directory:

1.3 Post Installation

Once you have installed the Plugin you will need to restart PL/SQL Developer 7.0.

You can verify that the Plugin has been installed by starting PL/SQL Developer and selecting 'Tools/Configure Plug-ins...'. If the plugin is correctly installed you will see a line like this:

Verify your PL/SQL Developer installation

You can check your Plugin version and expiry date by selecting 'Tools/OrindaBuild/About...'. This will bring up the window below:

2. Using OrindaBuild from within PL/SQL Developer

OrindaBuild is coupled with PL/SQL Developer. The core code generation component is a 100% Java application. The Plugin DLL implements an extended version of the OrindaBuild user interface from within PL/SQL Developer. The Plugin allows users to select PL/SQL to generate Java for using a single mouse click. The menu option 'Tools/OrindaBuild/Generate...' then invokes the Java code generation engine using an external DOS window.

To mark an item for Java generation from within PL/SQL Developer Right-click on it and select the 'Create Java With OrindaBuild' option:

PL/SQL Developer Pop Up menu

The following PL/SQL Developer Objects can be marked for Java Generation:

*PL/SQL Developer Object*	*Comment*
Functions	Stand alone oe packaged PL/SQL Functions. If a given function is overloaded all versions of the function will be marked for generation.
Procedures	Stand alone or packaged PL/SQL Procedures. If a given procedure is overloaded all versions of the procedure will be marked for generation.
Packages	All Procedures and Functions inside the package.
Package Bodies	All Procedures and Functions inside the package
Tables	Oracle Database Tables. Generated classes will have Create, Update and Delete methods and finder methods for the Primary key and each Unique key and Index.
Sequences	Oracle Sequence Objects
Synonyms	Whatever underlying object the synonym points to

3. Using The OrindaBuild Wizard

Once you have marked the database objects you wish to generate code for you need to run the OrindaBuild Wizard to generate the code. The Wizard is started with the menu option 'Tools/OrindaBuild/Run OrindaBuild Wizard...'. The Wizard consists of four tabbed pages and some buttons.

3.1 The 'Code Options' Page

Purpose of this page

This page is used to add control information such as comments and debug messages to the generated code.

Name and Version fields

Enter your name and the version number of your code. This information appears as header comments in the generated code.

Comments

This field can be used in one of two ways:

Enter comments for your code. These comments appear at the beginning of your generated code.
Enter the path to a file in include text as a comment in your code. Use this approach for copyright and other standard notices.

Code Options

Choose from the following options

Section Option Purpose

Messages in generated code Debug Messages in code Adding debug messages to the generated code

Other Messages in Code Adding other messages to the generated code

Comments in generated code Comments in Code Adding detailed comments to the generated code.

Maintain usage statistics Maintain Usage Statistics Generating OrindaBuild performance metrics. Each generated class implements the StatsInterface Java Interface.

Naming convention for
generated files

Java Naming Convention

Choosing the naming conventions for generated Java files:

JavaStandard.java	The filename starts with an upper case letter and then uses upper case letters where the previous character in the matching database object was an underscore.
InitialCapitalLetters.java	The filename is lower case except where the previous character was an underscore. This means that generated classes names will normally start with a lower case letter.
spaces_between_words.java	The filename is lower case. Underscores are left intact. This option is deprecated.

Target version of Oracle Oracle Version Generating code optimized for one of the following versions of Oracle:

8.1.5, 8.1.6, 8.1.7, 9.0.1, 9.2.0, 10.1.0, 10.2.0

Use the following numeric
data types

A checkbox for each of the following Java data types:

byte
short
int
long
float
double
java.lang.Byte
java.lang.short
java.lang.Integer
java.lang.Long
java.lang.Float
java.lang.Double

Whenever OrindaBuild is writing a method for passing numbers to or from a procedure, SQL statement or table its default behaviour is to generate a seperate method for each of the data types on the left. By unticking the appropriate checkboxes it is possible to prevent OrindaBuild from generating code with datatypes such as short, which are rarely used in practice. This feature was added in build 4.0.1709

Table Specific Options Check columns before insert or update If checked generated table access code will make sure that all non-null fields have values and that numeric fields do not have decimal places if they aren't supposed to. This feature was added in build 4.0.1709

Create TYPE definitions for tables in a file called 'extraObjects.sql' If checked a SQL file called 'extraObjects.sql' will be created in the 'tables' subdirectory of the generated code. This file contains matching oracle TYPE definitions for the tables selected by the user. For each table it will create a record type and an array type.

This code is provided to assist developers who need to pass arrays into PL/SQL procedures. Because of limitations in JDBC the only kind of array of records that can be passed from Java to a PL/SQL procedure is one that uses ORACLE TYPEs.

For further information on this subject see the manual entry for Working with PL/SQL Arrays that can not be directly accessed by JDBC. This feature was added in build 4.0.1935.

3.2 The 'Service Options' Page

Purpose

The Service Options screen allows you to

Pick a directory for generated code
Choose a package for generated code
Define how your Data Access Object Factory class will behave
Define how the Web Service classes behave.

Java code root directory

Enter the path to the directory on your computer for the generated code.

Package name

Enter the package name for the generated code. In the previous illustration, OrindaBuild generates code in the following directory:

C:/Test3/com/yourcompany/yourproduct

DAO Factory Class options

In addition to creating classes for each procedure and table in your database OrindaBuild can also create a single Data Access Object Factory class that handles logging, database connections and the creation of access class instances.

This step has the following options:

Option	Purpose
DAO Factory Class Name	The name of the class. This can be any valid Java class name.If this field is blank no field will be created.
Log messages using...	There are five ways of logging messages OrindaBuild's TextLog - Messages are written to text files. OrindaBuild's ConsoleLog - Messages are written to System.out and System.err Java 1.4 logging - Messages are written using java.util.logging. Log4J - Messages are written using Log4J User Implemented LogInterface - Messages are written using a user created class that implements the com.orindasoft.pub.LogInterface Interface.
Log name/Directory	If messages are logged using TextLog this will be the name of the log file directory. If Java 1.4 logging or Log4J are in use this will be the logging context.
Get DB Connection using...	There are four methods of obtaining a Connection: Hard coded connect string: A connect string used by the thin driver is entered in the field below. JNDI used to get DataSource: JNDI is used to retrieve a DataSource object. the JNDI resource name is entered in the field below. DataSource provided at runtime: The generated code will be passed a DataSource at runtime. oracle.jbo.server.DBTransaction: The connection will be extracted from an instance of this class which is passed in at runtime.
Connection Name	An Oracle thin driver connect string or the name of the DataSource in JNDI
Implement javax.ejb.SessionBean	If selected the generated class will implement the SessionBean interface.
Add 'finalize()' method	If selected the generated class will have a finalize method that releases database resources.
Close Connections	Close connections when releaseConnection() is called
Commit Connections	Commit connections when releaseConnection() is called
Temporary Directory	Location for temporary files when working with LOBS. Can be a real operating system directory or a System property such as 'user.dir'.
Temporary File Prefix/Suffix	Prefix and suffix used by temporary files.

Full information on how to use the DAO Factory class is available in the DAO Factory Manual section.

Web Service Class options

The 'Create Web Services' checkbox controls whether Web Services classes are created or not. The other options are:

Option	Purpose
Interface Class Name	The name of the interface class that contains web service methods
Implementing Class Name	The name of the class that implements the Web Service
Number Type used by service	By default generated code will use java.math.BigDecimal to represent numbers, as this is the closest match in Java to Oracle's number datatype. For convenience OrindaBuild can also use the following data types to represent numbers in Web Services code: int long float double
Always release database connection after call.	If selected releaseConnection() will be called after each service method call.
Add pre call code stubs	An empty method is created that is called by each service method just before execution. By extending the implementing class and overiding this method users can add custom functionality without modifiying generated code.
Add post call code stubs	An empty method is created that is called by each service method after execution. By extending the implementing class and overiding this method users can add custom functionality without modifiying generated code.
Web Service Record Type	Generated web service records can either be: public, no access methods - Records have public variables and no 'set' and 'get' methods private, 'set' and 'get' methods - Records have protected variables and you must use 'set' and 'get' methods.
Uploaded BFILE naming is abstract	OrindaBuild will write code that under some circumstances allows the creation of BFILEs by web services. This option gives the user control over how BFILEs are named. For more information see Creation of BFILEs using a Web Service

Full information on how to use the Web Services classes is available in the Web Service Manual section.

3.3 The 'Configure' Page

The fields in this screen are non-changable.

Field	Description
Configuration File Name	The system generated OrindaBuild configration file name
TCP/IP Hostname	The TCP/IP Hostname or IP address to be used by the Java code generator.
Oracle SID	The Oracle instance identifier
SQL*Net Port	The Port Number used by the listener
Oracle User	Oracle Username
Oracle Password	The password used by the code generator. Note that the password is stored in clear in the configuration file.
Connection Details	The TNSNAMES.ORA entry currently in use.

3.4 The 'Sql Statements' Page

Purpose

The Sql Statements screen allows the user to select a directory of SQL statements to generate Java code for and to give names and data types to parameters.

Enter the directory where you keep your SQL statement files in and click on the 'Refresh' button. Use Treeview to select the SQL statements you want to use.

The Tree structure on the left shows the parent directory and its SQL files. Clicking on a file brings it up in the file parameters window.

File Parameters Window.

This window consists of a checkbox, a table of Parameters and a listing of the SQL Statement.

The checkbox is used to control whether matching Java code is generated or not.

Parameter Table

The parameter table contains the following fields:

Field	Description
Line #	The number of the line in the SQL file that the parameter was found on.
Parameter Name	The Name that has been given to this parameter. This must be a valid Java identifier. When first selected the name will default to 'ParamX' where 'X' is the number of the parameter.If the same parameter name is reused it will be assumed that there is one parameter that is used in multiple locations. If This field is greyed out it means that the Parameter name has been set by a Hint in the SQL Statement.
Parameter Data Type	In addition to naming parameters you must also specify a data type. The list allows you to select from the following: STRING - Text Data types including VARCHAR and VARCHAR2 NUMBER - All numeric Oracle Data Types DATE - Date (to within 1 second) LONG - Oracle LONG columns CLOB - Oracle CLOB columns BLOB - Oracle BLOB columns BFILE - Oracle BFILE columns RAW - Oracle RAW columns LONG RAW - Oracle LONG RAW columns TIMESTAMP - Oracle TIMESTAMP columns If this field is greyed out it means that the Parameter Data type has been set by a Hint in the Sql Statement.

SQL Statement listing table

This lists the SQL statement and shows which parameter is on which line.

Support for SQL*Plus and JDBC Parameters

OrindaBuild supports SQL files that contain parameters in JDBC syntax ("empno = ?") or Sql*Plus syntax ("empno = &empno"). If processing a SQL*Plus parameter quotes on either side of it will be ignored and the statement will be presented to the user in JDBC syntax.

Hints in SQL statements

If the parameter is followed by a comment consisting of a name and a data type then they will be used by OrindaBuild. For example:

where
e.empno = ? /* empNumber NUMBER */

will lead to the parameter being called 'empNumber' and the data type being set to 'NUMBER'. If a comment is used then the Name and Data Type fields will be greyed out. Hints work slightly differently for SQL files that have been edited in SQL*Plus:

where
e.empno = &empNumber /* NUMBER *

Requirements for SQL files

OrindaBuild makes the following assumptions about the SQL files it uses:

Only one statement per file
Comments should be clearly marked with "/* ... */"
The file should end in .SQL
The SQL statement is a valid statement.

3.5 The 'Generate' Button

Java code can be generated in four ways:

By clicking the 'Generate' button in the OrindaBuild Wizard
By Selecting 'Tools/OrindaBuild/Generate Java Source...' from the Tools Menu
By manually opening the configuration file with OrindaBuild
As part of a build process using a tool such as make or ant

If you generate your Java from within PL/SQL Developer the OrindaBuild code generation engine, which is written in Java, will be invoked in a DOS window.

4. Next Steps

The OrindaBuild Manual is the core reference document and will help you get the most out of the product. The chapters relevent to PL/SQL Developer users are:

Using the DAO Factory Class: Using the DAO Factory Class - Advantages of the DAO Factory Class - Log File Management - Database Connection Management - Java Session Bean Support - Working with the DAO Factory class - Creating an instance of the class - Obtaining and using a database access class - Releasing and reaquiring resouces - Extending the DAO Factory class - DAO Factory class methods

Using the Web Service Classes:Purpose of generated web service classes - Description of generated web service classes - Altering Web Service Class Behaviour - DAOFactoryServiceImpl class Methods - Web Services with CLOBS, BLOBS and BFILES

Running OrindaBuild: Logging in - Selecting Database Objects and SQL files - Code Options - File Options - Generating Code - Note: This section refers to the Java Swing GUI used by the core OrindaBuild product.

Using Generated Code: required imports - implementing LogInterface - Create Instances of Generated Classes - set methods - execute methods - get methods - sequences - cursors - other generated methods - table persistance methods - table navigation methods

Command Line OrindaBuild: Using the command line version of OrindaBuild 5.0 to generate Java source as part of a build process

Orindasoft's com.orindasoft.pub Library: com.orindasoft.pub Java Class Libraries - JDBC Driver - Cursors - QueryStatement class - ReadOnlyRowSet class - StatsInterface - LogInterface - OracleResourceUser

PL/SQL Records: Using OrindaBuild to call stored procedures that have %ROWTYPE, %TYPE, PL/SQL Package records, INDEX BY tables, VARRAYS and Object TYPE's as parameters

LOB's: Generating JDBC access code for PL/SQL procedures and functions that have CLOB, BLOB or BFILE parameters - transparent support for inserting CLOB and BLOB columns in tables

Configuration File: what it is - what you can safely edit by hand

Limitations: Known Limitations - Composite Objects REF Objects - VARRAYS - Oracle 8.1.6 Limitations - Oracle 8.1.7 Limitations - Supported PL/SQL Datatypes

Code Sample: demoClass.java - create an instance of a generated class - set parameters - call execute method - get cursor - get sequence value

5. OrindaBuild / PL/SQL Developer Messages

While using the OrindaBuild Plugin for PL/SQL Developer 7.0 you may encounter the following messages:

*Normal Messages*	*Meaning*
'How To Generate Code' Message:	Each distinct combination of Database and User will have its own configuration file. This file is created the first time you log in to a given database as a given user
'Creating Configuration File' Message:	This message provides minimal usage information and is displayed the first time a user right clicks on a database object.
'Name Not Editable' Message:	This occurs in the SQL Statements screen when an attempt is made to change the name of a parameter that is set by the hint text in the file
'Type Not Editable' Message:	This occurs in the SQL Statements screen when an attempt is made to change the data type of a parameter that is set by the hint text in the file
*Error Messages*	*Meaning*
'Unable to read file filename' Message	This message indicates that the Plugin encountered an unanticipated I/O error while reading or writing an I/O file.
'Unable to generate Java: reason' Message	This message indicates that the Plugin could not generate Java when requested because it encountered a configuration error (reason) when starting.
'File filename does not exist' Message	This occurs when the OrindaBuild code generation engine can't find the configuration file create by the PL/SQL Developer Plugin.
'Unable to generate PL/SQL Hints: reason' Message	This occurs when the wizard fails to invoke the code generation engine in the background before displaying the 'SQL Statements' tab of the Wizard.
'Unable to open Help Page: reason' Message	This occurs if an attempt is made to access the 'Help' page after the Plugin failed to start properly.
'SQL Statement not usable: ' SQL Statement Text	This occurs in the 'SQL Statements' tab of the Wizard if an attempt is made to use a file that does not contain a valid SQL statement.

Legal Terms

Site Map

About Us