DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Ora-01422

  1. #1
    Join Date
    Jul 2005
    Posts
    23

    Ora-01422

    Can anyone help with the following problem

    Ive created a package with the following SQL

    IF title_level = 'P' THEN
    BEGIN
    SELECT od.description
    INTO description
    FROM object_descriptions od,
    V_P_TITLE_LEVEL ptl
    WHERE od.obj_id = ptl.prg_auk
    AND od.type = 1
    AND ptl.ss_box_cod = TO_CHAR(ss_box_cod);

    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
    END;
    RETURN;
    END IF;

    running it results in error ORA-01422
    'exact fetch returns more than the requested number of rows'

    I can understand this error if more than one row is returned, but running the sql as follows

    SELECT od.description
    FROM object_descriptions od,
    V_P_TITLE_LEVEL ptl
    WHERE od.obj_id = ptl.prg_auk
    AND od.type = 1
    AND ptl.ss_box_cod = TO_CHAR(1711634270);


    returns just one row

    **ss_box_cod is equal to 1711634270 when run inside the package.
    V_P_TITLE_LEVEL is a view

    tia

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    SELECT od.description
    INTO description
    FROM object_descriptions od,
    V_P_TITLE_LEVEL ptl
    WHERE od.obj_id = ptl.prg_auk
    AND od.type = 1
    AND ptl.ss_box_cod = TO_CHAR(ss_box_cod);

    The above select statement returns more than one row. You got error because you are assigning more than one values to description variable.
    It seems your logic or data may be a problem.

    Tamil

  3. #3
    Join Date
    Jul 2005
    Posts
    23

    Ora-01422

    I realised that more than one row is being returned and thats why I get ORA-01422, but when I run the sql as in the second example, I only get 1 row. I cant see why both bits of code return different numberrs of rows, as they should effectively be the same.

  4. #4
    Join Date
    Feb 2005
    Posts
    158
    The problem is here :
    AND ptl.ss_box_cod = TO_CHAR(ss_box_cod);
    You are matching the column ss_box_cod to the column ss_box_cod and all the rows match. When run explictly, you are using the number
    AND ptl.ss_box_cod = TO_CHAR(1711634270);

    The moral of this story is NEVER use PL/SQL variable names that are the same as column names. Prefix your variables with a V_

  5. #5
    Join Date
    Dec 2008
    Location
    Ukraine, Kharkov
    Posts
    2

    Thumbs up Thanks!!

    Couple of days ago I see that problem first time.
    Googling have no result.
    Answers like this
    http://www.sql.ru/forum/actualthread.aspx?tid=156187
    was too complex for me.

    And now I see the post of gamyers about "moral of this story"
    It helps me!!!
    Tnx!! ))))

    Sorry for my poor English

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by Comandor
    Sorry for my poor English
    No problem, we forgive both your English and the fact that you answered a three years old dead thread
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Dec 2008
    Location
    Ukraine, Kharkov
    Posts
    2
    Quote Originally Posted by PAVB
    No problem, we forgive both your English and the fact that you answered a three years old dead thread
    not closed thread - not dead
    three, or thirty three ... doesn't matter.
    It works! *yahoo*

    *Has left to learn English*

Posting Permissions

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


Click Here to Expand Forum to Full Width