Frank Schacherer Homepage
home technology bibliography

PL/SQL

[Angel.]

PL/SQL in my book stands for Pathetic Language/SQL. Without standing on your head, you can't even pass code references around, much less full blown objects. Then you have to take care of which version of oracle you are using, and what options it has enabled. There are bazillions of syntax idiosyncrasies, to the point that the only way to write syntactically correct code is with a syntax manual on your desk, or by cut, paste and modify. This all said, here are some tidbits that might be useful when you are forced to work with PL/SQL and SQL.

Cursors

You can work with cursors explicitly, opening them, iterating over them and closing them by hand. For this you always need a named, declared cursor.

Or you can do it implicitly: with a direct SELECT statement for cursors you expect to yield exactly a single row, or with a FOR statement for cursors that can yield any number of rows.

The SELECT creates a temporary anonymous cursor for you.

The FOR can use a named cursor or an anonymous one. The anonymous one can directly use variables from your code and implicitly defines the return record type, the named one needs to eat them up as parameters and needs an explicit return type. I prefer anonymous for cursors, they save you a lot of redundant typing, and feel quite perlish.

When you need to give the records of the result set to some function however, they need an explicitly named type, and you'll have to use a named cursor. You can use cursorname%ROWTYPE then. To avoid problems with several columns of the same name, give them aliases. When dereferencing those columns, you again have to give the alias name in double quotes: rec_varname."aliasname"

There are also cursor variables that hold references to a cursor and can be passed around. They are assigned to their cursor with a special OPEN varname FOR cursor-definition syntax, which also creates the cursor if it didn't exist yet. Several cursor vars can point to the same cursor object.

You can assign cursor vars to each other (like the pointers they are), or pass them on as arguments. The cursor objects created by opening a cursor var stays around as long as it is still referenced.

This wouldn't be PL/SQL if there wasn't a plethora of exceptions for cursor vars, like: you cannot declare them in a package header, compare them for equality, assign NULL to them, store them in lists (though you can do that for the records their associated cursor returns, of course), use them in dynamic PL/SQL and so on.

Lists, Arrays, Hashes

For storing lists of results (for example from cursors) in PL/SQL, there are three data types. They are declared in two steps: first declare the list type based on it's element type. Then declare the list variable as the list type. For PL/SQL work only, stick to the index-by tables.

Exceptions

Exceptions follow the same mechanism as in other languages. There are system and user defined exceptions. Both of them can be associated with a symbolic name, and common system exceptions already are, like NO_DATA_FOUND or TOO_MANY_ROWS.

In the real world, user defined exceptions are often thrown on the server to be handled in client code, where you don't know their name. Use RAISE_APPLICATION_ERROR(error_number, 'Error text'); You have a number range from -20000 to -20999.

Error numbers can be bound to an exception with the compiler pragma PRAGMA EXCEPTION_INIT(your_cute_name_here, error_number) after the named exception is declared with your_cute_name_here EXCEPTION;, so that you can catch the errors with a symbolic name in your exception handler. There can be several names for the same exception.

Changes to the database discarded when an unhandled exception occurs, but changes to package variables are not. There is no automatic ROLLBACK.

SQLCODE and SQLERRM contain the number and text of the exception last thrown.

Idiosyncrasies and Gotchas

An empty block has to contain at least the NULL; operation

DBMS_output does not auto-convert BOOLEAN to VARCHAR.

ROLLBACK doesn't take back changes on package vars.

Oracle and Tools

Database connection strings

There are myriad ways to connect to the database. The common one is a connection in the form of user/schema@connect. "connect" here can be either the global name of the database, which basically is a domain name, or it can be a net service name that is understood by Net8 or some other kind of nameserver. These servers then map the identifier to a database connection. Identifiers usually have the same name as the database, although this is convention only.

A global database name consists of the database name (up to 8 chars), and the domain name. If your domain is acme.com and the database name is mydb, the global name will be mydb.acme.com. You can see the global name with SELECT global_name FROM dual;. When no domain is specified, "world" is used as the domain, giving mydb.world as global name.

You can find out about name mappings in Net8 with the namesctl utility. list_objects will list all symbolic names. You can also dump the associated connection descriptions to a file. If not using Net8, your tnsnames.ora, under $ORACLE_HOME/network/admin, will contain those descriptions.

You can use a hard-coded connection in a private fixed user link CREATE DATABASE LINK link_name CONNECT TO user_name IDENTIFIED BY passwd USING 'service_name'; or in a public fixed user link CREATE PUBLIC DATABASE LINK mydb.acme.com CONNECT TO user_name AS passwd;. Note that in the public case, the global database name is used instead of a symbolic identifier, as there is no USING clause to figure out the connection.

SQL*PLUS

