how can I do this?

[rkonda99]

Hi all,
I need to write a procedure/function to get all the serial numbers based on user input. I might get 1 or many serial numbers back from the database. Now I need to use these serials in a different procedure/trigger to process (to get some other info based on each serial).
I wrote this whole thing in a trigger. It is working sometimes and not working at times. I don't know how to debug this. If it was a regular procedure or funtion, I know to use dbms_output.put_line to debug.
So, I was thinking an alternative ways of solving my situations. Any ideas?? (In a nutshell, I need to get serial numbers to begin with. Then, process each serial one at time to do something).
Thanks,
Ramesh

[magvivek]

here table1 = emp
table2= another_EMP
i am select empno from emp
based on that empno, iam again selecting records from another_EMP

---

SQL> ed
Wrote file afiedt.buf

1 declare
2 eno number;
3 cursor c1 is select empno from emp;
4 cursor c2 is select ename from another_emp where empno=eno;
5 begin
6 for mag in c1
7 loop
8 exit when c1%notfound;
9 eno:=mag.empno;
10 for mag2 in c2
11 loop
12 exit when c2%notfound;
13 dbms_output.put_line('eno is : '||eno ||' and name is :'||mag2.ename);
14 end loop;
15 end loop;
16* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> /
eno is : 7369 and name is :SMITH
eno is : 7499 and name is :ALLEN
eno is : 7521 and name is :WARD
eno is : 7566 and name is :JONES
eno is : 7654 and name is :MARTIN
eno is : 7698 and name is :BLAKE
eno is : 7782 and name is :CLARK
eno is : 7839 and name is :KING
eno is : 7844 and name is :TURNER
eno is : 7900 and name is :JAMES
eno is : 7902 and name is :FORD
eno is : 7934 and name is :MILLER
eno is : 111 and name is :mag
eno is : 111 and name is :vivek
eno is : 111 and name is :mag
eno is : 111 and name is :vivek

PL/SQL procedure successfully completed.

[rkonda99]

Thanks Mahesh for the fast reply.
Is it possible to use two different procedures, one cursor in each procedure. And, take the values from the first cursor (just like you did, i.e., C1) and use these values in Procedure 2 to do the other task?
Thanks,
Ramesh