Link to home
Create AccountLog in
Avatar of AbhiJeet
AbhiJeet

asked on

cursor

hi,

i am facing a weird problem, in one of my oracle package-procedure as explained below.
the package function is as below:
FUNCTION abc(id INTEGER) RETURN d PIPELINED IS
    CURSOR cur_a IS
      SELECT field1
        ,field2
        FROM table1
        where table1.id = id ;

    CURSOR cur_b(i_field1 VARCHAR2,i_field2 INTEGER) IS
      select field1, field2 from table2
        where table2.field1 = i_field1
        AND table2.field2 = i_field2;

    Rec1 cur_b%ROWTYPE;
   
BEGIN

    FOR rec2 in cur_a
    LOOP
    begin
      OPEN cur_b(rec2.field1,rec2.field2);
      FETCH cur_b INTO Rec1;
      IF cur_b%NOTFOUND THEN
        PIPE ROW(' field1 '||  rec2.field1 ||' field2 '|| rec2.field2|| ' some message');
      END IF;
      CLOSE cur_b;
      end;
      END LOOP;

    RETURN;

   END ;


This is just an overview of the function, the logic in the funtion is i have two parametric cursors, cur_a and cur_b, i am opening cur_a as a for loop cursor and and the values in field1 and field2 are input for cur_b, and i am opening the cur_b.
and if there is no record in cur_b for the input values, i am showing some message.

I tried to debug the function and found that cur_a has always static value for field1 and field2, could not figure out why the cur_a is always having static value for different inputs,
can anyone help in knowing what is messing up?
Quick responses will be highly appreciated, am allocating 500 points for the same.
Avatar of AbhiJeet
AbhiJeet

ASKER

any expert?
where are the experts?
Make cur_a parameterized and pass the value of ID when you open it in FOR loop.

It will not be able to read the arg ID that you have passed as argument to function.

That should fix the problem.
Avatar of Ora_Techie
Change you cursor definition from:

    CURSOR cur_a IS
      SELECT field1
        ,field2
        FROM table1
        where table1.id = id ;

to:

    CURSOR cur_a IS
      SELECT field1
        ,field2
        FROM table1
        where table1.id = abc.id ;

And you should get the desired result. Here is the test case:
SQL> SELECT * FROM test;

        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

SQL> create or replace function f_test (id number)
  2  return varchar2
  3  is
  4   cursor c_a is select * from test where test.id = id;
  5  begin
  6   for x in c_a
  7   loop
  8     dbms_output.put_line(x.id);
  9   end loop;
 10    return 'Function Called;;
 11  end;
 12  /

Warning: Function created with compilation errors.

SQL> ed
Wrote file afiedt.buf

  1  create or replace function f_test (id number)
  2  return varchar2
  3  is
  4   cursor c_a is select * from test where test.id = id;
  5  begin
  6   for x in c_a
  7   loop
  8     dbms_output.put_line(x.id);
  9   end loop;
 10    return 'Function Called';
 11* end;
SQL> /

Function created.

SQL> SELECT f_test(5) FROM dual;

F_TEST(5)
-----------------------------------------------------------
Function Called

1
2
3
4
5
6
7
8
9
10
SQL> ed
Wrote file afiedt.buf

  1* SELECT f_test(1) FROM dual
SQL> /

F_TEST(1)
-----------------------------------------------------------
Function Called

1
2
3
4
5
6
7
8
9
10

That shows no matter what value you use while calling function,it will retreive all records since it compares id of table to id of table (sort of 1=1).

Change the code as i suggested and you will see:


SQL> ed
Wrote file afiedt.buf

  1  create or replace function f_test (id number)
  2  return varchar2
  3  is
  4   cursor c_a is select * from test where test.id = f_test.id;
  5  begin
  6   for x in c_a
  7   loop
  8     dbms_output.put_line(x.id);
  9   end loop;
 10    return 'Function Called';
 11* end;
SQL> /

Function created.

SQL> SELECT f_test(5) FROM dual;

F_TEST(5)
------------------------------------------------------------------
Function Called

5
SQL> ed
Wrote file afiedt.buf

  1* SELECT f_test(2) FROM dual
SQL> /

F_TEST(2)
------------------------------------------------------------------
Function Called

2

Open in new window

I shall try the solutions, by the time can i know the reason, why the cursor is not reading the argument of the function, is it any known issue in oracle pl/sql?
ASKER CERTIFIED SOLUTION
Avatar of Ora_Techie
Ora_Techie

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
thanks.