Pl/SQL Procedure CURSOR For Loop

Question

I am new to PL/SQL and experimenting with CURSOR. I desire to verify an insertion procedure so I wrote another procedure to do so.

CREATE OR REPLACE PROCEDURE verify_insert

IS

   CURSOR map_cur IS

      SELECT Page_ID_NBR, Page_Type, Page_Dcpn FROM SSC_Page_Map;

      map_rec map_cur%ROWTYPE;

BEGIN

   OPEN map_cur;

   FOR map_rec in map_cur

   LOOP

      DBMS_OUTPUT.PUT_LINE('ID: ' || map_cur.Page_ID_NBR || ' ' || 'Type' ||  map_cur.Page_Type || ' ' || 'Description' || map_cur.Page_Dcpn);   

   END LOOP;

   CLOSE map_cur;

END;

SHOW ERRORS PROCEDURE verify_insert;

I am getting the following message

[Warning] ORA-24344: success with compilation error
19/44   PLS-00225: subprogram or cursor 'MAP_CUR' reference is out of scope
19/5    PL/SQL: Statement ignored
(47: 0): Warning: compiled but with compilation errors

I also see

Errors for PROCEDURE VERIFY_INSERT

LINE/COL ERROR                                                            
-------- -----------------------------------------------------------------
19/44    PLS-00225: subprogram or cursor 'MAP_CUR' reference is out of sco


19/5     PL/SQL: Statement ignored

As I wrote, I am new and trying to cobble together knowledge of PL/SQL from Oracle PL/SQL Programming (Feuerstein) and the net. Coming together but not as fast as I want.

Justin Cave · Accepted Answer · 2014-05-02 16:08:02Z

8

Your loop is fetching a row from the cursor into the record type. Inside the loop, you'd want to read the data from the record type. In your dbms_output.put_line call, you'd want to reference the record not the cursor.

DBMS_OUTPUT.PUT_LINE('ID: ' || map_rec.Page_ID_NBR || 
              ' ' || 'Type' || map_rec.Page_Type || 
              ' ' || 'Description' || map_rec.Page_Dcpn);

answered May 2, 2014 at 16:08

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mushy Over a year ago

Thank you. What I get for just copying bad examples off the net. Can you please point me to a great site where I can quickly increase my knowledge and avoid the hunt and peck approach that has its' pitfalls? Thank you.

Collectives™ on Stack Overflow

Pl/SQL Procedure CURSOR For Loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related