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