Table

Purpose

Example Value

Size (rows)

AIRLINES

Passenger and Cargo Airlines

United

386

AIRPORTS

Airfields used by Airlines

JFK - New York

986

AIRCRAFT

A Make and Model of aircraft

DC-9

176

FLIGHTS

A Service from a Departure AIRPORT to an Arrival AIRPORT by An AIRLINE using an AIRCRAFT

United 123

JFK --> LAX

8837

CREATE sequence sequential_number_generator;

CREATE TYPE city_pair_oracle_type AS OBJECT

(FROM_CITY VARCHAR2(16)

,TO_CITY VARCHAR2(16));

(CUSTOMER_NAME   VARCHAR2(12) 

,AIRLINE_NAME    VARCHAR2(50) 

,FLIGHT_NUMBER   NUMBER 

,DEPARTURE_TIME  DATE 

,SEAT            VARCHAR2(3));

CREATE TYPE bookings_table AS TABLE OF bookings_type;

CREATE TYPE flights_type AS OBJECT

(DEPARTURE_CITY  VARCHAR2(16) 

,ARRIVAL_CITY    VARCHAR2(16) 

,AIRLINE_NAME    VARCHAR2(50) 

,FLIGHT_NUMBER   NUMBER 

,DEPARTURE_TIME  DATE 

,ARRIVAL_TIME    DATE 

,AIRCRAFT_TYPE   VARCHAR2(50) );

CREATE TYPE flights_table AS TABLE OF flights_type;

create or replace PACKAGE CURSOR_EXAMPLE AS

--

/*

  This example shows how to call pl/sql procedure that returns one or more

cursors from Java.

*/

--

TYPE airline_refcursor_type IS REF CURSOR RETURN airlines%ROWTYPE;

--

TYPE airport_refcursor_type IS REF CURSOR RETURN airports%ROWTYPE;

--

TYPE aircraft_refcursor_type IS REF CURSOR RETURN aircraft%ROWTYPE;

--

TYPE flights_refcursor_type  IS REF CURSOR RETURN flights%ROWTYPE;

--

PROCEDURE getLists(p_airline_listing  out airline_refcursor_type

                  ,p_airport_listing  out airport_refcursor_type

                  ,p_aircraft_listing out aircraft_refcursor_type);

--

FUNCTION direct_flight_available(p_fromcity in  airports.airport_code%TYPE

                                ,p_tocity   in  airports.airport_code%TYPE)

RETURN boolean;

--

PROCEDURE find_direct_flights    (p_fromcity   in  airports.airport_code%TYPE

                                 ,p_tocity     in  airports.airport_code%TYPE

                                 ,p_orderby    in  number

                                 ,p_flightlist out flights_refcursor_type);

--

END;

create or replace PACKAGE OBJECT_ARRAY_EXAMPLE AS

/*

  This example shows how to call pl/sql procedure that takes an array defined

  as an oracle TYPE object as a parameter.

  We call this directly from Java using JDBC and a class that is represented

  by oracle.sql.ARRAY. Our generated Java class will use the JDBC interface

  oracle.sql.Datum

*/

--

-- p_city is the same data type as the column DEPARTURE_CITY in the FLIGHTS

-- table.

-- p_flights_from uses the Oracle TYPE object flights_table.

--

PROCEDURE get_object_array_of_flights

          (p_city in flights.departure_city%TYPE

          ,p_flights_from out nocopy flights_table);

--

PROCEDURE add_bookings_object_array 

            (p_customer       in customers%ROWTYPE

            ,p_booking_table  in bookings_table

            ,p_status_message    out nocopy varchar2);

--

END;

create or replace PACKAGE PACKAGE_ARRAY_EXAMPLE AS

/*

 This example shows how to call pl/sql procedure that takes a parameter

 defined in a PL/SQL package  as an array of %ROWTYPE (table record).

 There is no direct way to call this PL/SQL procedure from Java.

 Instead we use JDBCWizard to create Java JDBC code that can bypass this

limitation and

 return or accept a PL/SQL array from Java using JDBC.

*/

--

TYPE flights_plsql_array IS TABLE OF flights%ROWTYPE;

--

TYPE bookings_plsql_array IS VARRAY(30) OF bookings%ROWTYPE;

--

PROCEDURE get_plsql_array_of_flights

             (p_city         in     flights.departure_city%TYPE

             ,p_flights_from    out nocopy flights_plsql_array);

--

PROCEDURE add_bookings_plsql_array

         (p_customer       in     customers%ROWTYPE

         ,p_booking_table  in     bookings_plsql_array

         ,p_status_message    out nocopy varchar2);

--

END PACKAGE_ARRAY_EXAMPLE;

create or replace PACKAGE PACKAGE_IDXARRAY_EXAMPLE AS

/*

 This example shows how to call pl/sql procedure that takes a parameter

defined in a PL/SQL package  as an index by array of %ROWTYPE (table record).

 There is no direct way to call this PL/SQL procedure from Java.

 Instead we use JDBCWizard to create Java JDBC code that can bypass this

limitation

 and return an index by array from Java using JDBC.

*/

--

TYPE flights_plsql_array IS TABLE OF flights%ROWTYPE INDEX BY BINARY_INTEGER;

--

TYPE bookings_plsql_array IS TABLE OF bookings%ROWTYPE INDEX BY

BINARY_INTEGER;

--

PROCEDURE get_plsql_array_of_flights

             (p_city         in     flights.departure_city%TYPE

             ,p_flights_from    out nocopy flights_plsql_array);

--

PROCEDURE get_plsql_array_of_flights

         (p_city          in customers%ROWTYPE

         ,p_booking_table in bookings_plsql_array

         ,p_status_message    out nocopy varchar2);

--

END PACKAGE_IDXARRAY_EXAMPLE;

/**

 * 

 */

package com.orindasoft.demo;

import com.orindasoft.pub.*;

import com.orindasoft.demo.generated.*;

import com.orindasoft.demo.generated.plsql.*;

import com.orindasoft.demo.generated.table.instance.AirlinesRow;

import com.orindasoft.demo.generated.table.instance.AirportsRow;

import com.orindasoft.demo.generated.table.instance.FlightsRow;

import com.orindasoft.demo.generated.table.manager.AirlinesMgr;

import com.orindasoft.demo.generated.table.manager.AirportsMgr;

/**

 * @author Orinda Software, Dublin, Ireland. www.orindasoft.com

 * 

 */

