Oracle – TORA Execute Package Procedure with OUT REF CURSOR

oracleplsqltora

I'm trying to execute a Package Procedure that has a couple in parameters and a REF CURSOR out parameter but can't seem to figure it out for TORA (my SQL IDE)

Here is a contrived example of a procedure I'd like to execute and see the cursor for:

PROCEDURE get_peeps_in_city ( pi_city IN varchar(100), po_recordset OUT REF CURSOR )
    IS
    BEGIN
         OPEN po_recordset
         FOR Select Id, 
                FName, 
                LName
             FROM People
             WHERE City like '%' || pi_city || '%'
    END;

Here is what I've tried so far:

DECLARE
    v_cursor REF CURSOR;
BEGIN
    execute PKG_PEEPS.get_peeps_in_city('Chicago', v_cursor);
END

The error that I get is something along the lines of:

PLS-00103: Encountered the symbol "END" when expecting one of the
following:
begin function package pragma procedure subtype type use form current
cursor

Best Answer

You're probably looking for something like this:

DECLARE
    v_cursor SYS_REFCURSOR;
    v_Id NUMBER;
    v_FName VARCHAR2(200);
    v_LName VARCHAR2(200);
BEGIN
    PKG_PEEPS.get_peeps_in_city('Chicago', v_cursor);

    LOOP
      FETCH v_cursor INTO v_Id, v_FName, v_LName;
      EXIT WHEN v_cursor%NOTFOUND;
        -- do something with v_Id, v_FName, v_LName
    END LOOP;
    CLOSE v_cursor;
END;
/

Update:

You probably would like that the result of your query is displayed in your IDE as if you had run the SELECT statement directly. This is not going to happen with the above code and I don't know of any code that could achieve that (unless you install a specific table function).

The best thing you can do is output the retrieved data in the loop (using DBMS_OUTPUT.PUT_LINE).

Related Topic