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.



Labels:

Comments

  1. Sreekanth Babu VJuly 6, 2017 at 10:16 AM

    great tips... thanks Steve..

    ReplyDelete
  2. MuttleychessJuly 7, 2017 at 4:21 AM

    Thanks Steve

    ReplyDelete
  3. iudithJuly 8, 2017 at 7:10 AM

    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. Steven FeuersteinJuly 8, 2017 at 8:30 AM

      "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
  4. Jack TrocinskiJuly 26, 2017 at 6:20 AM

    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. Steven FeuersteinJuly 26, 2017 at 6:42 AM

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

      Delete
  5. AnonymousJuly 31, 2017 at 1:36 PM

    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. Peter NoskoAugust 1, 2017 at 4:47 PM

      Notepad?!? I hope you at least meant Notepad++.

      Delete

Post a Comment

Popular posts from this blog

Running out of PGA memory with MULTISET ops? Watch out for DISTINCT!

A PL/SQL team inside Oracle made excellent use of nested tables and MULTISET operators in SQL, blending data in tables with procedurally-generated datasets (nested tables).  All was going well when they hit the dreaded: ORA-04030: out of process memory when trying to allocate 2032 bytes  They asked for my help.  The error occurred on this SELECT: SELECT  *    FROM header_tab trx    WHERE (generated_ntab1 SUBMULTISET OF trx.column_ntab)       AND ((trx.column_ntab MULTISET             EXCEPT DISTINCT generated_ntab2) IS EMPTY) The problem is clearly related to the use of those nested tables. Now, there was clearly sufficient PGA for the nested tables themselves. So the problem was in executing the MULTISET-related functionality. We talked for a bit about dropping the use of nested tables and instead doing everything in SQL, to avoid the PGA error. That would, however require lots of work, revamping algorithms, ensuring correctness, you know the score. Then my eyes snagge
4 comments
Read more

How to Pick the Limit for BULK COLLECT

This question rolled into my In Box today: In the case of using the LIMIT clause of BULK COLLECT, how do we decide what value to use for the limit? First I give the quick answer, then I provide support for that answer Quick Answer Start with 100. That's the default (and only) setting for cursor FOR loop optimizations. It offers a sweet spot of improved performance over row-by-row and not-too-much PGA memory consumption. Test to see if that's fast enough (likely will be for many cases). If not, try higher values until you reach the performance level you need - and you are not consuming too much PGA memory.  Don't hard-code the limit value: make it a parameter to your subprogram or a constant in a package specification. Don't put anything in the collection you don't need. [from Giulio Dottorini] Remember: each session that runs this code will use that amount of memory. Background When you use BULK COLLECT, you retrieve more than row with each fetch,
4 comments
Read more

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
24 comments
Read more