Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    18

    Unanswered: PL/SQL Procedure

    Hi,
    In PL/SQL procedure I am using a IN parmater in the where condition of the declared cursor. when I see the output it returns all the rows from the table without filtering the records using the where condition.
    should I not use the IN parameter in the cursor declaration.
    I have used the following procedure.

    CREATE OR REPLACE PROCEDURE empProc2
    (empno IN NUMBER)
    IS
    BEGIN
    DECLARE
    no NUMBER(5);
    name VARCHAR2(50);
    CURSOR emp_cur IS
    SELECT empno,name FROM emp where empno = empno;
    BEGIN
    OPEN emp_cur;
    LOOP
    FETCH emp_cur
    INTO no,
    name;
    EXIT WHEN emp_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(no||'|'||name);
    END LOOP;
    CLOSE emp_cur;
    END;
    END;
    /

    Can some one help me to resolve this
    Thanks in advance

    Regards,
    Senthil

  2. #2
    Join Date
    Jan 2003
    Location
    Woking
    Posts
    107

    Re: PL/SQL Procedure

    Originally posted by senthil5_kumar
    Hi,
    In PL/SQL procedure I am using a IN parmater in the where condition of the declared cursor. when I see the output it returns all the rows from the table without filtering the records using the where condition.
    should I not use the IN parameter in the cursor declaration.
    I have used the following procedure.

    CREATE OR REPLACE PROCEDURE empProc2
    (empno IN NUMBER)
    IS
    BEGIN
    DECLARE
    no NUMBER(5);
    name VARCHAR2(50);
    CURSOR emp_cur IS
    SELECT empno,name FROM emp where empno = empno;
    BEGIN
    OPEN emp_cur;
    LOOP
    FETCH emp_cur
    INTO no,
    name;
    EXIT WHEN emp_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(no||'|'||name);
    END LOOP;
    CLOSE emp_cur;
    END;
    END;
    /

    Can some one help me to resolve this
    Thanks in advance

    Regards,
    Senthil

    Hi,
    You are getting this error because your in parameter variable name is same as the column name.
    That is when the following SQL is executed,
    SELECT empno,name FROM emp where empno = empno;
    The SQL tries to equate the empno column with the empno
    column and NOT the in parameter empno.
    Solution for this would be use the IN parameter name as in_empno.
    Hope it Helps
    nn

  3. #3
    Join Date
    Jan 2003
    Posts
    18

    Re: PL/SQL Procedure

    Hi,
    Thanks a lot
    Your idea worked
    Regards,
    Senthil


    Originally posted by NoviceNo1
    Hi,
    You are getting this error because your in parameter variable name is same as the column name.
    That is when the following SQL is executed,
    SELECT empno,name FROM emp where empno = empno;
    The SQL tries to equate the empno column with the empno
    column and NOT the in parameter empno.
    Solution for this would be use the IN parameter name as in_empno.
    Hope it Helps

  4. #4
    Join Date
    Jan 2003
    Location
    Bangalore
    Posts
    44

    Re: PL/SQL Procedure

    The in parameter name and the where clause name are same. Change the in parameter name to v_empno and change in where clause to empno=v_empno.
    Try doing this, it may help you

    Mash

    Originally posted by senthil5_kumar
    Hi,
    In PL/SQL procedure I am using a IN parmater in the where condition of the declared cursor. when I see the output it returns all the rows from the table without filtering the records using the where condition.
    should I not use the IN parameter in the cursor declaration.
    I have used the following procedure.

    CREATE OR REPLACE PROCEDURE empProc2
    (empno IN NUMBER)
    IS
    BEGIN
    DECLARE
    no NUMBER(5);
    name VARCHAR2(50);
    CURSOR emp_cur IS
    SELECT empno,name FROM emp where empno = empno;
    BEGIN
    OPEN emp_cur;
    LOOP
    FETCH emp_cur
    INTO no,
    name;
    EXIT WHEN emp_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(no||'|'||name);
    END LOOP;
    CLOSE emp_cur;
    END;
    END;
    /

    Can some one help me to resolve this
    Thanks in advance

    Regards,
    Senthil

  5. #5
    Join Date
    Jan 2003
    Posts
    18

    Re: PL/SQL Procedure

    Thanks Mahesh


    Originally posted by mahesh_bk1
    The in parameter name and the where clause name are same. Change the in parameter name to v_empno and change in where clause to empno=v_empno.
    Try doing this, it may help you

    Mash

Posting Permissions

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