Error handling in VB/oracle using stored procedures for DML

[Fig000]

Hi,

I am new to using oracle with VB. I started going the route where I use VB and only use oracle to populate record sets (no parameter queries or stored procedures of triggers). I found that the errors returned to VB were terse and didn't specify which table field was affect. For instance, if I had more than on NOT NULL field, all I would get was an error saying that "some field" had violated the not null constraint.
So I turned to using stored procedures for DML. I chose triggers since I could return the name of the field affected, but testing exceptions (such as violations of uniqueness constraints) seems impossible. I am confused as to how to get decent error messages back to vb with the names of the fields affected. Any suggestions?

Thanks,
Neil

 

[MikeJones]

Normally an application has a built in knowledge of which columns are nullable and can theefore do tests up front. If you didn't want to do this at design time though and instead wanted to check everything at run time while possible I would be worried about the performance hit the checking would have.

There is a Database view, USER_TAB_COLUMNS which will tell you whether a column can accept a null value, you would need to query this based on the table you are inserting into and then compare the results with what nulls you want to insert, you could then return an error.

However, I would not use this method without fully exploring other routes.

Futhermore testing things like uniueness is very easy in Oracle. You can create Unique Indexes on tables on 1 or more columns so that if you try and insert a duplicate accross those columns Oracle will throw a DUP_VAL_ON_INDEX exception. This can then be trapped and dealt with. If your new to Exceptions or Uniqiue Indexes on Oracle I'd strongly recommend a Book. The O'reilly series has rarely let me down.

HTH,

Mike.

 

[gandolf989]

Neil,

You should look at Microsoft ADO for doing DML (Data Manipulation Language). You can create resources in you VB environment for each table and or views and post the queries and updates directly to those resources. Also I only use system views such as user_tab_columns in ad hoc queries. It is a bad practice to program to system views. You are vulnerable to changes in these views between releases of Oracle, or any other DBMS.

Brian.