Why won't this PL/SQL function compile?

3988598

Hi,

I'm learning about Oracle Apex, Oracle SQL and PL/SQL and I'm currently learning about functions.  I have written a function in which a table is created, data is stored and the table is returned to the calling query.  The problem is the code won't compile.  I can't see where the problem may be and I've compared the code to various online resources including this site.  Everything looks fine to me, so the answer is not obvious to me.

This is the code I wrote:

    CREATE OR REPLACE TYPE t_table AS OBJECT
    (
      futureValues NUMBER
    );
   
    CREATE OR REPLACE TYPE t_futureValues AS TABLE OF t_table;
    /
   
    CREATE OR REPLACE FUNCTION "CALCULATE_VALUE"( lastRowMinus0 IN NUMBER DEFAULT 1,
                                                       lastRowMinus1 IN NUMBER DEFAULT 2,
                                                       lastRowMinus2 IN NUMBER DEFAULT 3,
                                                       lastRowMinus3 IN NUMBER DEFAULT 4,
                                                       lastRowMinus4 IN NUMBER DEFAULT 5,
                                                       lastRowMinus5 IN NUMBER DEFAULT 6,
                                                       lastRowMinus6 IN NUMBER DEFAULT 7 )
    RETURN t_futureValues AS
                            tableObject         t_futureValues;
                            predictedValue      NUMBER := 0;
                            lastRowMinus0Value  NUMBER := 0;
                            lastRowMinus1Value  NUMBER := 0;
                            lastRowMinus2Value  NUMBER := 0;
                            lastRowMinus3Value  NUMBER := 0;
                            lastRowMinus4Value  NUMBER := 0;
                            lastRowMinus5Value  NUMBER := 0;
                            lastRowMinus6Value  NUMBER := 0;
                            avgDiff             NUMBER := 0;
   
    BEGIN
   
            tableObject := t_futureValues();
   
            lastRowMinus0Value := 3;
            lastRowMinus1Value := 6;
            lastRowMinus2Value := 9;
            lastRowMinus3Value := 12;
            lastRowMinus4Value := 14;
            lastRowMinus5Value := 20;
            lastRowMinus6Value := 60;
   
            avgDiff := (lastRowMinus5Value - lastRowMinus6Value) + avgDiff;
            avgDiff := (lastRowMinus4Value - lastRowMinus5Value) + avgDiff;
            avgDiff := (lastRowMinus3Value - lastRowMinus4Value) + avgDiff;
            avgDiff := (lastRowMinus2Value - lastRowMinus3Value) + avgDiff;
            avgDiff := (lastRowMinus1Value - lastRowMinus2Value) + avgDiff;
            avgDiff := (lastRowMinus0Value - lastRowMinus1Value) + avgDiff;
            avgDiff := avgDiff / 6;                                           
   
            predictedValue := avgDiff + lastRowMinus0Value;                   
   
            begin
                for i in 2..13 loop
                    predictedValue := predictedValue + avgDiff;
   
                    IF predictedValue < 0 THEN
                       predictedValue := 0;
                    END IF;
   
                    insert into tableObject(futureValues)
                    values(predictedValue);
   
                end loop;
            end;
   
            RETURN (tableObject);
   
    END;

The error message I get is:

ORA-06545: PL&#x2F;SQL: compilation error - compilation aborted ORA-06550: line 6, column 1: PLS-00103: Encountered the symbol &quot;CREATE&quot; ORA-06550: line 0, column 0: PLS-00565: T_TABLE must be completed as a potential REF target (object type)

I have no doubt that the problem is something simple, so if anyone knows, then I'd be grateful.

James Su

add a "/" after you create the object type:

CREATE OR REPLACE TYPE t_table AS OBJECT

    (

      futureValues NUMBER

    );

/

jaramill

Also I'd remove the double quotes around the function name just to be safe

CREATE OR REPLACE FUNCTION "CALCULATE_VALUE"

Should be: CREATE OR REPLACE FUNCTION CALCULATE_VALUE

mathguy

Obviously, you are missing the terminating slash ( / ) on a line by itself, both after the first type declaration and after the function. Easy fix.

Jaramill gives you good advice. Best practice: do not create object names (table names, column names, type names, function names etc.) in double quotes. While that does not break your function, it will cause trouble in the future.

After you add the slashes (and leave the function name as is, in double quotes, or after you remove them), you will run into the real flaw in your code. Namely, here:

insert into tableObject(futureValues)values(predictedValue);

