| PackageArrayExampleGetPlsqlArrayOfFlights.java |
1 package com.orindasoft.demo.generated.plsql;
2
3 import java.sql.*;
4
5 import com.orindasoft.pub.LogInterface;
6 import com.orindasoft.pub.OracleResourceUser;
7 import com.orindasoft.pub.CSException;
8 import com.orindasoft.demo.generated.plsql.AbstractProcCall;
9
10 /**
11 * PackageArrayExampleGetPlsqlArrayOfFlights - Access Procedure PACKAGE_ARRAY_EXAMPLE.GET_PLSQL_ARRAY_OF_FLIGHTS
12 *
13 * <p>Generated by JDBCWizard./a> build 2776 at 2011/03/07 19:55:35.146 GMT
14 * <p>JDBCWizard./a> is made by Orinda Software Ltd, Dublin, Ireland
15 * <p>Target Database: Oracle 10.2.0
16 * <p> -------------------------------------------------------------------------
17 * <p> WARNING: This code will stop working around the time JDBCWizard./a> expires.
18 * <p> This restriction only exists in the demo version of JDBCWizard./a>.
19 * <p> -------------------------------------------------------------------------
20 * <p> JDBCWizard./a> Tips 17 of 19:
21 * <p> JDBCWizard./a> also runs as an extension to Oracle's JDeveloper 10g
22 * <p> -------------------------------------------------------------------------
23 *
24 * <p>Copyright Orinda Software 2003-2009
25 * <p>Access Basis: User Object
26 * <p>PLSQL source code:
27 * <code><pre>
28 -----------------------------------------------------------------
29 * PACKAGE PACKAGE_ARRAY_EXAMPLE AS
30 * /*
31 * THIS EXAMPLE SHOWS HOW TO CALL PL/SQL PROCEDURE THAT TAKES A PARAMETER DEFINED
32 * IN A PL/SQL PACKAGE AS AN ARRAY OF %ROWTYPE (TABLE RECORD).
33 *
34 * THERE IS NO DIRECT WAY TO CALL THIS PL/SQL PROCEDURE FROM JAVA.
35 *
36 * INSTEAD WE USE ORINDABUILD TO CREATE JAVA JDBC CODE THAT CAN BYPASS THIS LIMITATION AND
37 * RETURN OR ACCEPT A PL/SQL ARRAY FROM JAVA USING JDBC.
38 *
39 * --
40 * TYPE FLIGHTS_PLSQL_ARRAY IS TABLE OF FLIGHTS%ROWTYPE;
41 * --
42 * TYPE BOOKINGS_PLSQL_ARRAY IS VARRAY(30) OF BOOKINGS%ROWTYPE;
43 * --
44 *
45 * PROCEDURE GET_PLSQL_ARRAY_OF_FLIGHTS(P_CITY IN FLIGHTS.DEPARTURE_CITY%TYPE
46 * ,P_FLIGHTS_FROM OUT NOCOPY FLIGHTS_PLSQL_ARRAY);
47 * --
48 * PROCEDURE ADD_BOOKINGS_PLSQL_ARRAY (P_CUSTOMER IN CUSTOMERS%ROWTYPE
49 * ,P_BOOKING_TABLE IN BOOKINGS_PLSQL_ARRAY
50 * ,P_STATUS_MESSAGE OUT NOCOPY VARCHAR2);
51 * --
52 * END PACKAGE_ARRAY_EXAMPLE;
53 -----------------------------------------------------------------
54 * </pre></code>
55 * @author devteam60@orindabuild.com
56 * @version 1.1
57 */
58 public class PackageArrayExampleGetPlsqlArrayOfFlights extends AbstractProcCall implements OracleResourceUser
59 {
60
61 /**
62 * Variable to store P_CITY
63 */
64 public String paramPCity = null;
65
66 /**
67 * Variable to store P_FLIGHTS_FROM
68 */
69 public PackageArrayExampleFlightsPlsqlArray paramPFlightsFrom = null;
70
71 /**
72 * The name of the procedure or function we will access.
73 * This name be actually refer to a synonym or somebody else's code
74 */
75 public static final String procName = "PACKAGE_ARRAY_EXAMPLE.GET_PLSQL_ARRAY_OF_FLIGHTS";
76
77 /**
78 * Procedure Type
79 * What kind of function this is.
80 */
81 public static final int PROC_TYPE = AbstractProcCall.THIS_USERS_OBJECT;
82
83 /**
84 * Procedure call text
85 * The anonymous PL/SQL block that we use to run the procedure.
86 * This used to be a String but we now use a StringBuffer because
87 * procCall can be > 64K. We fill this in on first use.
88 */
89 private StringBuffer procCall = null;
90
91 /**
92 * Constructor that calls the Constructor of AbstractProcCall
93 *
94 */
95 public PackageArrayExampleGetPlsqlArrayOfFlights (Connection theConnection, LogInterface theLog)
96 {
97 super(theConnection, theLog);
98 theLog.debug("PackageArrayExampleGetPlsqlArrayOfFlights started"); //DEBUG
99 }
100
101
102 /**
103 * Method to set parameter P_CITY
104 * @param String paramPCity
105 */
106 public void setParamPCity(String paramPCity)
107 {
108 this.paramPCity = paramPCity;
109 }
110
111 /**
112 * Method to set parameter P_CITY to null
113 */
114 public void setParamPCityToNull()
115 {
116 this.paramPCity = null;
117 }
118
119
120 /**
121 * Method to get P_FLIGHTS_FROM
122 * @return PackageArrayExampleFlightsPlsqlArray
123 */
124 public PackageArrayExampleFlightsPlsqlArray getParamPFlightsFrom()
125 {
126 return(paramPFlightsFrom);
127 }
128
129
130 /**
131 * Unload OUT parameters
132 */
133 protected void getStatementResults() throws CSException
134 {
135 theLog.debug("Starting to unload data");
136
137 theParameters.unloadParameters(theCallableStatement);
138
139 // Unload parameter P_FLIGHTS_FROM
140
141 // Unload paramPFlightsFrom
142 theLog.debug("Unloading parameter paramPFlightsFrom" );
143 paramPFlightsFrom = (PackageArrayExampleFlightsPlsqlArray)theParameters.getParam(2);
144
145 try
146 {
147 theCallableStatement.clearParameters();
148 theParameters.clearParameters();
149 }
150 catch (SQLException e)
151 {
152 throw (new CSException("getStatementResults: Unable to clear parameters:" + e.toString()));
153 }
154 theLog.debug("Finished unloading data");
155 }
156
157
158
159 /**
160 * Associate parameters with statement
161 */
162 protected void bindParams() throws CSException
163 {
164 theLog.debug("Starting to bind parameters");
165 // Make sure P_FLIGHTS_FROM is not null
166 if (paramPFlightsFrom == null)
167 {
168 paramPFlightsFrom = new PackageArrayExampleFlightsPlsqlArray(theLog);
169 }
170
171 // Bind parameter P_CITY
172
173 theLog.debug("binding input parameter paramPCity to position 1");
174 theParameters.setParam(1,paramPCity);
175 // Bind parameter P_FLIGHTS_FROM
176 theLog.debug("binding output parameter paramPFlightsFrom to position 2" );
177 theParameters.setPlSqlTableArrayOutParam(2,paramPFlightsFrom);
178 theLog.debug("Associating parameters with statement");
179 theParameters.bindParameters(theCallableStatement);
180 theLog.debug("Finished binding parameters");
181 }
182
183
184 /**
185 * Return a SQL statement that will invoke this stored procedure
186 * This may be a synonym or owned by somebody else.
187 *
188 */
189 public String getProcCallStatement()
190 {
191 if (procCall == null)
192 {
193 procCall = new StringBuffer("DECLARE \n"); // 1
194 procCall.append("/* Created By JDBCWizard./a> 6.0.2776 */ \n"); // 2
195 procCall.append("/* Which can be obtained at www.orindasoft.com */ \n"); // 3
196 procCall.append("p_city VARCHAR2(16) := ?; \n"); // 4
197 procCall.append("P_FLIGHTS_FROM_T OSOFT446NDD6_A := OSOFT446NDD6_A(); \n"); // 5
198 procCall.append("p_flights_from PACKAGE_ARRAY_EXAMPLE.FLIGHTS_PLSQL_ARRAY := PACKAGE_ARRAY_EXAMPLE.FLIGHTS_PLSQL_ARRAY(); \n"); // 6
199 procCall.append("BEGIN \n"); // 7
200 procCall.append(" \n"); // 8
201 procCall.append("PACKAGE_ARRAY_EXAMPLE.GET_PLSQL_ARRAY_OF_FLIGHTS(p_city => p_city,p_flights_from => p_flights_from); \n"); // 9
202 procCall.append(" \n"); // 10
203 procCall.append("IF p_flights_from.COUNT > 0 THEN \n"); // 11
204 procCall.append(" P_FLIGHTS_FROM_T.EXTEND(p_flights_from.COUNT); \n"); // 12
205 procCall.append(" FOR i IN p_flights_from.FIRST..p_flights_from.LAST LOOP \n"); // 13
206 procCall.append(" P_FLIGHTS_FROM_T(i) := OSOFT446NDD6_T \n"); // 14
207 procCall.append(" (p_flights_from(i).DEPARTURE_CITY \n"); // 15
208 procCall.append(" ,p_flights_from(i).ARRIVAL_CITY \n"); // 16
209 procCall.append(" ,p_flights_from(i).AIRLINE_NAME \n"); // 17
210 procCall.append(" ,p_flights_from(i).FLIGHT_NUMBER \n"); // 18
211 procCall.append(" ,p_flights_from(i).DEPARTURE_TIME \n"); // 19
212 procCall.append(" ,p_flights_from(i).ARRIVAL_TIME \n"); // 20
213 procCall.append(" ,p_flights_from(i).AIRCRAFT_TYPE); \n"); // 21
214 procCall.append(" END LOOP; \n"); // 22
215 procCall.append("END IF; \n"); // 23
216 procCall.append("? := P_FLIGHTS_FROM_T; \n"); // 24
217 procCall.append("END; "); // 25 844 characters
218
219 }
220 return(procCall.toString());
221 }
222
223} // Generated by JDBCWizard./a> 6.0.2776
224