CODEWITHSUNDEEP
sqloracle-databasestored-procedures
deanmau5
deanmau5

Reputation: 871

Oracle Cursor not being populated from SELECT statement?

I've been writing up a Stored Proc to select a bunch of ID's from a table, save the resultset into a cursor, then loop through the Cursor, updating rows in a table table based on whichever ID is in focus in the Cursor. Planning to add in a few more conditional statements, but so far..

CREATE OR REPLACE PROCEDURE CHANGE_PESONAL_DETAILS
AS

CURSOR
person_cur
IS

select b.id
    from blah1 b
        inner join blah2 b2 on b.id = b2.fkid;
person_rec person_cur%ROWTYPE;

BEGIN
FOR person_rec in person_cur

LOOP

dbms_output.put_line
(
person_rec.id
);

UPDATE blah1 set Details = DetailsLineOne || ' ' || DetailsLineTwo WHERE id = person_rec.id;

END LOOP;
COMMIT;

END;

What I have here compiles fine, though when executed, the update never takes place. I've added in the output line to see what is even in the Cursor, but nothing gets printed.

My Select statement to populate the Cursor runs fine on its own, returning all the data I'd expect it to, though when executed in the above, nothing. It's as if everything after Begin isn't executing, though I can't understand why as previously mentioned, my Select returns results on its own.

Maybe in the context of populating a cursor something is behaving different? Any advice would be great..

Upvotes: 0

Views: 569

Answers (2)

TechDo
TechDo

Reputation: 18629

Please check the procedure without a cursor and loop.

CREATE OR REPLACE PROCEDURE CHANGE_PESONAL_DETAILS
AS
BEGIN
UPDATE blah1 set Details = DetailsLineOne || ' ' || DetailsLineTwo WHERE id IN (select b.id
    from blah1 b inner join blah2 b2 on b.id = b2.fkid);
END;

Check using loop

CREATE OR REPLACE PROCEDURE CHANGE_PESONAL_DETAILS
AS
CURSOR person_cur IS
  select b.id
    from blah1 b
        inner join blah2 b2 on b.id = b2.fkid;

  person_rec person_cur%ROWTYPE;
BEGIN
    OPEN person_cur;
    LOOP
      FETCH person_cur INTO person_rec;
      EXIT WHEN person_cur%NOTFOUND;

      UPDATE blah1 set Details = DetailsLineOne || ' ' || DetailsLineTwo WHERE id = person_rec.id;
    END LOOP;
    CLOSE person_cur;
END;

Upvotes: 1

Aspirant
Aspirant

Reputation: 2278

we can't declare a rowtype of cursor if we use the cursor in for loop the variable used in for loop will be used to access the columns in the cursor.

So remove the rowtype declaration and try like and moreover include the SET SERVEROUT ON command before executing the procedure to see the results(printable statements)

CREATE OR REPLACE PROCEDURE CHANGE_PESONAL_DETAILS
AS

CURSOR person_cur IS
     select b.id from blah1 b inner join blah2 b2 on b.id = b2.fkid;
BEGIN
     FOR person_rec in person_cur LOOP
          dbms_output.put_line(person_rec.id);
          UPDATE blah1 set Details = DetailsLineOne || ' ' || DetailsLineTwo WHERE id = person_rec.id;
     END LOOP;
     COMMIT;
END;

Upvotes: 0

Related Questions