Northern Nevada Software Developers Group

Doug Holland Presentation

NNSDG — Thu, 24 May 2012 22:41:17 +0000

A big thank you to Doug Holland for his Windows 8 METRO Style Apps presentation.

Bret Simmons Presentation

NNSDG — Thu, 26 Apr 2012 18:17:20 +0000

Thanks to Bret Simmons for his presentation on personal branding. You can view his website and use of social media as a great example of a personal brand. Also, thanks to the rest of the roundtable members: Ryan Polk and Jesse Anderson.

New Meeting Location

NNSDG — Fri, 06 Apr 2012 22:22:10 +0000

There are some changes for this meeting and all future meetings.

Bangkok Cuisine South has been a great host to our meetings, but we have a late scheduling conflict this month that we just found out about. We decided to look at another venue for this month’s and all future meetings. We will be hosting the meetings at The Firkin and Wolf at Summit Sierra by The Apple Store and Century Theatre. Here is the map in case you don’t know where The Apple Store or the Century Theatre is.

This venue change brings about some other good changes. We will have a room that is more conducive to training and presentations. We will no longer need to have an RSVP count. Once you get there, please put in your own order from ~~anything~~ the 4 we choose items off the menu. It will be up to each member to pay for their own food now. As before, there is no charge to attend the meeting. If you want to stick around after the meeting and socialize, there are darts and a pool table.

Some members don’t know and we would appreciate everyone letting other members know about the change.

Collecting Thoughts

David Fitzjarrell — Mon, 27 Feb 2012 20:21:00 +0000

Collections are an interesting lot. They can be one of the most useful tools in the Oracle arsenal, yet they can also be very frustrating to implement. For those unfamiliar with them a collection/varray is defined as "an ordered group of elements, all of the same type. Each element has a unique subscript that determines its position in the collection." The definition seems simple enough but it may be deceptively so. To make matters even more confusing to use a collection you must create a database type for it to reference; a varray requires a type as well but that type can be declared in the PL/SQL block. To clear the air a bit let's go through some examples of defining and using collections and varrays: The first example uses a collection to store vendor ids and then process them for a report. The code builds two 'tables' and compares the contents of them by loading collections and comparing one collection to the other; output is displayed for the conditions of the two tables being equal and the two tables not being equal:


SQL> 
SQL> set serveroutput on size 1000000
SQL> 
SQL> CREATE OR REPLACE type integer_table is table of integer;
  2  /

Type created.

