Evan J Palmer's Blog

A blog about learning (code, improv, film and, anything else).

Month: June, 2014

Using a sys_refcursor Output Parameter from a Oracle Stored Procedure

In your SQL IDE, such as PL SQL Developer it’s not entirely straight forward to access the results of an Oracle stored procedure, when the results are returned via an output parameter of type sys_refcursor.

In short, here’s the code:

l_rc sys_refcursor;
l_rec my_rules%rowtype;
mypackage."MyProcName"( l_rc );
FETCH l_rc INTO l_rec;

dbms_output.put_line( l_rec.field_one || ' ' || l_rec.field_two );

CLOSE l_rc;

An explanation:

Firstly we need to declare some variables to use. l_rc is a cursor that we will pass to the procedure, to store the output results.
l_rec is a record into which we’ll store each iteration of the cursor.

After the declaration we execute the package, populating the cursor.

We then loop over the cursor, storing the value in to record, or row, that we declared above, ending when the cursor is empty.

Remember to close the cursor to release the context area in memory.

Thank you to Justin on StackOverflow for answering my question.

As an aside, moving from SQL Server to Oracle has been interesting. I’ve found several commands or concepts that are trivial in SQL Server to be a little challenging in Oracle.

  • No auto increment for primary keys (before version 12) have to use triggers instead
  • The concept of Packages, with a public spec and a private body
  • We don’t have to tell parameters how long their nvarchar2 is, but we do in the table (this is cool logically, but feels inconsistent)
  • The concept of the ORA file

Respect For Women Characters


A group of women players I jam with were given the suggestion “rock band” in the last class.  They decided to do the role of band members as men.

In another scene, a married couple are having dinner of an evening and discussing their days. The male is a judge and the woman stays home and looks after the kids.

Later, a drunk piano player’s wife enters the room and is immediately endowed with the characteristics of being a floozy and prostitute.

When improvising I do think it’s important to play to characteristics and to recognize archetypes, but I’ve noticed that women are given derogatory or subordinate, low status roles  more often than not. Or, when choosing
a certain type of character, such as a lawyer or a baseball coach, they will choose to play it as a man.

Why couldn’t the band members be women? Why was the male a judge and the woman a stay-at-home mum? Why was the woman in the scene automatically a floozy?

Now, the people in my class are not sexist, and would never speak or act in a derogatory way towards women in real life. And it’s worth noting these character choices were made by both the guys and girls in the class. But I do think there’s an underlying lack of respect for women characters in general in my local improv scene.

There are two reasons why this should change.

Firstly, not respecting women characters reinforces stereo types in real life. Why propagate a shitty stereotype?

Secondly, the scenes will be more interesting without leaning on that predictable flow. Breaking that mold would likely result in cooler scenes and characters with more depth.

I think it’s worth keeping in mind.

Thanks to my awesome teacher, Pete Lead for bringing this to my attention.