Skip to main content

Three tips for getting started right with Oracle Database development

By "Oracle Database development", I mean, more or less, writing SQL and PL/SQL. I assume in this post that you have access to Oracle Database (which you can get via Cloud services, Docker, GitHub and OTN).

A. Use a powerful IDE, designed with database programming in mind.

There are lots of editors out there, and many IDEs that work with Oracle Database. Sure, you could use Notepad, but OMG the productivity loss. You could also use a popular editor like Sublime, and then it get it working with Oracle.

I suggest, however, that you download and install Oracle's own own, free, powerful IDE: SQL Developer.

If you like to complement your graphical IDE with a command line tool (or OMG if you actually prefer a command line tool to a graphical interface), you should also check out the relatively new and generating-lots-of-excitement SQLcl.

B. Enable compile-time warnings and PL/Scope.

The database has tons of useful functionality burned right into it, ready for you to use. For example, when PL/SQL program units are compiled, Oracle can give you feedback (aka, "compile-time warnings) to improve the quality and performance of your code.

In addition, PL/Scope - when enabled - will gather information about your identifiers and (in 12.2) SQL statements. This will allow you to do some very impressive impact analysis of your code.

Most developers are not aware of these features and so leave them turned off. Here's my suggestion for SQL Developer users:

Open up Preferences, type "compile" in the search field. Then change your settings to match these:


In other words:

1. Enable all warnings. 

This way, whenever you compile a program unit, Oracle will give you advice about ways to improve your code.

2. Treat all "severe" warnings as compile-time errors

If the PL/SQL team thinks these warnings are critical in some way, then I want to make my production code is free of such warnings. By setting this caregory to ERROR, I ensure that the code will not compile unless it is "clean". 

3. Tweak your optimization level up to 3 (all the good stuff plus subprogram inlining).

And even more important, take whatever steps are appropriate in your development environment to ensure that production code is compiled at this level of optimization as well. Check out this guidance from the PL/SQL dev team for more details.

4. Turn on PL/Scope.

You can then execute queries against your code to get information regarding naming conventions, sub-optimal code, and opportunities for performance improvements. 

Resources to help you with PL/Scope may be found on LiveSQL and GitHub.
Important Note: These are settings for use in DEVELOPMENT - and they will be applied to all connections made in SQL Developer. When you deploy to production, you should use a script that explicitly sets values for warnings, optimization level (still 3) and PL/Scope (off).
C. Decide RIGHT NOW on logging and instrumentation.

Before you start writing you next program, accept this reality: your code will be full of bugs. You will need to trace execution as well as log those bugs, in order to get your code ready for production and then keep it running smoothly in production.

You need a logging utility for this, and I suggest you use the open-source, widely-used Logger utility available from GitHub.



Comments

  1. Hello Steven,

    It might sound a little cynical from my side,
    but to me personally it looks like mastering PL/Scope at a level that would allow some really useful and deep code analysis
    is far more demanding than mastering all the other best practices of writing good, correct and efficient code.

    This goes "inline" with the fact that in most programming languages, mastering the debugger is much more difficult a task
    than never needing it at all ...

    A chicken and egg story ...


    Regarding the "clever" development tools ...
    As the old-fashioned that I am, all these IDE-s came in too late
    for me ... delving into any one of them would have reduced my already gained usual productivity back to zero ...

    I strongly believe that writing code using a "fashion-less"
    code editor like Notepad does "gym the brain" far stronger
    than any IDE tool that stops your thinking flow each moment with all kinds of bells and whistles attempting "to help you keep the spoon near your mouth" ...

    But, yes, those who start out today might think differently ...
    but only the exam of the results could really confirm which
    "thinking school" was better ...

    Thanks a lot & Best Regards,
    Iudith



    Thanks a lot & Best Regards,
    Iudith


    ReplyDelete
    Replies
    1. "mastering PL/Scope at a level that would allow some really useful and deep code analysis is far more demanding than mastering all the other best practices of writing good, correct and efficient code."

      Good point. We at Oracle need to build PL/Scope *into* products like SQL Developer, and not expect people to run their own queries, etc.

      But I don't think it's either-or. I am "old school" but I think that to ignore at least the basic productivity boost you get from an IDE over a "plain" editor is to sacrifice too much.

      And denying yourself some of that power does not, I think, lead you to a weaker knowledge of the language, or more careless application of it. Just the opposite - for example, if you follow my advice to use the compile-time warnings.

      Delete
  2. Hi Steven, recently discovered SQLcl and think it deserves a mention as well, great tool for use within the terminal. I typically use screen (alternatively tmux) to create a window dedicated to SQLcl, and a window for Vim and I'm good to go for most of my day to day tasks (which typically includes writing ad hoc queries, writing/editing plsql code, and some shell scripting). The big benefit for me is that I don't have to leave the terminal (hovering and clicking, ugh) and have fast access to all my favorite tools, e.g. awk, git, screen, sed, etc...

    ReplyDelete
    Replies
    1. Great suggestion, Jack. I will add that under the first tip.

      Delete
  3. Like iudith, I like to write PL/SQL in notepad, but I also like JDeveloper for debugging and comparing files, which are very useful. I recently became aware of the SQLcl at our local Oracle user group meeting.

    ReplyDelete
    Replies
    1. Notepad?!? I hope you at least meant Notepad++.

      Delete

Post a Comment

Popular posts from this blog

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel