Evan J Palmer's Blog

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

Category: Programming

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:


DECLARE
l_rc sys_refcursor;
l_rec my_rules%rowtype;
BEGIN
mypackage."MyProcName"( l_rc );
LOOP
FETCH l_rc INTO l_rec;
EXIT WHEN l_rc%NOTFOUND;

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

CLOSE l_rc;
END;

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
Advertisements

TDD – Testing [internal] classes from an external project

TLDR:

In Visual Studio, to access a C# class marked as internal from an external project, add this line to your properties\AssemblyInfo.cs file:

[assembly: InternalsVisibleTo("NameOfProjectAsString")]

The longer version:

When I’m doing Test Driven Development I tend end up with a lot of classes – each with a single well defined responsibility and no external dependencies*. Usually I’d write each class with a public access modifier so they can be tested, even though in the majority of cases the class will only be used in this project, and internal access should suffice.

For example consider the following simplified case for a class that might be used in a NuGetPackage:

namespace MyNugetPackage.Logic
{
    public class SpecialStringFormatter
    {
        ValueRetreiver _valueRetreiver = new ValueRetreiver();
        ContextDeterminer _contextDeterminer = new ContextDeterminer();

        public string FormatTheString()
        {
            // DO Something
        }
    }

    public class ValueRetreiver
    {
        public int GetInt()
        {
            // DO Something
        }

        public string GetString()
        {
            // DO Something
        }
    }

    public class ContextDeterminer
    {
        public object DetermineTheContext(string username)
        {
            // DO Something
        }
    }
}

With the above example, when accessing it from a project that includes that NuGet package, you’ll get the following Intellisense:

IntellisenseExample1

In this example, the only time ValueRetreiver and ContextDeterminer are used is in SpecialStringFormatter so really they are just obfuscating the intent.

Usually this isn’t a big problem since the class will probably be used by team members who would would probably know what to use, but I’m currently working on a nuget package and and I want the usage of my package to be easily discoverable with Intellisense (which is proabably a noble goal for all your code anyway).

If I switch these to internal the Intellisense looks like this:

IntellisenseExample2

Much nicer, don’t you agree?

Now the problem is, that my classes aren’t visible to my testing project. The solution to this it to allow access to a different project via the attribute:

[assembly: InternalsVisibleTo("NameOfProjectAsString")]

which I added to my properties\AssemblyInfo.cs file.

I also needed to give access to my mocking frame work (Moq) with this attribute, including that very specific string:

[assembly: InternalsVisibleTo("DynamicProxyGenAssembly2")]

Do you know of a better way of achieving this?

* well, I try.

[Chrome Extension] TPB IMDB Pirate Bay Seach Plugin

I wrote a Chrome Extension that adds a drop down list of the top five torrents to an IMDB page.

Image

Check it out here:

https://chrome.google.com/webstore/detail/tpb-imdb-pirate-bay-searc/balopjhbkeaphbdgegdgeckdjhoaencd?hl=en

The links are ordered by seeders, so what ever has the most seeders will be at position one and the least at position five.

You can see how big the file is too, so that’s handy.

Image

Take a look at a movie page, an artist page, a list page and a TV series page. The links are on there too!

I think this plug in covers the majority of cases, but I plan to add a preference page, so we can do things like exclude pron, have more items in the drop down list or optionally use the http://bayproxy.me/. Stuff like that.

I also have plans to hook this into Wikipedia, and Rotten Tomatoes, Google etc. Any movie page I can think of.

If you’d like to help with any of that, send me a pull request!

Aspect Oriented Programming Sounds Scary

20u8wtc

When I started my new job a few days ago and was asked to learn Aspect Oriented Programming (AOP) I was scared. I thought it would be a big paradigm shift from Object Oriented Programming (OOP).

This is not true at all.

In a nutshell, all AOP is is creating attributes on Classes/Methods/Members/Events that help move common code out of the body of the method or class, helping to keep everything DRY and honoring the Single Responsibility Principle.

