To help everyone use this tool Redgate is friendly to provide me with two (2) five (5) user licenses to their new product worth $1475 to give away.

How can you ‘win’ one of these licenses? Just write up why you really, really deserve a copy. Write up what problem will be fixed if you have source control or maybe in what nasty situation you got in because you didn’t have it.

I won’t be the only judge, but the guys at Redgate will help me decide who ‘wins’ the licenses.

I also added an option for you to show your appreciation for my work and donate using Paypal.

You can find the new plugins at the homepage http://bar-solutions.com and then follow the menu on the left:

[Software - Plug-ins]

Currently the following plugins have been rebuilt:

• ColumnEdit
• CommentLine
• Favorites
• InitCaps
• ReConnect
• SaveSeparate
• Template
• ToggleReadOnly

Update 2013-01-22: Editor Enhancement has been added.

Frits Hoogland @fritshoogland

@patch72 AFAIK, old computer systems registered everything in uppercase. I know of old govmt systems. prbly people just kept on doing that.

That would be a very plausible explanation. We do a lot of things in a special way because we are used to doing it like that.

Alex Nuijten @alexnuijten

@patch72 wouldn’t lower() introduce special characters like accents? With upper() this will not happen. @fritshoogland

I don’t think this will happen. Using a special character like an accent on a character should keep that accent in both upper and lower case.

Jurgen Kemmelings @jurgen_k

@alexnuijten @patch72 ik snap het niet? SQL> select upper(’hôtel’) from dual; UPPER —– HÔTEL

• If you run this query it is like I expected. The accent is preserved.

Marcel Hoefs @MarcelHoefs

@patch72 @alexnuijten@jurgen_k Of: SELECT NLS_UPPER(’große’, ‘NLS_SORT = XGerman’) nls_uppercase, UPPER(’große’) uppercase FROM DUAL;

• This query will replace the ß character by SS, but this is more a translation of the text. So it’s not just a case change (to UPPER) but also removing all special characters.

One of the most funny replies is this one:

Carsten Czarski@cczarski

@patch72 I always used upper(lower()) … to be sure

The idea is exactly the opposite. I don’t want to type the uppercase characters in my query. It involves keeping the shift key pressed (or enabling the CAPS LOCK), so using lower(upper()) would make more sense IMHO.

There were some more comments, but you can look them up yourself using this link.

Any other ideas on why this is? Please use the comments

.code, .code pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .code pre { margin: 0em; } .code .rem { color: #ff0000; } .code .kwrd { color: #008080; } .code .str { color: #0000ff; } .code .op { color: #0000c0; } .code .preproc { color: #cc6633; } .code .asp { background-color: #ffff00; } .code .html { color: #800000; } .code .attr { color: #ff0000; } .code .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .code .lnum { color: #606060; }

I have been appointed Track Leader for the Developer’s Toolkit for KScope13. This involves voting on abstracts. Everyone in the group should rate the abstracts on a scale of 1 to 5. Where 1 is not good (Not appropriate…) and 5 is very good (Highlight of the conference…). After all the members of the group have voted, my task is to figure out which abstracts have the highest rating.

The website shows the different abstract, your own rating and the average voting over all voters.

Coming up with the top sessions involves a lot of scrolling, writing stuff down and trying to make out the rankings. Luckily the site also provides the possibility to download the voting in a Comma Separated Values format so you can open it in Excel.

Since I am not (yet) an Excel Wizard this is not what I am looking for. Still takes a lot of work, which I don’t want to do.

Oracle provides the possibility to use a file with Comma Separated Values as an External Table. Taking this approach gives me the opportunity to apply my SQL skills to the data and have the database do the heavy lifting on figuring out which abstracts get the highest rating.

To create the external table I have to take these steps:

1) create a directory

   1:  -- Create directory 

   2:  create or replace directory EXTERNALDATA

   3:    as 'C:\MySources\ExternalData';

2) save the file to this folder on the database server (in this case my own machine)

