Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How do I continually add a pl/sql return value to the last recorded value in a table?

3988598Jul 28 2019 — edited Jul 29 2019

So I am quite new to SQL so please be gentle.

I am using Oracle Apex to generate a bar chart showing the amount of storage used in a particular database over the last 12 months. In this chart I am trying to add an additional feature where the chart will show (via a line) how much storage will likely be used on a fortnightly basis over the course of the next three months.

I have created a PL/SQL function (GET_PREDICTED_VALUE) which generates the anticipated value I require, but I am having trouble using that value to show the predicted trend. If the generated anticipated value is positive, that’s how much more storage is needed. If the anticipated value is negative, that’s how much less storage is needed (the arguments sent into this function are how many days ago do I want to draw the ‘storage used’ values. 30 will be 30 days ago, 150 is 150 days ago etc).

What I would like to do is use this anticipated value and add it to the last ‘current storage used’ value recorded in the database. I would like the anticipated value to be continually added so that a future trend can be observed.

For example, if the last ‘current storage used’ value saved in the database is 50, and the anticipated value is 3, then the value for the immediate future date should be 53. And the next future value should be 56, and the next should be 59, and so on up to three months ahead.

This is the code I have so far, and it works insofar that the chart I need does get generated and the trend line runs across the previous year and then the future three months. But for the future three months, I am only able to get the trend line to represent the anticipated value, not the value continually added E.g again, if the anticipated value is 3, my trend line shows 3 across all future dates.

WITH src AS ( SELECT GET_PREDICTED_VALUE(1, 30, 60, 90, 120, 150, 180) AS predictedValue FROM DUAL),mydays as ( select level mylevel, sysdate + level as futureDate from dual connect by level <= 90 ),future_values as ( SELECT MAX(src.predictedValue) AS "ASM Used", TO_CHAR(TRUNC(futureDate,'IW') ,'DD/MM/YY') AS "Capture Date", TRUNC(futureDate,'IW') AS capture_date_order FROM mydays CROSS JOIN src GROUP BY TRUNC(futureDate,'IW') ORDER BY TRUNC(futureDate,'IW') ),SRC_back AS ( SELECT TRUNC(capture_date,'IW') AS week, database_name, MAX((os_usable_total_mb-os_usable_free_mb)) AS ASMUsed FROM tablespace_used WHERE capture_date >= (SYSDATE - 360) GROUP BY TRUNC(capture_date,'IW'), database_name, tablespace_name),back_values as ( SELECT /*+ parallel(a, 8) */ TO_CHAR(week,'DD/MM/YY') AS "Capture Date", week AS capture_date_order, ROUND( MAX(ASMUsed/1024/1024 ) ) AS "ASM Used" FROM src_back a WHERE week >= (SYSDATE - 360) GROUP BY TO_CHAR(week,'DD/MM/YY'), week ORDER BY 2 ) select "Capture Date", capture_date_order, "ASM Used" from back_valuesunion select "Capture Date", capture_date_order, "ASM Used" from future_valuesorder by 2

I hope my explanation is clear, and if anyone could let me know what I have to do to get my trend line running the way I need?

(This is an image of how my chart looks so far):

chart.png

Comments

Rahul,

Always mention your JDev version.

Your usecase is not clear to me. If you want to split the array and display the strings in the output text, check out if you could use af:forEach / af:iterator (with an output text inside it).

-Arun

user8696578

Hi Arun,

Yes you are correct I am using af:foreach.

Can you tell me what would the value of the outputtext point to(since it is a String). As the iterator is pointing to a list of items. and it also has a var property as items.

Jdeveloper 11.1.1.7.0

Regards,

Rahul

Rahul,

Here is a simple ex:

JSPX :

<af:panelBox id="pb1">
 <af:forEach var="items" items="#{viewScope.MyBean.stringList}">
 <af:outputText id="ot1" value="#{items}"/>
 </af:forEach>
</af:panelBox>

Bean : 

public class MyBean {


private List<String> stringList;


    public void setStringList(List<String> stringList) {
        this.stringList = stringList;
    }




    public List<String> getStringList() {
        return stringList;
    }




    public MyBean() {
        super();
        stringList = new ArrayList<String>();
        stringList.add("One");
        stringList.add("Two");
        stringList.add("Three");
        stringList.add("Four");
        stringList.add("Five");


}


}

-Arun

B

user8696578

Thanks Arun. It Worked

1 - 4

Post Details

Added on Jul 28 2019
#sql-pl-sql
2 comments
278 views