We have built a dynamic stored proc reader due to the nature of our system. The only issue I have left is I am unable to read the htp.p output back into my application after calling the proc.
Due to the reader building the proc dynamical from a request I am unable to use CallableStatement.registerOutParameter() for specific points, unless there is some way of using it that is not obvious.
Here is the loop from the Oracle DB that returns the a single string with each pass:
while cRecords%found loop
htp.p(replace(replace(rec.title, chr(10), ''), chr(13), '')||'~'||
replace(replace(rec.city, chr(10), ''), chr(13), '')||'~'||
replace(replace(rec.venue, chr(10), ''), chr(13), '')||'~'||
replace(replace(rec.complex, chr(10), ''), chr(13), '')||'~'||
replace(replace(rec.startDate, chr(10), ''), chr(13), '')||'~'||
replace(replace(rec.endDate, chr(10), ''), chr(13), '')||'~'||
replace(replace(rec.minPrice, chr(10), ''), chr(13), '')||'~'||
replace(replace(rec.maxPrice, chr(10), ''), chr(13), '')||'~'||
replace(replace(rec.eventInfo, chr(10), ''), chr(13), '')||'~'||
replace(replace(rec.eventImage, chr(10), ''), chr(13), '')
);
fetch cRecords into rec.title, rec.city, rec.venue, rec.complex, rec.startDate, rec.endDate, rec.minPrice, rec.maxPrice, rec.eventInfo, rec.eventImage;
end loop;
This is a small snipet of the code:
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@10.0.0.2:8080:***dv", "user", "pass");
System.out.println("conn made");
String stmt = "{call EVENTMACHINE_JDBC_HTP(01/12/2017, 15/12/2017)}";
System.out.println(stmt);
cStmt = conn.prepareCall(stmt);
cStmt.execute();
I have tried looking on multiple sites to no avail and I am not able to see any methods that will help my cause.
Any help would be greatly appreciated.