Skip to content

Author Archives PL/SQL Best Practices

Table Encapsulation and %ROWTYPE (Q&A)

Steven, I have taken your advice about writing SQL statements to heart (don’t write SQL in application-level code; hide them behind a packaged API, with as much of it generated as possible). I also decided (and I am the team leader so my decision carries some weight) to go the full route and I revoked privileges on the tables, so my developers have no choice but to use the encapsulation packages.

Here’s my problem: another of your best practices is to avoid hard-coded declarations and anchor variables back to database tables and columns with %TYPE and %ROWTYPE. But my people can’t do that – because I revoked privileges, and they need SELECT on a table to do that.

What’s a best practice-oriented guy supposed to do?

Posted 15 February 2008 § Uncategorized ‡ Comments Off °

An Error FORALL? (Q&A)

We have been just amazed at how much better our programs perform when we use FORALL to do our inserts and updates. We are now building a new application on Oracle Database 10g Release 2, and we have run into a problem. In all previous usages of FORALL, we would generally take a collection that was populated with a BULK COLLECT and push it into one or more tables.

Now we have a more complicated scenario, in which we must go through our collection of “candidate” data for inserts and remove some (perhaps all) of the rows before doing the insert. When we try to use FORALL, we get this error message:

ORA-22160: element at index [2750] does
not exist
How can we avoid this error and get all our data inserted?

Posted 04 February 2008 § Uncategorized ‡ Comments Off °