3) create an External Table:

   1:  -- Create table

   2:  create table KSCOPE13_EXT_CSV

   3:  (

   4:    session_num       VARCHAR2(4000),

   5:    session_title     VARCHAR2(4000),

   6:    session_submitter VARCHAR2(4000),

   7:    primary_presenter VARCHAR2(4000),

   8:    co_presenter      VARCHAR2(4000),

   9:    voter             VARCHAR2(4000),

  10:    total             VARCHAR2(4000),

  11:    vote              VARCHAR2(4000),

  12:    session_comment   VARCHAR2(4000)

  13:  )

  14:  organization external

  15:  (

  16:    type ORACLE_LOADER

  17:    default directory EXTERNALDATA

  18:    access parameters 

  19:    (

  20:      records delimited by newline

  21:      NODISCARDFILE

  22:      NOBADFILE

  23:      NOLOGFILE

  24:      SKIP 1

  25:      fields terminated by ','

  26:      optionally enclosed by '"'

  27:      missing field values are null

  28:      (  Session_Num       char(4000)

  29:        ,Session_title     char(4000)

  30:        ,Session_Submitter char(4000)

  31:        ,Primary_Presenter char(4000)

  32:        ,Co_Presenter      char(4000)

  33:        ,Voter             char(4000)

  34:        ,Total             char(4000)

  35:        ,Vote              char(4000)

  36:        ,Session_Comment   char(4000)

  37:      )

  38:    )

  39:    location (EXTERNALDATA:'kscope2013.csv')

  40:  )

  41:  reject limit UNLIMITED;

4) Create the query:

   1:  SELECT DISTINCT t.session_title

   2:                 ,t.session_submitter

   3:                 ,round(avg(t.total) over (partition by t.session_title, t.session_submitter),4) average

   4:    FROM kscope13_ext_csv t

   5:   order by 3 desc;

5) Check the results

This makes my job a lot easier. Well, maybe not a lot, just a bit. Still had to build the schedule for the top abstracts. But at least it’s easier to decide which session get selected and which don’t and, maybe most important, I got to use my Oracle skills on a non-Oracle related issue.

I came up with something I didn’t know. I have a pipelined table function where I want to suppress a record from appearing when something is wrong (no data found or something like that). I know, suppressing errors is wrong, but in this case I will log the error. I just didn’t want it to appear in my data because the receiving application will generate errors and reject the entire set of data.

The easy way, of course, is not to pipe a row at all, but that would mean adding a lot of complexity to my code so I decided to try something different. Just set the record to NULL before it gets piped out. Adding this worked for my situation but it made me wonder: Is there an empty record added to the ‘table’?

This is why I wrote a simple script to try this:

   1:  CREATE OR REPLACE TYPE dummy_t AS OBJECT

   2:  (

   3:    dummy_id   NUMBER,

   4:    dummy_name VARCHAR2(30)

   5:  )

   6:  /

   7:  CREATE OR REPLACE TYPE dummy_ntt AS TABLE OF dummy_t

   8:  /

   9:  CREATE OR REPLACE FUNCTION dummy_f RETURN dummy_ntt

  10:    PIPELINED IS

  11:    l_returnvalue dummy_t;

  12:  BEGIN

  13:    l_returnvalue := dummy_t(1, 'Patrick');

  14:    PIPE ROW(l_returnvalue);

  15:    l_returnvalue := NULL;

  16:    PIPE ROW(l_returnvalue);

  17:    l_returnvalue := dummy_t(2, 'Mitchell');

  18:    PIPE ROW(l_returnvalue);

  19:    RETURN;

  20:  END dummy_f;

  21:  /

  22:  SELECT *

  23:    FROM TABLE(dummy_f)

  24:  /

As you can see, there are three PIPE ROW statements so you might expect three rows in the result. But luckily Oracle is smart enough to suppress the empty row:

This is what I hoped would happen and luckily it did so.

What else have I been up to?
I am currently in the process of rating abstracts for KScope13 being a Track Lead for the Developer’s Toolkit.
I have published a couple of articles on the AllThingsOracle website with the latest addition being an article on Edition Based Redefinition (part 1, part 2 hopefully next week). Other articles I wrote here can be found at this link.
Besides that I’m still playing around with APEX trying to rebuild a program I wrote a long time ago in PHP using MySQL to build my own version of Delicious.

On Saturday I went to the River Walk with Chet Justice, Jeff Smith, Dan McGhan and Galo Balda. Here we went to see The Alamo, had dinner at Café Ole and met up with Tim Gorman and Kellyn Pot’Vin.

On Sunday the Sunday symposiums took place. I kinda mixed the Application Express symposium with the DB & Developers toolbox. Saw some nice new things on APEX 4.2 (early adopter available here), learnt some nice stuff about the Oracle Cloud got some insight on how Oracle handles your queries.

The night started with the speaker/ambassador/mentor reception where I got to meet up with some old and new friends. Then it was right into the welcoming reception where I had some good laughs watching the thirsty games. Also, I want to thank Jeff Smith for getting me the first t-shirt of the conference. The ORACLENERD shirt.

On Monday the KScope sessions started. I went to see Tim Gorman on RDBMS Forensics. Learn a bit about how you can troubleshoot errors in you production database. Then it was Jonathan Lewis who explained Single Table Access Paths. I have been working with Oracle for over 10 years but only now I think I understand what’s going on ‘under the hood’. Then it was off to the APEX track for Christian Rokitta on APEX and JQuery Mobile. There was no ambassador for this session, so I decided to do this. I gained the ambassador’s badge for this.

