Getting output from Ref Cursors in PL/SQL

A colleague of mine (Martin, you know who you are), remarked the other week that he wasn’t overly interested in the contents of the blogosphere. He said that it usually put him in mind of the cartoon of the tag-cloud consisting solely of the word “me”. This got me to thinking, why do I do this ?
Let’s put my ego to one side for a moment ( pause to sounds of straining, followed by a dull thud). That was heavier than it looked.

One of the reasons for maintaining this blog is that I’ve got a quick reference to look at if I come across something I did a while ago and need a quick reminder of syntax etc. Also, my Mum likes to know what I’m up to.
The starting point for this entry was to attempt to drag together all the basic bits about Ref Cursors in PL/SQL – specifically, accessing them from within PL/SQL itself.

Whilst I was writing this, it was pointed out to me that SQLDeveloper doesn’t handle Ref Cursors quite as nicely as Toad. The specific issue was the difficulty in dumping the results into a grid, from whence it can be transferred to Open Office Spreadsheet ( or Excel).

For the most part, Ref Cursors are used to transfer data from the database to a web application. So, why would you need to start fiddling about with getting results back in PL/SQL ?
There are probably several answers to this question. However, for me, it’s mainly a case of having to trace problems raised in various support calls. Knowing what data results from each of the calls in a process usually helps a bit.

Let’s start with some test data.
Speaking of egos, this data is the England Squad for the Euro 2012 qualifiers against Bulgaria and Switzerland. Being the classy guy I am, I will resist pointing out that they should do really well playing away.

CREATE TABLE players(
    name VARCHAR2(30),
    position VARCHAR2(20),
    caps NUMBER(3),
    goals NUMBER(2))
/

--
-- Script to populate the players table
--
DECLARE
    PROCEDURE ins( p_name VARCHAR2, p_pos VARCHAR2, p_caps NUMBER,
        p_goals NUMBER)is
    BEGIN
        INSERT INTO players( name, position, caps, goals)
        VALUES( p_name, p_pos, p_caps, p_goals);
    END;
BEGIN
    ins('HART', 'GOALKEEPER',6,0);
    ins('CARSON','GOALKEEPER', 3,0);
    ins('FOSTER', 'GOALKEEPER', 4,0);
    ins('CAHILL', 'DEFENDER', 1,0);
    ins('COLE A','DEFENDER',85,0);
    ins('DAWSON','DEFENDER',2,0);
    ins('GIBBS','DEFENDER',1,0);
    ins('JAGIELKA','DEFENDER',6,0);
    ins('JOHNSON G', 'DEFENDER', 29,1);
    ins('LESCOTT','DEFENDER',10,0);
    ins('UPSON', 'DEFENDER', 21,2);
    ins('BARRY','MIDFIELDER',42,2);
    ins('CARRICK', 'MIDFIELDER',22,0);
    ins('GERRARD', 'MIDFIELDER', 87,19);
    ins('JOHNSON A', 'MIDFIELDER', 4,2);
    ins('MILNER','MIDFIELDER',14,0);
    ins('WALCOTT','MIDFIELDER',14,3);
    ins('WRIGHT-PHILLIPS','MIDFIELDER',35,6);
    ins('YOUNG','MIDFIELDER',9,0);
    ins('BENT','STRIKER',7,1);
    ins('COLE C', 'STRIKER',7,0);
    ins('CROUCH','STRIKER',40,21);
    ins('DEFOE','STRIKER',45,15);
    ins('ROONEY','STRIKER',67,26);
END;
/

Now let's write a procedure to return a Ref Cursor of all the players in a given position.

CREATE OR REPLACE PROCEDURE list_players_pr( 
    p_position IN VARCHAR2 DEFAULT 'ALL', p_result_set IN OUT SYS_REFCURSOR) AS
BEGIN
        OPEN p_result_set FOR
            SELECT name, position, caps, goals
            FROM players
            WHERE 
                CASE p_position 
                    WHEN 'ALL' THEN position 
                    ELSE p_position
                END = position;
END;
/

Possibly pedantic and irrational rant warning : to my mind, a PL/SQL Stored Program Unit that does no non-select DML and returns a single value should be a function and not a procedure. Function – take in argument(s), do some kind of translation / look-up, return a value.
Procedure – Take in argument(s), change something, report back if/how that something ( usually DML) worked.
If nothing else, you can usually call a function directly from SQL ( as distinct from SQL*Plus). This is not the case with a procedure.
Once again, it seems that the rest of the world hasn't caught up with me yet on this particular point ( oh, the ego's crept back in).

Anyhow, now we're here, let's start by getting some output using good old SQL*Plus :

var rs REFCURSOR
exec list_players_pr('GOALKEEPER' :rs)
print rs

NAME	     POSITION 		  CAPS     GOALS 
--------------------------------- ---------- ----------
HART	     GOALKEEPER		     6		0 
CARSON     GOALKEEPER		     3		0 
FOSTER   GOALKEEPER		     4		0 

You can reference the fields in the refcursor by name

format a30 name
format a20 position

As mentioned earlier, things are a bit more tricky using straight SQL. You could try using a wrapper function like this :

CREATE OR REPLACE FUNCTION wrap_list_players_fn ( p_position IN VARCHAR2 DEFAULT 'ALL')
    RETURN SYS_REFCURSOR AS
    p_rs SYS_REFCURSOR;
BEGIN
    list_players_pr(p_position, p_rs);
    RETURN p_rs;
END;
/

The output looks something like this :

SQL> SELECT wrap_list_players_fn('GOALKEEPER') FROM dual; 

WRAP_LIST_PLAYERS_FN 
-------------------- 
CURSOR STATEMENT : 1 

CURSOR STATEMENT : 1 

NAME	     POSITION            CAPS      GOALS 
--------   -------------------- ---------- ---------- 
HART     GOALKEEPER		     6		0 
CARSON GOALKEEPER		     3		0 
FOSTER  GOALKEEPER		     4		0 

OK, so what if you want to use SQLDeveloper to dump the results into a grid so you can do stuff like export it to an xls file, or even a csv.
SQLDeveloper doesn't really do Ref Cursors at the moment.
In order to get output using the first SQL*Plus example, you have to run the parameter setting, exec and print of the variable as a script so the output isn't presented in a results grid.
You can, of course, use the wrapper function route, but you'll get something like :

select wrap_list_players_fn('STRIKER') from dual;
{<NAME=BENT,POSITION=STRIKER,CAPS=7,GOALS=1>,<NAME=COLE C,POSITION=STRIKER,CAPS=7,GOALS=0>,<NAME=CROUCH,POSITION=STRIKER,CAPS=40,GOALS=21>,<NAME=DEFOE,POSITION=STRIKER,CAPS=45,GOALS=15>,<NAME=ROONEY,POSITION=STRIKER,CAPS=67,GOALS=26>,}

That's SQLDeveloper 1.5.5. Version 2.1.0 is even less useful.
It looks like the best way to do this is to use an anonymous PL/SQL block.

set serveroutput on size 1000000
spool player_list.csv
set feedback off
--
-- Script to format the output from a REF CURSOR returned from a 
-- procedure call
--
DECLARE 
    l_rs SYS_REFCURSOR;
    --
    -- You can use scalar variables here to retrieve each element of the row
    -- from the cursor, or you can use a record
    --
    TYPE rec_rs IS RECORD (
        name players.name%TYPE,
        position players.position%TYPE,
        caps players.caps%TYPE,
        goals players.goals%TYPE);
    --
    -- ...or in this case, a table of records
    --    
    TYPE typ_rs IS TABLE OF rec_rs INDEX BY PLS_INTEGER;
    
    tbl_rs typ_rs;
    l_index PLS_INTEGER := 1;
BEGIN
    --
    -- First output a header row
    --
    DBMS_OUTPUT.PUT_LINE('NAME,POSITION,CAPS,GOALS');
    list_players_pr(p_position => 'ALL', p_result_set => l_rs);
    LOOP
        FETCH l_rs INTO tbl_rs(l_index);
        EXIT WHEN l_rs%NOTFOUND;
        l_index := l_index + 1;
    END LOOP;
    --
    -- Now you've got the results in a PL/SQL table, you can do any kind
    -- of manipulation you like on the data. In this case, we're just
    -- going to print out comma separated records
    --
    FOR i IN 1..tbl_rs.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(tbl_rs(i).name
            ||','||tbl_rs(i).position
            ||','||tbl_rs(i).caps||','||tbl_rs(i).goals);
    END LOOP;
END;
/
spool off

Now, you can just open the csv file in the Spreadsheet, specify the seperator character ( comma in this case) and away you go.
I've tested this in Open Office Spreadsheet but the same is true in Excel.
That's all well and good for a strongly typed Ref Cursor, or even a weakly-typed one, provided you know the structure at compile time.
But... it's also possible to get output from a ref cursor without knowing ahead of time what it's structure is.

In the course of researching this article, I stumbled across a post on the Oracle Forums about how to interrogate a ref cursor and format the output using Oracle's XML functionality.

On the basis that, if an idea is good enough, it's worth plagiarising, I've tweaked the code a bit, added a Global Temporary Table, and come up with something that seems to do the job.
At this point, I should give an authoratitive explaination as to how it all works. Unfortunately, I haven't got a clue. Furthermore, It's not the neatest bit of code I've ever written and I'm sure someone out there could improve on it. However, for what it's worth...

CREATE GLOBAL TEMPORARY TABLE gtt_refcur (
    NAME VARCHAR2(100),
    VALUE VARCHAR2(4000))
    ON COMMIT DELETE ROWS
/   

Ordinary every-day Global Temporary Table to hold name / value pairs. As with the Ref Cursor, Global Temporary Tables reside in the PGA rather than the SGA. More on the finer points of this in a bit. In the meantime, here's the code to generate the csv file :

set serveroutput on size 1000000
spool player_list.csv
DECLARE
    l_rs SYS_REFCURSOR;
    TYPE typ_cols IS TABLE OF VARCHAR2(30) INDEX BY PLS_INTEGER;
    tbl_cols typ_cols;
    l_elem PLS_INTEGER := 0;
BEGIN
    list_players_pr( p_position => 'MIDFIELDER', p_result_set => l_rs);
    --
    -- Now we've got the cursor, convert the output into name/value pairs
    --
    INSERT INTO gtt_refcur( name, value) (
        SELECT t2.COLUMN_VALUE.getrootelement () name,
            EXTRACTVALUE( t2.COLUMN_VALUE, 'node()') value
        FROM TABLE( XMLSEQUENCE( l_rs)) t,
            TABLE( XMLSEQUENCE( EXTRACT( COLUMN_VALUE, '/ROW/node()'))) t2);
    --
    -- Now go through the GTT and get the column names
    --
    SELECT DISTINCT name BULK COLLECT INTO tbl_cols
    FROM gtt_refcur;
    --
    -- Output the header record
    --
    FOR z IN 1..tbl_cols.COUNT LOOP
        DBMS_OUTPUT.PUT(tbl_cols(z));
        IF z = tbl_cols.COUNT THEN
            DBMS_OUTPUT.NEW_LINE;
        ELSE
            DBMS_OUTPUT.PUT(',');
        END IF;
    END LOOP;
    --
    -- Now we need to get the element values
    --
    FOR r_rec IN ( SELECT name, value from gtt_refcur) LOOP
        l_elem := l_elem + 1;
        DBMS_OUTPUT.PUT(r_rec.value);
        IF MOD( l_elem, tbl_cols.COUNT) = 0 THEN
            DBMS_OUTPUT.NEW_LINE;
        ELSE
            DBMS_OUTPUT.PUT(',');
        END IF;
    END LOOP;
END;
/
spool off

First point to note – I've not tried this with any date data.
Second and more general point on Ref Cursors, Global Temporary Table, and PL/SQL Tables – they all reside in PGA memory. As such, they have the propensity to wreak havoc with the memory usage of the database as a whole if over-used. This particular piece of code is specifically for helping to track down a problem rather than for use as a basis for application code.
Next point - the Global Temporary Table will continue to contain data until you terminate the transaction ( COMMIT, ROLLBACK or a DDL statement).
Finally, you'll notice that the call to get the original ref cursor is hard-coded. You could dump this into a procedure if you feel so inclined, but I'm not particularly keen on programs requiring you to pass executable statements as a parameter, not least because they are potentially so vulnerable to abuse.
On that note, my ego and I are off to the pub.

About these ads

7 thoughts on “Getting output from Ref Cursors in PL/SQL

  1. here is someone’s code on Tkyte :D

    declare
    cur sys_refcursor;

    procedure p(p_str in varchar2) is
    l_str long := p_str || chr(10);
    l_piece long;
    n number;
    begin
    loop
    exit when l_str is null;
    n := instr(l_str, chr(10));
    l_piece := substr(l_str, 1, n – 1);
    l_str := substr(l_str, n + 1);
    loop
    exit when l_piece is null;
    dbms_output.put_line(substr(l_piece, 1, 250));
    l_piece := substr(l_piece, 251);
    end loop;
    end loop;
    end;

    function print_refcursor(cur sys_refcursor) return varchar2 as
    begin
    return xmltype(cur).getstringval();
    end print_refcursor;

    begin
    open cur for
    select * from tab;

    p(print_refcursor(cur));
    end;
    /

    • Oh that Tom Kyte – he’s such a clever clogs. He really should write a book :-)
      On a slightly more serious note, when you run this code, the output you get is XML…

      <?xml version="1.0"?>
      <ROWSET>
      <ROW>
      <NAME>HART</NAME>
      <POSITION>GOALKEEPER</POSITION>
      <CAPS>6</CAPS>
      <GOALS>0</GOALS>
      </ROW>
      <ROW>
      <NAME>CARSON</NAME>
      <POSITION>GOALKEEPER</POSITION>
      <CAPS>3</CAPS>
      <GOALS>0</GOALS>
      </ROW>
      <ROW>
      <NAME>FOSTER</NAME>
      <POSITION>GOALKEEPER</POSITION>
      <CAPS>4</CAPS>
      <GOALS>0</GOALS>
      </ROW>
      <ROW>
      <NAME>CAHILL</NAME>
      <POSITION>DEFENDER</POSITION>
      <CAPS>1</CAPS>
      <GOALS>0</GOALS>
      </ROW>
      <ROW>
      <NAME>COLE A</NAME>
      <POSITION>DEFENDER</POSITION>
      <CAPS>85</CAPS>
      <GOALS>0</GOALS>
      </ROW>
      <ROW>
      <NAME>DAWSON</NAME>
      <POSITION>DEFENDER</POSITION>
      <CAPS>2</CAPS>
      <GOALS>0</GOALS>
      </ROW>
      <ROW>
      <NAME>GIBBS</NAME>
      <POSITION>DEFENDER</POSITION>
      <CAPS>1</CAPS>
      <GOALS>0</GOALS>
      </ROW>
      <ROW>
      <NAME>JAGIELKA</NAME>
      <POSITION>DEFENDER</POSITION>
      <CAPS>6</CAPS>
      <GOALS>0</GOALS>
      </ROW>
      <ROW>
      <NAME>JOHNSON G</NAME>
      <POSITION>DEFENDER</POSITION>
      <CAPS>29</CAPS>
      <GOALS>1</GOALS>
      </ROW>
      <ROW>
      <NAME>LESCOTT</NAME>
      <POSITION>DEFENDER</POSITION>
      <CAPS>10</CAPS>
      <GOALS>0</GOALS>
      </ROW>
      <ROW>
      <NAME>UPSON</NAME>
      <POSITION>DEFENDER</POSITION>
      <CAPS>21</CAPS>
      <GOALS>2</GOALS>
      </ROW>
      <ROW>
      <NAME>BARRY</NAME>
      <POSITION>MIDFIELDER</POSITION>
      <CAPS>42</CAPS>
      <GOALS>2</GOALS>
      </ROW>
      <ROW>
      <NAME>CARRICK</NAME>
      <POSITION>MIDFIELDER</POSITION>
      <CAPS>22</CAPS>
      <GOALS>0</GOALS>
      </ROW>
      <ROW>
      <NAME>GERRARD</NAME>
      <POSITION>MIDFIELDER</POSITION>
      <CAPS>87</CAPS>
      <GOALS>19</GOALS>
      </ROW>
      <ROW>
      <NAME>JOHNSON A</NAME>
      <POSITION>MIDFIELDER</POSITION>
      <CAPS>4</CAPS>
      <GOALS>2</GOALS>
      </ROW>
      <ROW>
      <NAME>MILNER</NAME>
      <POSITION>MIDFIELDER</POSITION>
      <CAPS>14</CAPS>
      <GOALS>0</GOALS>
      </ROW>
      <ROW>
      <NAME>WALCOTT</NAME>
      <POSITION>MIDFIELDER</POSITION>
      <CAPS>14</CAPS>
      <GOALS>3</GOALS>
      </ROW>
      <ROW>
      <NAME>WRIGHT-PHILLIPS</NAME>
      <POSITION>MIDFIELDER</POSITION>
      <CAPS>35</CAPS>
      <GOALS>6</GOALS>
      </ROW>
      <ROW>
      <NAME>YOUNG</NAME>
      <POSITION>MIDFIELDER</POSITION>
      <CAPS>9</CAPS>
      <GOALS>0</GOALS>
      </ROW>
      <ROW>
      <NAME>BENT</NAME>
      <POSITION>STRIKER</POSITION>
      <CAPS>7</CAPS>
      <GOALS>1</GOALS>
      </ROW>
      <ROW>
      <NAME>COLE C</NAME>
      <POSITION>STRIKER</POSITION>
      <CAPS>7</CAPS>
      <GOALS>0</GOALS>
      </ROW>
      <ROW>
      <NAME>CROUCH</NAME>
      <POSITION>STRIKER</POSITION>
      <CAPS>40</CAPS>
      <GOALS>21</GOALS>
      </ROW>
      <ROW>
      <NAME>DEFOE</NAME>
      <POSITION>STRIKER</POSITION>
      <CAPS>45</CAPS>
      <GOALS>15</GOALS>
      </ROW>
      <ROW>
      <NAME>ROONEY</NAME>
      <POSITION>STRIKER</POSITION>
      <CAPS>67</CAPS>
      <GOALS>26</GOALS>
      </ROW>
      </ROWSET>
      
      PL/SQL procedure successfully completed.
      
      SQL> 
      

      This is slightly different to the starting point for this post, which was getting output in a CSV format. However, I'm sure there are people reading this who will find that it's just what they're looking for.
      Thanks for sharing,

      Mike

  2. I have to set a Cron job to run the package/procedure on a daily basis.
    Within the procedure , I have a ref cursor and i need to convert the data into a CSV or excel file . This proc will be called in a cron job and the output file will be placed in some folder within server or networl Drive , Please help

    Expample
    v_chr_sql := ‘SELECT ‘ || v_chr_sql || ‘ FROM powerseller.loan L, powerseller.gmacm_loan_search B’
    || ‘ WHERE L.servicing_number = B.servicing_number’
    || ‘ AND B.pool_name IN (SELECT column_value pool_name FROM THE’
    || ‘ (SELECT CAST(in_list_fnc(UPPER(”’ || pi_pool_id_list || ”’)) AS ListTableType) FROM DUAL))’
    || ‘ ORDER BY L.pool_name, L.servicing_number’;
    dbms_output.put_line(v_chr_sql);
    OPEN pio_cstdn_extract_cur FOR v_chr_sql;

    • Hi,

      From what you’ve said, I’m assuming that :

      1) The structure of the ref cursor you are querying (i.e. the fields it contains) is known at runtime
      2) You are running on a unix based os ( hence CRON)
      3) You want this job to run at regular intervals ( presumably once per day, overnight).

      There are also a couple of questions :

      1) Do you have the option of using DBMS_SCHEDULER to run the job entirely from within the database ? ( You can see examples of this here.
      2) Are you restricted as to where you can create the output file ( does it have to be in a certain directory ?)

      If you want to go down the DBMS_SCHEDULER route, you can use the UTL_FILE package to write the file out to a directory that you’ve defined in the database.
      There’s an example of how to use UTL_FILE here.
      Alternatively, you could create an anonymous PL/SQL block saved in a file ( and use DBMS_OUTPUT), call this from a shell script and use the SQL*Plus spool command to create your file.
      There’s an example of invoking SQL*Plus from a shell script here.

      HTH

      Mike

      • Thanks for the prompt response,
        I am using the following code and get the error as Data type inconsistent Error.
        Getting Error at FETCH custodian_extract_header
        INTO ce_line; since i have many columns with different date types and i have defined ce_line as Varchar 2 it giving me inconsistent data type.

        If i select only VARCHAR columns for my V_CHR_SQL, the below code runs perfectly fine.

        I need to know what data type has to be defined for ce_line so that it accepts any datatype.

        Decleare:
        ce_line varchar2(20000);
        V_DB_DIR_NAME varchar2 (34) := ‘ ‘;
        ce_file UTL_FILE.file_type;

        Body:

        open custodian_extract_header for V_CHR_SQL;

        IF UTL_FILE.is_open (ce_file)
        THEN
        LOOP
        FETCH custodian_extract_header
        INTO ce_line;

        EXIT WHEN custodian_extract_header%NOTFOUND;

        UTL_FILE.put_line (ce_file, ce_line, FALSE);
        END LOOP;
        END IF;

        CLOSE custodian_extract_header;
        UTL_FILE.fclose (ce_file);

      • Hi,

        it looks like custodian_extract_header is a cursor.
        If this is the case, then the simplest way to make sure that your ce_line variable is of the appropriate type is to define it as a record of the cursor record. Declare the variable after you’ve declared the cursor like this :

        ce_line custodian_extract_header%ROWTYPE;
        

        You can then fetch the cursor into this record. However, you will have to convert the separate cursor elements into a VARCHAR2 before writing to the file.
        So, if for example your cursor is returning a number, a date, and a varchar like this :

        CURSOR custodian_extract_header IS
           SELECT my_num, my_date, my_char
           FROM some_table;
        

        ... you would need to build the string to write to the file as follows ( assuming that l_buffer is declared as a varchar2(4000) and that you want to output comma separated values)...

        ...
        l_buffer := ce_line.my_num||','||TO_CHAR(ce_line.my_date)||','||ce_line.my_char;
        UTL_FILE(ce_file, ce_line, FALSE);
        

        HTH

        Mike

  3. Thank you! Not your complete aim, I’m aware, but finding the row output count in your example was just what I was after!

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