CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqlsys-refcursor
Oysio
Oysio

Reputation: 3318

How to get refcursor result/output to show as text?

I'm trying to call a stored procedure in Oracle and show the results of the call, the problem is that it crashes on the line FETCH v_cur into v_a; with error: ORA-06504: PL/SQL: Return types of Result Set variables or query do not match.

I guess the output of the query does not match v_a VARCHAR2(100), but I don't know what to put there instead. The stored procedure that's being called does a join of several tables and selects more than 20+ different columns belonging to different tables. So what I would want is to just view the output of the query without having to refer to each result column separately. How I would go and do this ?

I'm using SQL Navigator (not that important I guess).

DECLARE 
  v_cur SYS_REFCURSOR;
  v_a   VARCHAR2(100);
BEGIN
   pkg_get_results.get_rows(v_cur,to_date('2012/04/12', 'yyyy/mm/dd'),to_date('2012/04/12', 'yyyy/mm/dd'), '','','','');
  LOOP
    FETCH v_cur into v_a;  -- what to put here ?
    EXIT WHEN v_cur%NOTFOUND;
    dbms_output.put_line(v_a );
  END LOOP;
  CLOSE v_cur;
END;

Upvotes: 0

Views: 3020

Answers (1)

Igby Largeman
Igby Largeman

Reputation: 16747

SQL Navigator does have the ability to do this for you. How to do it exactly depends on your version of Navigator, and it's conceivable (though I don't know) some versions may not have it.

Instructions can be found in this thread: http://sqlnavigator.inside.quest.com/thread.jspa?threadID=2466

Incidentally, Toad also has this ability.

Upvotes: 2

Related Questions