Re: Help with Cursors !

Big George wrote:
> Hello,
>
> I need some help with code. I need to create and loop a cursor1 and
> then use cursor2 in order to store the rows that I am selecting in
> order to pass cursor2 for a datatable. Is that possible?
>
> PROCEDURE My_SP(i_param IN INTEGER)
> IS
> v_Field1 CHAR;
> v_rec INTEGER;
> CURSOR Cursor1
> IS
> SELECT Field1
> FROM MyTable ;
> BEGIN
> OPEN Cursor1;
> LOOP
> FETCH Cursor1 INTO v_Field1;
> EXIT WHEN Cursor1%NOTFOUND;
>
> SELECT count('x') INTO v_rec FROM OtherTables
> WHERE ThisField = v_Field1;
> IF v_rec > 0 THEN
> -- Here I have to store v_Field1 in some other cursor in order
> to
> -- have a list that I will use to populate a datatable in
> VB.NET
> -- So, I would like to construct Cursor_2 for doing something
> like:
> -- CURSOR_2 OUT SYS_REFCURSOR
> -- OPEN CURSOR_2 FOR
> -- SELECT
> -- Field1,
> -- Field2,
> -- FROM CURSOR_2;
> --
> -- Is it necessary to INSERT in a real table ?
> -- How can I accomplish this, please?
> END IF;
> END LOOP;
> CLOSE Cursor1;
> END My_SP;
>
> Thank you very much
>

It is possible but unless you have a neolithic version of Oracle highly inadvisable. You would be far better off doing array processing rather than single row processing.

Look at ARRAY PROCESSING demos in Morgan's Library at www.psoug.org.

Also look at the nested cursor loops demo in the library under CURSORS & CURSOR LOOPS.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org