| CursorExampleGetlists.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 * CursorExampleGetlists - Access Procedure CURSOR_EXAMPLE.GETLISTS
18 *
19 * <p>Generated by JDBCWizard./a> build 2776 at 2011/03/07 19:55:33.922 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 1 of 19:
27 * <p> If this was a full copy of JDBCWizard./a> you would own this code.
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 CURSOR_EXAMPLE AS
36 * --
37 * /*
38 * THIS EXAMPLE SHOWS HOW TO CALL PL/SQL PROCEDURE THAT RETURNS ONE OR MORE CURSORS FROM JAVA.
39 *
40 *
41 * --
42 * TYPE AIRLINE_REFCURSOR_TYPE IS REF CURSOR RETURN AIRLINES%ROWTYPE;
43 * --
44 * TYPE AIRPORT_REFCURSOR_TYPE IS REF CURSOR RETURN AIRPORTS%ROWTYPE;
45 * --
46 * TYPE AIRCRAFT_REFCURSOR_TYPE IS REF CURSOR RETURN AIRCRAFT%ROWTYPE;
47 * --
48 * TYPE FLIGHTS_REFCURSOR_TYPE IS REF CURSOR RETURN FLIGHTS%ROWTYPE;
49 * --
50 * PROCEDURE GETLISTS(P_AIRLINE_LISTING OUT AIRLINE_REFCURSOR_TYPE
51 * ,P_AIRPORT_LISTING OUT AIRPORT_REFCURSOR_TYPE
52 * ,P_AIRCRAFT_LISTING OUT AIRCRAFT_REFCURSOR_TYPE);
53 * --
54 * FUNCTION DIRECT_FLIGHT_AVAILABLE(P_FROMCITY IN AIRPORTS.AIRPORT_CODE%TYPE
55 * ,P_TOCITY IN AIRPORTS.AIRPORT_CODE%TYPE) RETURN BOOLEAN;
56 * --
57 * PROCEDURE FIND_DIRECT_FLIGHTS (P_FROMCITY IN AIRPORTS.AIRPORT_CODE%TYPE
58 * ,P_TOCITY IN AIRPORTS.AIRPORT_CODE%TYPE
59 * ,P_ORDERBY IN NUMBER
60 * ,P_FLIGHTLIST OUT FLIGHTS_REFCURSOR_TYPE);
61 * --
62 * END;
63 -----------------------------------------------------------------
64 * </pre></code>
65 * @author devteam60@orindabuild.com
66 * @version 1.1
67 */
68 public class CursorExampleGetlists extends AbstractProcCall implements OracleResourceUser
69 {
70
71 /**
72 * Variable to store P_AIRLINE_LISTING
73 */
74 public com.orindasoft.pub.ReadOnlyRowSet paramPAirlineListing = null;
75
76 /**
77 * Variable to store P_AIRPORT_LISTING
78 */
79 public com.orindasoft.pub.ReadOnlyRowSet paramPAirportListing = null;
80
81 /**
82 * Variable to store P_AIRCRAFT_LISTING
83 */
84 public com.orindasoft.pub.ReadOnlyRowSet paramPAircraftListing = null;
85
86 /**
87 * Variable to store Buffer Size for file access.
88 */
89 protected int bufferSize = 4096;
90
91 /**
92 * boolean flag that specifies whether lobs such as CLOBS, BLOBS and BFILES
93 * will be kept as Oracle objects or downloaded into Files.
94 * @since 4.0.1847
95 */
96 protected boolean keepLobs = false;
97
98 /**
99 * boolean flag that controls whether LOBs are turned into byte[]
100 * @since 5.0.2314
101 */
102 protected boolean useByteArraysForLongsAndLOBS = false;
103
104 /**
105 * Variable to store boolean flag that indicates whether created files should be deleted when the JVM exits.
106 */
107 protected boolean keepFiles = true;
108
109 /**
110 * Variable to store temporary directory for downloaded files.
111 */
112 protected java.io.File tempFileDir = new java.io.File (System.getProperty("user.dir"));
113
114 /**
115 * Variable to store Long object loader.
116 */
117 protected com.orindasoft.pub.LongObjectLoader objectLoader = new com.orindasoft.pub.LongObjectLoader();
118
119 /**
120 * The Prefix for downloaded files containing BLOB, CLOB and BFILE data
121 */
122 protected String tempFilePrefix = "OSOFT";
123
124 /**
125 * The Suffix for downloaded files containing BLOB, CLOB and BFILE data
126 */
127 protected String tempFileSuffix = ".tmp";
128
129 /**
130 * If set to true this flag causes Temporary LOB's to be created if no
131 * LOB parameter has been set by the time the database is accessed
132 * @since JDBCWizard./a> 4.0.2107 / Oracle 9.0.1
133 */
134 protected boolean createTempLobsIfNeeded = true;
135
136 /**
137 * If true this flag causes Temporary LOB's to be deleted after
138 * the database is accessed
139 * @since JDBCWizard./a> 4.0.2107 / Oracle 9.0.1
140 */
141 protected boolean deleteTempLobsAfterCall = false;
142
143 /**
144 * Variable to store max rows for ReadOnlyRowSet
145 */
146 protected int maxRows = 10000;
147
148 /**
149 * The name of the procedure or function we will access.
150 * This name be actually refer to a synonym or somebody else's code
151 */
152 public static final String procName = "CURSOR_EXAMPLE.GETLISTS";
153
154 /**
155 * Procedure Type
156 * What kind of function this is.
157 */
158 public static final int PROC_TYPE = AbstractProcCall.THIS_USERS_OBJECT;
159
160 /**
161 * Procedure call text
162 * The anonymous PL/SQL block that we use to run the procedure.
163 * This used to be a String but we now use a StringBuffer because
164 * procCall can be > 64K. We fill this in on first use.
165 */
166 private StringBuffer procCall = null;
167
168 /**
169 * Constructor that calls the Constructor of AbstractProcCall
170 *
171 */
172 public CursorExampleGetlists (Connection theConnection, LogInterface theLog)
173 {
174 super(theConnection, theLog);
175 theLog.debug("CursorExampleGetlists started"); //DEBUG
176 }
177
178
179 /**
180 * Set new file io buffer size
181 * @param int bufferSize A new Buffer size in bytes.
182 */
183 public void setBufferSize(int bufferSize)
184 {
185 this.bufferSize = bufferSize;
186 theLog.debug("bufferSize set to " + bufferSize);
187 }
188
189 /**
190 * Set keepLobs
191 * @param boolean keepLobs Keep Lobs as pointers rather than turn them into files.
192 * @since 4.0.1847
193 */
194 public void setKeepLobs(boolean keepLobs )
195 {
196 this.keepLobs = keepLobs;
197 theLog.debug("keepLobs set to " + keepLobs);
198 }
199
200 /**
201 * Set useByteArraysForLongsAndLOBS
202 * @param boolean useByteArraysForLongsAndLOBS Turn lobs into byte[]
203 * @since 5.0.2314
204 */
205 public void setUseByteArraysForLongsAndLOBS(boolean useByteArraysForLongsAndLOBS)
206 {
207 this.useByteArraysForLongsAndLOBS = useByteArraysForLongsAndLOBS;
208 theLog.debug("useByteArraysForLongsAndLOBSset to " + useByteArraysForLongsAndLOBS);
209 }
210
211 /**
212 * Set keepFiles
213 * @param boolean keepFiles Keep generated files after JVM exits
214 */
215 public void setKeepFiles(boolean keepFiles )
216 {
217 this.keepFiles = keepFiles;
218 theLog.debug("keepFiles set to " + keepFiles);
219 }
220
221 /**
222 * Delete all generated files
223 * This method deletes all Files created by accessing CLOB's, BLOB's, etc
224 * It was added because 'setKeepFiles' only works when the JVM exits, which
225 * could be several days in the future in some scenarios. Only use this method if
226 * you want <b>all<b> your generated files to be deleted.
227 * @since 2.0.1176
228 */
229 public void deleteGeneratedFiles()
230 {
231 theLog.debug("Starting to delete files if they exist");
232 theLog.debug("Delete files owned by ReadOnlyRowSetparamPAirlineListing if they exist");
233 // Delete ReadOnlyRowSet files for P_AIRLINE_LISTING
234 if (paramPAirlineListing != null)
235 {
236 // Because the fields in a cursor aren't known up front we
237 // have no way of telling whether any ReadOnlyRowSet
238 // has files in it so call the delete method regardless
239 paramPAirlineListing.deleteGeneratedFiles();
240 }
241
242 theLog.debug("Delete files owned by ReadOnlyRowSetparamPAirportListing if they exist");
243 // Delete ReadOnlyRowSet files for P_AIRPORT_LISTING
244 if (paramPAirportListing != null)
245 {
246 // Because the fields in a cursor aren't known up front we
247 // have no way of telling whether any ReadOnlyRowSet
248 // has files in it so call the delete method regardless
249 paramPAirportListing.deleteGeneratedFiles();
250 }
251
252 theLog.debug("Delete files owned by ReadOnlyRowSetparamPAircraftListing if they exist");
253 // Delete ReadOnlyRowSet files for P_AIRCRAFT_LISTING
254 if (paramPAircraftListing != null)
255 {
256 // Because the fields in a cursor aren't known up front we
257 // have no way of telling whether any ReadOnlyRowSet
258 // has files in it so call the delete method regardless
259 paramPAircraftListing.deleteGeneratedFiles();
260 }
261
262 theLog.debug("Finished deleting files");
263 }
264
265 /**
266 * Set temporary directory
267 * @param java.io.File tempFileDir a new Temporary Directory
268 * @throws CSException if the directory is not viable
269 */
270 public void setTempDir(java.io.File tempFileDir) throws CSException
271 {
272 if (tempFileDir == null)
273 {
274 throw (new CSException("Attempt made to set tempFileDir to null"));
275 }
276 else if (! tempFileDir.exists())
277 {
278 try
279 {
280 theLog.info("Creating temporary directory " + tempFileDir.getAbsolutePath());
281 tempFileDir.mkdirs();
282 }
283 catch (Exception e)
284 {
285 throw (new CSException("tempFileDir " + tempFileDir.getAbsolutePath() + " can not be created"));
286 }
287 }
288
289 this.tempFileDir = tempFileDir;
290 theLog.debug("tempFileDir set to " + tempFileDir.getAbsolutePath());
291 }
292
293 /**
294 * Set the prefix used for generating temporary files
295 */
296 public void setTempFilePrefix(String tempFilePrefix)
297 {
298 this.tempFilePrefix = tempFilePrefix;
299 }
300
301 /**
302 * Set the suffix used for generating temporary files
303 */
304 public void setTempFileSuffix(String tempFileSuffix)
305 {
306 this.tempFileSuffix = tempFileSuffix;
307 }
308
309 /**
310 * Set flag that controls whether Temporary Lobs are created before
311 * each database call if no LOB parameter has been set
312 * @since JDBCWizard./a> 4.0.2107 / Oracle 9.0.1
313 */
314 public void setCreateTempLobsIfNeeded(boolean createTempLobsIfNeeded)
315 {
316 this.createTempLobsIfNeeded = createTempLobsIfNeeded;
317 }
318
319 /**
320 * Set flag that controls whether Temporary Lobs are deleted after each database call
321 * @since JDBCWizard./a> 4.0.2107 / Oracle 9.0.1
322 */
323 public void setDeleteTempLobsAfterCall(boolean deleteTempLobsAfterCall)
324 {
325 this.deleteTempLobsAfterCall = deleteTempLobsAfterCall;
326 }
327
328 /**
329 * Set limit to number of rows returned in ReadOnlyRowSets
330 * @param int maxRows Maximum number of rows that will be retrieved. Some JDBC drivers will
331 * stop returning rows after about 30,000.
332 */
333 public void setMaxRows(int maxRows)
334 {
335 this.maxRows = maxRows;
336 theLog.debug("maxRows set to " + maxRows);
337 }
338
339 /**
340 * Get number of rows returned in ReadOnlyRowSets
341 * @return int maxRows Maximum number of rows that will be retrieved.
342 */
343 public int getMaxRows()
344 {
345 return(maxRows);
346 }
347
348
349 /**
350 * Method to get P_AIRLINE_LISTING
351 * @return com.orindasoft.pub.ReadOnlyRowSet
352 * @throws NullPointerException if P_AIRLINE_LISTING is null
353 */
354 public com.orindasoft.pub.ReadOnlyRowSet getParamPAirlineListing()
355 {
356 return (paramPAirlineListing);
357 }
358
359 /**
360 * Return paramPAirlineListing as an array of CursorExampleAirlineRefcursorTypeAttrs
361 * @return CursorExampleAirlineRefcursorTypeAttrs[] an Array of CursorExampleAirlineRefcursorTypeAttrs
362 * @throws CSException
363 * @since JDBCWizard./a> V4.0.2160
364 */
365 public CursorExampleAirlineRefcursorTypeAttrs[] getParamPAirlineListingArray() throws CSException
366 {
367 if (paramPAirlineListing == null)
368 {
369 return(null);
370 }
371
372 return ((CursorExampleAirlineRefcursorTypeAttrs[])CursorExampleAirlineRefcursorType.createCursorExampleAirlineRefcursorTypeArrayFromRowSet(paramPAirlineListing,theLog));
373 }
374
375
376 /**
377 * Method to get P_AIRPORT_LISTING
378 * @return com.orindasoft.pub.ReadOnlyRowSet
379 * @throws NullPointerException if P_AIRPORT_LISTING is null
380 */
381 public com.orindasoft.pub.ReadOnlyRowSet getParamPAirportListing()
382 {
383 return (paramPAirportListing);
384 }
385
386 /**
387 * Return paramPAirportListing as an array of CursorExampleAirportRefcursorTypeAttrs
388 * @return CursorExampleAirportRefcursorTypeAttrs[] an Array of CursorExampleAirportRefcursorTypeAttrs
389 * @throws CSException
390 * @since JDBCWizard./a> V4.0.2160
391 */
392 public CursorExampleAirportRefcursorTypeAttrs[] getParamPAirportListingArray() throws CSException
393 {
394 if (paramPAirportListing == null)
395 {
396 return(null);
397 }
398
399 return ((CursorExampleAirportRefcursorTypeAttrs[])CursorExampleAirportRefcursorType.createCursorExampleAirportRefcursorTypeArrayFromRowSet(paramPAirportListing,theLog));
400 }
401
402
403 /**
404 * Method to get P_AIRCRAFT_LISTING
405 * @return com.orindasoft.pub.ReadOnlyRowSet
406 * @throws NullPointerException if P_AIRCRAFT_LISTING is null
407 */
408 public com.orindasoft.pub.ReadOnlyRowSet getParamPAircraftListing()
409 {
410 return (paramPAircraftListing);
411 }
412
413 /**
414 * Return paramPAircraftListing as an array of CursorExampleAircraftRefcursorTypeAttrs
415 * @return CursorExampleAircraftRefcursorTypeAttrs[] an Array of CursorExampleAircraftRefcursorTypeAttrs
416 * @throws CSException
417 * @since JDBCWizard./a> V4.0.2160
418 */
419 public CursorExampleAircraftRefcursorTypeAttrs[] getParamPAircraftListingArray() throws CSException
420 {
421 if (paramPAircraftListing == null)
422 {
423 return(null);
424 }
425
426 return ((CursorExampleAircraftRefcursorTypeAttrs[])CursorExampleAircraftRefcursorType.createCursorExampleAircraftRefcursorTypeArrayFromRowSet(paramPAircraftListing,theLog));
427 }
428
429
430 /**
431 * Get current file io buffer size
432 * @return int bufferSize Buffer size in bytes.
433 */
434 public int getBufferSize()
435 {
436 return (bufferSize);
437 }
438
439 /**
440 * Get keepLobs flag
441 * @return int <code>true</code> if LOBS are left as they are found in the DB
442 * @return int <code>false</code> if LOBS are turned into files on retrieval.
443 * @since 4.0.1847
444 */
445 public boolean getKeepLobs()
446 {
447 return (keepLobs);
448 }
449
450 /**
451 * Get flag that controls whether LOBs are turned into byte[].
452 * @return boolean useByteArraysForLongsAndLOBS
453 * @since 5.0.2314
454 */
455 public boolean getUseByteArraysForLongsAndLOBS()
456 {
457 return (useByteArraysForLongsAndLOBS);
458 }
459
460 /**
461 * Get keepFiles flag
462 * @return int <code>true</code> if temporary files are kept after the JVM exits.
463 * @return int <code>false</code> if temporary files are deleted after the JVM exits.
464 */
465 public boolean getKeepFiles()
466 {
467 return (keepFiles);
468 }
469
470 /**
471 * Get temporary directory
472 * @return java.io.File tempFileDir the temporary Directory
473 */
474 public java.io.File getTempFileDir()
475 {
476 return(tempFileDir);
477 }
478
479 /**
480 * Get the prefix used for generating temporary files
481 */
482 public String getTempFilePrefix()
483 {
484 return (tempFilePrefix);
485 }
486
487 /**
488 * Get the suffix used for generating temporary files
489 */
490 public String getTempFileSuffix()
491 {
492 return (tempFileSuffix);
493 }
494
495
496 /**
497 * Unload OUT parameters
498 */
499 protected void getStatementResults() throws CSException
500 {
501 theLog.debug("Starting to unload data");
502 objectLoader.setBufferSize(bufferSize);
503 objectLoader.setKeepFiles(keepFiles);
504
505 theParameters.unloadParameters(theCallableStatement);
506
507 // Unload parameter P_AIRLINE_LISTING
508
509 theLog.debug("Unloading parameter paramPAirlineListing" );
510 try
511 {
512 paramPAirlineListing = new com.orindasoft.pub.ReadOnlyRowSet((ResultSet)theParameters.getParam(1), getProcCallStatement(),maxRows, theLog,tempFileDir,keepFiles,tempFilePrefix,tempFileSuffix,keepLobs, useByteArraysForLongsAndLOBS);
513 }
514 catch (com.orindasoft.pub.CSDBNullObjectException e)
515 // A CSDBNullObjectException here means that a stored procedure or function
516 // has returned a null Ref Cursor
517 {
518 paramPAirlineListing = null;
519 theLog.debug("Ref Cursor for paramPAirlineListing is null. No ReadOnlyRowSet created." );
520 }
521
522 // Unload parameter P_AIRPORT_LISTING
523
524 theLog.debug("Unloading parameter paramPAirportListing" );
525 try
526 {
527 paramPAirportListing = new com.orindasoft.pub.ReadOnlyRowSet((ResultSet)theParameters.getParam(2), getProcCallStatement(),maxRows, theLog,tempFileDir,keepFiles,tempFilePrefix,tempFileSuffix,keepLobs, useByteArraysForLongsAndLOBS);
528 }
529 catch (com.orindasoft.pub.CSDBNullObjectException e)
530 // A CSDBNullObjectException here means that a stored procedure or function
531 // has returned a null Ref Cursor
532 {
533 paramPAirportListing = null;
534 theLog.debug("Ref Cursor for paramPAirportListing is null. No ReadOnlyRowSet created." );
535 }
536
537 // Unload parameter P_AIRCRAFT_LISTING
538
539 theLog.debug("Unloading parameter paramPAircraftListing" );
540 try
541 {
542 paramPAircraftListing = new com.orindasoft.pub.ReadOnlyRowSet((ResultSet)theParameters.getParam(3), getProcCallStatement(),maxRows, theLog,tempFileDir,keepFiles,tempFilePrefix,tempFileSuffix,keepLobs, useByteArraysForLongsAndLOBS);
543 }
544 catch (com.orindasoft.pub.CSDBNullObjectException e)
545 // A CSDBNullObjectException here means that a stored procedure or function
546 // has returned a null Ref Cursor
547 {
548 paramPAircraftListing = null;
549 theLog.debug("Ref Cursor for paramPAircraftListing is null. No ReadOnlyRowSet created." );
550 }
551
552 try
553 {
554 theCallableStatement.clearParameters();
555 theParameters.clearParameters();
556 }
557 catch (SQLException e)
558 {
559 throw (new CSException("getStatementResults: Unable to clear parameters:" + e.toString()));
560 }
561 theLog.debug("Finished unloading data");
562 }
563
564
565
566 /**
567 * Associate parameters with statement
568 */
569 protected void bindParams() throws CSException
570 {
571 theLog.debug("Starting to bind parameters");
572 // Bind parameter P_AIRLINE_LISTING
573 theLog.debug("binding output parameter paramPAirlineListing to position 1" );
574 theParameters.setOutParam(1,OracleTypes.CURSOR);
575 // Bind parameter P_AIRPORT_LISTING
576 theLog.debug("binding output parameter paramPAirportListing to position 2" );
577 theParameters.setOutParam(2,OracleTypes.CURSOR);
578 // Bind parameter P_AIRCRAFT_LISTING
579 theLog.debug("binding output parameter paramPAircraftListing to position 3" );
580 theParameters.setOutParam(3,OracleTypes.CURSOR);
581 theLog.debug("Associating parameters with statement");
582 theParameters.bindParameters(theCallableStatement);
583 theLog.debug("Finished binding parameters");
584 }
585
586
587 /**
588 * Return a SQL statement that will invoke this stored procedure
589 * This may be a synonym or owned by somebody else.
590 *
591 */
592 public String getProcCallStatement()
593 {
594 if (procCall == null)
595 {
596 procCall = new StringBuffer("DECLARE \n"); // 1
597 procCall.append("/* Created By JDBCWizard./a> 6.0.2776 */ \n"); // 2
598 procCall.append("/* Which can be obtained at www.orindasoft.com */ \n"); // 3
599 procCall.append("TYPE P_AIRLINE_LISTING_RC IS REF CURSOR; \n"); // 4
600 procCall.append("p_airline_listing P_AIRLINE_LISTING_RC; \n"); // 5
601 procCall.append("TYPE P_AIRPORT_LISTING_RC IS REF CURSOR; \n"); // 6
602 procCall.append("p_airport_listing P_AIRPORT_LISTING_RC; \n"); // 7
603 procCall.append("TYPE P_AIRCRAFT_LISTING_RC IS REF CURSOR; \n"); // 8
604 procCall.append("p_aircraft_listing P_AIRCRAFT_LISTING_RC; \n"); // 9
605 procCall.append("BEGIN \n"); // 10
606 procCall.append(" \n"); // 11
607 procCall.append("CURSOR_EXAMPLE.GETLISTS(p_airline_listing => p_airline_listing,p_airport_listing => p_airport_listing,p_aircraft_listing => p_aircraft_listing); \n"); // 12
608 procCall.append("? := p_airline_listing; \n"); // 13
609 procCall.append("? := p_airport_listing; \n"); // 14
610 procCall.append("? := p_aircraft_listing; \n"); // 15
611 procCall.append("END; "); // 16 554 characters
612
613 }
614 return(procCall.toString());
615 }
616
617} // Generated by JDBCWizard./a> 6.0.2776
618