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!

Creating stored procedure issue ORA01422

Discussion in 'SQL PL/SQL' started by pacodiaz2004, Sep 20, 2014.

  1. pacodiaz2004

    pacodiaz2004 Guest

    I need help in creating a stored proc. I am getting ora01422 error when I execute the stored proc.

    does anyone has an answer on the below. thx.

    Does anyone can help me fix the above issue in my query below
    I am new to PL SQL and I am trying to create a stored proc but it is returning an error message saying
    ORA-01422: exact fetch returns more than requested number of row
    create or replace procedure test
    (date1 in date,date2 in date)
    as
    column1 varchar2(10);
    column2 varchar2(10);
    column3 varchar2(10);
    Begin

    select t1.c1.t2.c2,t1.c3
    into column1,
    column2,
    column3

    from table1 t1 inner join tabe2 t2 on t1.c1 = t2.c2
    where t1.date>=date1 and t1.date<=date2;
    end;
    /
     
  2. swetha pasupuleti

    swetha pasupuleti Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    I think you forgot to put comma in this statement t1.c1.t2.c2,t1.c3. You can try by placing comma t1.c1,t2.c2,t1.c3.
     
  3. indhureva

    indhureva Guest

    Hi,
    I think join condition in select statement also returning more than one row .You can check once the join condition .
     
  4. rajenb

    rajenb Forum Expert

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

    The procedure has several potential sources of errors ... which are not handled at all :) and this is a basic feature which must be included.

    We don't know the structure of your tables and don't have any sample data to assist you in "debugging" it but try to explore what the previous members have suggested.

    From my side, I would add or recommend you google around for materials of PL/SQL Strored procedures (Oracle documentation or tutorials, for ex., http://plsql-tutorial.com/plsql-procedures.htm.

    And then try to review and debug your procedure step by step.