SQL> 
SQL> DECLARE
  2  
  3  
  4   vendor_key_table   integer_table;
  5   vendor_key_table2   integer_table;
  6   CURSOR tst
  7   IS
  8      SELECT   purch_order, SUM (dollars),
  9        CAST (COLLECT (TO_NUMBER (vendor_key)) AS integer_table)
 10          FROM (SELECT 1 purch_order, 3 dollars, 435235 vendor_key
 11           FROM DUAL
 12         UNION ALL
 13         SELECT 1 purch_order, 8 dollars, 123452 vendor_key
 14           FROM DUAL
 15         UNION ALL
 16         SELECT 2 purch_order, 7 dollars, 433738 vendor_key
 17           FROM DUAL
 18         UNION ALL
 19         SELECT 2 purch_order, 4 dollars, 383738 vendor_key
 20           FROM DUAL
 21         UNION ALL
 22         SELECT 2 purch_order, 5 dollars, 387118 vendor_key
 23           FROM DUAL)
 24      GROUP BY purch_order;
 25  
 26  
 27    CURSOR tst2
 28   IS
 29    SELECT purch_order, SUM (dollars),
 30        CAST (COLLECT (TO_NUMBER (vendor_key)) AS integer_table)
 31          FROM (SELECT 1 purch_order, 3 dollars, 435235 vendor_key
 32           FROM DUAL
 33         UNION ALL
 34         SELECT 2 purch_order, 4 dollars, 383738 vendor_key
 35           FROM DUAL
 36         UNION ALL
 37         SELECT 2 purch_order, 7 dollars, 433738 vendor_key
 38           FROM DUAL
 39         UNION ALL
 40         SELECT 2 purch_order, 5 dollars, 387118 vendor_key
 41           FROM DUAL)
 42      GROUP BY purch_order;
 43   v_purch_order    NUMBER;
 44   v_dollars    NUMBER;
 45  
 46  
 47   mystr     VARCHAR2 (4000);
 48  
 49  
 50   v_purch_order2     NUMBER;
 51   v_dollars2     NUMBER;
 52  
 53  
 54   mystr2      VARCHAR2 (4000);
 55  BEGIN
 56   OPEN tst;
 57   open tst2;
 58  
 59  
 60   LOOP
 61      mystr := NULL;
 62      mystr2 := NULL;
 63  
 64  
 65      FETCH tst
 66       INTO v_purch_order, v_dollars, vendor_key_table;
 67  
 68  
 69      FETCH tst2
 70       INTO v_purch_order2, v_dollars2, vendor_key_table2;
 71  
 72  
 73      IF tst%NOTFOUND
 74      THEN
 75         EXIT;
 76      END IF;
 77  
 78  
 79      if vendor_key_table = vendor_key_table2 then
 80          dbms_output.put_line('equal');
 81      else
 82          dbms_output.put_line(' not equal');
 83      end if;
 84  
 85  
 86      -- loop through the collection and build a string so that
 87      -- we can display it and prove that it works
 88      FOR cur1 IN (SELECT COLUMN_VALUE vendor_key
 89       FROM TABLE (vendor_key_table))
 90      LOOP
 91         mystr := mystr || ',' || cur1.vendor_key;
 92         -- /* based on the value of the sum, you can do something with each detail*/
 93         -- if v_dollars > 12 then
 94         --   UPDATE VENDOR SET paid_status = 'P' where vendor_key = cur1.vendor_key;
 95         -- end if;
 96      END LOOP;
 97  
 98  
 99      DBMS_OUTPUT.put_line (   'Purchace Order-> '
100       || TO_CHAR (v_purch_order)
101       || ' dollar total-> '
102       || TO_CHAR (v_dollars)
103       || ' vendorkey list-> '
104       || SUBSTR (mystr, 2));
105  
106  
107      -- loop throught the collection and build a string so that
108      -- we can display it and prove that it works
109      FOR cur2 IN (SELECT COLUMN_VALUE vendor_key
110       FROM TABLE (vendor_key_table2))
111      LOOP
112         mystr2 := mystr2 || ',' || cur2.vendor_key;
113      END LOOP;
114  
115  
116      DBMS_OUTPUT.put_line (   'Purchace Order-> '
117       || TO_CHAR (v_purch_order2)
118       || ' dollar total-> '
119       || TO_CHAR (v_dollars2)
120       || ' vendorkey list-> '
121       || SUBSTR (mystr2, 2));
122  
123  
124   END LOOP;
125   CLOSE tst;
126   CLOSE tst2;
127  END;
128  /
not equal
Purchace Order-> 1   dollar total-> 11   vendorkey list-> 435235,123452
Purchace Order-> 1   dollar total-> 3   vendorkey list-> 435235
equal
Purchace Order-> 2   dollar total-> 16   vendorkey list-> 433738,387118,383738
Purchace Order-> 2   dollar total-> 16   vendorkey list-> 383738,387118,433738

PL/SQL procedure successfully completed.

SQL>

Comparing the collections rather than looping through each table makes the work much easier to complete. Notice we only needed one type created; the same type satisfied the conditions for both collection tables.