If you’ve written a custom attribute, you’ve done AOP*.

The meatier part of AOP is when you use a framework (like PostSharp). If you use a framework, the attributes have more knowledge of the caller. We can get arguments, return values and, method/class names (without costly reflection). We can even alter the flow of the method (or other) that was decorated with the attribute. We can also add compile-time options which don’t exist for regular custom attributes.

So to be 100% clear, as far as I can tell, AOP is a subset of OOP. You need to know how to program in OOP to be able to do AOP.

AOP cannot replace OOP – being a subset, AOP only does a subset of the work. It’s an idea for making you a better OOP developer.

AOP is not a difficult concept – and is pretty cool… and you’re already probably familiar with it if you’ve used/written attributes.

So relax. Have a beer. Don’t go in to work today. Why not watch a movie? Or old Jerry Springer episodes on YouTube?**

Also, stay tuned for more AOP stuff as I learn about it.

* I’m actually over simplyfying it here. I think technically, if you don’t have the cool shit that frameworks offer – like access to members, arguments, return values, compile-time overrirde- you’re probably not doing AOP. BUT the concept is so friggen similar, I’d argue little distinction needs to be made.

** Not actual advice. You should go to work.

ASYNC as a parameter

maxresdefault

Just a small, useless thought I had today regarding using async as a parameter – for example in an asynchronous jquery ajax call:

$.ajax({
  url: "file.php",
  type: "POST",
  async: true
});

Considering the word “asynchronous” is the antonym of its root “synchronous” – it means, “not synchronous” – shouldn’t we use “sync” and invert the defaults? I mean, you would never give any other parameter or method a negated name. You’d never see this for example:

