I have the following Stored Procedure coded in an Oracle 12C database
CREATE OR REPLACE EDITIONABLE PROCEDURE "SRO"."SP_IPEDS_YEARENROLL_A"
(
V_UNIVERSITY varchar2,
V_YEAR varchar2
)
AS
C1 OracleCursors.ref_cursor;
BEGIN
open C1 for
SELECT temp.IPEDSUnitID,
NVL(temp.SLevel,0) as SLEVEL,
NVL(temp.Race,0) as RACE,
NVL(temp.SEX,0) as SEX,
NVL(Temp.StudentCount, 0) as STUDENTCOUNT
from (Select I.IPEDSUnitID,
Case When MF.UG_GR = 'UG' then 1
When MF.UG_GR = 'GR' then 3
Else 0
End as SLevel,
Case When MF.ReportingEthnicity = 'NR' then 1
When MF.ReportingEthnicity = '04' then 2
When MF.ReportingEthnicity = '02' then 3
When MF.ReportingEthnicity = '03' then 4
When MF.ReportingEthnicity = '01' then 5
When MF.ReportingEthnicity = '07' then 6
When MF.ReportingEthnicity = '05' then 7
When MF.ReportingEthnicity = '90' then 8
When MF.ReportingEthnicity = '99' then 9
End as Race,
Case When MF.Gender = 'M' then 1
When MF.Gender = 'F' then 2
End as SEX,
COUNT(Distinct(MF.PASSHEStudentID)) as STUDENTCOUNT
From IPEDS12MonthsHistory MF
Inner Join SRO.tblLkupInstitution I on (MF.FICECode = I.FICECode)
Where IPEDSYear = V_Year and SUBSTR(UPPER(Univ),1,2) = SUBSTR(V_University,1,2) and NVL(Excluded, 0) = 0
Group By I.IPEDSUnitID,
Case When MF.UG_GR = 'UG' then 1
When MF.UG_GR = 'GR' then 3
Else 0
End,
Case When MF.ReportingEthnicity = 'NR' then 1
When MF.ReportingEthnicity = '04' then 2
When MF.ReportingEthnicity = '02' then 3
When MF.ReportingEthnicity = '03' then 4
When MF.ReportingEthnicity = '01' then 5
When MF.ReportingEthnicity = '07' then 6
When MF.ReportingEthnicity = '05' then 7
When MF.ReportingEthnicity = '90' then 8
When MF.ReportingEthnicity = '99' then 9
End,
Case When MF.Gender = 'M' then 1
When MF.Gender = 'F' then 2
End) temp
Order By temp.IPEDSUnitID, temp.SLevel, temp.Race, Temp.SEX;
dbms_sql.return_result(C1);
END SP_IPEDS_YEARENROLL_A;
I've read many articles on retrieving the result set from this procedure in JAVA, but I am still not getting results.
public class MainYearEnrollIPEDSUpload {
// public String conURL = "jdbc:sqlserver://oocsqldbp01;databaseName=MasterCensusEnrollment;Trusted_Connection=Yes;integratedSecurity=true;";
public String conURL = "jdbc:oracle:thin:@//Myserver:2014/SID";
String singleUniversity;
static ResultSet rs1;
static Date now = new Date();
static SimpleDateFormat sdfDate = new SimpleDateFormat("yyyyMMdd_HHmmss");
static String runTime = sdfDate.format(now);
static boolean errorFlag = false;
static String myErrorFile;
static String myDebugFile;
public MainYearEnrollIPEDSUpload() {
FileWriter errorFile = null;
FileWriter debugFile = null;
PrintWriter error = null;
PrintWriter debug = null;
try {
myErrorFile = "FallEnrollIPEDSUpload_error" + runTime + ".txt";
errorFile = new FileWriter(myErrorFile);
error = new PrintWriter(errorFile);
myDebugFile = "FallEnrollUpload_debug" + runTime + ".txt";
debugFile = new FileWriter(myDebugFile);
debug = new PrintWriter(debugFile);
} catch (IOException e1) {
errorFlag = true;
e1.printStackTrace();
}
try {
List<String> universityArray = Arrays.asList("BL", "CA", "CH",
"CL", "EA", "ED", "IN", "KU", "LO", "MA", "MI", "SH", "SL",
"WE");
String[] unitCodeArray = { "211158", "211361", "211608", "211644",
"212115", "212160", "213020", "213349", "213613", "213783",
"214041", "216010", "216038", "216764" };
boolean badValue = true;
String inputUniversity;
do {
badValue = true;
inputUniversity = javax.swing.JOptionPane
.showInputDialog("Please Type a Valid University Code or All: ");
if (universityArray.contains(inputUniversity.toUpperCase())) {
badValue = false;
} else if (inputUniversity.toUpperCase().equals("ALL")) {
badValue = false;
}
} while (badValue);
String inputYearSemester;
do {
inputYearSemester = javax.swing.JOptionPane
.showInputDialog("Please Type a Valid Year");
} while (inputYearSemester.length() != 4);
inputYearSemester = inputYearSemester + "1";
debug.println("User chose to run for " + inputUniversity);
System.out.println("User chose to run for " + inputUniversity);
JFileChooser fc;
File dummy_file = new File(
new File("J:\\IPEDS\\").getCanonicalPath());
fc = new JFileChooser();
fc.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
fc.setCurrentDirectory(dummy_file);
int returnVal = fc.showSaveDialog(null);
if (returnVal == JFileChooser.APPROVE_OPTION) {
String ssFile = fc.getSelectedFile() + "\\YearEnrollmentIPEDSUpload_"
+ inputUniversity + "_" + runTime + ".txt";
FileWriter outFile = new FileWriter(ssFile);
PrintWriter out = new PrintWriter(outFile);
ProgressMonitor progressMonitor = new ProgressMonitor(null,
"Monitor Upload", "Running", 0, 140);
int progStat = 0;
boolean norecords = true;
// Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Class.forName("oracle.jdbc.driver.OracleDriver");
// Connection conect2 = DriverManager
// .getConnection(conURL);
// DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
Connection conect2 = DriverManager
.getConnection(conURL, "USER","PASS");
CallableStatement ps1;
System.out.println("Gets here: ");
for (String university : universityArray) {
progStat = progStat + 10;
progressMonitor.setNote(university);
progressMonitor.setProgress(progStat);
if (inputUniversity.toUpperCase().equals("ALL")
|| (university
.equals(inputUniversity.toUpperCase()))) {
String mainUnit = unitCodeArray[universityArray
.indexOf(university)];
debug.println(mainUnit);
System.out.println(mainUnit + " " + university);
String queryString = "begin SP_IPEDS_YEARENROLL_A(?,?); end;";
debug.println("A : " + queryString);
System.out.println("A : " + queryString);
ps1 = conect2.prepareCall(queryString.trim(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
// ps1.registerOutParameter(1, OracleTypes.CURSOR);
ps1.setString(1, university);
ps1.setString(2, inputYearSemester);
norecords = true;
System.out.println(ps1.toString());
ps1.executeQuery();
while (ps1.getMoreResults())
rs1 = ps1.getResultSet();
// rs1 = ((OracleCallableStatement)ps1).getCursor(1); // oracle way with ref cursor RETURN
System.out.println(rs1.toString());
// if (rs1.first()) {
// do {
//
while (rs1.next()) {
// if (rs1.getInt("IPEDSUNITID") > 0) {
System.out.println("Gets here 2: " + rs1.getObject("IPEDSUNITID"));
out.println("UNITID=" + rs1.getInt(1)
+ ",SURVSECT=" + "E1D" + ",PART="
+ "A"
+ ",SLEVEL=" + rs1.getInt(2)
+ ",RACE="
+ rs1.getInt(3)
+ ",SEX="
+ rs1.getInt(4)
+ ",COUNT="
+ rs1.getInt(5));
norecords = false;
// }
// }
// } while (rs1.next());
}
if (norecords) {
out.println("UNITID=" + mainUnit + ",SURVSECT="
+ "E1D" + ",PART=" + "A" +
",SLEVEL=" + "1"
+ ",RACE=" + "1"
+ ",SEX=" + "1"
+ ",COUNT="
+ "0");
}
rs1.close();
ps1.close();
}
// } while (rs1.next());
// }
if (norecords) {
out.println("UNITCD=" + mainUnit + ",SURVSECT="
+ "E1D" + ",PART=" + "B" + ",CREDHRSU="
+ "0" + ",CONTHRS=" + "0"
+ ",CREDHRSG=" + "0" + ",RDOCFTE=" + "0");
}
rs1.close();
ps1.close();
}
}
progressMonitor.close();
out.close();
conect2.close();
conect2.close();
}
} catch (Exception e) {
System.out.println("Generated execption E: " + e);
e.printStackTrace();
error.println("Generated execption E: " + e);
errorFlag = true;
}
error.close();
debug.close();
javax.swing.JOptionPane.showMessageDialog(null,
"Program Finished Successfully");
if (!(errorFlag)) {
boolean success = (new File(myErrorFile)).delete();
success = (new File(myDebugFile)).delete();
if (success) {
}
}
}
public static void main(String args[]) {
new MainYearEnrollIPEDSUpload();
}
}
When I run this code: I get
User chose to run for ALL
Gets here:
211158 BL
A : begin SP_IPEDS_YEARENROLL_A(?,?); end;
oracle.jdbc.driver.OracleCallableStatementWrapper@33b08060
oracle.jdbc.driver.ForwardOnlyResultSet@27755c59
Generated execption E: java.sql.SQLException: Closed Resultset: next
java.sql.SQLException: Closed Resultset: next
at oracle.jdbc.driver.InsensitiveScrollableResultSet.next(InsensitiveScrollableResultSet.java:565)
at passhe.edu.ipeds.MainYearEnrollIPEDSUpload.<init>(MainYearEnrollIPEDSUpload.java:214)
at passhe.edu.ipeds.MainYearEnrollIPEDSUpload.main(MainYearEnrollIPEDSUpload.java:347)
It seems as though it connect to Oracle, runs the stored procedures.. but then fails to translate the result set. I also tried this as function that returns a ref cursor and a stored procedure with an output parameter.
I am utilizing ojdbc6.jar.
Thanks