Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

PL/SQL in JSP reports in PPM

SOLVED
Go to solution
Highlighted
JHarris941
Regular Collector

PL/SQL in JSP reports in PPM

Hello,

 

 

I was hoping someone had experience with using PL/SQL in custom JSP reports.

We cannot seem to get the report working. Any advice would be great. I attached the report so that you can see what we are trying to do. Thanks

4 REPLIES
Mohit_Agrawal
Frequent Visitor

Re: PL/SQL in JSP reports in PPM

Hi..

 

I looked at your query and the flaw is that: you cannt directly run the qurey. You need to create strings and connection...then execute..

 

You can see below example as how we execute queries in JSP..

 

<%
String query_output = null;
Statement get_sum = connection.createStatement();
ResultSet rs1 = null;

 

<% -- Execute query -->
quey_output = "Select * from dual";
rs1 = get_sum.executeQuery(query_fs);

%>

 

 

Hope this helps!

 

Thanks!!

Mohit Agrawal

Jason Nichols K
Honored Contributor
Solution

Re: PL/SQL in JSP reports in PPM

Using the JSP EL and the rpt:openStatement tag, you have to use a SELECT statement.  It is not meant for running anonymous PL/SQL blocks of code.  Looking at your code, you would want to put the cursor code into the rpt:openStatement tag and then after you put in the rpt:runStatement tag, you need to use the c:forEach tag.  Here is an example from a report I built for a client:

 

<rpt:runStatement stmtName="getResPoolSQL" var="rpResults" />

<%-- Loop through each result and build a string to be used later in collecting the data --%>
<c:forEach items="${rpResults.rowsByIndex}" var="rpResult" varStatus="rpResultsStatus">

  <c:choose>
     <c:when test="${empty rpList}">
    <c:set var="rpList" value="${rpResult[0]}" />
  </c:when>
  <c:otherwise>
    <c:set var="rpList" value="${rpList};${rpResult[0]}" />
  </c:otherwise>
  </c:choose>
 
</c:forEach>

 

You would need a similar structure and in that block you would be the logic that's in the loop section of your PL/SQL Block to do the rest of the logic and output of the values that want to be displayed in the report.  Remember that the report will be in HTML format and that you will need to put appropriate HTML tags in handle formatting like line breaks. 

 

Jason

JHarris941
Regular Collector

Re: PL/SQL in JSP reports in PPM

Thanks alot guys

PPMRam
Collector

Re: PL/SQL in JSP reports in PPM

Hi,

 

We are trying to create new JSP report. Can you please let us know what are the steps we need to follow.

 

If possible can you please share a sample report and list of steps needs to be follwed.

 

Thanks in advance.

 

Regards

//Add this to "OnDomLoad" event