public void UpdateStatus(bool unsuccessful){ ...

or

public bool DetermineRecordIsNotNew(RecordType record){...

or

public class Performance {
string Name { get; set; }
DateTime StartDate {get; set;}
bool IsNotCancelled {get; set;}
}

Anywho, that’s what I think.

I also don’t think that asynchronicity should be true as a default in most cases either.

Meh, whatever.

Book Review: Pragmatic Thinking and Learning

ahptl

I was looking to learn more about Agile practices and asked around to a few of my friends. One guy recommended Pragmatic Thinking and Learning by Andy Hunt.

http://pragprog.com/book/ahptl/pragmatic-thinking-and-learning

Funnily enough, I wouldn’t call this a book on Agile per se. It’s more like a discussion on some excellent ways of finding problems with your learning or productivity with the goal of being aware of them and correcting them.

Now, this is something that I’ve been looking for for a long time and was extremely happy to find it. I couldn’t recommend this book more highly – it’s full of excellent tips to make your brain work better and faster.

For me it also highlighted the importance of some things I am already doing (eg. blogging) and helped me solidify some ideas I’ve already had around situations in the workplace/learning – for example, defining skill stages of novices, advanced beginner, competent, ¬†proficient and experts, and the role and importance of each in the work place.

Should we use arrays or Generic Collections?

la_matanza_de_texas_1974_4

I tend to not use arrays when writing new code.

I recently noticed arrays in another developer’s code and started thinking more about them and why they are absent from my box of tools. In short, I think they there are better structures for handling collections in C# and if we use them, instead of the array, we’re giving the consumer of the service (or class or method or whatever) a small present in the extra features that better structures have. And you get this for free – as far as I know there’s no performance hit.

I actually would go so far as to say that as of C# 2.0 (when generics were introduced) arrays have become obsolete. That is to say, I can’t think of a time when I would choose to use an array over a generic list.

Eric Gunnerson wrote a great post on small in, big out, that resonates with my thinking about arrays.

http://blogs.msdn.com/b/ericgu/archive/2012/11/19/small-in-big-out.aspx

EDIT:

Just found this too:
http://blogs.msdn.com/b/ericlippert/archive/2008/09/22/arrays-considered-somewhat-harmful.aspx

Referencing MVC Actions in Javascript

live-free-or-die-hard-poster

How do you access an MVC endpoint from some Javascript code?

One way to do it would be to just manually resolve the URL of your endpoint and hardcode it:

var url = '/Home/GetFeeForOrder';
// perform a get on this URL

I don’t like this too much. Mainly because string literals like that scare me. I mean, things change and it makes leaning on the IDE tools to find references much harder.

I use a different pattern in which I let the Framework resolve the path and put it in a data attribute. I then access the data attribute from the JS file. For example:

View:


<div id="order" data-fee-url="@Url.Action("GetFeeForOrder", "Home")">

</div>

Javascript file:

var url = $('#order').data().feeUrl;
// perform a get on this URL

I think this is nicer, as we no longer have the magic string. It also means that any route changes will automatically work and when I use visual studio to find all references, I’ll get more accurate results. It also cuts down on any human error when writing URL – granted, that’s something that would be evident quite quickly.

I’ve been told this method is convoluted, and I do admit, it’s is a little more time consuming than just writing the URL, but think this is outweighed by the benefits.

Jump to the top of outer frame, from inner frame

542189_orig

I have a long-ish form on my site. When you submit this form a validation message appears at the top of the screen and the page is scrolled up – I’m not particularly fond of this UX, but that’s how it is unfortunately.

Now, this works well on the site, but when I embed this in Facebook, inside an iFrame, it no longer scrolls. That’s becuase the inner frame is already set to 100% (so there’s no way to scroll the frame, and we don’t have access to the outer frame, or window.document, because that’s controlled by Facebook.

So how can I make it scroll to the top in this case?

I ended up just usign javascript to focus on a div that I placed at the top of the page. I’m aware this is a hack, but it really helps the UX when using Facebook, so i think it’s warranted.

Here’s code:


function jumpToTopOfIframe() {<
var facebookTop = document.getElementById('facebooktop');
if (facebookTop != null) {
    facebookTop.style.display='block';
    facebookTop.text = '.';
    facebookTop.focus();
    facebookTop.text = '';
    facebookTop.style.display='none';
  }
}

And the mark up:


<div tabindex="0" id="facebooktop" name="facebooktop"><!-- This is here so we can jump to the top of the frame when loaded in facebook. It needs to be visible (not display: none). --></div>

What’s going on:

I’ve put a div at the top of the page that I want to target and given it an Id so I can access to it, and a tab index so the browser is able to focus on it (a tab index of zero means the browser can work it out). Now, I don’t want the div visible, but I can’t put a dispay: none on it, because the browser won’t let us focus on a div with no display. Likewise it needs some text it in. So in the javascript, I show the frame and add some text, now I can focus on it. I then remove the inner text and hide it so it doesn’t screw with the layout.

And that’s it.

Hacky? Yes. But it works.

…I should get that last line printed onto a t-shirt.

Convert a date in the format DD-MMM-YYYY in JavaScript in IE

primer-2004-time-travel

So when I’m developing, I use Chrome. (Who doesn’t?)

Eventually I test in IE and fix whatever I find.

Today I found that IE doesn’t parse dates in the format dd-mmm-yyyy.

Try it:


new Date('12-Aug-2012')

I searched around for a standard solution to this, and couldn’t find one. I find this really surprising considering it’s not an uncommon date format.

So I just knocked one up myself based on this stack overflow response:

http://stackoverflow.com/questions/16449934/is-there-a-way-to-shim-the-dd-mon-yyyy-date-format-for-ie/16530920#16530920


var customDateDdMmmYyyy = function (dateString) {
 var dateParts = dateString.split(/-/);
 return new Date((dateParts[2] * 1), ($.inArray(dateParts[1].toUpperCase(), ["JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC"]) * 1), dateParts[0] * 1);
 };

My answer is different to the stack overflow for a couple of reasons:

1) I’ve changed the code to return a new date object
2) I’m not dealing with a datatable
3) I chose not to use modernizr, and just always call my custom date function

I hope someone finds this useful, but because I feel like there must be a better, more standard solution than this.

Sadly, I couldn’t find it.