Blog Archives

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: , ,

Sequences in PL/SQL Expressions

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

This entry is part 3 of 8 in the series New Pl/Sql features 11gWith oracle 11g you can finally use sequences directly in any pl/sql expression wherever a number expression can be used. You no longer need to select from

Posted in Oracle, SQL Developer Tagged with: , ,