Advanced Web Programming outside of APEX

I finally post the"slides" from my presentation the "Oracle PL/SQL Programming Conference (OPP) 2010 Europe" that took place in Brussels last October.
I think that they have enough info to be interesting.

Quest Code Tester for Oracle 2.0 released

Quest Code Tester for Oracle has a new release (2.0) that you can try.
The new features are:

• Redesigned the Main Window: The Dashboard is replaced by a new console window, Test Explorer, that offers much greater usability and functionality.
• Powerful suite management: You can now define suites within suites and set up test workflow with branching logic.
• Multiple Test Definitions Per Program Unit: You can new create more than one test definition for the same program unit. This makes it easier for multiple developers to build and run tests for the same program unit.
• Importing and Exporting From the Database Server: Import and export can now be performed through a PL/SQL API
• New Command Line Utility Separate From Code Tester Executable.

There is a webcast (50 minutes long) of Steven Feuerstein presenting the new features.

Diminishing chaos

Following Tom Kyte's Blog I was introduced to the "Geek and Poke" comics. Maybe because I'm in a crazy working phase that makes me want to find fun in work (the kind of fun that makes you laugh about yourself and your work), tonight I spent some time browsing it, and briefly I found some very good amusing ones.
From the first one I show below I start writing this post about documentation and then I found out some other strips that are perfect to make you not forget the message (or at least smile a little :-))

Nulls the novice and experts trap

Novices and experts alike can fall in the null's hole.

In Oracle there are many reasons for that:

The most asked question about oracle from newbies

The most asked question that newbies ask me when starting using Oracle is:
How to create an "autonumber" column?

Unfortunately there is not way to directly create that (or to associate a sequence to a table for future reference).
You have to:

LISTAGG: 11g r2 new feature

LISTAGG it's a new aggregate function in Oracle 11g release 2. It performs string aggregation.
LISTAGG can optionally be used as an analytic (i.e. the optional OVER() clause).


LISTAGG Syntax structure:


LISTAGG( [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )]


User Enable Editions troubles and solutions

In Oracle 11g r2 the editions were the main new feature for database developers.

If a user has editions enabled some other user that has nor edition enabled cannot create a synonym to the first user (the one with editions enabled) objects.

You will need to enable also the editions for the second user.

alter user <user2> enable editions; (This clause is not reversible.)

If the schema contains any objects that are not editionable and that depend on editionable type objects in the schema, then you must specify FORCE to enable editions for this schema (otherwise you will get an ORA-38819: user USER2 owns one or more objects whose type is editionable and that have noneditioned dependent objects). In this case, all those objects will become invalid and you need to recompile them.

But be very careful, because you should not have table with object type columns where the object type is in that user (with enable editions)! That's because noneditioned objects like tables cannot depend on editioned objects like object types (you would get an "ORA-04063 table <:table_name> has errors" with those tables).
Solution: move the object types to an user with no "enable editions" set.

BTW (By the way) if you try to create a table depending in an edition object you would get an "ORA-38818 illegal reference to editioned object string.string"
The solution is the same as the above.

• Oracle 11R2 Editionable Objects
  
• Oracle Edition-Based Redefinition: Interview with Bryn Llewellyn
  

Steven Feuerstein share advice for Developers video

It's dated from August but it's always interesting and actual

Access security in mod_plsql or EPG

With mod_plsql or EPG (embedded pl/sql gateway) all the procedures (that meet the conditions) are available to be accessed by the web. That could be a security problem if you do not protect your procedure for that.
mod_plsql and EPG (DBMS_EPG) provide two way to deal with that:

The problem with Hot-Patching and having Result-Cached Functions

Sometimes we bypass some of the documentation: sometimes that is due to time constraints, other times it's due to "I already know this" or cross-reading.
Or maybe we read but forgot :-).

I wanted to store here (so I will remember) the solution for a problem I got when updating a package that had result-cached functions and those functions where used by other result-cached functions in another package.

Result-Cached Function

Due to the Automatic Detection of Data Sources of Result-Cached Function in Oracle 11R2 this is the code I'm doing in my 10r2 environment to prepare it for a 11R2 (or future migration. I will not prepare it to 11R1 :-) )

Best Practices PL/SQL (1)

There are several documents and also a book by Steven (with 2 editions that have some differences) about Best and Bad practices on PL/SQL.

The major contributions are from that book and recently from Gojko

I will in this topic show my best practices.
For now I will present then with no specific order (for instance importance), but maybe later I will compile then in some order.

SIMPLE_INTEGER

As I expected the new SIMPLE_INTEGER in Oracle 11g is faster than the previous integers datatypes.

As I had already mentioned in a previous post this new type has a NOT NULL constraint (cannot have null value) and wraps (does not give an overflow error).

What I was not expecting was that I needed to change the compilation type to native to see that speed improvement (in the default compilation mode (INTERPRETED) the improvement is not that great :-( ).

ALTER SESSION SET PLSQL_CODE_TYPE = 'NATIVE'; (default is INTERPRETED).

Of course that (changing to native compilation) is the first thing a person should do in 11g (specially in prodution)!
Why?
Without the need of an external C compiler and filesystem storage area and a faster than 10g native compilation this is the natural way to have a production system and even a developement and test system. (Ok it can make a very small delay when compiling but I did not notice it).

Do great coding with this two tips :-)!