public class RunJDBCWizard.xamples {

	public RunJDBCWizard.xamples() {

		// see 'main' method ...

	}

	public static void main(String[] args) {

		com.orindasoft.pub.ConsoleLog theLog = new ConsoleLog();

		theLog.info("Log in to database");

		theLog.info("Create a service using our DB Connection");

		com.orindasoft.demo.generated.DAOFactoryServiceImpl

generatedService 

		         = new DAOFactoryServiceImpl(theLog);

		theLog.info("Demonstrate using our service to exectute

multiple database queries in one call");

		showCursorExample(theLog, generatedService);

		theLog

				.info("Demonstrate passing arrays of Oracle

TYPE objects back and forth to PL/SQL");

		showObjectArrayPassing(theLog, generatedService);

		theLog

				.info("Create extra TYPE objects we need for

passing arrays back and forth");

		createExtraTypeObjects(theLog, generatedService);

		theLog

				.info("Demonstrate passing PL/SQL arrays back

and forth to PL/SQL");

		showPlsqlArrayPassing(theLog, generatedService);

		theLog

				.info("Demonstrate passing PL/SQL Index By

arrays back and forth to PL/SQL");

		showPlsqlIdxByArrayPassing(theLog, generatedService);

		theLog

				.info("drop extra TYPE objects we needed for

passing arrays back and forth");

		dropExtraTypeObjects(theLog, generatedService);

		theLog

				.info("Show how PARAM_PROD_NAME also works as

a Data Access Object (DAO) for Oracle");

		showDaoFunctionality(theLog, generatedService);

	}

	private static void showPlsqlArrayPassing(

			com.orindasoft.pub.LogInterface theLog,

			com.orindasoft.demo.generated.DAOFactoryServiceImpl

generatedService) {

		/*

		 * In the demo below we will get an array of flights leaving

		 * from a

		 * given airport and then create an array of bookings for a

		 * few of them.

		 */

		// We're going to look for flights from Los Angeles...

		final String departureCity = "LAX";

		try {

			/*

			 * Get a list of flights that leave our city. Note how

			 * little actual

			 * code is needed to do this. By examining the

			 * generated code you

			 * can see just how much time and energy it saves. Our

			 * goal is to

			 * call the PL/SQL procedure below:

			 * 

			 * TYPE flights_plsql_array IS TABLE OF

			 * flights%ROWTYPE;

			 * 

			 * PROCEDURE get_plsql_array_of_flights(p_city in

			 * flights.departure_city%TYPE ,p_flights_from out

			 * nocopy

			 * flights_plsql_array);

			 * 

			 * It isn't possible to directly call this from Java.

			 * Instead we use

			 * one of the TYPE objects we created above and run a

			 * anonymous

			 * PL/SQL block that unloads the PL/SQL package array

			 * (which JDBC

			 * can't handle) into an Oracle TYPE array (which it

			 * can):

			 * 

			 * DECLARE

			 * 

			 * p_city VARCHAR2(16) := ?; p_flights_from_A

			 * OB446NDD6_A :=

			 * OB446NDD6_A(); p_flights_from

			 * PACKAGE_ARRAY_EXAMPLE.FLIGHTS_PLSQL_ARRAY :=

			 * PACKAGE_ARRAY_EXAMPLE.FLIGHTS_PLSQL_ARRAY();

			 * 

			 * BEGIN

			 * 

			 * PACKAGE_ARRAY_EXAMPLE.GET_PLSQL_ARRAY_OF_FLIGHTS(p_city,

			 * p_flights_from);

			 * 

			 * IF p_flights_from.COUNT > 0 THEN

			 * p_flights_from_A.EXTEND(p_flights_from.COUNT); FOR

			 * i IN

			 * p_flights_from.FIRST..p_flights_from.LAST LOOP

			 * p_flights_from_A(i) := OB446NDD6_T

			 * (p_flights_from(i).DEPARTURE_CITY

			 * ,p_flights_from(i).ARRIVAL_CITY

			 * ,p_flights_from(i).AIRLINE_NAME

			 * ,p_flights_from(i).FLIGHT_NUMBER

			 * ,p_flights_from(i).DEPARTURE_TIME

			 * ,p_flights_from(i).ARRIVAL_TIME

			 * ,p_flights_from(i).AIRCRAFT_TYPE); END LOOP; END

			 * IF; ? :=

			 * p_flights_from_A; END;

			 */

			// FlightsAttrs is a generated class that maps to the

			// FLIGHTS table.

			// We populate it

			// by calling the service.

			FlightsAttrs[] cityFlights = generatedService

					.servicePlsqlPackageArrayExampleGetPlsqlArrayOfFlights(departureCity);

			// To prove that we did in fact get data back log a

			// message

			theLog.info(cityFlights.length + " flights departing "

					+ departureCity + " found.");

			/*

			 * Having got our flights we then need to make some

			 * bookings. This

			 * involves passing a %rowtype record that represents

			 * a CUSTOMER and

			 * a PL/SQL VARRAY using JDBC.

			 * 

			 * The procedure returns a VARCHAR2 with a status

			 * message.

			 * 

			 * TYPE bookings_plsql_array IS VARRAY(30) OF

			 * bookings%ROWTYPE;

			 * 

			 * PROCEDURE add_bookings_plsql_array (p_customer in

			 * customers%ROWTYPE ,p_booking_table in

			 * bookings_plsql_array

			 * ,p_status_message out nocopy varchar2);

			 * 

			 * As in the previous case this can't be called

			 * directly from Java.

			 * We pass a %ROWTYPE parameter to JDBC by passing in

			 * the individual

			 * fields and assembling a record in the anonymous

			 * block. We also

			 * pass in a PL/SQL package array parameter by sending

			 * it as an

			 * Oracle TYPE array and loading it into an

			 * appropriate variable

			 * before we issue the PL/SQL call. In the code below

			 * the Oracle

			 * TYPE array we use for transport is called

			 * 'p_booking_table_A' and

			 * the PL/SQL package array variable is called

			 * 'p_booking_table'.

			 * 

			 * DECLARE

			 * 

			 * p_customer CUSTOMERS%ROWTYPE; p_booking_table_A

			 * OB46ND2_A :=

			 * OB46ND2_A(); p_booking_table

			 * PACKAGE_ARRAY_EXAMPLE.BOOKINGS_PLSQL_ARRAY :=

			 * PACKAGE_ARRAY_EXAMPLE.BOOKINGS_PLSQL_ARRAY();

			 * p_status_message

			 * VARCHAR2(4000) := null;

			 * 

			 * BEGIN

			 * 

			 * p_customer.name := ?; p_customer.address := ?;

			 * p_customer.city :=

			 * ?; p_customer.state := ?; p_customer.zip := ?;

			 * p_customer.birthdate := ?; p_customer.phone := ?;

			 * 

			 * p_booking_table_A := ?; p_status_message := null;

			 * 

			 * IF p_booking_table_A.COUNT > 0 THEN

			 * p_booking_table.EXTEND(p_booking_table_A.COUNT);

			 * FOR i IN

			 * p_booking_table_A.FIRST..p_booking_table_A.LAST

			 * LOOP

			 * p_booking_table(i).CUSTOMER_NAME :=

			 * p_booking_table_A(i).COL_0;

			 * p_booking_table(i).AIRLINE_NAME :=

			 * p_booking_table_A(i).COL_1;

			 * p_booking_table(i).FLIGHT_NUMBER :=

			 * p_booking_table_A(i).COL_2;

			 * p_booking_table(i).DEPARTURE_TIME :=

			 * p_booking_table_A(i).COL_3;

			 * p_booking_table(i).SEAT :=

			 * p_booking_table_A(i).COL_4; END LOOP;

			 * END IF;

			 * 

			 * PACKAGE_ARRAY_EXAMPLE.ADD_BOOKINGS_PLSQL_ARRAY(p_customer,

			 * p_booking_table,p_status_message); ? :=

			 * p_status_message; END;

			 */

			// As in the first example we have generated classed

			// that match the

			// database

			// objects we work with. We create some to use as

			// parameters.

			Customers ourCustomer = new Customers();

			ourCustomer.paramName = "John Smith";

			ourCustomer.paramBirthdate = new java.util.Date(System

					.currentTimeMillis());

			ourCustomer.paramCity = "Walnut Creek";

			ourCustomer.paramState = "CA";

			ourCustomer.paramPhone = "555 1212";

			Bookings ourFirstBooking = new Bookings();

			ourFirstBooking.paramCustomerName =

ourCustomer.paramName;

			ourFirstBooking.paramAirlineName =

cityFlights[0].paramAirlineName;

			ourFirstBooking.paramFlightNumber =

cityFlights[0].paramFlightNumber;

			ourFirstBooking.paramDepartureTime =

cityFlights[0].paramDepartureTime;

			ourFirstBooking.paramSeat = "1F";

			Bookings ourSecondBooking = new Bookings();

			ourSecondBooking.paramCustomerName =

ourCustomer.paramName;

			ourSecondBooking.paramAirlineName =

cityFlights[1].paramAirlineName;

			ourSecondBooking.paramFlightNumber =

cityFlights[1].paramFlightNumber;

			ourSecondBooking.paramDepartureTime =

cityFlights[1].paramDepartureTime;

			ourSecondBooking.paramSeat = "1A";

			Bookings ourThirdBooking = new Bookings();

			ourThirdBooking.paramCustomerName =

ourCustomer.paramName;

			ourThirdBooking.paramAirlineName =

cityFlights[2].paramAirlineName;

			ourThirdBooking.paramFlightNumber =

cityFlights[2].paramFlightNumber;

			ourThirdBooking.paramDepartureTime =

cityFlights[2].paramDepartureTime;

			ourThirdBooking.paramSeat = "2A";

			Bookings[] ourBookingArray = { ourFirstBooking,

ourSecondBooking,

					ourThirdBooking };

			// The service returns a status message that describes

			// what it did

			String statusMessage = generatedService

					.servicePlsqlPackageArrayExampleAddBookingsPlsqlArray(

							ourCustomer,

ourBookingArray);

			// log the status message.

			theLog.info(statusMessage);

		} catch (DAOFactoryServiceException e) {

			theLog.error(e);

		}

	}

	private static void showDaoFunctionality(ConsoleLog theLog,

			DAOFactoryServiceImpl generatedService) {

		try {

			theLog.info("");

			theLog.info("Creating an instance of the DAO Factory

class.");

			theLog.info("");

			// We don't need to do anything to create a DAO

			// Factory -

			// the generated service extends a DAO factory class,

			// so we

			// cast it back to DAOFFactory for the purposes of

			// this demo

			DAOFactory theFactory = generatedService;

			theLog.info("Getting airports DAO...");

			// Get airports data access object.

			AirportsMgr theAirportsMgr =

theFactory.getAirportsTableDAO();

			theLog.info("Getting airport record for San

Francisco...");

			// Now we have an instance of AirportsMgr we can get

			// an instance by

			// using the Primary Key of airports...

			AirportsRow sfo = theAirportsMgr.getByPk("SFO");

			theLog.info("Found airport: " +

sfo.getRowAirportCode() + " "

					+ sfo.getRowAirportName());

			theLog.info("Finding flights that leave from SFO:");

			// We pass the record for 'sfo' to the airports

			// manager DAO and ask

			// it

			// to follow the foreign key to Flights....

			FlightsRow[] sfoFlights = theAirportsMgr

					.getChildByFkFltDepCity(sfo);

			for (int i = 0; i < 10 && i < sfoFlights.length; i++)

{

				theLog.info(i + " " +

sfoFlights[i].getRowAirlineName() + " "

						+

sfoFlights[i].getRowFlightNumberInt() + " to "

						+

sfoFlights[i].getRowArrivalCity());

			}

			theLog.info("Creating a new airline...");

			// Get airlines data access object.

			AirlinesMgr theAirlinesMgr =

theFactory.getAirlinesTableDAO();

			AirlinesRow newAirline = new AirlinesRow();

			newAirline.rowAirlineName = ("ThinAir");

			theAirlinesMgr.rowInsert(newAirline);

			theLog.info("Created 'ThinAir'");

			// Now delete our record, so we can run this demo

			// again...

			theAirlinesMgr.rowDelete(newAirline);

			theLog.info("Deleted 'ThinAir'");

			theLog.info("");

			theLog

					.info("Now use a Data Access Object to

run a stored procedure.");

			theLog

					.info("We'll call

'directFlightAvailable' for the parameters 'SFO' and 'LAX'");

			// Get an instance of

			// SimpleExamplesDirectFlightAvailable from the

			// DAOFactory class. Note that we don't have to worry

			// about database

			// Connections as the DAO Factory looks after them for

			// us.

			CursorExampleDirectFlightAvailable

directFlightAvailable = theFactory

					.getCursorExampleDirectFlightAvailablePlSqlDAO();

			directFlightAvailable.setParamPFromcity("SFO");

			directFlightAvailable.setParamPFromcity("LAX");

			directFlightAvailable.executeProc();

			if (directFlightAvailable.getFunctionResultBoolean())

{

				theLog.info("A direct flight exists between

SFO and LAX");

			} else {

				theLog.info("No direct flight exists between

SFO and LAX");

			}

			theLog.info("Closing database connection.");

			// release database connection. Note that the factory

			// class will

			// inform

			// any Data Access Objects we created for us.

			// eFactory.commit();

			theFactory.releaseResources();

			theLog.info("DAOFactoryDemo Finished");

			theLog.info("");

		} catch (com.orindasoft.pub.CSException e) {

			theLog.syserror(e);

			if (e.getMessage().startsWith("Login Error")) {

				theLog

						.info("Check the

'confirmConnection' method of DAOFactory");

				theLog.info("and make sure the connect string

is valid.");

			}

			theLog.syserror("Exiting due to OrindaSoft library

Exception");

			System.exit(2);

		} catch (Exception e) {

			theLog.syserror(e);

			theLog.syserror("Exiting due to Other Exception");

			System.exit(4);

		}

	}

	private static void showCursorExample(ConsoleLog theLog,

			DAOFactoryServiceImpl generatedService) {

		try {

			theLog

					.info("Get list of Airports, Airplanes

and Airlines in our database");

			theLog

					.info("To do this we call a procedure

that returns multiple cursors...");

			theLog.info("");

			// Because web services take the 'functions must not

			// modify

			// their arguments' rule seriously PARAM_PROD_NAME

			// creates special

			// Java classes to hold output parameters when a

			// procedure returns

			// more than 1.

			CursorExampleGetlistsReturn theLists =

generatedService

					.servicePlsqlCursorExampleGetlists();

			// 'theLists' consists of 3 arrays. Print out the

			// first few elements

			// of each.

			theLog.info(" ");

			theLog

					.info(" The first ten airfields in our

database. Note that our database contains ");

			theLog

					.info(" lots of obscure airfields that

don't have scheduled passenger services");

			theLog.info(" ");

			// Loop through airportList until we get to 10 records

			// or run out of

			// records

			for (int i = 0; i < 10 && i <

theLists.paramPAirportListing.length; i++) {

				// Once you have picked your row most methods

				// will operate on

				// the current one.

				// the ":" character is used as a field

				// seperator

				// the "?" character is used when the field is

				// unprintable.

				theLog.info("   "

						+

theLists.paramPAirportListing[i].paramAirportCode

						+ " "

						+

theLists.paramPAirportListing[i].paramAirportName);

			}

			theLog.info(" ");

			theLog.info(" The first ten airlines in our database

");

			theLog.info(" ");

			// Loop through airline List until we get to 10

			// records or run out

			// of records

			for (int i = 0; i < 10 && i <

theLists.paramPAirlineListing.length; i++) {

				theLog.info("   "

						+

theLists.paramPAirlineListing[i].paramAirlineName);

			}

			theLog.info(" ");

			theLog.info(" The first ten aircraft types in our

database ");

			theLog.info(" ");

			// Loop through aircraft List until we get to 10

			// records or run out

			// of records

			for (int i = 0; i < 10 && i <

theLists.paramPAircraftListing.length; i++) {

				theLog.info("   "

						+

theLists.paramPAircraftListing[i].paramAircraftType);

			}

		} catch (DAOFactoryServiceException e) {

			theLog.error(e);

		}

		String fromCity = "LAX";

		String toCity = "JFK";

		try {

			boolean flightAvailable = generatedService

					.servicePlsqlCursorExampleDirectFlightAvailable(fromCity,

							toCity);

			if (flightAvailable) {

				theLog.info("A direct flight from " + fromCity

+ " to "

						+ toCity + " is available");

			} else {

				theLog.info("No direct flight from " +

fromCity + " to "

						+ toCity + " is available");

			}

		} catch (DAOFactoryServiceException e) {

			theLog.error(e);

		}

		try {

			double customOrderBy = 3;

			CursorExampleFlightsRefcursorTypeAttrs[]

directFlightsArray = generatedService

					.servicePlsqlCursorExampleFindDirectFlights(fromCity,

							toCity,

customOrderBy);

			theLog.info("   Airline,Flight, From =>

To,Equipment");

			for (int i = 0; i < 7 && i <

directFlightsArray.length; i++) {

				theLog.info("   " +

directFlightsArray[i].paramAirlineName

						+ ", "

						+

directFlightsArray[i].paramFlightNumber.toString()

						+ ", " +

directFlightsArray[i].paramDepartureCity

						+ " => " +

directFlightsArray[i].paramArrivalCity

						+ ", " +

directFlightsArray[i].paramAircraftType);

			}

		} catch (DAOFactoryServiceException e) {

			theLog.error(e);

		}

		try {

			double customOrderBy = 3;

			CursorExampleFlightsRefcursorTypeAttrs[]

directFlightsArray = generatedService

					.servicePlsqlCursorExampleFindDirectFlights(fromCity,

							toCity,

customOrderBy);

			theLog.info("   Airline,Flight, From =>

To,Equipment");

			for (int i = 0; i < 7 && i <

directFlightsArray.length; i++) {

				theLog.info("   " +

directFlightsArray[i].paramAirlineName

						+ ", "

						+

directFlightsArray[i].paramFlightNumber.toString()

						+ ", " +

directFlightsArray[i].paramDepartureCity

						+ " => " +

directFlightsArray[i].paramArrivalCity

						+ ", " +

directFlightsArray[i].paramAircraftType);

			}

		} catch (DAOFactoryServiceException e) {

			theLog.error(e);

		}

	}

	private static void showObjectArrayPassing(ConsoleLog theLog,

			DAOFactoryServiceImpl generatedService) {

		/*

		 * In the demo below we will get an array of flights leaving

		 * from a

		 * given airport and then create an array of bookings for a

		 * few of them.

		 */

		// We're going to look for flights from Oakland...

		final String departureCity = "OAK";

		try {

			/*

			 * Get a list of flights that leave our city. Note how

			 * little actual

			 * code is needed to do this. By examining the

			 * generated code you

			 * can see just how much time and energy it saves. Our

			 * goal is to

			 * call the PL/SQL procedure below:

			 * 

			 * PROCEDURE get_object_array_of_flights(p_city in

			 * flights.departure_city%TYPE ,p_flights_from out

			 * nocopy

			 * flights_table); -- PROCEDURE

			 * add_bookings_object_array

			 * (p_customer in customers%ROWTYPE ,p_booking_table

			 * in

			 * bookings_table ,p_status_message out nocopy

			 * varchar2); -- END;

			 */

			// FlightsAttrs is a generated class that maps to the

			// FLIGHTS table.

			// We populate it

			// by calling the service.

			FlightsTypeAttrs[] cityFlights = generatedService

					.servicePlsqlObjectArrayExampleGetObjectArrayOfFlights(departureCity);

			// To prove that we did in fact get data back log a

			// message

			theLog.info(cityFlights.length + " flights departing "

					+ departureCity + " found.");

			/*

			 * Having got our flights we then need to make some

			 * bookings. This

			 * involves passing a %rowtype record that represents

			 * a CUSTOMER and

			 * an Oracle TYPE array using JDBC.

			 * 

			 * The procedure returns a VARCHAR2 with a status

			 * message.

			 * 

			 * PROCEDURE add_bookings_object_array (p_customer in

			 * customers%ROWTYPE ,p_booking_table in

			 * bookings_table

			 * ,p_status_message out nocopy varchar2);

			 */

			// As in the first example we have generated classed

			// that match the

			// database

			// objects we work with. We create some to use as

			// parameters.

			Customers ourCustomer = new Customers();

			ourCustomer.paramName = "John Smith";

			ourCustomer.paramBirthdate = new java.util.Date(System

					.currentTimeMillis());

			ourCustomer.paramCity = "Walnut Creek";

			ourCustomer.paramState = "CA";

			ourCustomer.paramPhone = "555 1212";

			BookingsTypeAttrs ourFirstBooking = new

BookingsTypeAttrs();

			ourFirstBooking.paramCustomerName =

ourCustomer.paramName;

			ourFirstBooking.paramAirlineName =

cityFlights[0].paramAirlineName;

			ourFirstBooking.paramFlightNumber =

cityFlights[0].paramFlightNumber;

			ourFirstBooking.paramDepartureTime =

cityFlights[0].paramDepartureTime;

			ourFirstBooking.paramSeat = "1F";

			BookingsTypeAttrs ourSecondBooking = new

BookingsTypeAttrs();

			ourSecondBooking.paramCustomerName =

ourCustomer.paramName;

			ourSecondBooking.paramAirlineName =

cityFlights[1].paramAirlineName;

			ourSecondBooking.paramFlightNumber =

cityFlights[1].paramFlightNumber;

			ourSecondBooking.paramDepartureTime =

cityFlights[1].paramDepartureTime;

			ourSecondBooking.paramSeat = "1A";

			BookingsTypeAttrs ourThirdBooking = new

BookingsTypeAttrs();

			ourThirdBooking.paramCustomerName =

ourCustomer.paramName;

			ourThirdBooking.paramAirlineName =

cityFlights[2].paramAirlineName;

			ourThirdBooking.paramFlightNumber =

cityFlights[2].paramFlightNumber;

			ourThirdBooking.paramDepartureTime =

cityFlights[2].paramDepartureTime;

			ourThirdBooking.paramSeat = "2A";

			BookingsTypeAttrs[] ourBookingArray = {

ourFirstBooking,

					ourSecondBooking, ourThirdBooking };

			// The service returns a status message that describes

			// what it did

			String statusMessage = generatedService

					.servicePlsqlObjectArrayExampleAddBookingsObjectArray(

							ourCustomer,

ourBookingArray);

			// log the status message.

			theLog.info(statusMessage);

		} catch (DAOFactoryServiceException e) {

			theLog.error(e);

		}

	}

	private static void showPlsqlIdxByArrayPassing(ConsoleLog theLog,

			DAOFactoryServiceImpl generatedService) {

		/*

		 * In the demo below we will get an array of flights leaving

		 * from a

		 * given airport and then create an array of bookings for a

		 * few of them.

		 */

		// We're going to look for flights from San Francisco...

		final String departureCity = "SFO";

		try {

			/*

			 * Get a list of flights that leave our city. Note how

			 * little actual

			 * code is needed to do this. By examining the

			 * generated code you

			 * can see just how much time and energy it saves. Our

			 * goal is to

			 * call the PL/SQL procedure below:

			 * 

			 * TYPE flights_plsql_array IS TABLE OF

			 * flights%ROWTYPE;

			 * 

			 * PROCEDURE get_plsql_array_of_flights(p_city in

			 * flights.departure_city%TYPE ,p_flights_from out

			 * nocopy

			 * flights_plsql_array);

			 * 

			 * It isn't possible to directly call this from Java.

			 * Instead we use

			 * one of the TYPE objects we created above and run a

			 * anonymous

			 * PL/SQL block that unloads the PL/SQL package array

			 * (which JDBC

			 * can't handle) into an Oracle TYPE array (which it

			 * can):

			 * 

			 * DECLARE

			 * 

			 * p_city VARCHAR2(16) := NULL; p_flights_from_A

			 * OSOFT446NDD6_A :=

			 * OSOFT446NDD6_A(); p_flights_from

			 * PACKAGE_IDXARRAY_EXAMPLE.FLIGHTS_PLSQL_ARRAY;

			 * 

			 * BEGIN p_city := ?;

			 * 

			 * PACKAGE_IDXARRAY_EXAMPLE.GET_PLSQL_ARRAY_OF_FLIGHTS(p_city,

			 * p_flights_from);

			 * 

			 * IF p_flights_from.COUNT > 0 THEN

			 * p_flights_from_A.EXTEND(p_flights_from.COUNT); FOR

			 * i IN

			 * p_flights_from.FIRST..p_flights_from.LAST LOOP IF

			 * p_flights_from.EXISTS(i) THEN p_flights_from_A(i)

			 * :=

			 * OSOFT446NDD6_T (p_flights_from(i).DEPARTURE_CITY

			 * ,p_flights_from(i).ARRIVAL_CITY

			 * ,p_flights_from(i).AIRLINE_NAME

			 * ,p_flights_from(i).FLIGHT_NUMBER

			 * ,p_flights_from(i).DEPARTURE_TIME

			 * ,p_flights_from(i).ARRIVAL_TIME

			 * ,p_flights_from(i).AIRCRAFT_TYPE); END IF; END

			 * LOOP; END IF; ? :=

			 * p_flights_from_A; END;

			 */

			// FlightsAttrs is a generated class that maps to the

			// FLIGHTS table.

			// We populate it

			// by calling the service.

			FlightsAttrs[] cityFlights = generatedService

					.servicePlsqlPackageArrayExampleGetPlsqlArrayOfFlights(departureCity);

			// To prove that we did in fact get data back log a

			// message

			theLog.info(cityFlights.length + " flights departing "

					+ departureCity + " found.");

			/*

			 * Having got our flights we then need to make some

			 * bookings. This

			 * involves passing a %rowtype record that represents

			 * a CUSTOMER and

			 * a PL/SQL VARRAY using JDBC.

			 * 

			 * The procedure returns a VARCHAR2 with a status

			 * message.

			 * 

			 * TYPE bookings_plsql_array IS VARRAY(30) OF

			 * bookings%ROWTYPE;

			 * 

			 * PROCEDURE add_bookings_plsql_array (p_customer in

			 * customers%ROWTYPE ,p_booking_table in

			 * bookings_plsql_array

			 * ,p_status_message out nocopy varchar2);

			 * 

			 * As in the previous case this can't be called

			 * directly from Java.

			 * We pass a %ROWTYPE parameter to JDBC by passing in

			 * the individual

			 * fields and assembling a record in the anonymous

			 * block. We also

			 * pass in a PL/SQL package array parameter by sending

			 * it as an

			 * Oracle TYPE array and loading it into an

			 * appropriate variable

			 * before we issue the PL/SQL call. In the code below

			 * the Oracle

			 * TYPE array we use for transport is called

			 * 'p_booking_table_A' and

			 * the PL/SQL package array variable is called

			 * 'p_booking_table'.

			 * 

			 * DECLARE

			 * 

			 * p_customer CUSTOMERS%ROWTYPE; p_booking_table_A

			 * OSOFT46ND2_A :=

			 * OSOFT46ND2_A();

			 * 

			 * p_booking_table

			 * PACKAGE_IDXARRAY_EXAMPLE.BOOKINGS_PLSQL_ARRAY;

			 * p_status_message VARCHAR2(4000) := null;

			 * 

			 * BEGIN

			 * 

			 * p_customer.name := ?; p_customer.address := ?;

			 * p_customer.city :=

			 * ?; p_customer.state := ?; p_customer.zip := ?;

			 * p_customer.birthdate := ?; p_customer.phone := ?;

			 * p_booking_table_A := ?; p_status_message := null;

			 * 

			 * IF p_booking_table_A.COUNT > 0 THEN FOR i IN

			 * p_booking_table_A.FIRST..p_booking_table_A.LAST

			 * LOOP

			 * p_booking_table(i).CUSTOMER_NAME :=

			 * p_booking_table_A(i).COL_0;

			 * p_booking_table(i).AIRLINE_NAME :=

			 * p_booking_table_A(i).COL_1;

			 * p_booking_table(i).FLIGHT_NUMBER :=

			 * p_booking_table_A(i).COL_2;

			 * p_booking_table(i).DEPARTURE_TIME :=

			 * p_booking_table_A(i).COL_3;

			 * p_booking_table(i).SEAT :=

			 * p_booking_table_A(i).COL_4; END LOOP;

			 * END IF;

			 * 

			 * PACKAGE_IDXARRAY_EXAMPLE.ADD_BOOKINGS_PLSQL_ARRAY(p_customer,

			 * p_booking_table,p_status_message);

			 * 

			 * ? := p_status_message;

			 * 

			 * END;

			 */

			// As in the first example we have generated classed

			// that match the

			// database

			// objects we work with. We create some to use as

			// parameters.

			Customers ourCustomer = new Customers();

			ourCustomer.paramName = "John Smith";

			ourCustomer.paramBirthdate = new java.util.Date(System

					.currentTimeMillis());

			ourCustomer.paramCity = "Walnut Creek";

			ourCustomer.paramState = "CA";

			ourCustomer.paramPhone = "555 1212";

			Bookings ourFirstBooking = new Bookings();

			ourFirstBooking.paramCustomerName =

ourCustomer.paramName;

			ourFirstBooking.paramAirlineName =

cityFlights[0].paramAirlineName;

			ourFirstBooking.paramFlightNumber =

cityFlights[0].paramFlightNumber;

			ourFirstBooking.paramDepartureTime =

cityFlights[0].paramDepartureTime;

			ourFirstBooking.paramSeat = "1F";

			Bookings ourSecondBooking = new Bookings();

			ourSecondBooking.paramCustomerName =

ourCustomer.paramName;

			ourSecondBooking.paramAirlineName =

cityFlights[1].paramAirlineName;

			ourSecondBooking.paramFlightNumber =

cityFlights[1].paramFlightNumber;

			ourSecondBooking.paramDepartureTime =

cityFlights[1].paramDepartureTime;

			ourSecondBooking.paramSeat = "1A";

			Bookings ourThirdBooking = new Bookings();

			ourThirdBooking.paramCustomerName =

ourCustomer.paramName;

			ourThirdBooking.paramAirlineName =

cityFlights[2].paramAirlineName;

			ourThirdBooking.paramFlightNumber =

cityFlights[2].paramFlightNumber;

			ourThirdBooking.paramDepartureTime =

cityFlights[2].paramDepartureTime;

			ourThirdBooking.paramSeat = "2A";

			Bookings[] ourBookingArray = { ourFirstBooking,

ourSecondBooking,

					ourThirdBooking };

			// The service returns a status message that describes

			// what it did

			String statusMessage = generatedService

					.servicePlsqlPackageArrayExampleAddBookingsPlsqlArray(

							ourCustomer,

ourBookingArray);

			// log the status message.

			theLog.info(statusMessage);

		} catch (DAOFactoryServiceException e) {

			theLog.error(e);

		}

	}

	private static void createExtraTypeObjects(ConsoleLog theLog,

			DAOFactoryServiceImpl generatedService) {

		/*

		 * Before we can pass a PL/SQL array to Java we must create

		 * some extra

		 * Oracle TYPE objects first.

		 * 

		 * This step only needs to happen once - once the objects are

		 * created

		 * the rest of the generated code will always run. We provide

		 * both the

		 * Java method below and a SQL script for the objects.

		 * 

		 * The statement below creates the following objects:

		 * 

		 * CREATE OR REPLACE TYPE OB46ND2_T AS OBJECT (COL_0

		 * VARCHAR2(16) ,COL_1

		 * VARCHAR2(64) ,COL_2 NUMBER ,COL_3 DATE ,COL_4 VARCHAR2(4));

		 * 

		 * CREATE OR REPLACE TYPE OB46ND2_A AS TABLE OF OB46ND2_T;

		 * 

		 * CREATE OR REPLACE TYPE OB446NDD6_T AS OBJECT" (COL_0

		 * VARCHAR2(16)

		 * ,COL_1 VARCHAR2(16) ,COL_2 VARCHAR2(64) ,COL_3 NUMBER

		 * ,COL_4 DATE

		 * ,COL_5 DATE ,COL_6 VARCHAR2(64));

		 * 

		 * CREATE OR REPLACE TYPE OB446NDD6_A AS TABLE OF OB446NDD6_T;

		 * 

		 * Note that the objects have highly generic names. We do this

		 * for two

		 * reasons. Firstly it avoids confusion by not creating new

		 * Oracle TYPE

		 * objects that have names almost identical to existing PL/SQL

		 * Package

		 * Arrays. Secondly we want to reduce the number of objects we

		 * create to

		 * a minimum - a single generated TYPE object is used

		 * everywhere a

		 * record with the same pattern of column data types and

		 * lengths is

		 * encountered.

		 */

		try {

			generatedService.createExtraTypeObjects();

		} catch (CSException e1) {

			theLog.error(e1);

		}

	}

	private static void dropExtraTypeObjects(ConsoleLog theLog,

			DAOFactoryServiceImpl generatedService) {

		/*

		 * For the sake of neatness we drop the extra type objects. We

		 * wouldn't

		 * do this in real life, as we would need them the next time

		 * we ran the

		 * code. If you create and drop TYPE objects repeatedly within

		 * the same

		 * session you can also get ORA-902 for no apparent reason...

		 */

		try {

			generatedService.dropExtraTypeObjects();

		} catch (CSException e1) {

			theLog.error(e1);

		}

	}

}

2009/10/20 19:31:47 IST:Information:Create Service Using our DB Connection

2009/10/20 19:31:47 IST:Information:Demonstrate using our service to exectute

multiple database queries in one call

2009/10/20 19:31:47 IST:Information:Get list of Airports, Airplanes and

Airlines in our database

2009/10/20 19:31:47 IST:Information:To do this we call a procedure that

returns multiple cursors...

2009/10/20 19:31:47 IST:Information:

2009/10/20 19:31:48 IST:Information:Logged in as user ORINDADEMO

2009/10/20 19:31:49 IST:Information: 

2009/10/20 19:31:49 IST:Information: The first ten airfields in our database.

Note that our database contains 

2009/10/20 19:31:49 IST:Information: lots of obscure airfields that don't have

scheduled passenger services

2009/10/20 19:31:49 IST:Information: 

2009/10/20 19:31:49 IST:Information:   07FA ,  (07FA)

2009/10/20 19:31:49 IST:Information:   0S9 Port Townsend, WA (0S9)

2009/10/20 19:31:49 IST:Information:   1V6 Canon City, CO (1V6)

2009/10/20 19:31:49 IST:Information:   20V Kremmling, CO (20V)

2009/10/20 19:31:49 IST:Information:   3J7 Greensboro, GA (3J7)

2009/10/20 19:31:49 IST:Information:   3S8 Grants Pass, OR (3S8)

2009/10/20 19:31:49 IST:Information:   42B East Haddam, CT (42B)

2009/10/20 19:31:49 IST:Information:   4B8 Plainville, CT (4B8)

2009/10/20 19:31:49 IST:Information:   5U3 Ennis, MT (5U3)

2009/10/20 19:31:49 IST:Information:   68J Tallahassee /Havana/, FL (68J)

2009/10/20 19:31:49 IST:Information: 

2009/10/20 19:31:49 IST:Information: The first ten airlines in our database 

2009/10/20 19:31:49 IST:Information: 

2009/10/20 19:31:49 IST:Information:   ACM Air Charter

2009/10/20 19:31:49 IST:Information:   Action Airlines

2009/10/20 19:31:49 IST:Information:   Aer Arann Express

2009/10/20 19:31:49 IST:Information:   Aer Lingus

2009/10/20 19:31:49 IST:Information:   Aero California

2009/10/20 19:31:49 IST:Information:   Aeroflot-Russian Intl

2009/10/20 19:31:49 IST:Information:   Aeromar C. Por. A.

2009/10/20 19:31:49 IST:Information:   Aeromexico

2009/10/20 19:31:49 IST:Information:   Aeromyl

2009/10/20 19:31:49 IST:Information:   Air 2000

2009/10/20 19:31:49 IST:Information: 

2009/10/20 19:31:49 IST:Information: The first ten aircraft types in our

database 

2009/10/20 19:31:49 IST:Information: 

2009/10/20 19:31:49 IST:Information:   A124

2009/10/20 19:31:49 IST:Information:   A332

2009/10/20 19:31:49 IST:Information:   A333

2009/10/20 19:31:49 IST:Information:   A342

2009/10/20 19:31:49 IST:Information:   A343

2009/10/20 19:31:49 IST:Information:   ATR-42-200/300/320

2009/10/20 19:31:49 IST:Information:   ATR-72

2009/10/20 19:31:49 IST:Information:   Airbus A300B2

2009/10/20 19:31:49 IST:Information:   Airbus A300B4-600

2009/10/20 19:31:49 IST:Information:   Airbus A310

2009/10/20 19:31:49 IST:Information:A direct flight from LAX to JFK is

available

2009/10/20 19:31:49 IST:Information:   Airline,Flight, From => To,Equipment

2009/10/20 19:31:49 IST:Information:   United, 14, LAX => JFK, Boeing 767-200

2009/10/20 19:31:49 IST:Information:   United, 12, LAX => JFK, Boeing 767-200

2009/10/20 19:31:49 IST:Information:   United, 12, LAX => JFK, Boeing 767-200

2009/10/20 19:31:49 IST:Information:   American, 30, LAX => JFK, Boeing

767-200

2009/10/20 19:31:49 IST:Information:   United, 4, LAX => JFK, Boeing 767-200

2009/10/20 19:31:49 IST:Information:   American, 2, LAX => JFK, Boeing 767-200

2009/10/20 19:31:49 IST:Information:   American, 2, LAX => JFK, Boeing 767-200

2009/10/20 19:31:49 IST:Information:   Airline,Flight, From => To,Equipment

2009/10/20 19:31:49 IST:Information:   United, 14, LAX => JFK, Boeing 767-200

2009/10/20 19:31:49 IST:Information:   United, 12, LAX => JFK, Boeing 767-200

2009/10/20 19:31:49 IST:Information:   United, 12, LAX => JFK, Boeing 767-200

2009/10/20 19:31:49 IST:Information:   American, 30, LAX => JFK, Boeing

767-200

2009/10/20 19:31:49 IST:Information:   United, 4, LAX => JFK, Boeing 767-200

2009/10/20 19:31:49 IST:Information:   American, 2, LAX => JFK, Boeing 767-200

2009/10/20 19:31:49 IST:Information:   American, 2, LAX => JFK, Boeing 767-200

2009/10/20 19:31:49 IST:Information:Demonstrate passing arrays of Oracle TYPE

objects back and forth to PL/SQL

2009/10/20 19:31:49 IST:Information:65 flights departing OAK found.

2009/10/20 19:31:50 IST:Information:New Flights for John Smith: Added Aloha

473 31-MAR-03;Added America West 630 19-MAR-03;Added America West 841

19-MAY-03;

2009/10/20 19:31:50 IST:Information:Create extra TYPE objects we need for

passing arrays back and forth

2009/10/20 19:31:50 IST:Information:CREATE OR REPLACE TYPE OSOFT46ND2_T AS

OBJECT (COL_0 VARCHAR2(16) ,COL_1 VARCHAR2(64) ,COL_2 NUMBER ,COL_3 DATE

,COL_4 VARCHAR2(4))

2009/10/20 19:31:51 IST:Information:CREATE OR REPLACE TYPE OSOFT46ND2_A  AS

TABLE OF OSOFT46ND2_T;

2009/10/20 19:31:51 IST:Information:CREATE OR REPLACE TYPE OSOFT446NDD6_T AS

OBJECT (COL_0 VARCHAR2(16) ,COL_1 VARCHAR2(16) ,COL_2 VARCHAR2(64) ,COL_3

NUMBER ,COL_4 DATE ,COL_5 DATE ,COL_6 VARCHAR2(64))

2009/10/20 19:31:51 IST:Information:CREATE OR REPLACE TYPE OSOFT446NDD6_A  AS

TABLE OF OSOFT446NDD6_T;

2009/10/20 19:31:51 IST:Information:Demonstrate passing PL/SQL arrays back and

forth to PL/SQL

2009/10/20 19:31:51 IST:Information:301 flights departing LAX found.

2009/10/20 19:31:51 IST:Information:New Flights for John Smith: Added Air New

Zealand 2 01-APR-03;Added Air Transport Intl 801 10-JUN-03;Added Aer Lingus

144 08-MAY-03;

2009/10/20 19:31:51 IST:Information:Demonstrate passing PL/SQL Index By arrays

back and forth to PL/SQL

2009/10/20 19:31:51 IST:Information:159 flights departing SFO found.

2009/10/20 19:31:51 IST:Information:New Flights for John Smith: Added

Aeroflot-Russian Intl 324 07-MAY-03;Added Aeroflot-Russian Intl 324

09-JUN-03;Added Air Canada 546 09-JUN-03;

2009/10/20 19:31:51 IST:Information:drop extra TYPE objects we needed for

passing arrays back and forth

2009/10/20 19:31:51 IST:Information:DROP TYPE OSOFT46ND2_A

2009/10/20 19:31:52 IST:Information:DROP TYPE OSOFT46ND2_T

2009/10/20 19:31:52 IST:Information:DROP TYPE OSOFT446NDD6_A

2009/10/20 19:31:52 IST:Information:DROP TYPE OSOFT446NDD6_T

2009/10/20 19:31:52 IST:Information:Show how PARAM_PROD_NAME also works as a

Data Access Object (DAO) for Oracle

2009/10/20 19:31:52 IST:Information:

2009/10/20 19:31:52 IST:Information:Creating an instance of the DAO Factory

class.

2009/10/20 19:31:52 IST:Information:

2009/10/20 19:31:52 IST:Information:Getting airports DAO...

2009/10/20 19:31:52 IST:Information:Getting airport record for San

Francisco...

2009/10/20 19:31:52 IST:Information:Found airport: SFO San Francisco, CA (SFO)

2009/10/20 19:31:52 IST:Information:Finding flights that leave from SFO:

2009/10/20 19:31:52 IST:Information:0 Aeroflot-Russian Intl 324 to SEA

2009/10/20 19:31:52 IST:Information:1 Aeroflot-Russian Intl 324 to SEA

2009/10/20 19:31:52 IST:Information:2 Air Canada 546 to YVR

2009/10/20 19:31:52 IST:Information:3 Air Canada 754 to YYZ

2009/10/20 19:31:52 IST:Information:4 Air Canada 756 to YYZ

2009/10/20 19:31:52 IST:Information:5 Air Canada 758 to YYZ

2009/10/20 19:31:52 IST:Information:6 Air France 83 to LFPG

2009/10/20 19:31:52 IST:Information:7 Alaska 519 to PDX

2009/10/20 19:31:52 IST:Information:8 Alaska 567 to SEA

2009/10/20 19:31:52 IST:Information:9 Alaska 599 to YVR

2009/10/20 19:31:52 IST:Information:Creating a new airline...

2009/10/20 19:31:52 IST:Information:Created 'ThinAir'

2009/10/20 19:31:52 IST:Information:Deleted 'ThinAir'

2009/10/20 19:31:52 IST:Information:

2009/10/20 19:31:52 IST:Information:Now use a Data Access Object to run a

stored procedure.

2009/10/20 19:31:52 IST:Information:We'll call 'directFlightAvailable' for the

parameters 'SFO' and 'LAX'

2009/10/20 19:31:52 IST:Information:A direct flight exists between SFO and LAX

2009/10/20 19:31:52 IST:Information:Closing database connection.

2009/10/20 19:31:52 IST:Information:DAOFactoryDemo Finished

2009/10/20 19:31:52 IST:Information: