Results 1 to 5 of 5

Thread: Ref cursor

  1. #1
    Join Date
    Jan 2003
    Posts
    18

    Unanswered: Ref cursor

    Hi,
    I have created a procedure like this.
    Create or replace procedure proc1(no in number)
    is
    type typ1 is ref cursor;
    cur1 typ1;
    begin
    if <condition 1> then
    open cur1 for
    select * from emp
    where empno = no;
    else
    if <condition 1> then
    open cur1 for
    select * from emp
    where deptno = no;
    end if;
    ...............
    ......
    end;
    I am using the IN parameter in the where clause of the query,When I execute the query it doesnt select any row.But when use the IN parameter in a Explicit cursor I get the desired result.But I need to use the ref cursor only because at run time only I can decide which query should be executed.
    Can any one help me to solve this problem.
    Thanks in advance.

    Regards,
    Senthil

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

    Re: Ref cursor

    Originally posted by senthil5_kumar
    Hi,
    I have created a procedure like this.
    Create or replace procedure proc1(no in number)
    is
    type typ1 is ref cursor;
    cur1 typ1;
    begin
    if <condition 1> then
    open cur1 for
    select * from emp
    where empno = no;
    else
    if <condition 1> then
    open cur1 for
    select * from emp
    where deptno = no;
    end if;
    ...............
    ......
    end;
    I am using the IN parameter in the where clause of the query,When I execute the query it doesnt select any row.But when use the IN parameter in a Explicit cursor I get the desired result.But I need to use the ref cursor only because at run time only I can decide which query should be executed.
    Can any one help me to solve this problem.
    Thanks in advance.

    Regards,
    Senthil
    Hi,
    There must be something wrong in the IF CONDITIONS that you are using. That is the control is not coming into any of the if statements and hence the cursor is empty when you try to fetch it.
    Other-wise,
    The PL/SQL is correct.

    From the condition you have quoted above, you are checking for a
    particular condition in the IF statement and you are checking for the same condition in the else statement. Please verify this.

    Hope it Helps
    Regards
    nn

  3. #3
    Join Date
    Jan 2003
    Posts
    18

    Re: Ref cursor

    Hi,
    The control is coming to if condition.
    I checked giving a print.
    I am also getting the required result when I hardcode the
    value passed in where condition ie instead of using the IN parameter.
    ie
    if <condition 1> then
    open cur1 for
    select * from emp
    where empno = 10;
    ......
    Regards,
    Senthil


    Originally posted by NoviceNo1
    Hi,
    There must be something wrong in the IF CONDITIONS that you are using. That is the control is not coming into any of the if statements and hence the cursor is empty when you try to fetch it.
    Other-wise,
    The PL/SQL is correct.

    From the condition you have quoted above, you are checking for a
    particular condition in the IF statement and you are checking for the same condition in the else statement. Please verify this.

    Hope it Helps
    Regards

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Ref cursor

    It's hard to identify what's wrong looking at pseudo-code. Post the real code and maybe someone can spot the problem.

  5. #5
    Join Date
    Jan 2003
    Posts
    18

    Re: Ref cursor

    Hi,
    Thanks a Lot for your help.
    I identified the problem.
    the variable used in Where condition
    was manipulated from IN paramter.
    actually that step was found after the 'open cursor..'.
    That was the problem.
    Regards,
    Senthil


    Originally posted by NoviceNo1
    Hi,
    There must be something wrong in the IF CONDITIONS that you are using. That is the control is not coming into any of the if statements and hence the cursor is empty when you try to fetch it.
    Other-wise,
    The PL/SQL is correct.

    From the condition you have quoted above, you are checking for a
    particular condition in the IF statement and you are checking for the same condition in the else statement. Please verify this.

    Hope it Helps
    Regards

Posting Permissions

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