Reading Oracle htp.p output into Java app

Viewed 115

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.

0 Answers
Related