The next example shows how things can go astray with the bulk collect operation when the limit does not evenly divide the result set. In the first part of the example we use the well-known 'exit when cursor%notfound;' directive with less than stellar results (we miss inserting 5 records into the second table); the second part of the example shows how to properly implement an exit from a bulk collect operation; this one uses a varray:

 
SQL> 
SQL> set echo on linesize 150 trimspool on
SQL> 
SQL> create table emp_test as select * From emp where 0=1;

Table created.

SQL> 
SQL> declare
  2        type empcoltyp is table of emp%rowtype;
  3        emp_c empcoltyp;
  4  
  5        cursor get_emp_data is
  6        select * from emp;
  7  
  8  begin
  9        open get_emp_data;
 10        loop
 11        fetch get_emp_data bulk collect into emp_c limit 9;
 12        exit when get_emp_data%notfound;
 13  
 14        for i in 1..emp_c.count loop
 15         insert into emp_test (empno, ename, sal)
 16         values (emp_c(i).empno, emp_c(i).ename, emp_c(i).sal);
 17        end loop;
 18  
 19        end loop;
 20  
 21        commit;
 22  
 23  end;
 24  /

PL/SQL procedure successfully completed.

SQL> 
SQL> select * from emp_test;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH                                            800
      7499 ALLEN                                           1600
      7521 WARD                                            1250
      7566 JONES                                           2975
      7654 MARTIN                                          1250
      7698 BLAKE                                           2850
      7782 CLARK                                           2450
      7788 SCOTT                                           3000
      7839 KING                                            5000

9 rows selected.

SQL> 
SQL> truncate table emp_test;

Table truncated.

SQL> 
SQL> declare
  2        type empcoltyp is table of emp%rowtype;
  3        emp_c empcoltyp;
  4  
  5        cursor get_emp_data is
  6        select * from emp;
  7  
  8  begin
  9        open get_emp_data;
 10        loop
 11        fetch get_emp_data bulk collect into emp_c limit 9;
 12        exit when emp_c.count = 0;
 13  
 14        for i in 1..emp_c.count loop
 15         insert into emp_test (empno, ename, sal)
 16         values (emp_c(i).empno, emp_c(i).ename, emp_c(i).sal);
 17        end loop;
 18  
 19        end loop;
 20  
 21        commit;
 22  
 23  end;
 24  /

PL/SQL procedure successfully completed.

SQL> 
SQL> select * from emp_test;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH                                            800
      7499 ALLEN                                           1600
      7521 WARD                                            1250
      7566 JONES                                           2975
      7654 MARTIN                                          1250
      7698 BLAKE                                           2850
      7782 CLARK                                           2450
      7788 SCOTT                                           3000
      7839 KING                                            5000
      7844 TURNER                                          1500
      7876 ADAMS                                           1100

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES                                            950
      7902 FORD                                            3000
      7934 MILLER                                          1300

14 rows selected.

SQL>

What happened in the first part? Since the limit was more than the number of remaining records the NOTFOUND indicator was set at the end of the fetch. We had 5 records left to process in the varray but the 'exit when cursor%notfound;' statement terminated the loop BEFORE we could get the remainng 5 records inserted into our table, thus they were lost. Using the 'exit when collection.count = 0;' construct prevents us from missing records since the count was greater than 0 even when the NOTFOUND indicator was set. This allows us to process the remaining records in the varray before exiting the loop. [Yes, the exit could be coded at the end of the loop rather than the beginning and the 'exit when cursor%NOTFOUND;' would process the remaing records but that, to me, defeats the purpose of the conditional exit. As I see it we want to exit the loop when no more work is to be done, not look for partial sets of data to apply then exit before the next (unsuccessful) fetch.]

Our next example does two things: loads data using bulk collect into a varray then uses the FORALL loop construct to quickly process the collection and insert the data into a staging table. The second part is a bit contrived as it uses a collection to process deletes from a table -- deletes that could have easily been executed with a single SQL statement -- but it does show the power of using collections and varrays:


