Hey Frans,

Did my first SQL to Oracle (10g) conversion using the custom templates and the project conversion tool. A few gotchas along the way (mainly because I'm no Oracle expert), but overall this saved us a HUGE amount of time.

One issue (not sure if I would call it a bug) that we ran into was the DDL that the templates generated put double quotes around every table/column name. It seems that when you create tables/columns in Oracle and use quotes around their name in the DDL, you must use quotes whenever you query that table/field. For example:

SELECT * FROM "customer";

instead of

SELECT * FROM customer;

Reserved words require the quotes,of course, similar to square brackets ("[user]") in SQL Server.

Again, I'm no Oracle expert, so this may be a setting that can be turned off in Oracle.

Thanks,

Phil

I thought LLBLgenPro always generate queries with double quotes, doesn't it?

Walaa wrote:

I thought LLBLgenPro always generate queries with double quotes, doesn't it?

Sorry, I wasn't clear in my post--I meant that any time you reference a table name or column name in a stored procedure or ad hoc query, you need to put quotes around its name. This is a bit of a pain, but maybe that's the way people normally do it in Oracle?

Phil

I don't know much about this also, but as far as I know double qoutes make table and fields names case sensetive, so may be it can work if you tryied the exact casing of the names without the double quotes.

Hey, Phil. Once you get your issue resolved (sorry I can't help with that) would you elaborate a bit for posterity on what the gotchas were in your conversion?

Thanks.

Jeff

jeffreygg wrote:

Hey, Phil. Once you get your issue resolved (sorry I can't help with that) would you elaborate a bit for posterity on what the gotchas were in your conversion?

Hey Jeff,

Most of it really had little or nothing to do with LLBL per se, and most of it was easily correctable. So these are more general gotchas then LLBL-generated DDL gotchas.

I should also say that I may not remember the whole trial and error process accurately, as I had to get the new version to compile (not run) as fast as I could, and once I got something working I never really looked back.

1. The issue with the quotes, as described in my original post.

2. Some names were truncated down to 30 characters, and some were not. Tables names were not, if I recall correctly. Foreign Key constraint names were truncated, but oddly not always properly. One example:

"FK_lead_activity_document973AD3D7"

is 33 characters. Not sure if it ignored the "FK_" when generating the name? I should have documented this as a possible bug, but (again) I was really pressed for time.

1. Stored procedures in Oracle can only return DataSets, not DataTables, regardless of how many resultsets they return, so interfaces for SPs had to be refactored.

2. I was concerned with having to change a bunch of interfaces for SPs again for each retreival procudure, since I would have to use a ref cursor as an IN/OUT parameter for each one. But as it turns out, LLBL handles this for you.

3. Of course, Oracle sequences had to be created for all SQL IDENTITY columns, and then had to be mapped in the LLBL designer.

4. I had to use the 10.1x ODP driver instead of the 10.2x driver. I was never able to figure out why I couldn't get the newer driver to work.

That's all I can think of at the moment. If I think of anything else I'll post it.

Overall, it was a pretty easy conversion. I actually converted from Version 1 of LLBL to version 2 at the same time, and got everything to compile in about 4 hours, including writing a script in SQL to generate Oracle SP stubs.

Phil