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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tay JohnsonAuthor Commented:
Thank you so much
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PL/SQL

From novice to tech pro — start learning today.