Cursors in oracle for updating all records examples


12-Jun-2017 09:12

Question: I have a ref cursor defined as an OUT parameter and I am trying to get a receiving PL/SQL to accept the ref cursor.Can I see some examples of working ref cursor examples, especially passing a ref cursor between two programs?

By strongly typing the cursor variable, you define what the cursor can return.

The cursor variable can also be returned by a function and assigned to another variable.

The REF CURSOR variable is not a cursor, but a variable that points to a cursor.

declare2 type auth_cursor is ref cursor return author%rowtype;3 c2 auth_cursor;4 r_c2 c2%rowtype;56 function get_auth return auth_cursor7 is8 c1 auth_cursor; 9 begin10 open c1 for select * from author;11 return c1; 12 end;1314 begin15 c2 := get_auth;16 loop17 fetch c2 into r_c2;18 exit when c2%notfound;19 dbms_output.put_line(initcap(r_c2.author_last_name));20 end loop;21 close c2;22 end;23 / Line 2 defines the cursor type.

Lines 3 and 4 define the cursor and return record used in the body.declare2 type auth_cursor is ref cursor;3 c1 auth_cursor; 4 r_c1 author%rowtype;5 r2_c1 book%rowtype;6 r3_c1 number; 7 begin8 open c1 for select * from author;9 fetch c1 into r_c1;10 close c1;11 open c1 for select * from book;12 fetch c1 into r2_c1;13 close c1;14 open c1 for select sum(quantity)15 from store join sales using (store_key) 16 group by store_name;17 fetch c1 into r3_c1;18 close c1; 19 end;20 / Although the block does not do anything but open and close the cursor variable, it does demonstrate that weakly typed variables can be defined differently each time the variable is opened.



While Angelus and Darla were nearly inseparable throughout their history together, Angel later commented that what they had was never true love, as they were both incapable of feeling love, and sometimes abandoned one another out of self-preservation.… continue reading »


Read more

Una rivalidad futbolística que va más allá del juego, ya que no sólo hay 22 jugadores defendiendo el color de su equipo, sino además el... Manuel Acuña Asenjo Puede llamar la atención la circunstancia que las clases dominadas (llamadas, a menudo, ‘pueblo’, ‘población’, ‘masas’) no sean consideradas en este análisis.… continue reading »


Read more

When Tony gets a call telling him that he has a son he could not believe it.… continue reading »


Read more


One big no-no is to blow your nose during the meal – preposterously unfair, given the spice level of pretty much every Korean dish.… continue reading »


Read more

Dance clubs and dive bars may have worked in the '90s, but now, even if you’re out, your phone is a much easier way to find someone to "watch Netflix and chill" with (especially someone you won’t regret tomorrow).… continue reading »


Read more