Ruby and Oracle Enhanced Adapter: Strange date values causing errors

Seems that oracle not only allows ‘null’ dates, but also ‘empty’ dates. On ‘empty’ dates, the error would come up! I totally misjudged the results of the error then, it seems…

Below is what I changed in oracle_enhanced_oci_connection.rb in lib\ruby\gems\activerecord-oracle_enhanced-adapter-1.2.1\lib\connection_adapters\. The changed lines are marked with #MVR as comment above it. All I did is make ‘empty’ dates detectable and skip converting them to a date too, effectively making them ‘null’ values too Starts around line 128:


# ruby-oci8 1.0 returns OraDate
when OraDate
# MVR: treat 'empty' date/time strings as null too!
if !(v.hour == 0 && v.minute == 0 && v.second == 0 && v.year == 0 && v.month == 0 && v.day == 0)
# RSI: added emulate_dates_by_column_name functionality
if OracleEnhancedAdapter.emulate_dates && (v.hour == 0 && v.minute == 0 && v.second == 0)
v.to_date
else
# code from Time.time_with_datetime_fallback
begin
Time.send(Base.default_timezone, v.year, v.month, v.day, v.hour, v.minute, v.second)
rescue
offset = Base.default_timezone.to_sym == :local ? ::DateTime.local_offset : 0
::DateTime.civil(v.year, v.month, v.day, v.hour, v.minute, v.second, offset)
end
end
end
# ruby-oci8 2.0 returns Time or DateTime
when Time, DateTime
# MVR: treat 'empty' date/time strings as null too!
if !(v.hour == 0 && v.minute == 0 && v.second == 0 && v.year == 0 && v.month == 0 && v.day == 0)
if OracleEnhancedAdapter.emulate_dates && (v.hour == 0 && v.min == 0 && v.sec == 0)
v.to_date
else
# recreate Time or DateTime using Base.default_timezone
begin
Time.send(Base.default_timezone, v.year, v.month, v.day, v.hour, v.min, v.sec)
rescue
offset = Base.default_timezone.to_sym == :local ? ::DateTime.local_offset : 0
::DateTime.civil(v.year, v.month, v.day, v.hour, v.min, v.sec, offset)
end
end
end
else v
end


Hope this is of any help to those fighting with Oracle Cheers

Fixing Oracle 8i ‘ORA-00904: invalid column name’ on export

One of those days: You just want to migrate an ancient Oracle 8i database to a fresh ‘new’ Oracle 10g instance, when a simple bog-standard export (exp.exe) fails, displaying this dreaded error:

EXP-00008: ORACLE error 904 encountered
ORA-00904: invalid column name
EXP-00000: Export terminated unsuccessfully

---- start rant here ----

Ofcourse, no help provided as Oracle likes you to get your hands dirty (yes, pun intended): Tracing and monitoring SQL calls made by it’s exp.exe utility (and don’t even dare to use a utility for this they supplied themselves with Oracle 8), reading through log files, searching the internet to find what those SQL calls you finally found actually mean… the whole lot. Try that, if you’re not doing Oracle 24/7!

Somehow I always get somewhat ‘aggregated’ when a product is this ‘arrogant’, just assuming you live to work with it and have nothing else to be interested in! Even worse: When they supply tools with their product, that are as equally arrogant: If everything is configured OK, the tool works perfectly. But if something is wrong, you’ll tell the user nothing more than that something is wrong, leaving the user clueless and without a hint towards a solution. If there is one at all. Try letting the customer pay for that….

----- end rant here ----

Finding a solution:

Reading the error literally, I assumed one of my database’s column names were invalid, using some kind of Oracle / Java keyword that only on export would cause some kind of ‘clash’. As the error appeared while exporting Sequences, I searched through my primary key field names but found nothing wrong, from the looks of it. In the export’s parm file, I even tried exporting only specific tables, or just one table, to eliminate which table’s field names could cause the error…. Nothing.

As I knew my database was not using any of the Java magic internally, I searched the internet to find a way of ‘disabling’ support for Java in my database, trying to get the database to the most ‘basic’ state I could imagine. And then I arrived, after a long search, at this post on LazyDBA, referring to ORACLE MetaLink DocID 196921.1 (Oracle Meta…. WHAT? Geez…):

If Java is enabled, the Export utility uses DBMS_JAVA.LONGNAME in a query
while exporting synonyms. If the DBMS_JAVA or DBMS_JAVA_TEST package has not been
installed or somehow cannot be executed, this query will fail with and ORA-
00904: “invalid column name”.

Derived from the comments made in this post and the Oracle article, I came to the following steps to repair my Oracle database.

The solution:

Warning: Following these steps will complete remove the Java support for your database. In the Metalink article, and in the LazyDBA post, you’ll also find instructions on how to re-install the Java support, if your database needs it, but I have not tested this yet.

1. Start SQL*Plus and log in as ‘sys’ to the database
2. In SQL*Plus execute the following (ofcourse, use your own Oracle HOME path here):
   1. @D:\Oracle\ora81\javavm\install\rmjvm.sql
   2. @D:\Oracle\ora81\rdbms\admin\catalog.sql
   3. @D:\Oracle\ora81\rdbms\admin\catproc.sql
   4. @D:\Oracle\ora81\rdbms\admin\catexp.sql
   5. @D:\Oracle\ora81\rdbms\admin\utlrp.sql
3. Stop and start your Oracle 8i database (I just used Oracle’s DBA Studio for this)

Rant / Conclusion:

Why does Oracle make me feel so…. ‘old-skool‘? Every Oracle-delivered tool to ‘administrate’ their database:

1. Starts slowly, very slowly or just delivers a plain shell-like interface
2. Their user interface tools do not conform to any User Interface Design guideline I have ever seen. Ever!
   1. I mean: I know it’s Java, I know it can look different and even more ugly if you really want it to. Though I have seen Java front-end applications that look and behave like you would expect them to.
   2. If you make a command-line interface, at least include some user-friendly support in it: Not being able to scroll back with the cursor (only with backspace), not being able to select text with the keyboard, not being able to get previously entered commands back… I mean, come on! It’s almost surprising you can copy and paste with this beast.
3. Even TOAD, which seems to be the only ‘real’ alternative out there, while still being coded in Delphi by the way, is a commercial tool you have to pay for, if you want all the (so needed) features. That the license costs of Oracle do not even get you a normal DBA tool says enough for me. On a side note, though only user interface related: even TOAD makes me feel old-skool.

As the last line in this blog post, one positive comment:

At least Oracle knows how to make a fast database, if you configure and install it properly!