Declare 2 explicit cursors in the same procedure with same table

Hello, is it possible to declare two cursors with the same table and some of the same column names?

Cursor r_cur
Is
Select a, b, c;
R_fhe gssh_cur%ROWTYPE;

Cursor y_cur
Is
Select b, c, f, j, g;
T_fhe gshh_cur%ROWTYPE;
Tay JohnsonAsked:
Who is Participating?
 
johnsoneSenior Oracle DBACommented:
As others have said, without your code, we certainly cannot tell you why you are getting an error.  To show you that it works, here are some examples.

The setup:
CREATE TABLE mytab 
  ( 
     n NUMBER 
  ); 

INSERT INTO mytab 
VALUES      (1); 

INSERT INTO mytab 
VALUES      (2); 

INSERT INTO mytab 
VALUES      (3); 

COMMIT; 

Open in new window

Doing cursors the hard way:
DECLARE 
    CURSOR c1 IS 
      SELECT n 
      FROM   mytab; 
    c1_rec c1%ROWTYPE; 
    CURSOR c2 IS 
      SELECT n 
      FROM   mytab; 
    c2_rec c2%ROWTYPE; 
BEGIN 
    OPEN c1; 

    LOOP 
        FETCH c1 INTO c1_rec; 

        exit WHEN c1%NOTFOUND; 

        dbms_output.Put_line('c1 value    -> ' 
                             || c1_rec.n); 

        OPEN c2; 

        LOOP 
            FETCH c2 INTO c2_rec; 

            exit WHEN c2%NOTFOUND; 

            dbms_output.Put_line('c2 value    -> ' 
                                 || c2_rec.n); 
        END LOOP; 

        CLOSE c2; 
    END LOOP; 

    CLOSE c1; 
END; 

/ 

Open in new window

Using a cursor for loop (let the database do the work)
DECLARE 
    CURSOR c1 IS 
      SELECT n 
      FROM   mytab; 
    CURSOR c2 IS 
      SELECT n 
      FROM   mytab; 
BEGIN 
    FOR c1_rec IN c1 LOOP 
        dbms_output.Put_line('c1 value    -> ' 
                             || c1_rec.n); 

        FOR c2_rec IN c2 LOOP 
            dbms_output.Put_line('c2 value    -> ' 
                                 || c2_rec.n); 
        END LOOP; 
    END LOOP; 
END; 

/ 

Open in new window

Or, don't even use a declared cursor (my personal favorite if parameterized cursors are not needed)
BEGIN 
    FOR c1_rec IN (SELECT n 
                   FROM   mytab) LOOP 
        dbms_output.Put_line('c1 value    -> ' 
                             || c1_rec.n); 

        FOR c2_rec IN (SELECT n 
                       FROM   mytab) LOOP 
            dbms_output.Put_line('c2 value    -> ' 
                                 || c2_rec.n); 
        END LOOP; 
    END LOOP; 
END; 

/ 

Open in new window

All three examples extract the same set of data from the same table.  The second set is opened inside the first one.
0
 
johnsoneSenior Oracle DBACommented:
Yes.

Your sample code doesn't look correct at all.
0
 
Mark GeerlingsDatabase AdministratorCommented:
Yes, that is possible.  I agree that the syntax you posted is at least incomplete, and may be incorrect.  It should be more like this:

Cursor r_cur
 Is
 Select a, b, c
    from [some_table]
    where [some conditions are true];
 r_cur_rec r_cur%ROWTYPE;
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Tay JohnsonAuthor Commented:
You can create 2. Select statement almost identical same table, same columns except one cursor has 2 more columns then use them in the same procedure?
0
 
johnsoneSenior Oracle DBACommented:
Sounds like the same question as the original.

Yes.

You can create 2 identical cursors, as long as they have different names.
0
 
Tay JohnsonAuthor Commented:
Thanks guys
0
 
Tay JohnsonAuthor Commented:
I tried that and it didn't work.
0
 
slightwv (䄆 Netminder) Commented:
Post the code you tried.
0
 
Tay JohnsonAuthor Commented:
My cursors have different names but use the same table and somes of the same colums in the same procedure and ot doesn't work
0
 
Tay JohnsonAuthor Commented:
It says invalid cursor
0
 
slightwv (䄆 Netminder) Commented:
We need to see your code.
0
 
Mark GeerlingsDatabase AdministratorCommented:
"It says invalid cursor"

Then either:
1. You declared a cursor with one name (like: "r_cur") but tried to refer to it with a different name (like: "p_cur" for example).
2. Or, you tried to refer to the cursor after it was closed.

But, as slightwv indicated, if you chose to not post your code here, it is very difficult for us to tell you exactly where the error may be.
0
 
Tay JohnsonAuthor Commented:
Thank you so much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.