SQL> 
SQL> set echo on timing on
SQL> 
SQL> create table stage_data(
  2        uname varchar2(30),
  3        ujob varchar2(20),
  4        usal number
  5  );

Table created.

SQL> 
SQL> begin
  2        for i in 1..1000000 loop
  3        insert into stage_data
  4        values ('Blorp'||i, 'Job'||i, 1200*(mod(i,3)));
  5        end loop;
  6  
  7        commit;
  8  
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> 
SQL> 
SQL> create table forall_load(
  2        uname varchar2(30),
  3        ujob varchar2(20),
  4        usal number
  5  );

Table created.

SQL> 
SQL> create table forall_load2(
  2        uname varchar2(30),
  3        ujob varchar2(20),
  4        usal number
  5  );

Table created.

SQL> 
SQL> CREATE OR REPLACE PROCEDURE fast_way IS
  2  
  3  TYPE myarray IS TABLE OF stage_data%ROWTYPE;
  4  l_data myarray;
  5  
  6  CURSOR r IS
  7  SELECT *
  8  FROM stage_data;
  9  
 10  BEGIN
 11   OPEN r;
 12   LOOP
 13     FETCH r BULK COLLECT INTO l_data LIMIT 1000;
 14  
 15     FORALL i IN 1..l_data.COUNT
 16        INSERT INTO forall_load VALUES l_data(i);
 17     FORALL i IN 1..l_data.COUNT
 18        INSERT INTO forall_load2 VALUES l_data(i);
 19  
 20     EXIT WHEN l_data.count=0;
 21    END LOOP;
 22    COMMIT;
 23    CLOSE r;
 24  END fast_way;
 25  /

Procedure created.

SQL> 
SQL> exec fast_way;

PL/SQL procedure successfully completed.

SQL> 
SQL> select * from forall_load where uname like '%99999%';

UNAME                          UJOB                       USAL
------------------------------ -------------------- ----------
Blorp99999                     Job99999                      0
Blorp199999                    Job199999                  1200
Blorp299999                    Job299999                  2400
Blorp399999                    Job399999                     0
Blorp499999                    Job499999                  1200
Blorp599999                    Job599999                  2400
Blorp699999                    Job699999                     0
Blorp799999                    Job799999                  1200
Blorp899999                    Job899999                  2400
Blorp999990                    Job999990                     0
Blorp999991                    Job999991                  1200

UNAME                          UJOB                       USAL
------------------------------ -------------------- ----------
Blorp999992                    Job999992                  2400
Blorp999993                    Job999993                     0
Blorp999994                    Job999994                  1200
Blorp999995                    Job999995                  2400
Blorp999996                    Job999996                     0
Blorp999997                    Job999997                  1200
Blorp999998                    Job999998                  2400
Blorp999999                    Job999999                     0

19 rows selected.

SQL> select * from forall_load2 where uname like '%99999%';

UNAME                          UJOB                       USAL
------------------------------ -------------------- ----------
Blorp99999                     Job99999                      0
Blorp199999                    Job199999                  1200
Blorp299999                    Job299999                  2400
Blorp399999                    Job399999                     0
Blorp499999                    Job499999                  1200
Blorp599999                    Job599999                  2400
Blorp699999                    Job699999                     0
Blorp799999                    Job799999                  1200
Blorp899999                    Job899999                  2400
Blorp999990                    Job999990                     0
Blorp999991                    Job999991                  1200

UNAME                          UJOB                       USAL
------------------------------ -------------------- ----------
Blorp999992                    Job999992                  2400
Blorp999993                    Job999993                     0
Blorp999994                    Job999994                  1200
Blorp999995                    Job999995                  2400
Blorp999996                    Job999996                     0
Blorp999997                    Job999997                  1200
Blorp999998                    Job999998                  2400
Blorp999999                    Job999999                     0

19 rows selected.

SQL>

Trust me that the data loads took very little time to process. Here is the contrived part, but it is still a good example of the power of using collections:


