Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58

    Question Unanswered: Creating a function produces error: PL/SQL: ORA-00947: not enough values

    Please help.
    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
    (a NUMBER(2),
    b TIMESTAMP(6),
    c TIMESTAMP(6),
    d VARCHAR2(10),
    e NUMBER(7,6)
    );

    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 ;
    BEGIN
    SELECT
    a
    , b
    , c
    , d
    , e
    INTO table_t
    FROM view_x ;

    RETURN( table_t );
    END;

    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


    Thanks for your assistance.

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    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
    Code:
    SELECT table_columns(a, b, c, d, e)
    BULK COLLECT INTO table_t
    FROM view_x ;

  3. #3
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58
    Your suggestion worked!!

    Thank you very much!!!

    By the way ... what is "documentation"?

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    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)?

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by tomstone_98
    Your suggestion worked!!

    Thank you very much!!!

    By the way ... what is "documentation"?
    The underscored word documentation was a url link to the documentation to solve your problem.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •