An interview with…Steven Feuerstein

Some time age one of my colleagues, An V., played with the idea to start a company magazine.  Everybody liked the idea and our manager gave a green light to create “De Poreiflash”.  Why it’s called “De Poreiflash” is a long story and I have to kill you when you don’t work for iAdvise and I tell you this.

But to keep the story short, she asked colleagues to contribute and asked me if I could write something about PL/SQL.
Sure I wanted to do that!  But about what in PL/SQL?  There so much to talk about…
I got “carte blanch”, so I started looking for topics: hints and tips, must know features, technical solutions,…
What else comes up when thinking about PL/SQL?
Indeed…Steven Feuerstein.

Wouldn’t it be cool to start with an interview with Steven Feuerstein?
I contacted Steven and he was prepared to answer some questions for “De Poreiflash”.
The first issue of “De Poreiflash” appeared two weeks ago.  An did a great job with our company magazine, it looks fantastic!
And here’s the interview how it appeared in “De Poreiflash”.
Somethings are already outdated: in the meantime Steven became a grandfather!
Congratulations Steven!

Q. Steven Feuerstein, the person with a PL/SQL obsession.  Introduction is probably not needed for people working with Oracle. Maybe a short one?

Sure. Well, yes, I think I do qualify as having an obsession about PL/SQL. I’ve written 10 books on the language and have spent most of my professional life since 1994 reading about, writing about, writing and generally wrestling with PL/SQL. Along the way, I’ve gathered some awards and related projects: I’ve been developing software since 1980, spent five years with Oracle (1987-1992), and have served as PL/SQL Evangelist for Quest Software since January 2001.

I am an Oracle ACE Director and write regularly for Oracle Magazine, which named me the PL/SQL Developer of the Year in both 2002 and 2006. I am also the first recipient of ODTUG’s Lifetime Achievement Award (2009).

In 2010, I started the PL/SQL Challenge, an online, daily PL/SQL quiz (now also offering weekly APEX, SQL and logic quizzes). In 2011, I launched PL/SQL Channel, a library of over 27 hours of detailed training on Oracle PL/SQL.

Q. How did you enter the Oracle world and got obsessed with PL/SQL?

I “fell” into the world of Oracle entirely by chance. I came out of university a Fortran programmer and ended up at Abbott Laboratories as a consultant, during which time I was introduced to databases on DEC10s and DEC20s. From there, I moved to CNA (insurance) in downtown Chicago and that was an incredibly boring job, though I did spend more time with a non-relational database while there.

So, bored out of my mind, with a newborn baby eating me out of house and home, I decided to look for a new position, and came across a Help Wanted ad looking for a person with relational database experience. Well, one of those words was familiar! So I got hold of an article on relational databases (Codd’s 12 rules), memorized that, and went to the interview. Turned out to be for a pre-sales job with Oracle. They were more interested in my ability to speak to large groups than my technical background. In any case, I was hired – the main issue for me was deciding whether or not I wanted to take a job that required wearing a suit and tie every day.

I spent a couple of years following salespeople around to accounts and doing the dog-and-pony shows for them with SQL*Plus, SQL*Forms, etc. Presales was interesting and sometimes exciting, but programming was way better, so I would constantly dabble with the Oracle tools, building little apps for my co-workers to use. This developed into TeamSell, a sales support application that caught the eye of Mike Fields, head of U.S. Sales in the early ’90s. I was drafted to join a small dev team and we started building some very cool SQL*Forms apps to support the U.S. sales force.

Then Larry canned Mike, and I was told to go back out on the road to help sell Oracle. I said no thanks, and took the first consulting job I was offered. Two years later, I saw an appeal on CompuServe for Oracle authors and I thought “Why not? I can write.” So I wrote Oracle PL/SQL Programming, which was the first independent text on PL/SQL, and it changed my life. From that point on, I was virtually a full-time student of the PL/SQL language, researching, writing, teaching, building code, etc. As I mentioned earlier, once you can structure your own time, all it takes is discipline­and reasonably good typing skills­and you can accomplish an awful lot!

I can still remember thinking as I signed the contract with O’Reilly that stipulated a book of 400 pages: “Is there really that much to write about PL/SQL?” 1200 pages later, we had to cut 400 pages to publish the first edition. Those 400 pages were all about writing PL/SQL in Oracle Forms and Reports. We should have published that immediately as a “side book.” Ah well….

Q. What’s your current job?

I am the PL/SQL Evangelist for Quest Software, have been since 2001. What this means, for the most part, is that Quest gives me lots of freedom to continue to explore the language, and support the worldwide PL/SQL community. Of course, I also do some product development, design and marketing. But I do think that Quest deserves lots of credit and a big thank you from PL/SQL developers around the world for making it possible for me to maintain my expertise and help other developers around the world.

Q. Other activities?

Ah, well, I used to play racquetball, go on lots of long bicycle rides, all sorts of things. Now, as I move through my sixth decade on Planet Earth, I find myself spending more time than ever in front of my laptop (believe me, it is not easy to write five new PL/SQL quizzes each week!) and THAT IS NOT A GOOD THING. So I struggle to keep my body in decent shop and avoid the problems associated with lots of typing and lots of staring at screens. But soon I will have a major new activity: I will be a grandfather by early November. Very exciting!

Q. About the PL/SQL Challenge…  What is it?

The PL/SQL Challenge is my latest and greatest effort to (a) coalesce an active, engaged, global community of PL/SQL developers, and (b) “download” my PL/SQL expertise from my brain into a format that is accessible to all developers and will be around long after I am gone. There are lots of sites that offer Q&A for PL/SQL and SQL; I am not interested in providing another of those. Instead, I think that developers are like “normal” human beings: we like to compete, we like to win prizes, we like to be recognized, we like to be challenged.

The PL/SQL Challenge does all of that: you take daily, weekly and monthly quizzes, competing with hundreds (and hopefully soon thousands) of other Oracle technologists. Of course, you don’t have to “compete”. You can simply take the quizzes (using, by the way, a nickname that does not reveal your true identity) as another way to deepen your knowledge of PL/SQL and related technologies like SQL and APEX.

So today it is a totally free quiz-driven learning experience. Soon, we will make it possible for you to join the PL/SQL Challenge by paying a small annual membership fee. Members will be able to utilize special features such as Practice Makes Expert, in which you can take past quizzes as often as you like, thereby sharpening your command of the PL/SQL language, and Quizbook, which will allow you to export quizzes as PDF “books” that you can use when away from the website – or to present to your manager to show her how much effort you are making to improve your technical skills.

If you haven’t registered and tried out the PL/SQL Challenge, I strongly encourage you to do so!

Q. Why should one participate?

Why not? It just takes a few minutes a day, and you are sure to either learn something new or demonstrate to everyone that you are well-versed in that particular feature of PL/SQL. When you play, you have the chance to win some prizes (ranging from O’Reilly ebooks to $250 Amazon.com gift cards).

Q. Some tips about developing PL/SQL?  Eg. on performance or hardcoding.

If you are not thoroughly comfortable with and applying everything on the list below, then you have a clear set of next steps when it comes to your PL/SQL training:

  • Collections
  • BULK COLLECT and FORALL
  • Autonomous transactions
  • Function result cache
  • DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
  • DBMS_UTILITY.FORMAT_ERROR_STACK
  • DBMS_UTILITY.FORMAT_CALL_STACK
  • Table functions
  • NOCOPY

Q. Can you recommend books, websites, presentations,… about PL/SQL?

There are lots of excellent Q&A forums for PL/SQL, with the OTN forum being about the best. Stackoverflow.com also attracts lots of PL/SQL-related questions.
Another great site for PL/SQL developers is oracle-developer.net, provided by Adrian Billington.
Tim Hall’s ORACLE-BASE.com is also an excellent all-around resource for Oracle technologists.
I do encourage your readers to check out all the sites and resources I’ve been putting together in recent years:

Q. With your books and presentations you inspired a lot of PL/SQL developers. How does that feel?

It feels great! I expect that many companies have indirectly benefited from my writings. But what I find most rewarding is the impact I have had on the lives of individual programmers. It is not terribly uncommon to have a person walk up to me at a conference or training and offer up a variation on this story, told to me back in 1998: “I was a union electrician at a steel mill in northern Indiana. When I got laid off, I went back to school and studied Oracle programming. I got hold of your book and it changed my life. Now I have a great job, my wife doesn’t have to work and can raise our kids.” Ah, that is so satisfying!

Now, just to make sure there is no misunderstanding: I am not saying that I think everyone should follow a lifestyle in which the man works and the wife stays home with the kids. :-)

But to have a  developer tell me his or her life was changed by my writing? Wow, what could be better? So, dear readers, don’t ever hesitate to send me an email (mailto: steven@stevenfeuerstein.com) or tell me in person about the impact my work has had on your life. I can’t get enough of that! :-)

Q. A last message to our readers?

I just turned 53 years old. I’ve been writing software rather intensively since 1980. Thirty years of programming, thirty years of (largely) sitting in front of keyboards and screens.

It’s been a great life (and I expect lots more good years to come), but I feel more and more of late that I haven’t maintained enough of a balance. There’s a very big world out there beyond my laptop, and I should be experiencing more of it.

So that will be my last message for your readers: Enjoy writing your software, but don’t let it consume your life!

OBIEE 11G – Error in Importing Metadata

Recently I start creating a repository (11g version) in offline mode and I want to import metadata which failed by the error ‘The connection has failed’.. I have searched for a solution and I will explain this in the next sections.

We can import some metadata from the Oracle BI Administration Tool > File > Import > from Database …  In the Import dialog box, we have to select a connection type, in my case ‘OCI 10g/11g’. Next step is to enter a Data Source Name (orcl) and a username and password from which you want to import the tables.

Import Metadata

By clicking on the ‘next’-button it troughs an error ‘The connection has failed..’ This was very weird because my Oracle DB and Listener were up-and-running. I had checked this before to make a connection via SQLdeveloper.

At this point I had to find a solution before I could go further on my repository modeling. On Oracle forums I found a nice tip which solves the problem.

The root cause can be found in the following directory:

<biee11>\instances\instance1\bifoundation\OracleBIApplication\coreapplication\setup

There you will find a file named ‘user.cmd’ respectively ‘user.sh’. When you open the .cmd file you will see that you can set a TNS_ADMIN. This was not done earlier and this causes the problem of importing metadata through the wizard.

Solution: you have to set the TNS_ADMIN to an appropriate path such as <biee11>\Oracle_BI1\network\admin. This is the location where your tnsnames.ora is stored.

Edit User Command

Afterwards, you have to save the .cmd file and try again to import some metadata in the Administration Tool.

Note: maybe you have to close the Administration Tool or restart your BI-services. For me it was sufficient to restart the Administration Tool and everything was working fine!

Important remark: when you are creating Dashboards & Answers or you are working in Online mode and you want to retrieve some data for a certain table by selecting the option ‘View Data’, you will also get the error: ‘The connection has failed..’. This problem will only occur when your tnsnames.ora is not stored in the Oracle_BI1 directory.

My Oracle Sources

Here’s a post with Oracle sources(blogs, twitter, websites,…) I use.
To get a nice overview I use igoogle.
It’s easy to use, you can embed bookmarks, RSS feeds, gadgets, … into your igoogle page.
And it’s available wherever you are(at work, at a customer, at home,…).

First page with Oracle and  Oracle Forms bookmarks(google bookmarks) and a twitter gadget.

 

Another tab with oracle related blogs.  A lot of blogs with interesting stuff.  The Amis blog is one of my favorites with lots of interesting technical articles.

 

And a last oracle related tab for SQL, PL/SQL and database.

 

And of course all pimped up with the iAdvise igoogle theme ;-)

When you have some tips to add more interesting links, sources, … feel free to comment.

Hot or Not … – Java For Enterprise Apps

A new article has popped up on the world wide web titled ‘Java is a Dead-End for Enterprise Apps‘ … this will certainly juice up some communities :-)

If you read along in the article you will find different interesting aspects regarding enterprise application development. The discussion that’s unveiled in the article regarding ‘Java is too complex for business application development’ is something I won’t get into. What I do find interesting in the article are the different technologies and frameworks being discussed such as JavaFX, Ajax, Spring, Hibernate, … and the focus on richer User Experience that leads to new frameworks and new insights.

The end-user wants to do more in his data entry / web applications … He wants to be able to interact in an intuitive and integrated way with co-workers, interchange data with other applications enterprise-wide and most of all reuse existing services within the company to reduce the manual workload.

In other words when we look at the applications of yesterday, today and tomorrow we see a steady leap towards interchangeability, interaction, social networking with a clear focus on Business Process Reengineering and let’s not forget most of all user-experience is key.

As mentioned in the article Business Process Modeling is becoming more and more a decisive factor in defining the strategy of a company and it’s IT development. In other words business process reengineering (BPR) is needed to rethink how work is done today within the organization in order to improve customer service, cut operation costs and improve agility and interchangeability.

For a more compelling story, have a look at: Java the Javatar … a must see for every IT consultant.

BI Publisher caching results problem

If you are experiencing behavior that indicates that your report is being cached and you can’t refresh it then it could be that a reporting setting is indicating that it can cache your result set.

To check this setting go to “Edit Report” and look at the bottom in the list of options. You will find a check-box with the label “Enable document cache”. Unchecking this check-box will probably solve your problem.

By default, all Oracle Enterprise Repository reports uses Document Caching to reduce database roundtrips on the generation of report data. While the data is cached, the rendered report may not always show the most recent activities in Oracle Enterprise Repository. Setting the cache expiration changes depending on how frequently BI Publisher needs to refresh the data for the second and subsequent rendering of a report.

It is possible to change the default cache expiration (30 minutes) under Admin -> Server configuration -> Cache Section.

More information can be found in the BI Publisher.

Oracle Discoverer to Oracle BI Enterprise Edition

Tuesday I went to a session regarding migrating Oracle Discoverer to OBIEE which was a very interesting resume on do’s and don’ts when migrating.

First of all only metadata is migrated, not the reports itself. When you think about that is a logical approach as well because you don’t want all your front-end reports and logic to be delivered in OBI. OBI has a lot of more features which you wouldn’t be using when migrating as well metadata as reports.

Tip: Use different rpd-files when you have loads of data to resolve performance issues because BI Server reads through the whole rpd-file when starting up.

How to start migrating:

  • Export ‘End User Layer’ in discoverer to a .eex file
  • The migrate tool itseld is a windows based tool which you can use command-line to migrate the .eex file to an .rpd file. You can change the properties of the migration tool in the MigrationConfig.properties file.
  • Copy the .rpd-file to your BI Server/Repository folder
  • Define which .rpd-file to start-up with in the NQSConfig.INI file
  • Update the .rpd-file online
  • Login to your OBI-environment and open up the Answers-tab to look through the Subject Area and data that has been created using the .rpd file. From now on you start creating your own reports as a business user.

What isn’t migrated when opening the rpd-file in OBI EE:

  • You have to reestablish the connection to your database
  • You have to define a password for your Administrator user otherwise your BI Server won’t come up on your linux environment. This is a very important tip: because it takes a while to find out which error occured when you’ve migrated your discoverer metadata to an .rpd file and your BI Server crashes.

PL/SQL Developer, Quiz thyself

This morning I went to a session of Steven Feuerstein: PL/SQl Developer, quiz thyself. A very interactive and fun presentation where the attendees had to answer questions regarding SQL and PL/SQL.

First we played a game of mastermind in which you can sharpen your deductive skills as Steven mentioned. Playing mastermind improves your skills to debug your code and eliminate factors out.

You can find all the material of his presentations and the quizes in the demo.zip file which is on his website (have a look at training, seminars).

Some items I’ve noted during the session:

How to continue past exceptions

  • Use format_error_stack to display you errors instead of SQLERR using the DBMS_UTILITY package
  • Use DBMS_ERRLOG.create_error_log to create an error log table based on your table on which your performing the DML on
  • Surpress exceptions at row level => only possible way is when using log_errors()
  • Exception raising is expensive in PL/SQL it’s better to trap exceptions

Performing bulk processing:

  • Use forall to perform bulk processing instead of looping over cursors and performing DML within the loop

Ask for improvements for 12G release: Use the website ‘I love PL/SQL and …’ to add your suggestions regarding functionality to add in 12g release.

  • plitblm holds all functions you can use when working with associative arrays
  • format_error_backtrace gives you the possibility to get the line number where the error was raised. Have a look at Quest Error Manager to get the error-log framework

The entire session was very interesting, especially because it was a fun and practical game where you can teach thyself.

Oracle Open World 2010

Me and my colleagues arrived saturday afternoon in San Francisco, we rushed to our hotel and after a refreshing shower after a whole day trip we went to our first event: drinking and eating ;o)

It was a fun event with a lot of benelux partners but we didn’t keep up very late because of the jet lag.

The next morning we went on a sailing trip with the Oracle Partners, had a nice lunch and then went to pick up our badges at Moscone West. As expected there are a lot of people attending Open World.

Regarding the Jet Lag, my internal clock is still running in the Belgium timezone … hopefully that’ll sort itself out asap ;o)

Today we’re going to start quizing ourselves with Steven Feuerstein …

To all, have a wonderfull Open World and don’t miss our session regarding Forms Modernisation Wednesday !