Unanswered: Creating a function produces error: PL/SQL: ORA-00947: not enough values
I think I am missing something simple but I can't see it.
I am getting 2 errors when trying to create a function that selects data from a view and returns a table. The view joins 3 tables, has a derived column, a column derived from the SUM aggregate function, and a GROUP BY. I am currently passing in a parameter, but not using it yet.
The code is similar to below:
CREATE TYPE table_columns AS OBJECT
CREATE TYPE tabletype AS TABLE OF table_columns;
CREATE OR REPLACE FUNCTION get_data( current_day IN number )
RETURN tabletype DETERMINISTIC
AS table_t tabletype ;
FROM view_x ;
RETURN( table_t );
Here are the errors:
batch line column error message
1 4 5 PL/SQL: SQL Statement ignored
1 11 5 PL/SQL: ORA-00947: not enough values
As described in documentation:
The columns a,b,c,d,e have probably simple datatypes (matching the object ones), so you should create object from them by calling its constructor. Moreover you select multiple rows into its collection, so you should use BULK COLLECT.
So I would change the query to
SELECT table_columns(a, b, c, d, e)
BULK COLLECT INTO table_t
FROM view_x ;
Not sure what you point at with your last question.
I could be more exact as your problem is not stated there literally.
However you were working with objects in PL/SQL, so I thought it would be useful to provide you with link to appropriate part in PL/SQL User's Guide and Reference as it contains many useful information covered with good examples.
By the way, how did you get the correct syntax of CREATE TYPE and CREATE FUNCTION commands (within PL/SQL block inside)?