PLSQL package best practice (fast changing schema)

I has been using PLSQL to build storage procedures in my daily work for a while.
Here are some lessons I have learned in our code that could have saved me some trouble in the future.

  1. Be careful where you commit your changes to data.
  2. Create a separated script for schema changes. Easier to track changes.
  3. Use one single wrapper object as the args for procedure.
  4. Use SYSREFCUSOR to return result set.
  5. Close resultSet (must)/ Statement (must)/ Connections(depends) after finished.
  6. Wrapping insert/update clause into functions will saves a lot of effort when table/type change.

Leave a Reply