problem with code generation from Oracle

[John Keller]

I'm just trying out jOOQ for first time and having problems generating code from an Oracle database.

When I use 2.3.0, I get 0 schemata created (similar output as reported in this post: https://groups.google.com/forum/#!topic/jooq-user/4vbCjO91QKE)

When I use 2.0.6, I get the Schema and Factory objects generated, but nothing else.

Is there a way I can turn on more verbose logging output to help diagnose the problem?

thanks,
- john

[Lukas Eder]

Hello John,

> When I use 2.3.0, I get 0 schemata created (similar output as reported in
> this post: https://groups.google.com/forum/#!topic/jooq-user/4vbCjO91QKE)

Yes that issue will be fixed ASAP. You're using Postgres too, then?

> When I use 2.0.6, I get the Schema and Factory objects generated, but
> nothing else.

jOOQ can only generate what is available through the dictionary views
(i.e. information_schema). As stated in that other thread, this means
that the database user you're using to generate code with must have
some sort of privilege to the desired objects (tables, routines, etc).

> Is there a way I can turn on more verbose logging output to help diagnose
> the problem?

Yes, the easiest way is to put log4j on the classpath and use a simple
log4j.xml like this one here:
https://github.com/jOOQ/jOOQ/blob/master/jOOQ-test/src/log4j.xml

Setting the priority to debug will log all queries and results to the
dictionary views

[Lukas Eder]

I'm sorry I've missed the subject line. Your case is about Oracle....
Can you post the code generation properties xml? Also, can you please
indicate what type of user you're using to generate code with?

[John Keller]

Lukas,

I figured out my problem just after I posted here. I had the inputSchema in lower-case! When I changed it to upper-case, it worked beautifully.

Two minor suggestions:
1. since Oracle only allows upper-case schema names, you could use upper(schema_name) in your code and thus avoid this problem (basically treat it as if schema name is case-insensitive). This could be done for all other object names in Oracle as well.
2. if the code generation creates no table objects, you could output the query that likely caused this (in my case, the one that used the lower-case schema name) as informational (INFO) message to user. Since this would generally be an unexpected result, it would be helpful for newbies to see some output that helps them solve the problem.

In any case, it is a satisfying first step to get that working. I'm looking forward to exploring further...

thanks,
john

[Lukas Eder]

Hi John,

> 1. since Oracle only allows upper-case schema names, you could use
> upper(schema_name) in your code and thus avoid this problem (basically treat
> it as if schema name is case-insensitive). This could be done for all other
> object names in Oracle as well.

I wasn't aware of this, but you're right, as the documentation suggests:
http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements008.htm

So it might make sense to support case-insensitive <inputSchema>
filters in the source code generator. This would also have to apply
for the <outputSchema>, though. At least for Oracle. I'll file this as
feature request #1418:
https://sourceforge.net/apps/trac/jooq/ticket/1418

> 2. if the code generation creates no table objects, you could output the
> query that likely caused this (in my case, the one that used the lower-case
> schema name) as informational (INFO) message to user. Since this would
> generally be an unexpected result, it would be helpful for newbies to see
> some output that helps them solve the problem.

You're probably right. Then again, the includes / excludes filters may
cause this as well, and there are other reasons, too. I'll think about
this:
https://sourceforge.net/apps/trac/jooq/ticket/1419

Cheers
Lukas

[Lukas Eder]

I'm "waking up" this discussion because in fact, the assumptions under which we added case-insensitive OWNER search in jOOQ 2.4 were wrong. It *is* possible to create case sensitive user / schema names. The following code illustrates this:

CREATE USER "AaXx" IDENTIFIED BY abc;
CREATE TABLE "AaXx"."Tt"("Vv" VARCHAR2(30));
SELECT * FROM all_tables WHERE table_name = 'Tt';

Result:

OWNER  TABLE_NAME  ...
----------------------
AaXx   Tt

I've created https://github.com/jOOQ/jOOQ/issues/5990 to fix this in jOOQ 3.10

Lukas