TORA Execute Package Procedure with OUT REF CURSOR

Question

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

You can probably ignore any syntax errors, the code is not the same as my production code. I can guarantee the procedure code works correctly and is syntactically sound because it works for several different applications. The core of the question is how to call a stored procedure with a ref cursor out parameter from TORA specifically. — Jacob, Aug 10 '11 at 14:19
i see... in this case i have no idea)) i've downloaded and installed this software since it's intrested me (i hear about it first from you). it looks cute and comfortable, but for some reason it has crashed after 1 minute of my using it (( — heximal, Aug 10 '11 at 14:25
and btw, what if we omit execute keyword? as i remember it's not necessary in plsql — heximal, Aug 10 '11 at 14:27

Codo · Accepted Answer · 2011-08-10T20:19:03.570

3

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

edited Aug 10 '11 at 20:19

answered Aug 10 '11 at 18:07

Codo

75,595
17
168
206

that looks promising... but when I give it a try, I get some new errors about v_cursor ref cursor. > PLS-00201: identifier 'CURSOR' must be declared. – Jacob Aug 10 '11 at 19:23
Sorry, I forgot that you have to use the old LOOP / FETCH / NOTFOUND / CLOSE thing when using a weakly typed cursor. You might have a strongly typed one. But since your procedure declaration is invalid, I can't tell for sure. Anyway, I've updated my answer. – Codo Aug 10 '11 at 20:20
2

@Codo - you can get the results displayed directly in SQL*Plus or SQL Developer, but I can't see how to do it in TOra (and don't know about Toad etc.). You can use `variable v_cursor refcursor`, `exec` the procedure with `:v_cursor` as the out parameter, and then just `print :v_cursor`. The loop/dbms_output looks like the only way to do it in TOra, though. – Alex Poole Aug 11 '11 at 10:55
That's cool. I didn't know that. But it then is most likely restricted to SQL*Plus and SQL Developer because it's a SQL*Plus command and SQL Developer is the only other tool with a rather complete SQL*plus command implementation. – Codo Aug 11 '11 at 14:37

TORA Execute Package Procedure with OUT REF CURSOR

1 Answers1