SQL> CREATE OR REPLACE PROCEDURE del_rows IS
  2  
  3  TYPE myarray IS TABLE OF stage_data.uname%TYPE;
  4  l_data myarray;
  5  
  6  CURSOR r IS
  7  SELECT uname
  8  FROM stage_data
  9  where uname like '%9999%';
 10  
 11  BEGIN
 12   OPEN r;
 13   LOOP
 14     FETCH r BULK COLLECT INTO l_data LIMIT 1000;
 15  
 16     FORALL i IN 1..l_data.COUNT
 17        delete from forall_load where uname = l_data(i);
 18     FORALL i IN 1..l_data.COUNT
 19        delete from forall_load2 where uname = l_data(i);
 20  
 21     EXIT WHEN l_data.count=0;
 22    END LOOP;
 23    COMMIT;
 24    CLOSE r;
 25  END del_rows;
 26  /

Procedure created.

SQL> 
SQL> show errors
No errors.
SQL> 
SQL> exec del_rows;

PL/SQL procedure successfully completed.

SQL> 
SQL> select * from forall_load where uname like '%99999%';

no rows selected

SQL> select * from forall_load2 where uname like '%99999%';

no rows selected

SQL>

Collections can be a real timesaver for bulk processng of data; they may not be applicable in every sitution but when the conditions are right they can make your job so much easier. Master collections and varrays and you may be able to amaze your friends.

Collections, anyone?

Jason Jarrett Presentation

NNSDG — Thu, 23 Feb 2012 22:55:29 +0000

Thanks to Jason Jarrett for his presentation on S.O.L.I.D principles. You can view the syndication of his posts on his site or right here on the NNSDG site.

NuGet Project Uncovered: SpecificationExtensions.[MSTest | NUnit | Xunit]

Jason Jarrett — Thu, 23 Feb 2012 05:05:00 +0000

If you are coming to this series of posts for the first time you might check out my introductory post for a little context.

NOTE: this project is one I created and as it turns out this has now become it’s introductory post.

The SpecificationExtensions.[MSTest | NUnit | Xunit] are a set of NuGet packages that add C# fluent specification extensions to your test project. I first blogged about this in early 2009 and have had a set of these that I take with me for every project I work on.

There are a number of other options out there for specification extensions, but since I first created my original set, I haven’t used anything else (although I should as I might be able to learn a little from each).

NuGet Project Uncovered: EventAggregator.Net

Jason Jarrett — Wed, 22 Feb 2012 05:04:00 +0000

If you are coming to this series of posts for the first time you might check out my introductory post for a little context.

NOTE: this project is one I created and as it turns out this has now become it’s introductory post.

EventAggregator.Net is a single C# file that can provide a basis for a simple in memory Pub/Sub event aggregator.

I extracted this out of my StatLight project as I found that I often wanted a similar one and kept finding myself copy/pasting this into projects. I figured a single location for this project would be better and I use StatLight as the first dog bowl when I need to dog food the project.

If you’re familiar with the…

Install-Package Caliburn.Micro.EventAggregator

…then you know probably know what this project is like.

Its history starts a few years ago when I read Jeremey Miller’s Braindump on Event Aggregator Pattern and decided I wanted rip out StatLight’s usage of the Prism event aggregator and replace it with a similar one to the one found in StoryTeller. It’s gone through quite a few revisions inside of StatLight since then and eventually made its way into its own project.

Some thanks have to go out to the great feedback and pull requests from Jake Ginnivan who found this project on his own (before I publicized it).

If you’re interested in using it, I’d recommend checking out the source’s test project and the SampleUsage project. The SampleUsage project demonstrates how you can configure the tool to publish events in an async mode.

One concept introduced in this EventAggregator is taking the IEventAggregator interface and breaking it up into two interfaces (IEventPublisher and IEventSubscriptionManager). This proved extremely useful when trying to diagnose components that did both aggregator subscription management vs ones that only published events. It even helped to easily diagnose components that did not correctly unregister objects.

