1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

PL/SQL: ORA-00947: not enough values..

Discussion in 'SQL PL/SQL' started by Vicky, Aug 11, 2014.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    create or replace TYPE t_tf_row as object(
    emp_id NUMBER,
    emp_name VARCHAR2(50)
    );

    create or replace TYPE t_tf_tab IS TABLE OF t_tf_row;


    create or replace function f_get(v number)
    return t_tf_tab
    pipelined is
    v_t_tf_tab t_tf_tab := t_tf_tab();
    begin
    select emp_id, emp_name
    bulk collect
    into v_t_tf_tab
    from emp;
    if v_t_tf_tab.count > 0 then
    for i in v_t_tf_tab.first .. v_t_tf_tab.last loop
    pipe row(v_t_tf_tab(i));
    end loop;
    end if;
    return;
    end f_get;


    While executing the func, I'm facing the err below:

    PL/SQL: ORA-00947: not enough values..

    Could U tel me wat's the problm with the function../?
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Where do you define t_tf_tab? I see t_tf_row but nowhere in your code is the return variable t_tf_tab declared as a usable object.
     
  3. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Try creating a package spec for your TYPES and FUNCTION declaration like :

    Code (SQL):
    CREATE OR REPLACE package MYCUSTOM_PKG AS
        -- Define a record and table of those records to be used in the function
        TYPE t_tf_rcd IS RECORD(emp_id NUMBER, emp_name VARCHAR2(50));

        TYPE t_tf_tbl IS TABLE OF t_tf_rcd;

        FUNCTION f_get(v NUMBER) RETURN t_tf_tab PIPELINED;

    END;
    Then put the Function Body in the Package Body...

    HTH

    CJ
     
    Vicky likes this.
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    needed conversion to object type : .... select t_tf_row(emp_id, emp_name) ....

    Code (SQL):


    CREATE OR REPLACE TYPE t_tf_row AS object(
    emp_id NUMBER,
    emp_name VARCHAR2(50)
    );
    /
    CREATE OR REPLACE TYPE t_tf_tab IS TABLE OF t_tf_row;
    /
    CREATE OR REPLACE FUNCTION f_get(v NUMBER)
    RETURN t_tf_tab
    pipelined IS
    v_t_tf_tab t_tf_tab := t_tf_tab();
    BEGIN
    SELECT  
              t_tf_row(emp_id, emp_name)
    bulk collect
    INTO v_t_tf_tab
    FROM emp;
    IF v_t_tf_tab.COUNT > 0 THEN
    FOR i IN v_t_tf_tab.FIRST .. v_t_tf_tab.LAST loop
    pipe ROW(v_t_tf_tab(i));
    END loop;
    END IF;
    RETURN;
    END f_get;
    /


     
     
    Vicky likes this.
  5. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Thanks Sergey., It workzz...:)