Oracle PL/SQL using cursor with for loop for data retrieving

Question

Hi I am trying to use for loop for retrieving data in oracle pl/sql but I am getting an error can someone help me

SET SERVEROUTPUT ON
DECLARE

  fname employees.first_name%TYPE;
  empid employees.employee_id%TYPE;
  CURSOR emp_cursor IS
  SELECT employee_id,first_name from employees ORDER BY employee_id;

BEGIN

  open emp_cursor;  
  FOR empid IN emp_cursor loop

  DBMS_OUTPUT.PUT_LINE('employee id is ' ||empid || 'first name is '||fname);

end LOOP;

END;

I am getting an error on the DBMS output line something like the number or type of variables not right. I am trying to retrieve employee id and first name from employees table in oracle sample schema.

Can someone please guide me

u need not issue an OPEN statement.. As FOR LOOP do it implicitly for you! — Maheswaran Ravisankar
– Maheswaran Ravisankar, Commented Jan 27, 2014 at 8:46
What happened with the solution below? Please clarify why it was unaccepted! — Maheswaran Ravisankar
– Maheswaran Ravisankar, Commented May 5, 2018 at 17:33

Maheswaran Ravisankar · Accepted Answer · 2014-01-27 08:53:01Z

USING FOR..LOOP in CURSORS.

SET SERVEROUTPUT ON
DECLARE
  /* DECLARE Cursor */
  CURSOR emp_cursor IS
  SELECT employee_id,first_name from employees ORDER BY employee_id;

BEGIN
  FOR empid IN emp_cursor loop
     /* empid already has the row details, you don't need to have any other variables */
     DBMS_OUTPUT.PUT_LINE('employee id is ' ||empid.employee_id || 'first name is '||empid.first_name);

  end LOOP;

END;
/

USING OPEN FETCH and CLOSE

SET SERVEROUTPUT ON
DECLARE

  fname employees.first_name%TYPE;
  empid employees.employee_id%TYPE;
  CURSOR emp_cursor IS
  SELECT employee_id,first_name from employees ORDER BY employee_id;

BEGIN

  open emp_cursor; 

  /* LOOP until the cursor is empty */
  LOOP 
      FETCH emp_cursor INTO empid,fname;
      /* Now we fetch data from cursor, and put it into our variables */
      EXIT WHEN emp_cursor%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE('employee id is ' ||empid || 'first name is '||fname);
  END LOOP;

  /* As we OPEN'ed the cursor manually, we have to CLOSE it without fail */
  CLOSE emp_cursor;

END;
/

Collectives™ on Stack Overflow

Oracle PL/SQL using cursor with for loop for data retrieving

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related