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