Blog Archives

Recently I needed to clean some address data at a customer. Although the national addresses were stored  highly normalized whenever it a foreign address was used all bets seemed to be off. For most part city field seems to contain

Posted in Oracle

Execute immediate results in ora-00911 invalid character

Note to self: when receiving this error while executing DML using execute immediate, check for a semicolon (;) at the end of your statement. REMOVE! Share PostTwitterFacebookGoogle +1Email

Posted in Oracle, PEBKAC, Pl/Sql Tagged with: , ,

Unintended recursiveness

Today I learned another PL/SQL feature… the hard way. I was busy writing  some new code to import and process meta data. When testing the procedures performing a simple import  my procedure call kept running indefinitely and the OS load

Posted in Oracle Tagged with: ,

DBMS_ASSERT.SQL_OBJECT_NAME

DBMS_ASSERT is one of the oracle supplied pl/sql packages which contains useful functionality. Amongst others it supplies the function dbms_assert.sql_object_name. Although the function is very useful, it’s poorly documented and has several caveats you should be aware that are not

Posted in Oracle Tagged with: ,

About triggers

A trigger is a named PL/SQL unit that is stored in the database and fired (run) in response to a specified event. The specified event is defined on (associated with) either a table, a view, a schema, or the database,

Posted in Oracle, Triggers Tagged with: ,

Compound triggers

This entry is part 8 of 8 in the series New Pl/Sql features 11g

This entry is part 8 of 8 in the series New Pl/Sql features 11gIf you put business rules into triggers validating your data your could easily grow a considerable forest around your tables. Making it hard to see for anyone

Posted in Oracle, Pl/Sql Tagged with: , , ,

Predictable execution order of triggers

This entry is part 7 of 8 in the series New Pl/Sql features 11g

This entry is part 7 of 8 in the series New Pl/Sql features 11gPrior to 11g if you defined multiple triggers of the same type on the same table it was impossible to guarantee which trigger would fire first. Oracle

Posted in Oracle, Triggers Tagged with: , ,

PL/SQL Function Result Cache

This entry is part 6 of 8 in the series New Pl/Sql features 11g

This entry is part 6 of 8 in the series New Pl/Sql features 11gWith release 1 of 11g Oracle introduced PL/SQL Function Result Cache. As of release 2 of 11g Oracle further improved PL/SQL Function Result Cache. I’m not saying

Posted in Oracle, Pl/Sql Tagged with: , ,

Use named and mixed notation from SQL

This entry is part 5 of 8 in the series New Pl/Sql features 11g

This entry is part 5 of 8 in the series New Pl/Sql features 11gFor Pl/SQL program invocations it has named & mixed has been around for quite some time.However it you attempted the same function call from SQL prior to

Posted in Oracle, Pl/Sql Tagged with: , ,

Dynamic SQL Enhancements

This entry is part 4 of 8 in the series New Pl/Sql features 11g

This entry is part 4 of 8 in the series New Pl/Sql features 11gChanges have been made to both Native Dynamic SQL (NDS) and the DBMS_SQL package: Changes to NDS: the 32k limit has been lifted; EXECUTE IMMEDIATE now allows

Posted in Oracle, Pl/Sql Tagged with: , ,