tableObject is not an Oracle table; it is a nested table (a type of PL/SQL collection, supported in Oracle SQL also). You don't "insert into" it as you do in a normal table. Instead, you must use nested table methods. For example, this should work (instead of the two lines of code above):

tableObject.extend(1);tableObject(tableObject.last) := t_table(predictedValue);

Sven W.

This code won't work. The main issue (apart from the mentioned syntax error) is that you do not have a table TABLEOBJECT.

Therefore this insert will not work.

  insert into tableObject(futureValues)                    values(predictedValue);

What you have is a table object type (correct name: nested table). Kind of a memory structure that looks like a table. but no data can be persisted in there.

You have a variable tableObject  of that object type. To fill this variable, you would use a constructor or you need to extend it each time.

The syntax should be similar to this (untested!).

tableObject.extend;tableObject(tableObject.last).futureValues := predictedValue;

-- Edit: mathguy already made the same comment. He used the object type constructor to set the new entry in the collection. This might be missing in my version.

tableObject.extend;tableObject(tableObject.last) := empty_row;   -- empty_row needs to be previously declared as: empty_row t_table;tableObject(tableObject.last).futureValues := predictedValue;

mathguy

With that said:

While what I explained in Reply 3 will allow your function to compile (and it will actually run and produce a result), the whole thing makes no sense to me. What are you really trying to accomplish?

I believe you started another thread a few days ago, about some predictions you are trying to make. Are you able to explain, IN PLAIN ENGLISH and using mathematical / algebraical notation and terminology, but with no reference to any code - in ANY programming language - your method for predicting values? Start with what the inputs are (what quantities, what they represent, etc.) Then what it is that you are trying to predict, and what method you are trying to use.