After lunch I decided not to go see any session and to prepare for my own session: Should Invoker Rights Be Used. I am not too pleased with how it went. In my opinion it was too short, I was too tired (which you could hear in my speech) but the good thing is I had Steven Feuerstein and Bryn Llewellyn in the audience, so we had a couple of nice discussions at the and of the session.

After this there was the ACE reception. My very first one. It is nice to be part of this. Had some chats with a couple of other ACE’s amongst who Peter Raganitsch.

Then it was time for the keynote by Cary Millsap. This was totally not technical, but man, how right he was. You can checkout the keynote here. If you have an hour or so to spare, I urge you to take a look.

APEX Open Mic Night concluded day 1 for me. It was nice to see what people are doing using APEX. How different all their applications are. Shows how you can really build what you want using APEX.

Tuesday started off with a nice session by Tom Kyte. Five things you probably didn’t know about PL/SQL.

More Code = More Bugs

Less Code = Less Bugs

The fastest way to do something is NOT doing it

I decided to skip the vendor track, so my next session was Optimizing a Two-table Join by Jonathan Lewis. This was actually a continuation of the session of the day before. Even more insight for me. Then it was off to the lunch and learn session with a panel of Jonathan Lewis, Cary Millsap, Steven Feuerstein and Maria Colgan. Some nice discussions on amongst others Table Functions.

Next stop was a presentation by Rick Greenwald on RESTful Web Services. Not only can APEX 4.2 consume Web Services, it can now also provide them. Then it was Kim Berg Hansen’s first appearance on the KScope stage with a session on Analytic Functions.

Closing session for the day was Debugging PL/SQL with SQL Developer by Jeff Smith. It was nice to see how it works. I think SQL Developer is better in remote debugging than PL/SQL Developer. But in other aspects they are mostly equivalent to each other.

The evening was concluded for me by having a nice dinner with the people of Redgate. Dinner with Cary Millsap, Dominic Delmolino, Martin Giffy D’Souza and of course the people from Redgate. After this I went up to my room to run some final checks on my presentation on Wednesday.

Wednesday started of with a presentation by Cary Milsap on how instrumentation is useful in your code. After this I attended a session by Steven Feuerstein on Error Management in PL/SQL. Then it was time for my second presentation. Can collections speed up your PL/SQL? This one just went great, I am so happy with this one. I even had to skip through a couple of slides to finish in time. If you were there, I hope you enjoyed it.

In the afternoon I first attended a session about Binding by Tom Kyte and for the last session I was an ambassador for the session by Anthony Rayner on building Highly accessible products using APEX. This presentation was co-done by Paul Holliman who is blind. It was amazing to see and hear how he uses APEX to develop applications. It’s amazing how he finds his way around the builder using a screen reader.

The night was reserved for the Ranch and Rodeo Special Event which was a night filled with live music, armadillo races, a mechanical bull and a full-blown rodeo. The rodeo is something I would not go to myself, but it has been a great experience to have seen it at least once.

The final day of the conference started of with ‘An Oracle Geek’s Guide to Successful Dating’ which was, as you already expected, all about using dates and timestamps. Then there was the closing session, which is kind of strange when there are still two sessions to come.

I went to go see Kent Graziano with his talk on Reverse Engineering using SQL Developer Data Modeler. To bad his projector had some trouble, but he managed pretty good.

The final session of my KScope12 experience was APEX Developer Pro Secrets by Peter Raganitsch. Peter is maintaining the ApexLib Framework which was built by him and Patrick Wolf. There were other things in the presentation, some of which I knew, some of which were new.

The day was closed off by having dinner with Erik van Roon at a restaurant at the River Walk in San Antonio. After that I hung out in the bar at the hotel with Lori and her husband Ryan, amongst others.

On Friday I want home again, which was great too. It’s great to be back with my wife and kids after a great week in San Antonio. Thanks to ODTUG for organizing such a great conference. I hope to be back for KScope13 in New Orleans.

All questions that cannot be easily answered, except for the ‘go back in time’ one. You can’t (yet). This is where version control comes in.

I have worked with a number of version control systems and they all have their pros and cons.

First there was manual backing up of files, before you start working on a program. This works perfectly if you are the only developer on the project and if you are very precise in what you do. Problem is when you accidently overwrite a working version of the code with a buggy version.

