SQL & PL/SQL

Hi all,

I'm working in an existing codebase (VB.Net) that is using an Oracle Database. One of the things the application is doing is getting a copy of the default value for the different columns. It looks something like this:

If Not String.IsNullOrEmpty(myRow(0).Item("Data_Default").ToString) Then
myCol.DefaultValue=myRow(0).Item("Data_Default").ToString
End If

That Data_Default value is being populated from the ALL_TAB_COLUMNS table; using a DataAdapter.Fill statement.

Currently, the system is using a stored procedure, and inside that stored proc we're converting the DATA_DEFAULT column (which is a LONG) into a VARCHAR2. If that conversion doesn't happen, and we simply include the DATA_DEFAULT column in our SELECT statement, the datatable that the adapter fills will have a value of 'NULL' for DATA_DEFAULT in each and every row.

This conversion takes a long time. Doing the query without the conversion in PL/SQL takes .2 seconds. Doing the conversion takes *40* seconds. That's 200x as long!

I'm certain that something is being done incorrectly here. Can someone let me know where I've gone wrong? I just feel like there has to be a better way to do this; but I'm not sure what.