Skip to main content

Resources for New PL/SQL Developers

I just received this question in my In Box:

"Which book do you recommend for people, brand new to PL/SQL? Assume the user is already familiar with SQL and now wants to start programming in Oracle."
First, I must confess that I spend too little time thinking about and being in contact with developers who are brand-new to PL/SQL. That is something I need to and plan to correct, especially given that when I search on "PL/SQL" in search engines, 3-4 of the top 10 hits are for tutorial sites. There is clearly a demand.

Until I put together my own set of tutorials, however, I thought I would offer the following:

Books

Oracle PL/SQL for Dummies

Written by my good friends and really outstanding Oracle technologists, Michael Rosnblum and Paul Dorsey of Dulcian. These guys really know their stuff and have put together an accessible text.

Learning Oracle PL/SQL

Written primarily by my co-author on the massive Oracle PL/SQL Programming, Bill Pribyl, this book is, admittedly, dated, but since the fundamentals of PL/SQL haven't changed (ever!), I believe you can still benefit from this text.

Beginning PL/SQL From Novice to Professional

I confess: I have not read this book, but the author, Don Bales, knows his stuff well and I am sure you will find the book helpful.

So those are books targeting beginners. Having said that, for anyone who has ever programmed in any language, you will find PL/SQL to be quite straightforward. So if the idea of programming and the concepts of loop, case, exception handling, etc. are not new to you, you will probably have lots of fun and learn all you need to know about PL/SQL from the 6th edition (covering all PL/SQL features through Oracle Database 12c) of Oracle PL/SQL Programming.

In addition, it's never to soon to pick up tips for writing high quality PL/SQL code. Sound good? Check out my Oracle PL/SQL Best Practices. A relatively short book that describes dozens of best practices that teaches best practices by following the challenges of a development team writing code for a make-believe company, MyFlimsyExcuse.com.

There are, of course, many other books on PL/SQL and more generally Oracle application development. Tom Kyte's Effective Oracle by Design, for example, is often named by Oracle technologists as a must-have book.

Websites

OK, so much for books. Of course, most people don't read books anymore. They just check their latest twitter feed for all the knowledge they (think) they need. So don't forget to follow me on twitter: @sfonplsql. :-)

And there are many, many sites that offer various kinds of content on PL/SQL. Time for another confession: I have not visited nor do I use many of them. But I offer below sites that I know offer very high quality content on PL/SQL.

PL/SQL Home Page

A great place to start, offering an overview of PL/SQL, with links to downloads, documentation and useful resources from the community.

Oracle Dev Gym

The Dev Gym (and it's previous incarnation, PL/SQL Challenge) is a website that promotes “active learning” — rather than passively reading a book or web page, you take quizzes on PL/SQL, SQL, logic, Database Design, and Oracle Application Express, thereby testing your knowledge. I wrote a daily quiz for this site for four years (over 1000 quizzes). Whew. If you have not checked out PL/SQL Challenge and you want to get serious about PL/SQL, you gotta visit!

Practically Perfect PL/SQL

My YouTube channel! Dozens of videos on PL/SQL, as well coding therapy sessions and tips on doing great presentations.

Oracle Developer Community for PL/SQL

The Oracle Developer Community “provides services and resources that de‐ velopers need to build, test, and deploy applications” based on Oracle technology. Boasting membership in the millions, OTN is a great place to download Oracle software, documentation, and lots of sample code. The PL/SQL page offers some excellent resources, and we will be adding to it greatly in the coming months (now: May 2014).

AskTom

AskTom is the amazing and incredibly popular Q&A forum that was hosted by Tom Kyte for 15 years, and is now handled by two members of the Developer Advocates team, Chris Saxon and Connor McDonald, with contributions from Maria Colgan, aka SQL Maria. AskTom is a bit of a "grab bag" - you never quite know what you will find in the sometimes lengthy discussion threads, but you are sure to find many answers to questions there. Tom and the rest of the team also offers great lists of links, resources and so on.

oracle-developer.net

Maintained by Adrian Billington (Who wrote the section in Oracle PL/SQL Programming Chapter 21 on pipelined table functions), this site is a resource for Oracle database developers which contains an outstanding collection of articles, tutorials, and utilities. Adrian offers in-depth treatments of new features in each release of Oracle Database, full of examples, performance analysis scripts, and more.

ORACLE-BASE

ORACLE-BASE is another fantastic resource for Oracle technologists built and maintained by a single Oracle expert: Tim Hall. Tim is an Oracle ACE Director, OakTable Network member, and was chosen as Oracle ACE of the Year 2006 by Oracle Magazine Editor’s Choice Awards. He has been invovled in DBA, design, and development work with Oracle databases since 1994. See http://oracle- base.com.

Rubber Hitting Road

So you read books, you check out websites. You realize that you can do this. You can write PL/SQL. You can manipulate the powerful Oracle Database using SQL and the simple, procedural constructs of PL/SQL.

Now it is time to write code. Because you never know a language until you start wrestling with the realities of making something work.

So that's your next task: write code!

Of course, many of you will be writing code for your job, so we don't to say a whole lot more about this.

If, on the other hand, you are looking to hone your skills to strengthen your ability to get a job, then I suggest the following:

Learn and use Oracle Application Express. With nothing more than SQL, PL/SQL and just enough HTML, CSS, etc. to make it look as "pretty" as you like, you can build powerful websites against the Oracle database. Apply for a workspace online, and get coding! Build an app for yourself or your family. It's free!

Learn and use SQL Developer, an "integrated development environment" (editor) for SQL and PL/SQL. It's from Oracle and it's free.

Labels:

Comments

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