- 385.5K All Categories
- 4.9K Data
- 2.5K Big Data Appliance
- 2.4K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
Why won't this PL/SQL function compile?
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
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
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;
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;
for i in 2..13 loop
predictedValue := predictedValue + avgDiff;
IF predictedValue < 0 THEN
predictedValue := 0;
insert into tableObject(futureValues)
The error message I get is:
ORA-06545: PL/SQL: compilation error - compilation aborted ORA-06550: line 6, column 1: PLS-00103: Encountered the symbol "CREATE" 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 Member Posts: 1,169 Gold Trophy
add a "/" after you create the object type:
CREATE OR REPLACE TYPE t_table AS OBJECT
jaramill Member Posts: 4,299 Gold Trophy
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 Member Posts: 11,041 Black Diamond
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. GermanyMember Posts: 10,562 Gold Crown
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 Member Posts: 11,041 Black Diamond
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).
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 Member Posts: 3,410 Gold Trophy
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.
SQL*Plus: Release 22.214.171.124.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 126.96.36.199.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>
SQL> CREATE OR REPLACE TYPE t_futureValues AS TABLE OF t_table; / 2Type created.
So that works.
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 Member Posts: 14,493 Silver Crown
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?
Thank you @Cookiemonster76. Separating them out into three scripts, and implementing the corrections suggested above, worked.
L. Fernigrini Database Practice Lead Rosario, ArgentinaMember Posts: 4,196 Gold Crown
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.