How to spit out a result set returned by an Oracle sproc

When a sproc returns a result set, in Oracle it takes a little more work to view the contents of that result set. But it can be done.

Simple example

As an example, say you want to call the spir_get_fooids_by_bazidkey sproc which returns a result set of foo ids, and you want to see what foo ids it returned. Here’s how that would look:

DECLARE
  IBAZIDKEY NVARCHAR2(200);
  OUTPUT_CV ir40_pkg.cur_fooId;
  v_recid RAW(32);
BEGIN
  IBAZIDKEY :=   '3001125370050120517';
  SPIR_GET_RECIDS_BY_BAZIDKEY(
    IBAZIDKEY => IBAZIDKEY,
    OUTPUT_CV => OUTPUT_CV
  );
  
  --Here's how we loop through a result set cursor
  LOOP
    FETCH OUTPUT_CV
    INTO  v_recid;
    EXIT WHEN output_cv%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(RAWTOHEX(v_recid));
  END LOOP;
  CLOSE output_cv;
  
END;

Advanced example

If the cursor’s package type has more than one column in it, you can fetch the output cursor into multiple variables and include them in the output. Or better yet, fetch ’em into a package-type variable so that you don’t have to declare 50 individual variables, like in the following example.

Here’s an example where the sproc returns five result sets, two of which are large. We fetch one row at a time into a package variable and then print out the columns we care about.

set serveroutput on
DECLARE
  IFOOID RAW(16);
  output_cv                       ir40_pkg.cur_FooMedia;
  output_cv_rel                   ir40_pkg.cur_RelatedFoo;
  output_cv_events                ir40_pkg.cur_FooEvent;
  output_cv_tags                  ir40_pkg.cur_FooTag;
  output_cv_custattrs             ir40_pkg.cur_CustomAttribute;
  v_successcode INTEGER;
  v_recid RAW(32);
  v_foomedia        ir40_pkg.rec_FooMedia;
  v_rel                   ir40_pkg.rec_RelatedFoo;
  v_events                ir40_pkg.rec_FooEvent;
  v_tags                  ir40_pkg.rec_FooTag;
  v_custattrs             ir40_pkg.rec_CustomAttribute;
BEGIN
  IFOOID :=   HEXTORAW('12939DFBEF28CFD08F02115D2C560001');
  spir_qry_foo(
    IFOOID => IFOOID,
    OUTPUT_CV => OUTPUT_CV,
    OUTPUT_CV_REL => OUTPUT_CV_REL,
    OUTPUT_CV_EVENTS => OUTPUT_CV_EVENTS,
    OUTPUT_CV_TAGS => OUTPUT_CV_TAGS,
    OUTPUT_CV_CUSTATTRS => OUTPUT_CV_CUSTATTRS,
    oSuccessCode => v_successcode
  );
  
  DBMS_OUTPUT.PUT_LINE('');
  DBMS_OUTPUT.PUT_LINE('FooMedia data:');
  LOOP
    FETCH OUTPUT_CV
    INTO  v_foomedia;
    EXIT WHEN output_cv%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Foo ID:' || RAWTOHEX(v_foomedia.m_fooid) || ' FooDate:' || TO_CHAR(v_foomedia.m_foodate, 'YYYY-MM-DD HH24:MI:SS') || ' Duration:' || v_foomedia.m_Duration);
  END LOOP;
  CLOSE output_cv;
  
  DBMS_OUTPUT.PUT_LINE('');
  DBMS_OUTPUT.PUT_LINE('Related foo data:');
  LOOP
    FETCH OUTPUT_CV_REL
    INTO  v_rel;
    EXIT WHEN output_cv_rel%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('FooId:' || RAWTOHEX(v_rel.m_FooId) || ' MediaType:' || v_rel.m_MediaType);
  END LOOP;
  CLOSE output_cv_rel;
  
  DBMS_OUTPUT.PUT_LINE('');
  DBMS_OUTPUT.PUT_LINE('IR_Event data:');
  LOOP
    FETCH OUTPUT_CV_events
    INTO  v_events;
    EXIT WHEN output_cv_events%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('FooId:' || RAWTOHEX(v_events.m_FooId) || ' FooDate:' || TO_CHAR(v_events.m_eventdate, 'YYYY-MM-DD HH24:MI:SS'));
  END LOOP;
  CLOSE output_cv_events;
  --Looping through the tags and custattrs cursors is left as an exercise for the reader
  
END;

Acknowledgments
Thanks to Akadia.com (and StackOverflow.com for the pointer).

Advertisements

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s