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;