[protege-discussion] Issue with Strings of length > 4000 when converting a project to Oracle XE database backend

[Daniel Holbert]

Hi all,

I never heard back about this bug I reported below (from 12/18) -- is 
it in Bugzilla?

Thanks,
~Daniel

Daniel Holbert wrote:
> Hello all,
> 
> I've run into a bug when converting a standard Protege project to 
> database-backed project,
> using Oracle 10g Express Edition as the database, for String fields with 
> more than 4000
> characters.
> 
> System info:
> ------------
>     OS: Ubuntu Linux 6.06.1
>     Protege: ver. 3.2 build 355
>     Database: Oracle 10g Express Edition
> 
> 
> Steps to reproduce the bug:
> -------------------
> 
> 1. Install Oracle 10g Express Edition. (Actually, it likely occurs with 
> other versions of
> Oracle, too -- this is just the only one I've tried.)
>      Download link:
>         
> http://www.oracle.com/technology/software/products/database/xe/index.html
> 
>      On Debian or Ubuntu Linux, use these instructions to get it via 
> your package manager:
>         
> http://www.oracle.com/technology/tech/linux/install/xe-on-kubuntu.html
> 
> 2. Copy the Oracle JDBC Driver into your Protege installation directory, 
> as "driver.jar" (or
> driver1.jar, etc)  In Ubuntu, this jarfile is located at
> /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/jdbc/lib/ojdbc14.jar
> 
> 3. Create the database user "protege".
>       - Open http://127.0.0.1:8080/apex in a web browser, and log in as 
> "system" (the root user).
>       - Click the "Administration" button, then "Database users", then 
> "Create >"
> 
> 4. Run Protege 3.2, and create a new "Protege Files" (standard) project.
> 
> 5. Create a class called "MyClass", with one String slot called "MySlot"
> 
> 6. In the instances tab, create an instance of MyClass.  Populate its 
> "MySlot" field with 4001
> characters. (for convenience, I'm attaching a text file that contains a 
> line with 4001 a's)
> 
> 7. Choose "Convert Project to Format" from the File menu, and select 
> "Protege Database".
> 
> 8. Fill in the following values:
>      Project:     [some new pprj file on your system]
>      JDBC Driver: oracle.jdbc.OracleDriver
>      JDBC URL:    jdbc:oracle:thin:@localhost:1521:XE
>      Table:       test
>      Username:    protege
>      Password:    [whatever password you set for the 'protege' db user]
> 
> This should cause Protege to pop up an error message saying:
> 
> **********
> There were errors at performing operation.
> 
> 1. Errors at copying knowledgebase jdbc:oracle:thin:@curators:1521:XE
>        Exception class java.sql.BatchUpdateException.  Message: 
> ORA-01400: cannot insert NULL
> into ("PROTEGE"."TEST"."VALUE_TYPE")
> 
> 
> See console and log for more details.
> **********
> 
> 
> If you repeat this using only 4000 a's (instead of 4001), it succeeds.  
> So, it seems 4000 is
> the cutoff.  Also,
> interestingly, I'm able to add extra characters to slots in existing 
> Database projects,
> pushing fields over 4000 characters, with no trouble.  The 
> 4000-character limit only seems to
> be a problem during the actual conversion to database-format.
> 
> This may be due to the issue "Oracle has the infamous problem of not 
> being able to store more
> than 4000 characters in a VARCHAR2 text field", mentioned at
> http://jira.atlassian.com/browse/JRA-4361.  I'm not sure how that 
> applies, because the string
> ends up being stored as a LONG data type -- not as a VARCHAR2.  Perhaps 
> it has to do with some
> intermediate conversion that involves a VARCHAR2.
> 
> I also found one workaround by editing the source code:  If you open up 
> the code for the class
> edu.stanford.smi.protege.storage.database.DatabaseFrameDb and set the 
> variable MAX_BATCH to 1
> or 2 (instead of the original value of 1000), the problem disappears. If 
> you use any value
> higher than 2, however, the problem will remain.
> 
> Thanks!
> Daniel Holbert
> PharmGKB
> Stanford University
> 
> 
> ------------------------------------------------------------------------
> 
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> 
> 
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> protege-discussion mailing list
> protege-discussion at lists.stanford.edu
> https://mailman.stanford.edu/mailman/listinfo/protege-discussion
> 
> Instructions for unsubscribing: http://protege.stanford.edu/doc/faq.html#01a.03