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

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

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

CONTINUE Statement

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

This entry is part 2 of 8 in the series New Pl/Sql features 11gThe CONTINUE statement exits the current iteration of a loop and transfers control to the next iteration (in contrast with the EXIT statement, which exits a loop

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

SIMPLE Data Types

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

This entry is part 1 of 8 in the series New Pl/Sql features 11gOracle introduced new datatype in release 1 of 11g. The simple stuf! SIMPLE_INTEGER; subtype of PLS_INTEGER SIMPLE_FLOAT; subtype of BINARY_FLOAT SIMPLE_DOUBLE; subtype of BINARY_DOUBLE These simple datatypes

Posted in Pl/Sql Tagged with: , ,