Then there was Microsoft Visual Sourcesafe, which was a lot better already, because before you could change a program, you had to check it out (like getting it from a safe (hence the icon)) and when you were done with it, you checked it in (put it back in the safe) so other people could see you changes and continue working on them. I made it nearly impossible for two or more people to work on the same file at the same time. Biggest problem with VSS was (IMHO) that there was only a Windows client available (at that time at least). Luckily this was the main development platform for lots of Oracle developers at the time, and my personal favorite IDE PL/SQL Developer by Allround Automations has a nice plugin available so you don’t have to leave your IDE to perform the check-out and check-in of your code.

After this there were some other tools I used, mostly in the freeware world.

At my current job we decided to use SubVersion which is open source and has a lot of clients available, for different platforms. I mainly use TortoiseSVN which has a nice Windows explorer integration. One of the main advantages of this tool is that multiple developers can work on the same file and when they check-in their changes, the tool tries to merge the files automatically. If it finds stuff it cannot merge automatically it prompts the user to decide what change should be kept.

The biggest problem when developing code in a team is working on the same file simultaneously. Most of the tools available overcome this problem either by making it virtually impossible to simultaneously work on the same file or by automatically merging the different versions.

Redgate is currently developing Source Control for Oracle. What is needed for this tool is to be decided by us, the developers. Make sure you visit the site and fill out the survey to have a better chance of you desires to make it into a version of the product.

And, if you are at KScope12, stop by their booth to see the product being used in a live development environment.

Ref:

http://en.wikipedia.org/wiki/Microsoft_Visual_SourceSafe

http://en.wikipedia.org/wiki/Apache_Subversion

http://en.wikipedia.org/wiki/TortoiseSVN

http://www.red-gate.com/products/oracle-development/source-control-for-oracle/

Articles:

After KScope12 things will hopefully be a bit more relaxed again, so I will have some more time to write, either on the blog or on AllThingsOracle.

Just to let you guys know I am still alive

SQL today is the foundation for any successful application. Whether it is a SOA implementation, an ADF or APEX application, a Forms application or any other usage of the Oracle Database (or any other relational database), SQL is key for the success. AMIS has embraced the evolution of SQL through the subsequent releases of the Oracle Database - trying to stay abreast of the latest features and adopting new functionality whenever it made pragmatic sense. In 2004 we developed the 7Up training - that introduced Oracle developers who started their Oracle careers with Oracle7 to the wonders of Oracle 9i. Through the years, this training evolved into a 7-11 training and we kept on presenting, demonstrating and writing about new ways of using SQL - through gems such as inline views and subquery factoring, analytic functions, the model clause, aggregations, multi-table DML, Flashback, intervals and many more. We must be one of the few companies in the world able to demonstrate all of the above using the DEPT and EMP tables in the SCOTT schema.

In the month of April, we make another community contribution in the area of Oracle SQL: AMIS is the proud sponsor of the SQL Challenge - the world wide quiz for Oracle SQL developers that publishes weekly challenges, accessible via http://www.plsqlchallenge.com. Various SQL-smiths at AMIS gathered to produce five challenges to be published in five consecutive weeks. Challenges ranging from beginner level questions through the advanced level questions. Demonstrating the power of SQL and the fun of playing with it. Hopefully you find a mix of both easy and mind boggling questions. Maybe there are some things you had never thought of or even heard of and can be really useful in your day-to-day work.

AMIS provides prizes for contestants during AMIS April at the SQL Challenge. We will be selecting 2 winners from the people who have the highest score for all the quizzes. Besides that we will also select one random winner from all the people who played at least one of the five quizzes. Prizes include your choice of a SQL related eBook and for the first place winner also a SQL related book.

You can expect new quizzes every Saturday for the coming 5 weeks starting March 31st.

I will be presenting at the OGh Apex Event in Zeist (the Netherlands). I will be doing a presentation on how to build a plug-in for Apex, together with Alex Nuijten.

AMIS will be sponsoring the SQL Challenge in April. I have made a couple of questions for that. Lucas Jellema also made a couple of questions and we are now in the process of reviewing and approving them.

I will be doing two presentations at KScope12.

Should Invoker Rights Be Used?

When: Monday June 25, Session 3, 11:30 am - 12:30 pm

How can you ensure users use only their data and not someone else’s? How can you do this with minimal effort? How can you get rid of multiple codebases. How can you (partially) protect yourself against SQL Injection? Explore these questions and more in this informative session.

And

Can Collections Speed Up Your PL/SQL?

When: Wednesday June 27, Session 12, 8:30 am - 9:30 am

Collections (array-like structures in PL/SQL) are used in two of the most important performance features of PL/SQL: BULK COLLECT for data retrieval and FORALL for data modification. This session will cover these topics and more.

And, as a cherry on the cake, I received my ACE Award yesterday