| PackageArrayExampleAddBookingsPlsqlArray.java |
1 package com.orindasoft.demo.generated.plsql;
2
3 import java.sql.*;
4
5 // From oracle 9.0.1 "oracle.jdbc.driver" is deprecated and
6 // its contents move to "oracle.jdbc". "oracle.jdbc.driver"
7 // still exists in 9.2.0.
8 import oracle.jdbc.OracleTypes;
9 // import oracle.jdbc.driver.OracleTypes;
10
11 import com.orindasoft.pub.LogInterface;
12 import com.orindasoft.pub.OracleResourceUser;
13 import com.orindasoft.pub.CSException;
14 import com.orindasoft.demo.generated.plsql.AbstractProcCall;
15
16 /**
17 * PackageArrayExampleAddBookingsPlsqlArray - Access Procedure PACKAGE_ARRAY_EXAMPLE.ADD_BOOKINGS_PLSQL_ARRAY
18 *
19 * <p>Generated by JDBCWizard./a> build 2776 at 2011/03/07 19:55:34.870 GMT
20 * <p>JDBCWizard./a> is made by Orinda Software Ltd, Dublin, Ireland
21 * <p>Target Database: Oracle 10.2.0
22 * <p> -------------------------------------------------------------------------
23 * <p> WARNING: This code will stop working around the time JDBCWizard./a> expires.
24 * <p> This restriction only exists in the demo version of JDBCWizard./a>.
25 * <p> -------------------------------------------------------------------------
26 * <p> JDBCWizard./a> Tips 9 of 19:
27 * <p> JDBCWizard./a> generated code is compatible with Javadoc
28 * <p> -------------------------------------------------------------------------
29 *
30 * <p>Copyright Orinda Software 2003-2009
31 * <p>Access Basis: User Object
32 * <p>PLSQL source code:
33 * <code><pre>
34 -----------------------------------------------------------------
35 * PACKAGE PACKAGE_ARRAY_EXAMPLE AS
36 * /*
37 * THIS EXAMPLE SHOWS HOW TO CALL PL/SQL PROCEDURE THAT TAKES A PARAMETER DEFINED
38 * IN A PL/SQL PACKAGE AS AN ARRAY OF %ROWTYPE (TABLE RECORD).
39 *
40 * THERE IS NO DIRECT WAY TO CALL THIS PL/SQL PROCEDURE FROM JAVA.
41 *
42 * INSTEAD WE USE ORINDABUILD TO CREATE JAVA JDBC CODE THAT CAN BYPASS THIS LIMITATION AND
43 * RETURN OR ACCEPT A PL/SQL ARRAY FROM JAVA USING JDBC.
44 *
45 * --
46 * TYPE FLIGHTS_PLSQL_ARRAY IS TABLE OF FLIGHTS%ROWTYPE;
47 * --
48 * TYPE BOOKINGS_PLSQL_ARRAY IS VARRAY(30) OF BOOKINGS%ROWTYPE;
49 * --
50 *
51 * PROCEDURE GET_PLSQL_ARRAY_OF_FLIGHTS(P_CITY IN FLIGHTS.DEPARTURE_CITY%TYPE
52 * ,P_FLIGHTS_FROM OUT NOCOPY FLIGHTS_PLSQL_ARRAY);
53 * --
54 * PROCEDURE ADD_BOOKINGS_PLSQL_ARRAY (P_CUSTOMER IN CUSTOMERS%ROWTYPE
55 * ,P_BOOKING_TABLE IN BOOKINGS_PLSQL_ARRAY
56 * ,P_STATUS_MESSAGE OUT NOCOPY VARCHAR2);
57 * --
58 * END PACKAGE_ARRAY_EXAMPLE;
59 -----------------------------------------------------------------
60 * </pre></code>
61 * @author devteam60@orindabuild.com
62 * @version 1.1
63 */
64 public class PackageArrayExampleAddBookingsPlsqlArray extends AbstractProcCall implements OracleResourceUser
65 {
66
67 /**
68 * Variable to store P_CUSTOMER
69 */
70 public Customers paramPCustomer = null;
71
72 /**
73 * Variable to store P_BOOKING_TABLE
74 */
75 public PackageArrayExampleBookingsPlsqlArray paramPBookingTable = null;
76
77 /**
78 * Variable to store P_STATUS_MESSAGE
79 */
80 public String paramPStatusMessage = null;
81
82 /**
83 * The name of the procedure or function we will access.
84 * This name be actually refer to a synonym or somebody else's code
85 */
86 public static final String procName = "PACKAGE_ARRAY_EXAMPLE.ADD_BOOKINGS_PLSQL_ARRAY";
87
88 /**
89 * Procedure Type
90 * What kind of function this is.
91 */
92 public static final int PROC_TYPE = AbstractProcCall.THIS_USERS_OBJECT;
93
94 /**
95 * Procedure call text
96 * The anonymous PL/SQL block that we use to run the procedure.
97 * This used to be a String but we now use a StringBuffer because
98 * procCall can be > 64K. We fill this in on first use.
99 */
100 private StringBuffer procCall = null;
101
102 /**
103 * Constructor that calls the Constructor of AbstractProcCall
104 *
105 */
106 public PackageArrayExampleAddBookingsPlsqlArray (Connection theConnection, LogInterface theLog)
107 {
108 super(theConnection, theLog);
109 theLog.debug("PackageArrayExampleAddBookingsPlsqlArray started"); //DEBUG
110 }
111
112
113 /**
114 * Method to set parameter P_CUSTOMER
115 * @param Customers paramPCustomer
116 */
117 public void setParamPCustomer(Customers paramPCustomer)
118 {
119 this.paramPCustomer = paramPCustomer;
120 }
121
122 /**
123 * Method to set parameter P_CUSTOMER to null
124 */
125 public void setParamPCustomerToNull()
126 {
127 this.paramPCustomer = null;
128 }
129
130 /**
131 * Method to set parameter P_BOOKING_TABLE
132 * @param PackageArrayExampleBookingsPlsqlArray paramPBookingTable
133 */
134 public void setParamPBookingTable(PackageArrayExampleBookingsPlsqlArray paramPBookingTable)
135 {
136 this.paramPBookingTable = paramPBookingTable;
137 }
138
139 /**
140 * Method to set parameter P_BOOKING_TABLE to null
141 */
142 public void setParamPBookingTableToNull()
143 {
144 this.paramPBookingTable = null;
145 }
146
147
148 /**
149 * Method to get P_STATUS_MESSAGE
150 * @return String
151 */
152 public String getParamPStatusMessage()
153 {
154 if (paramPStatusMessage == null)
155 {
156 return(null);
157 }
158
159 return (new String(paramPStatusMessage.toString()));
160 }
161
162
163 /**
164 * Unload OUT parameters
165 */
166 protected void getStatementResults() throws CSException
167 {
168 theLog.debug("Starting to unload data");
169
170 theParameters.unloadParameters(theCallableStatement);
171
172 // Unload parameter P_STATUS_MESSAGE
173
174 theLog.debug("Unloading parameter paramPStatusMessage" );
175 paramPStatusMessage = (String)theParameters.getParam(9);
176
177 try
178 {
179 theCallableStatement.clearParameters();
180 theParameters.clearParameters();
181 }
182 catch (SQLException e)
183 {
184 throw (new CSException("getStatementResults: Unable to clear parameters:" + e.toString()));
185 }
186 theLog.debug("Finished unloading data");
187 }
188
189
190
191 /**
192 * Associate parameters with statement
193 */
194 protected void bindParams() throws CSException
195 {
196 theLog.debug("Starting to bind parameters");
197 // Make sure P_BOOKING_TABLE is not null
198 if (paramPBookingTable == null)
199 {
200 paramPBookingTable = new PackageArrayExampleBookingsPlsqlArray(theLog);
201 }
202
203 // Bind parameter P_CUSTOMER
204 // Prevent null pointer exception later on
205 if (paramPCustomer == null)
206 {
207 paramPCustomer = new Customers(theLog);
208 }
209
210 // Bind parameter NAME
211
212 theLog.debug("binding input parameter paramPCustomer.paramName to position 1");
213 theParameters.setParam(1,paramPCustomer.paramName);
214 // Bind parameter ADDRESS
215
216 theLog.debug("binding input parameter paramPCustomer.paramAddress to position 2");
217 theParameters.setParam(2,paramPCustomer.paramAddress);
218 // Bind parameter CITY
219
220 theLog.debug("binding input parameter paramPCustomer.paramCity to position 3");
221 theParameters.setParam(3,paramPCustomer.paramCity);
222 // Bind parameter STATE
223
224 theLog.debug("binding input parameter paramPCustomer.paramState to position 4");
225 theParameters.setParam(4,paramPCustomer.paramState);
226 // Bind parameter ZIP
227
228 theLog.debug("binding input parameter paramPCustomer.paramZip to position 5");
229 theParameters.setParam(5,paramPCustomer.paramZip);
230 // Bind parameter BIRTHDATE
231
232 theLog.debug("binding input parameter paramPCustomer.paramBirthdate to position 6");
233 if (paramPCustomer.paramBirthdate != null)
234 {
235 theParameters.setParam(6,paramPCustomer.paramBirthdate);
236 }
237 else
238 {
239 theParameters.setParam(6,(java.util.Date)null);
240 }
241 // Bind parameter PHONE
242
243 theLog.debug("binding input parameter paramPCustomer.paramPhone to position 7");
244 theParameters.setParam(7,paramPCustomer.paramPhone);
245 // Bind parameter P_BOOKING_TABLE
246
247 theLog.debug("binding input parameter paramPBookingTable to position 8");
248 try
249 {
250 theParameters.setPlSqlTableArrayParam(8,paramPBookingTable);
251 }
252 catch (Exception e)
253 {
254 throw (new CSException("PL/SQL Array Input Parameter paramPBookingTable could not be loaded:" + e.toString()));
255 }
256
257 // Bind parameter P_STATUS_MESSAGE
258 theLog.debug("binding output parameter paramPStatusMessage to position 9" );
259 theParameters.setOutParam(9,OracleTypes.VARCHAR);
260 theLog.debug("Associating parameters with statement");
261 theParameters.bindParameters(theCallableStatement);
262 theLog.debug("Finished binding parameters");
263 }
264
265
266 /**
267 * Return a SQL statement that will invoke this stored procedure
268 * This may be a synonym or owned by somebody else.
269 *
270 */
271 public String getProcCallStatement()
272 {
273 if (procCall == null)
274 {
275 procCall = new StringBuffer("DECLARE \n"); // 1
276 procCall.append("/* Created By JDBCWizard./a> 6.0.2776 */ \n"); // 2
277 procCall.append("/* Which can be obtained at www.orindasoft.com */ \n"); // 3
278 procCall.append("p_customer CUSTOMERS%ROWTYPE; \n"); // 4
279 procCall.append("P_BOOKING_TABLE_T OSOFT46ND2_A := OSOFT46ND2_A(); \n"); // 5
280 procCall.append("p_booking_table PACKAGE_ARRAY_EXAMPLE.BOOKINGS_PLSQL_ARRAY := PACKAGE_ARRAY_EXAMPLE.BOOKINGS_PLSQL_ARRAY(); \n"); // 6
281 procCall.append("p_status_message VARCHAR2(4000) := null; \n"); // 7
282 procCall.append("BEGIN \n"); // 8
283 procCall.append("p_customer.name := ?; \n"); // 9
284 procCall.append("p_customer.address := ?; \n"); // 10
285 procCall.append("p_customer.city := ?; \n"); // 11
286 procCall.append("p_customer.state := ?; \n"); // 12
287 procCall.append("p_customer.zip := ?; \n"); // 13
288 procCall.append("p_customer.birthdate := ?; \n"); // 14
289 procCall.append("p_customer.phone := ?; \n"); // 15
290 procCall.append("P_BOOKING_TABLE_T := ?; \n"); // 16
291 procCall.append("p_status_message := null; \n"); // 17
292 procCall.append("IF P_BOOKING_TABLE_T.COUNT > 0 THEN \n"); // 18
293 procCall.append(" p_booking_table.EXTEND(P_BOOKING_TABLE_T.COUNT); \n"); // 19
294 procCall.append(" FOR i IN P_BOOKING_TABLE_T.FIRST..P_BOOKING_TABLE_T.LAST LOOP \n"); // 20
295 procCall.append(" p_booking_table(i).CUSTOMER_NAME := P_BOOKING_TABLE_T(i).COL_0; \n"); // 21
296 procCall.append(" p_booking_table(i).AIRLINE_NAME := P_BOOKING_TABLE_T(i).COL_1; \n"); // 22
297 procCall.append(" p_booking_table(i).FLIGHT_NUMBER := P_BOOKING_TABLE_T(i).COL_2; \n"); // 23
298 procCall.append(" p_booking_table(i).DEPARTURE_TIME := P_BOOKING_TABLE_T(i).COL_3; \n"); // 24
299 procCall.append(" p_booking_table(i).SEAT := P_BOOKING_TABLE_T(i).COL_4; \n"); // 25
300 procCall.append(" END LOOP; \n"); // 26
301 procCall.append("END IF; \n"); // 27
302 procCall.append(" \n"); // 28
303 procCall.append("PACKAGE_ARRAY_EXAMPLE.ADD_BOOKINGS_PLSQL_ARRAY(p_customer => p_customer,p_booking_table => p_booking_table,p_status_message => p_status_message); \n"); // 29
304 procCall.append("? := p_status_message; \n"); // 30
305 procCall.append("END; "); // 31 1212 characters
306
307 }
308 return(procCall.toString());
309 }
310
311} // Generated by JDBCWizard./a> 6.0.2776
312