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:
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
).