Forum OpenACS Development: schema-browser in 4.6 broken for Oracle

Collapse
1: schema-browser in 4.6 broken for Oracle
Posted by Jon Griffin on 04/09/03 01:14 AM
It appears that something broke in schema browser around 4.6.
Oracle driver 2.6/OACS oacs-4-6 cvs, and also 4.6.1.
Error appears in this query (I think, logs aren't very helpful)

        select
            user_tab_columns.column_name,
            data_type,
            data_length,
            user_col_comments.comments as column_comments,
            user_tab_columns.data_default,
            decode(nullable,'N','NOT NULL','') as nullable
        from
            user_tab_columns,
            user_tables,
            user_col_comments
        where
            user_tables.table_name = upper(:table_name) and
            user_tab_columns.table_name = upper(:table_name) and
            user_col_comments.table_name(+) =  upper(:table_name) and
            user_col_comments.column_name(+) = user_tab_columns.column_name
        order by
            column_id

ORA-01405: fetched column value is NULL

is the browser error.

Collapse
2: Re: schema-browser in 4.6 broken for Oracle (response to 1)
Posted by Dave Hwang on 04/09/03 10:49 AM
The NULL column is user_tab_columns.data_default, which is defined as a LONG datatype. If it's null, we can't really do much about it in the SELECT statement, because NVL doesn't work on LONGs. The Oracle Docs say that we should use the OCI interface (i.e. the aD oracle-driver) to convert LONG datatypes.

I tried commenting out that column from the corresponding query in the TCL proc "sb_get_table_description" and the error just gets pushed to the next LONG column we select (user_constraints.search_condition). So I commented out all the references to this column as well in "sb_get_table_condition", and the error went away and I could select individual tables from /packages/schema-browser/www/index.tcl. HOWEVER, I'm not familiar enough with Schema Browser to know what information I'm losing by doing this.

I also found a note from Jeff Davis on old WebDB bboard about a modification to the aD oracle driver that makes this problem go away, though it may not be the right way to do it. I don't know what right thing to do is.

Collapse
3: Re: schema-browser in 4.6 broken for Oracle (response to 1)
Posted by Dave Hwang on 04/09/03 10:55 AM
Clarification: I removed the selects of user_constraints.search_condition and user_tab_columns.data_default from the tcl proc sb_get_table_description. That's the only proc I modified. There is no sb_get_table_condition. That's a typo. Sorry.
Collapse
4: Re: schema-browser in 4.6 broken for Oracle (response to 1)
Posted by Barry Books on 04/09/03 11:54 AM
I put the patch to ignore the error in my oracle driver about 2 years ago. I  have not seen any problems caused by it and it does fix the problem.
Collapse
5: Re: schema-browser in 4.6 broken for Oracle (response to 1)
Posted by Andrew Piskorski on 04/09/03 01:58 PM
Looks like the current nsoracle ora8.c sources from SourceForge already have Jeff's old fix applied, probably (I'm guessing) as part of "version 2.7". (Thanks for pointing that out, Dave!) Jon, Dave, have you tried that version?

Unfortunately, doesn't seem like nsoracle has been cvs tagged for release since October. But, it's only a few files anyway so just using the head isn't too tricky.

Collapse
9: Re: schema-browser in 4.6 broken for Oracle (response to 5)
Posted by Andrew Piskorski on 04/09/03 04:11 PM
My mistake, I must have read the sources wrong. But now I know nsoracle has the fix, because I just saw Jeff commit it. :)
Collapse
6: Re: schema-browser in 4.6 broken for Oracle (response to 1)
Posted by Dave Hwang on 04/09/03 02:15 PM
Yep. I patched my copy of oracle driver and it makes the error go away (well, we just ignore the error, but you know what I mean).
Collapse
7: Re: schema-browser in 4.6 broken for Oracle (response to 1)
Posted by Dave Hwang on 04/09/03 02:35 PM
Andrew, Sorry, I didn't answer your question properly (I'm too sleepy). I've only verified that patching v2.6 with Jeff's fix makes Schema Browser work. Where does the official oracle driver live? The AOLServer SourceForge Page shows version 2.6. I checked out the CVS version, and it does not seem to have the fix (and Schema Browser still throws the error). acs-misc.sourceforge.net doesn't have a link to any version of the oracle driver.
Collapse
8: Re: schema-browser in 4.6 broken for Oracle (response to 7)
Posted by Jeff Davis on 04/09/03 03:00 PM
The canonical source for oracle and postgres drivers is aolserver.com.

I applied and committed that change.

I have used a version of the driver with that change for some time with no apparent problems (although it should be pointed out that other than munging around in the data dictionary LONGs are not used in openacs, so that's not that telling).

Collapse
10: Re: schema-browser in 4.6 broken for Oracle (response to 1)
Posted by C. R. Oldham on 04/14/03 07:11 PM
So if I want the driver with this fix I need to get it from CVS HEAD on sourceforge?  Can someone point me to instructions?
Collapse
11: Re: schema-browser in 4.6 broken for Oracle (response to 10)
Posted by Dave Hwang on 04/14/03 11:05 PM
So if I want the driver with this fix I need to get it from CVS HEAD on sourceforge? Can someone point me to instructions?
http://sourceforge.net/cvs/?group_id=3152