| CursorExampleDirectFlightAvailable.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 * CursorExampleDirectFlightAvailable - Access Procedure CURSOR_EXAMPLE.DIRECT_FLIGHT_AVAILABLE
18 *
19 * <p>Generated by JDBCWizard./a> build 2776 at 2011/03/07 19:55:33.309 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 10 of 19:
27 * <p> JDBCWizard./a> allows you to run complex stored procedures without
28 * <p> using a precompiler such as SQLJ
29 * <p> -------------------------------------------------------------------------
30 *
31 * <p>Copyright Orinda Software 2003-2009
32 * <p>Access Basis: User Object
33 * <p>PLSQL source code:
34 * <code><pre>
35 -----------------------------------------------------------------
36 * PACKAGE CURSOR_EXAMPLE AS
37 * --
38 * /*
39 * THIS EXAMPLE SHOWS HOW TO CALL PL/SQL PROCEDURE THAT RETURNS ONE OR MORE CURSORS FROM JAVA.
40 *
41 *
42 * --
43 * TYPE AIRLINE_REFCURSOR_TYPE IS REF CURSOR RETURN AIRLINES%ROWTYPE;
44 * --
45 * TYPE AIRPORT_REFCURSOR_TYPE IS REF CURSOR RETURN AIRPORTS%ROWTYPE;
46 * --
47 * TYPE AIRCRAFT_REFCURSOR_TYPE IS REF CURSOR RETURN AIRCRAFT%ROWTYPE;
48 * --
49 * TYPE FLIGHTS_REFCURSOR_TYPE IS REF CURSOR RETURN FLIGHTS%ROWTYPE;
50 * --
51 * PROCEDURE GETLISTS(P_AIRLINE_LISTING OUT AIRLINE_REFCURSOR_TYPE
52 * ,P_AIRPORT_LISTING OUT AIRPORT_REFCURSOR_TYPE
53 * ,P_AIRCRAFT_LISTING OUT AIRCRAFT_REFCURSOR_TYPE);
54 * --
55 * FUNCTION DIRECT_FLIGHT_AVAILABLE(P_FROMCITY IN AIRPORTS.AIRPORT_CODE%TYPE
56 * ,P_TOCITY IN AIRPORTS.AIRPORT_CODE%TYPE) RETURN BOOLEAN;
57 * --
58 * PROCEDURE FIND_DIRECT_FLIGHTS (P_FROMCITY IN AIRPORTS.AIRPORT_CODE%TYPE
59 * ,P_TOCITY IN AIRPORTS.AIRPORT_CODE%TYPE
60 * ,P_ORDERBY IN NUMBER
61 * ,P_FLIGHTLIST OUT FLIGHTS_REFCURSOR_TYPE);
62 * --
63 * END;
64 -----------------------------------------------------------------
65 * </pre></code>
66 * @author devteam60@orindabuild.com
67 * @version 1.1
68 */
69 public class CursorExampleDirectFlightAvailable extends AbstractProcCall implements OracleResourceUser
70 {
71
72 /**
73 * Variable to store result of calling function
74 */
75 public Boolean functionResult = null;
76
77 /**
78 * Variable to store P_FROMCITY
79 */
80 public String paramPFromcity = null;
81
82 /**
83 * Variable to store P_TOCITY
84 */
85 public String paramPTocity = null;
86
87 /**
88 * The name of the procedure or function we will access.
89 * This name be actually refer to a synonym or somebody else's code
90 */
91 public static final String procName = "CURSOR_EXAMPLE.DIRECT_FLIGHT_AVAILABLE";
92
93 /**
94 * Procedure Type
95 * What kind of function this is.
96 */
97 public static final int PROC_TYPE = AbstractProcCall.THIS_USERS_OBJECT;
98
99 /**
100 * Procedure call text
101 * The anonymous PL/SQL block that we use to run the procedure.
102 * This used to be a String but we now use a StringBuffer because
103 * procCall can be > 64K. We fill this in on first use.
104 */
105 private StringBuffer procCall = null;
106
107 /**
108 * Constructor that calls the Constructor of AbstractProcCall
109 *
110 */
111 public CursorExampleDirectFlightAvailable (Connection theConnection, LogInterface theLog)
112 {
113 super(theConnection, theLog);
114 theLog.debug("CursorExampleDirectFlightAvailable started"); //DEBUG
115 }
116
117
118 /**
119 * Method to set parameter P_FROMCITY
120 * @param String paramPFromcity
121 */
122 public void setParamPFromcity(String paramPFromcity)
123 {
124 this.paramPFromcity = paramPFromcity;
125 }
126
127 /**
128 * Method to set parameter P_FROMCITY to null
129 */
130 public void setParamPFromcityToNull()
131 {
132 this.paramPFromcity = null;
133 }
134
135 /**
136 * Method to set parameter P_TOCITY
137 * @param String paramPTocity
138 */
139 public void setParamPTocity(String paramPTocity)
140 {
141 this.paramPTocity = paramPTocity;
142 }
143
144 /**
145 * Method to set parameter P_TOCITY to null
146 */
147 public void setParamPTocityToNull()
148 {
149 this.paramPTocity = null;
150 }
151
152
153 /**
154 * Method to get _function_result_1
155 * @return Boolean
156 */
157 public Boolean getFunctionResultBooleanObj()
158 {
159 if (functionResult == null)
160 {
161 return(null);
162 }
163
164 return (new Boolean(functionResult.booleanValue()));
165 }
166
167 /**
168 * Method to get _function_result_1
169 * @return boolean
170 * @throws NullPointerException if _function_result_1 is null
171 */
172 public boolean getFunctionResultBoolean()
173 {
174 return (functionResult.booleanValue());
175 }
176
177 /**
178 * Method to get _function_result_1 or a default value
179 * @param defaultValue boolean
180 * @return boolean
181 * @since JDBCWizard./a> V5.0.2293
182 */
183 public boolean getFunctionResultBoolean(boolean defaultValue)
184 {
185 if (functionResult == null)
186 {
187 return(defaultValue);
188 }
189
190 return (functionResult.booleanValue());
191 }
192
193
194 /**
195 * Unload OUT parameters
196 */
197 protected void getStatementResults() throws CSException
198 {
199 theLog.debug("Starting to unload data");
200
201 theParameters.unloadParameters(theCallableStatement);
202
203 // Unload parameter that will hold function result
204
205 theLog.debug("Unloading parameter functionResult" );
206 // Booleans come back from the DB as int and have to be converted
207 if ( theParameters.getParam(3) == null
208 || ((java.math.BigDecimal)theParameters.getParam(3)).intValue() == 0)
209 {
210 functionResult = null;
211 }
212 else if (((java.math.BigDecimal)theParameters.getParam(3)).intValue() == -1)
213 {
214 functionResult = new Boolean(false);
215 }
216 else
217 {
218 functionResult = new Boolean(true);
219 }
220
221 try
222 {
223 theCallableStatement.clearParameters();
224 theParameters.clearParameters();
225 }
226 catch (SQLException e)
227 {
228 throw (new CSException("getStatementResults: Unable to clear parameters:" + e.toString()));
229 }
230 theLog.debug("Finished unloading data");
231 }
232
233
234
235 /**
236 * Associate parameters with statement
237 */
238 protected void bindParams() throws CSException
239 {
240 theLog.debug("Starting to bind parameters");
241 // Bind parameter that will hold function result
242 theLog.debug("binding output parameter functionResult to position 3" );
243 theParameters.setOutParam(3,OracleTypes.NUMBER);
244 // Bind parameter P_FROMCITY
245
246 theLog.debug("binding input parameter paramPFromcity to position 1");
247 theParameters.setParam(1,paramPFromcity);
248 // Bind parameter P_TOCITY
249
250 theLog.debug("binding input parameter paramPTocity to position 2");
251 theParameters.setParam(2,paramPTocity);
252 theLog.debug("Associating parameters with statement");
253 theParameters.bindParameters(theCallableStatement);
254 theLog.debug("Finished binding parameters");
255 }
256
257
258 /**
259 * Return a SQL statement that will invoke this stored procedure
260 * This may be a synonym or owned by somebody else.
261 *
262 */
263 public String getProcCallStatement()
264 {
265 if (procCall == null)
266 {
267 procCall = new StringBuffer("DECLARE \n"); // 1
268 procCall.append("/* Created By JDBCWizard./a> 6.0.2776 */ \n"); // 2
269 procCall.append("/* Which can be obtained at www.orindasoft.com */ \n"); // 3
270 procCall.append("functionResult BOOLEAN := null; \n"); // 4
271 procCall.append("FUNCTIONRESULT_SN SIGNTYPE := null; \n"); // 5
272 procCall.append("p_fromcity VARCHAR2(16) := ?; \n"); // 6
273 procCall.append("p_tocity VARCHAR2(16) := ?; \n"); // 7
274 procCall.append("BEGIN \n"); // 8
275 procCall.append(" \n"); // 9
276 procCall.append("functionResult := CURSOR_EXAMPLE.DIRECT_FLIGHT_AVAILABLE(p_fromcity => p_fromcity,p_tocity => p_tocity); \n"); // 10
277 procCall.append(" \n"); // 11
278 procCall.append("IF functionResult IS NULL THEN \n"); // 12
279 procCall.append(" FUNCTIONRESULT_SN := 0; \n"); // 13
280 procCall.append("ELSIF functionResult = FALSE THEN \n"); // 14
281 procCall.append(" FUNCTIONRESULT_SN := -1; \n"); // 15
282 procCall.append("ELSIF functionResult = TRUE THEN \n"); // 16
283 procCall.append(" FUNCTIONRESULT_SN := 1; \n"); // 17
284 procCall.append("END IF; \n"); // 18
285 procCall.append(" \n"); // 19
286 procCall.append("? := FUNCTIONRESULT_SN; \n"); // 20
287 procCall.append("END; "); // 21 536 characters
288
289 }
290 return(procCall.toString());
291 }
292
293} // Generated by JDBCWizard./a> 6.0.2776
294