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!

Function for returning multiple values.,/

Discussion in 'SQL PL/SQL' started by Vicky, Nov 1, 2014.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    create or replace TYPE "emp_rec" IS OBJECT
    (
    emp_name VARCHAR2(30),
    salary VARCHAR2(20),
    city VARCHAR2(50));

    CREATE OR REPLACE TYPE "emp_list" AS TABLE OF
    "emp_rec";


    create or replace FUNCTION Femp (i_emp_id IN VARCHAR2)
    RETURN emp_list PIPELINED
    AS

    tab_txn emp_list := emp_list();

    BEGIN
    SELECT emp_name, salary, city
    bulk collect INTO tab_txn
    FROM employees
    WHERE emp_id=i_emp_id;

    if (tab_txn.count > 0) then
    for rec in tab_txn.first .. tab_txn.last loop
    pipe row(tab_txn(rec));
    end loop;
    end if;
    RETURN;
    END Femp;

    while compiling the function above I'm getting the error below..,:

    Error(10,9): PL/SQL: ORA-00947: not enough values

    Could U tel me Wat's the prblm with the functiion/?
     
  2. db_a

    db_a Guest

    It looks like the issue is coming in the WHERE caluse of your SELECT statement.
    I may be wrong as I am not much used to PLSQL; just beginner.
     
  3. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Vicky,

    Try:

    Code (SQL):
    SELECT emp_rec(emp_name, salary, city)
    bulk collect INTO tab_txn
    FROM employees
    WHERE emp_id=i_emp_id;
    When you fetch values into a table of OBJECT, you have to "convert" the values fetched into an OBJECT by using above syntax.
     
    Vicky likes this.
  4. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Tnx Rajen,..,

    It's working fine while fetching single values.,,.

    DECLARE
    emp_val employees%rowtype;
    --cursor c1 is (SELECT emp_name, salary, city FROM TABLE(femp('30')));
    BEGIN
    --FOR rec IN c1 loop
    SELECT emp_name, salary, city
    INTO emp_val.emp_name, emp_val.salary, emp_val.city
    FROM TABLE(femp('30'));
    dbms_output.put_line('emp_val.emp_name='||emp_val.emp_name);
    dbms_output.put_line('emp_val.salary='||emp_val.salary);
    dbms_output.put_line('emp_val.city='||emp_val.city);
    --end loop;
    END;

    O/P:
    anonymous block completed
    emp_val.emp_name=celin
    emp_val.salary=5000
    emp_val.city=Delhi

    But, how to fetch values, when it's returning multiple rows?!
     
  5. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Just loop through the records like a usual cursor (assuming you have more than 1 record for emp_id=30 to be able to demonstrate it):

    Code (SQL):
    BEGIN
    FOR rec IN (SELECT emp_name, salary, city
    FROM TABLE(femp('30')))
    loop
    dbms_output.put_line('emp_name='||rec.emp_name);
    dbms_output.put_line('salary='||rec.salary);
    dbms_output.put_line('city='||rec.city );
    END loop;
    END;
    /
     
     
    Vicky likes this.
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation