Oracle – for when it was like that when you got there

Main menu

Post navigation

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.

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 :

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 :

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…

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 :

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.

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,

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

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.

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.

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 :