WORKING WITH REF CURSOR

I create and populate the following table:
CREATE TABLE plch_tab (item VARCHAR2 (10))
/

BEGIN
   INSERT INTO plch_tab
        VALUES ('Keyboard');

   INSERT INTO plch_tab
        VALUES ('Mouse');

   COMMIT;
END;
/
I then create this function to fetch a row from a cursor variable and return the item:
CREATE OR REPLACE FUNCTION plch_getitem (
   plch_cur IN SYS_REFCURSOR)
   RETURN plch_tab.item%TYPE
IS
   lvretval   plch_tab.item%TYPE;
BEGIN
   FETCH plch_cur INTO lvretval;

   RETURN lvretval;
END plch_getitem;
/
Now I need to write a block that will fetch the rows from the table and display both the count of rows fetched and the name of that last item fetched. Here is an almost complete version of this block:
DECLARE
   lvitem     plch_tab.item%TYPE;
   test_cur   SYS_REFCURSOR;
BEGIN
   OPEN test_cur FOR
        SELECT * FROM plch_tab ORDER BY item;

   /*FETCH*/

   DBMS_OUTPUT.put_line ('Count = ' || test_cur%ROWCOUNT);
   DBMS_OUTPUT.put_line ('Item = ' || NVL (lvitem, 'NOT SET'));

   CLOSE test_cur;
END;
/
Which of the choices offer a replacement for the /*FETCH*/ so that after the resulting block is executed, the following output will be shown on the screen?
Count = 2
Item = Mouse



After opening a cursor variable, you can fetch the rows of the query result set with the same FETCH statement you would use with an explicit cursor. You can fetch from the cursor variable in the same block in which it was opened, but also from within any block in which the cursor variable can be referenced (for example, if you passed it as an argument to a subprogram).
The return type of the cursor variable must be compatible with the into_clause of the FETCH statement. If the cursor variable is strong, PL/SQL catches incompatibility at compile time. If the cursor variable is weak, PL/SQL catches incompatibility at run time, raising the predefined exception ROWTYPE_MISMATCH before the first fetch.
You should use a strong cursor variable (declared with a TYPE that has a RETURN clause) whenever you are opening a cursor variable for a static query.

Comments

Popular posts from this blog

Queries For Oracle Interface Errors Records.

Customising PO Output For Communication Report in Oracle Purchasing

Oracle APPS Useful Queries