How to execute an oracle procedure with an out cursor parameter in an anonymous PL/SQL block?

Question

I am learning PL/SQL using the Oracle XE's HR database.

I have created the following stored procedure:

CREATE OR REPLACE PROCEDURE get_employees( p_country_id IN CHAR
                                         , p_emp        OUT SYS_REFCURSOR) 
IS
BEGIN

  OPEN p_emp FOR
    SELECT e.first_name
          ,e.last_name
          ,e.department_id
          ,d.department_name
          ,l.city
          ,l.state_province
      FROM employees e
     INNER JOIN departments d
        ON e.department_id = d.department_id
     INNER JOIN locations l
        ON d.location_id = l.location_id
     WHERE l.country_id = p_country_id; 
END;

I know how to execute it in SQL Developer GUI interface and see the results. I also learned from Justin Cave at here and here how to execute it and see the results the SQL*Plus style like so:

VARIABLE CE REFCURSOR;
EXEC GET_EMPLOYEES('US', :CE);
PRINT CE;

I'd like to execute the stored procedure in an anonymous PL/SQL block and see the results in a grid, but it has not been successful.

Like what Justin Cave suggested, the following executes just fine, but results are not displayed:

DECLARE
  C_EMP SYS_REFCURSOR;
BEGIN
  GET_EMPLOYEES('US', C_EMP);
END;

The following will fail:

DECLARE
  C_EMP SYS_REFCURSOR;
  L_REC C_EMP%ROWTYPE; --THIS LINE FAILS.
BEGIN
  GET_EMPLOYEES('US', C_EMP);
  -- LOOP AND FETCH GOES HERE.
END;

The error message says:

PLS-00320: the declaration of the type of this expression is incomplete or malformed

I don't understand it. I have been doing that in a few other anonymous PL/SQL blocks and it worked perfectly. What's wrong with that line here? Can't figure out.

Answer 1

I think you are misunderstanding the use of %ROWTYPE. You should just use %ROWTYPE when you are storing all rows from a table. Instead of using %ROWTYPE, make your own type(record) that fits the datatype of the columns that you are fetching. Try this:

DECLARE
  C_EMP SYS_REFCURSOR;
  TYPE new_type IS RECORD(FIRST_NAME VARCHAR2(100), LAST_NAME VARCHAR2(200), DEPARTMENT_ID NUMBER, DEPARTMENT_NAME VARCHAR2(200), CITY VARCHAR2(200), STATE_PROVINCE VARCHAR2(200));
  L_REC new_type; --instead of using %ROWTYPE, use the declared type
BEGIN
  GET_EMPLOYEES('US', C_EMP);
  LOOP
 FETCH c_emp INTO l_rec;
 EXIT WHEN c_emp%NOTFOUND;

     dbms_output.put_line(l_rec.first_name||'_'||
                          l_rec.last_name||'_'||
                          l_rec.department_id||'_'||
                          l_rec.department_name||'_'||
                          l_rec.city||'_'|| 
                          l_rec.state_province);
 END LOOP;

CLOSE c_emp;
END;

Answer 2

I'm sure there is no short answer for this question.

To understand what the problem is you should investigate what strong typed and weak typed refcursors are.

Oracle DBMS has no built-in tools to put refcursor results into grid. If you're gonna write one try DBMS_SQL package and dynamic PL/SQL - it's definitly possible to write a program producing the result you expect (i.e. putting any sys_refcursor into grid).

But if you just started to learn PL/SQL, please don't waste your time now - first, get some experience, and you will see how to do it. And as long as it did not happen, use SQL Developer's hack.