It is almost certain that the function you wrote will not do what you need. For one thing, the IN parameters (variables) are NOT USED ANYWHERE in the function body, which is almost certainly incorrect no matter what you are trying to do. Then, you have some silly stuff: You create local variables in the declarations section and assign the value 0 to all of them - only to assign new, non-zero values (hard-coded, too, which can't be good) right at the top of the executable section. So, what's the point of assigning 0 to them in the declarations section?

Beyond that, I can't help you, if I don't even know what you are trying to do in code. What you are trying to do should be crystal clear (in English and math notation and concepts) BEFORE you write the first line of code. Let's focus on that part first, if you are interested in my help (at least).

3988598

Hi.  So following all the above suggestions, I implemented all the advice, but still the exact same error message appears.  I even had a colleague implement the suggestions (to confirm I did it right) and he was getting the same error message.

Hi.  Thanks for all your suggestions.  I know the function doesn't make much sense (with reference to the variables and parameters) and that is because I gutted out from the function most of the unnecessary details.  These details had no impact on the compilation of the function, nor do they affect the table I'm trying to create, so I removed them to make it easier to see where the problem would be.

And to answer the second part of your question, I'm using Oracle Apex to create a bar chart with an overlapping trend line.  The first 3/4 of the chart shows how much memory of a database has been used for the last 12 months.  The last 1/4 of the chart represents the next 3 months.

I previously created a another function which has the same parameters and variables and this function returns a NUMBER variable to the calling query.  This number represents how much more or less storage will be required in a database based on how much was used previously.  I had planned on using this NUMBER in my calling query for my trend line, so I could see the rate at which the trend line increases or decreases.  I had planned on adding this NUMBER to the last recorded value held to represent what would be required one week in the future.  I would then add the NUMBER again to represent the second week in the future, and add it again for three weeks and so on up until three months in the future.  This would show the rate at which storage is increasing or decreasing.

However, I was not able to get the result I wanted with this NUMBER.  So I thought that perhaps it might be easier to get my trend line working the way I want by returning a table which will contain all the values I need for the three months.

This previously-created function works so I don't understand why, no matter what solutions I implement, the function returning a table won't?

I hope this answers your questions sufficiently?  If not, then let me know and I'll clarify.

Cookiemonster76

3988598 wrote:Hi. So following all the above suggestions, I implemented all the advice, but still the exact same error message appears. I even had a colleague implement the suggestions (to confirm I did it right) and he was getting the same error message.

I assume part of the problem is you're trying to create the three objects using one script. Do each one separately and make sure each works before moving onto the next.

e.g.

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 31 13:39:12 2019Copyright (c) 1982, 2014, Oracle.  All rights reserved.Last Successful login time: Wed Jul 31 2019 11:35:35 +01:00Connected to:Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit ProductionSQL> CREATE OR REPLACE TYPE t_table AS OBJECT    (      futureValues NUMBER    );  2    3    4  5

So that's not done anything. As the others say add a slash:

  5  /Type created.SQL>

Next one:

SQL> CREATE OR REPLACE TYPE t_futureValues AS TABLE OF t_table;    /  2Type created.

So that works.

Finally:

SQL>     CREATE OR REPLACE FUNCTION "CALCULATE_VALUE"( lastRowMinus0 IN NUMBER DEFAULT 1,  2                                                         lastRowMinus1 IN NUMBER DEFAULT 2,  3                                                         lastRowMinus2 IN NUMBER DEFAULT 3,  4                                                         lastRowMinus3 IN NUMBER DEFAULT 4,  5                                                         lastRowMinus4 IN NUMBER DEFAULT 5,  6                                                         lastRowMinus5 IN NUMBER DEFAULT 6,  7                                                         lastRowMinus6 IN NUMBER DEFAULT 7 )  8      RETURN t_futureValues AS  9                              tableObject         t_futureValues; 10                              predictedValue      NUMBER := 0; 11                              lastRowMinus0Value  NUMBER := 0; 12                              lastRowMinus1Value  NUMBER := 0; 13                              lastRowMinus2Value  NUMBER := 0; 14                              lastRowMinus3Value  NUMBER := 0; 15                              lastRowMinus4Value  NUMBER := 0; 16                              lastRowMinus5Value  NUMBER := 0; 17                              lastRowMinus6Value  NUMBER := 0; 18                              avgDiff             NUMBER := 0; 19 20      BEGIN 21 22              tableObject := t_futureValues(); 23 24              lastRowMinus0Value := 3; 25              lastRowMinus1Value := 6; 26              lastRowMinus2Value := 9; 27              lastRowMinus3Value := 12; 28              lastRowMinus4Value := 14; 29              lastRowMinus5Value := 20;            lastRowMinus6Value := 60; 30   31 32              avgDiff := (lastRowMinus5Value - lastRowMinus6Value) + avgDiff; 33              avgDiff := (lastRowMinus4Value - lastRowMinus5Value) + avgDiff; 34              avgDiff := (lastRowMinus3Value - lastRowMinus4Value) + avgDiff; 35              avgDiff := (lastRowMinus2Value - lastRowMinus3Value) + avgDiff;            avgDiff := (lastRowMinus1Value - lastRowMinus2Value) + avgDiff; 36   37              avgDiff := (lastRowMinus0Value - lastRowMinus1Value) + avgDiff; 38              avgDiff := avgDiff / 6; 39 40              predictedValue := avgDiff + lastRowMinus0Value; 41 42              begin 43                  for i in 2..13 loop 44                      predictedValue := predictedValue + avgDiff; 45 46                      IF predictedValue < 0 THEN 47                         predictedValue := 0; 48                      END IF; 49 50                      insert into tableObject(futureValues) 51                      values(predictedValue); 52 53                  end loop; 54              end; 55 56              RETURN (tableObject); 57 58      END; 59

Done nothing, so add a slash:

59  /Warning: Function created with compilation errors.SQL>

Lets see what the error is:

SQL> show errorsErrors for FUNCTION "CALCULATE_VALUE":LINE/COL ERROR-------- -----------------------------------------------------------------50/21    PL/SQL: SQL Statement ignored50/33    PL/SQL: ORA-00942: table or view does not existSQL>

It's complaining about the insert statement, which the others have already mentioned, not the error you're getting.

John Thorton

IMO, looking at "memory used" or "free memory" on any Virtual memory operating system is a useless exercise.

The OS will use RAM as it desires because FREE RAM is a wasted resource that benefits nobody.

Please answer this simple question below regarding Windows OS.

How do you conclusively know or decide when RAM is MAJOR OS system bottleneck? This is regardless if Oracle is running on the system or not.

Strictly at OS level which metric at what value tells you that this system would greatly benefit from having more RAM in it; that OS is memory starved?

If you can't answer question above, why are you wasting time diddling about with Oracle "memory" statistics?

3988598

Thank you @Cookiemonster76.  Separating them out into three scripts, and implementing the corrections suggested above, worked. 

L. Fernigrini

You do not need to have them in 3 scripts, just make sure that you have a "/" in a single line after each object creation in a single script.

The / tells SQL*Plus that the PL/SQL block (that usually includes many lines) has ended and that it should be executed.