NuGet Project Uncovered: DumpToText

Jason Jarrett — Tue, 21 Feb 2012 05:01:00 +0000

If you are coming to this series of posts for the first time you might check out my introductory post for a little context.

NOTE: this project is one I created and as it turns out this has now become its introductory post.

DumpToText is a single C# extension I wrote a little while back. The inspiration from this came from the need to view the values of an object graph quickly and easily during a TDD session.

Have you ever been doing TDD and something isn’t working quite as expected? Would it be nice to just dump out the values of an object quickly without having to spin up the debugger?

The inspiration for this project came from an amazing feature of LINQPad. If you have ever used LINQPad then you’re aware of the amazing ability for it to take any object and create a view of it’s data. Take the simple anonymous type below.

Now wouldn’t it be great to have that “.Dump()” extension method at hand anywhere in your code and during a TDD session?

That’s why I create DumpToText.

Now if I have a test as follows and want to see it’s data. I can use the ‘.DumpToText()” extension method to have it print out an ASCII based representation of the object graph.

By default this just uses the System.Diagnostics.Trace(…) to write the output to, but you can override the “write” implementation by giving your own delegate as shown below.

The below shows an example of a nested object that also has an array of items.

Anyone out there using ApprovalTests? (You can get it on NuGet)

I’ve not taken the chance to use ApprovalTests yet in a project, but I have a strong feeling that my DumpToText helper could be very useful when leveraged in conjunction with ApprovalTests. If anyone out there is using ApprovalTests, I’d love to hear how it’s going, and if you think that DumpToText would be useful there.

NuGet Project Uncovered: Nancy

Jason Jarrett — Mon, 20 Feb 2012 04:59:00 +0000

If you are coming to this series of posts for the first time you might check out my introductory post for a little context.

Nancy is another project founded by an elegant coder. Andreas has blogged about it a number of times here on ElegantCode.

Nancy is a lightweight HTTP framework for building web services and sites. The framework runs on both the .net framework and Mono.

I have not used this project myself, but as I started to look it over I think I might have to spin up a site quickly just to try it out.

NuGet Project Uncovered: Extended.Wpf.Toolkit

Jason Jarrett — Sun, 19 Feb 2012 04:58:00 +0000

If you are coming to this series of posts for the first time you might check out my introductory post for a little context.

Extended.Wpf.Toolkit is a project that should not need an introduction, and if you follow this blog you’ve probably heard Brian talk about it. If not, check out some of the posts on the Extended WPF Toolkit!

This project is one of the most download on codeplex, discussed on Channel9, Coding4Fun, and is being leveraged by Telerik in their Open Access ORM product.

Below is a sample of some of the controls you can find in the toolkit:
(but make sure you check out the project site for the full list of controls)

BusyIndicator
Calculator
ChildWindow
ColorCanvas
ColorPicker
DateTimePicker
Magnifier
MultiLineTextEditor
PrimitiveTypeCollectionEditor
RichTextBox
SplitButton
WatermarkTextBox
Wizard

Northern Nevada Software Developers Group

Doug Holland Presentation

Bret Simmons Presentation

New Meeting Location

Collecting Thoughts

Jason Jarrett Presentation

NuGet Project Uncovered: SpecificationExtensions.[MSTest | NUnit | Xunit]

NuGet Project Uncovered: EventAggregator.Net

NuGet Project Uncovered: DumpToText

Anyone out there using ApprovalTests? (You can get it on NuGet)

NuGet Project Uncovered: Nancy

NuGet Project Uncovered: Extended.Wpf.Toolkit

Below is a sample of some of the controls you can find in the toolkit: (but make sure you check out the project site for the full list of controls)

Below is a sample of some of the controls you can find in the toolkit:
(but make sure you check out the project site for the full list of controls)