cursor error1

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • orajit
    New Member
    • Nov 2007
    • 75

    cursor error1

    I have two tables emp and dept .
    Dept table has one column called depno and the values are 10,30,40.
    I have used following query

    [code=oracle]

    Declare
    v_empno number ;
    v_ename name;
    v_sal ;
    cursor c1 is select deptno from dept ;
    begin
    for r1 in c1
    loop
    select empno,ename,sal into v_empno,v_ename ,v_sal from emp
    where deptno=r.deptno ;
    end loop;
    end;
    /
    [/code]


    but i am getting below error as

    ORA-01422: exact fetch returns more than requested number of rows


    cud u please advice .

    Thanks
    Last edited by amitpatel66; Jan 23 '08, 05:44 AM. Reason: code tags
  • nramrits
    New Member
    • Sep 2007
    • 18

    #2
    Originally posted by orajit
    I have two tables emp and dept .
    Dept table has one column called depno and the values are 10,30,40.
    I have used following query


    Declare
    v_empno number ;
    v_ename name;
    v_sal ;
    cursor c1 is select deptno from dept ;
    begin
    for r1 in c1
    loop
    select empno,ename,sal into v_empno,v_ename ,v_sal from emp
    where deptno=r.deptno ;
    end loop;
    end;
    /

    but i am getting below error as

    ORA-01422: exact fetch returns more than requested number of rows


    cud u please advice .

    Thanks
    hai,
    we can only pass the sigle value from the INTO clause but the deptno have a morethan number of columns .
    plz try this query.
    cursor c1 is
    select
    empno,ename,sal
    from emp ,dept
    where deptno=deptno;

    Comment

    • orajit
      New Member
      • Nov 2007
      • 75

      #3
      its not working ... cud u plz send me the solution plz

      Comment

      • amitpatel66
        Recognized Expert Top Contributor
        • Mar 2007
        • 2358

        #4
        [code=oracle]
        Declare
        TYPE emp_Det IS TABLE OF emp%ROWTYPE;
        empd emp_det;
        cursor c1 is select deptno from dept ;
        begin
        for r1 in c1
        loop
        select * into empd from emp where deptno=r.deptno ;
        end loop;
        end;
        /
        [/code]

        Comment

        • amitpatel66
          Recognized Expert Top Contributor
          • Mar 2007
          • 2358

          #5
          orajit,

          I have requested you many times to make use of CODE tags if you are posting any source code but you are repeatedly not following the POSTING GUIDELINES.

          You can have a look at POSTING GUIDELINES and this will be last time I am asking you to make use of CODE tags else I will need to raise an official warning against you!!

          Comment

          • orajit
            New Member
            • Nov 2007
            • 75

            #6
            Thanks for ur reply ,,, but i wanted only selected columns eg ename,empno and sal in my select statement .
            So could you please tell me how to do that ... can you use collection ..

            thanks

            Comment

            • debasisdas
              Recognized Expert Expert
              • Dec 2006
              • 8119

              #7
              Originally posted by orajit
              Thanks for ur reply ,,, but i wanted only selected columns eg ename,empno and sal in my select statement .
              So could you please tell me how to do that ... can you use collection ..

              thanks
              then try this
              [code=oracle]
              DECLARE
              eno emp.empno%type;
              ena emp.ename%type;
              esal emp.sal%type;
              empd emp_det;
              CURSOR c IS SELECT deptno FROM dept ;
              BEGIN
              FOR r IN c
              LOOP
              SELECT *empno,ename,sa l Into eno,ena.esal FROM emp WHERE deptno=r.deptno ;
              END LOOP;
              END;[/code]

              Comment

              • orajit
                New Member
                • Nov 2007
                • 75

                #8
                i am getting below error for above code ...
                ORA-01422: exact fetch returns more than requested number of rows

                could you please advice me .

                Comment

                • debasisdas
                  Recognized Expert Expert
                  • Dec 2006
                  • 8119

                  #9
                  Originally posted by orajit
                  i am getting below error for above code ...
                  ORA-01422: exact fetch returns more than requested number of rows

                  could you please advice me .
                  That is because the select statement is fetching more than one row and the target can store only 1 row. You need to pass proper value to the where clause to avoid the same.

                  Comment

                  • orajit
                    New Member
                    • Nov 2007
                    • 75

                    #10
                    yes you are right... can u please send me code for parametric cursor .. I would use that cusor in my code .

                    Comment

                    • debasisdas
                      Recognized Expert Expert
                      • Dec 2006
                      • 8119

                      #11
                      Originally posted by orajit
                      yes you are right... can u please send me code for parametric cursor .. I would use that cusor in my code .
                      Please check in the HowTo section of oracle for some sample code.

                      Comment

                      • subashsavji
                        New Member
                        • Jan 2008
                        • 93

                        #12
                        This Example Will Help To Solve Your Problem!!!!!!!! !!!!!!!!1

                        [code=oracle]

                        Declare
                        Cursor Dept_cur Is
                        Select Deptno Deptnum, Dname Deptname From Dept;
                        --where Deptno>20;
                        Cursor Emp_cur(p_deptn o Number) Is
                        Select * From Emp
                        Where Deptno=p_deptno ;
                        Begin
                        For Dept_rec In Dept_cur
                        Loop
                        Dbms_output.put _line(dept_rec. deptnum || ' ' || Dept_rec.deptna me);
                        For Emp_rec In Emp_cur(dept_re c.deptnum)
                        Loop
                        Dbms_output.put _line(emp_rec.e name || ' ' || Emp_rec.job || ' ' ||emp_rec.sal );
                        End Loop;
                        Dbms_output.put _line(chr(10));
                        End Loop;
                        End;
                        /
                        [/code]
                        Last edited by amitpatel66; Jan 30 '08, 05:29 AM. Reason: code tags

                        Comment

                        Working...