PL/SQL standards

Some links for PL/SQL standards documents

http://www.redhat.com/docs/manuals/waf/rhea-dg-waf-en-6.0/ap-sql-standards.html
http://www.orafaq.com/faqplsql.htm
http://www.orafaq.com/node/48
http://www.bbc.co.uk/guidelines/newmedia/technical/databases.shtml
http://www.williamrobertson.net/documents/plsqlcodingstandards.html
http://docs.online.bg/DB/Oracle_8_Automation/appendix-d.html
http://www.oreilly.com/catalog/oraclep2/chapter/ch03.html
http://apex.oracle.com/pls/otn/f?p=2853:4:548429873816108::NO::P4_QA_ID:3262
http://www.oreview.com/9701ault.htm

Good SQL practices

Youtube video (in 3 parts) derived from a presentation Stephane Faroult did to some IT managers several months ago about "Good SQL practices" (or should I say "the worst pratices"?)

Ten Things You Should Never Do in PL/SQL

Here are the ten "nevers":

1. Never ask for help
2. Never skip the coffee.
3. Never share what you learn.
4. Never doubt the gurus.
5. Never hide the details in your code.
6a. Never let anyone else read your code.
6b. Never read someone else's code.
7. Never worry about tomorrow. Code for today.
8. Never fix bugs that users haven't found.
9. Never assume an Oracle bug will be fixed.
10. Never say never.

Videos de apresentações do Steven Feuerstein

Encontrei estes videos no YouTube disponibilidados pela Quest Software com apresentações do Steven Feuerstein

Mantra

O Tom (do asktom.com) na pagina 71 do ultimo Oracle Magazine expõem outra vez a sua mantra já que está sempre a responder .

I have a pretty simple mantra when it comes to developing database software, and I have written this many times over the years:

• You should do it in a single SQL statement if at all possible.
  
• If you cannot do it in a single SQL statement, do it in PL/SQL.
  
• If you cannot do it in PL/SQL, try a Java stored procedure.
  
• If you cannot do it in Java, do it in a C external procedure.
  
• If you cannot do it in a C external procedure, you might want to seriously
  think about why it is you need to do it.
  

If you can do it in a single SQL statement, by all means do it in a single SQL statement. Do not waste time, energy, and CPU cycles writing procedural code that will run slower than regular SQL. This question comes up frequently on Ask Tom (asktom.oracle.com), and my answer has always been very consistent. If you don’t have to write procedural code, don’t. If you can erase many lines of procedural code and replace it with a single SQL statement, do so. Less code = fewer bugs; more code = more bugs. Remove bugs by utilizing SQL.

O custo de verificar a não duplicação de chaves

Hoje descobri que, segundo um artigo de http://www.jlcomp.demon.co.uk/, no caso de termos uma constrainst de unique_key e desta ser violada num statement o Oracle irá efectuar um select de modo a descobrir o nome da constraint para dar o erro
ORA-00001: unique constraint (<constraint_name>) violated.
No caso de somente usarmos um index (unique key) para além desse select será efectuado outro para detectar o nome do indice respectivo que foi violado.
O autor logicamente chega à conclusão que é melhor em termos de performance ter constraints associadas aos índices.
Uma outra "descoberta" é que um insert que dá erro de constraint realmente faz os seguintes passos:

• efectua um insert da linha completa
  
• actualiza os indices por ordem de criação (é então sugerido que os indices sejam criados por probabilidade de darem erro, ou seja PK e UK anteriormente a os outros)....falha
  
• redo do insert (que implica redo dos indices que tenham sido actualizados)
  

Minha conclusão: A importância desta informação depende do sistema. Alguns programadores preferem verificar no cliente a existência de colisão de uma futura inserção (via select 1 into L_verify from tabela where key=:p_nova_key) antes de tentarem inserir ou fazer update.
Outros preferem fazer o insert e no caso de lançamento de exception de DUPLICATE_KEY fazer update e outros o MERGE.

É necessário fazer testes no sistema para verificar qual será mais eficaz no caso da performance ser um problema.
Em termos de leitura o MERGE e o modo da exception são mais compreensivos.

Valor actual de uma sequência

Hoje uma ajuda simples.

O ORACLE não providencia uma função para saber o valor corrente de uma sequência.

A chamada a CURRVAL, por exemplo
select <sequencia>.CURRVAL from dual;

dá erro ORA-08002: sequence DESTAQUES_SEQ.CURRVAL is not yet defined in this session
pois só funciona depois da sequencia ter sido usada (incrementada) via por exemplo

select <sequencia>.NEXTVAL from dual;

A solução é verificar pelas tabelas internas do ORACLE assim:

select last_number from user_sequences where sequence_name='<sequencia>';


Boa codificação


Actualização: este post foi traduzido para inglês e actualizado