As a text based client for Oracle, SQL*Plus is useful for automation and because you can use it from within emacs. For formating results, it's a nightmare and I highly recommend making your queries from T.O.A.D., SQL Navigator or something similar.

If you are getting an error of the form "Input truncated to x characters" when you try to run your file, put an empty line at the end of your .sql file. This seems to make the error go away.

To disable all output formating use

    SET ECHO OFF
    SET NEWPAGE 0
    SET SPACE 0
    SET PAGESIZE 0
    SET FEEDBACK OFF
    SET HEADING OFF
    SET TRIMSPOOL ON

The most useful option is SET PAGES 0, which will suppress all headings, page breaks and titles.

Use desc tablename; to get a tablke description.

Shooting down frozen processes

    SELECT osuser, sid, serial#, program FROM v$session WHERE osuser = 'frs';
    ALTER SYSTEM KILL SESSION 'sid,serial#';
    

Synonyms, Views, Grants

Synonyms are like symbolic links. They save you extra typing.

Views are stored aliases for queries. Since every query returns a table, they can be queried like a table, save typing effort and allow people that are not so familiar with SQL to find all their information in one neat table. Because they really are a query that has to be executed in the background when they are used, they are bad for performance.

Grants give rights to an object to another user. E.g. GRANT SELECT ON tablename TO user;

Tuning 101

Your application is too slow? Usually the reason is in the embedded SQL. To make it run faster, create indices on your tables and calculate statistics for the optimizer. Only after this, if you're still to slow, you give direct hints to the optimizer.

You can find out where an index would be useful by using EXPLAIN PLAN. This writes an execution plan into PLAN_TABLE (which is not created by default, find it in $ORACLE_HOME/rdbms/admin/utlxplan.sql). You then can laboriously list this table. In SQL*PLUS use SET AUTOTRACE ON to automatically generate a plan for each query you do, which may not help if it takes hours for the query to complete. Graphical tools also have some way to show you the plan, usually.

    CREATE INDEX indexname ON tablename (fieldname,..) [TABLESPACE spacename]
    ANALYZE TABLE tablename  COMPUTE STATISTICS
    SELECT /*+ hints */ ...
    

Indices

If you keep your indices in a different tablespace, INSERTS and UPDATES, which also have to update the index, can do this in parallel and will be not so much slower than without an index.

Hints

    SELECT /*+ hints */ ...
    
full (tablename) cache(tablename)

For full table scans, cache the whole table in memory to avoid costly hard-drive reads. Your buffer cache (DB_block_buffers) must be large enough to hold the table, and you must analyze it.

index (tablename indexname)

Use the index in your query. Useful if Oracle for some reason decides the index is not worth it (while it is).

SQL

Tips and Tricks

DECODE can be used like an if-then-else column filter clause on your original result set. That is, you can pick values from a row or not based on other values in the row.

For example sum(decode(condition_column, 'comparison value', result_colum, 0)) will sum up the values of result_column for all rows if the row has the 'comparison value' in condition colum.

Elementary SQL Syntax

    UPDATE tablename SET fieldname1 = value, fieldname2 = value, ...  WHERE condition
    CREATE INDEX indexname ON tablename (fieldname)
    INSERT INTO tablename (fieldname1, fieldname2, ...) VALUES (value1, value2, ...)
    

SEQUENCES. They can be used to generate an ever increasing count, for example for rowids. A sequence can be accessed by many users concurrently with no waiting or locking. Use CURRVAL to reuse the last NEXTVAL (you alsways have to initialize in a new session by using a NEXTVAL first).

INSERT INTO xyz VALUES(my_sequence.NEXTVAL, ...)

GROUP BY. The simplest explanation I can come up with is to imagine group by doesn't do a thing to your original result set. It only changes the way aggregate functions are resolved and imposes a constraint of what you may put in the select clause: an aggregate function or one of the group by items.

Normally, aggregate functions aggregate over the whole result set. When a group by exists, they instead aggregate over the subsets imposed by the group by: all rows in the original result set that have the same content in all the group by items are considered a subset.

In Ora, HAVING allows you to search with a condition that applies to a group (e.g. count(aggreg) > 300). The WHERE clause always applies to a single row.

How can you manage to put overall information with each of the groups, when functions are always applied to a group? This is possible only in newer versions of Oracle.

Idiosyncrasies and Gotchas

Newlines in Strings can be concatenated in as CHR(10), tabs as CHR(9).

The empty string ('') is treated as NULL.

...WHERE 0 = (sub-query) is ok, the other way round is not.

Reference

SQL for Web Nerds by Philip Greenspun.

Oracle PL/SQL Programming by Steven Feuerstein and Bill Pribyl

Oracle8: The Complete Reference