Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2010
    Posts
    30

    Unanswered: Oracle iSQL*Plus - PL/SQL Function problem

    Hi there,

    I'm a University student and currently practicing PL/SQL Functions, and I'm having
    PL/SQL: ORA-00947: not enough values
    error when I tried to create a following function.

    CREATE OR REPLACE FUNCTION undeli(idord item.ordid%TYPE, iditm item.itemid%TYPE) RETURN item.qty%TYPE IS

    return_val item.qty%TYPE;

    BEGIN

    SELECT i.ordid, i.itemid, i.qty "Items Ordered", SUM(NVL(di.qty,0)) "Items Delivered", i.qty - SUM(NVL(di.qty,0)) "Total Undelivered"
    INTO return_val
    FROM item i, delivered_item di
    WHERE i.ordid = di.ordid(+)
    AND i.itemid = di.itemid(+)
    AND idord = i.ordid
    AND iditm = i.itemid;

    RETURN return_val;

    END;
    /
    SHOW ERROR
    I've just spent like 2 hours looking on the Internet for a solution as well as reading the old resource notes from my course BUT I can't seem to find anything.

    Please, can I have some suggestions as well as explanations?

    Sorry if the question is really noobie (because I am lol).

    Many thanks and regards.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    You are selecting 5 columns, yet only 1 is listed in the INTO clause.

    You need to store every column you select into a declared variable.

  3. #3
    Join Date
    Nov 2010
    Posts
    30
    Thank you very much for fast reply, I needed that.

    Why do you say I'm selecting 5 columns?
    Technically, isn't it meant to be 4?
    Because I'm using the same columns in SUM functions?

    Anyway, so the main cause of the problem is because I'm reading in more than one columns into ONE variable?

    Will that solve if I declare five of them and return all?

    Please let me know.

    Many thanks and regards.

  4. #4
    Join Date
    Nov 2010
    Posts
    30

    It worked

    Hey, I did just as you said about reading into more than one variables and it worked!!

    I even found an easier way of creating this function.

    So, thanks ever so much for your help.

    Cheers.

    Regards.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by db_newbie View Post
    Why do you say I'm selecting 5 columns?
    Technically, isn't it meant to be 4?
    Run that select in a query tool and you'll see that there are 5 columns.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Oracle iSQL*Plus
    iSQL*Plus did not exist before V10 & does not exist in V11.
    you should consider using different client As Soon As Possible.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Nov 2010
    Posts
    30
    Quote Originally Posted by anacedent View Post
    >Oracle iSQL*Plus
    iSQL*Plus did not exist before V10 & does not exist in V11.
    you should consider using different client As Soon As Possible.
    Thank you and I'd love to use a different version BUT I'm a university student and they only provide us with iSQL Plus

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Thank you and I'd love to use a different version BUT I'm a university student and they only provide us with iSQL Plus

    Open Source solution(s) exist

    SQuirreL SQL Client Home Page
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Nov 2010
    Posts
    30
    Quote Originally Posted by anacedent View Post
    >Thank you and I'd love to use a different version BUT I'm a university student and they only provide us with iSQL Plus

    Open Source solution(s) exist

    SQuirreL SQL Client Home Page
    Thank you, I'll try that when I have a chance.

Posting Permissions

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