SQL & PL/SQL

Hi all,

Here's my problem: Our front end application sends insert and update queries to a pl/sql package. I've got a procedure that gets a PL/SQL table with all the column names an values, a table-name and a primary key name and value. That procedure creates an insert or update statement. So far, so good.

Now the problem: our front end doesn't know what the default value for a column is. So when a number field doesn't get a value in the front-end, it's inserted with a value '0' though is should ben NULL. My sollution for this is to know the default value of a column: when there's a default of '0', then the value that will be inserted is '0'. When there's no default value and the column can ben NULL, it'll be inserted as NULL. if the column has a not null constraint, a '0' will be inserted.

Ofcourse I can get the default value from the system-views all_tab_columns or user_tab_columns, but our application is installed at some 100 clients, and has various database installations. Most of the times, the tables are in the same schema as our procedure that performs the insert or update, but sometimes some of the tables are in another schema (in the same database) and also sometimes some tables are stored in another database. In all these situations, a synonym for that table exists in the schema.

Is there a api function or procedure that I can call to get the default value of a column? I looked at dbms_sql and dbms_metadata, but those packages don't give me that perticular information. if not, I'll stuck with the 'does table exists in schema' yes->use table, no query user_synonyms to look for the owner of the table (and database link) and query all_tab_columns to get the default value. Only this seems a bit overkill to me.

I hope I'm clear enough? And please don't reply with "don't do inserts this way"! I know, this is not the most optimal sollution, but it does gives us a couple of advantages in our application...