This isn’t a Designer question – it’s a database question.
We have most of our database experience on Sybase. We are going to be moving to Oracle, so we’re migrating our many legacy tables over.
In Sybase, if a column is defined as char, you can compare a Char(10) column versus a Char(5) or a varchar(x) column – and as long as the significant characters (not counting trailing blanks) are the same – they match – whether done in a join or a limit, or versus a literal string ‘ABC’, for example.
This does not appear to be true in Oracle. We are in the unfortunate position of having a new member of the DBA team as our Oracle DBA, and he’s both foreign, and very difficult to communicate with in discussing generic topics. He told us to just use RTRIM. (We could change the columns to be varchar2 – but would prefer to not have to revisit every table to do that, unless it’s absolutely necessary. We could change views to RTRIM – but that would take about about the same amount of effort.)
If you know Oracle well – can you confirm that this is Oracle’s default behavior, and that there is no server setting that could change this behavior? The server is currenly Oracle 9i.
No, BLANK_TRIMMING doesn’t do it. I had previously tried an internet search (since the DBA didn’t have any suggestions) – and got him to try changing that in the init file. It didn’t work.
The DBA subsequently claimed that BLANK_TRIMMING is only designed to handle assignment statements (eg: insert or select as insert statements where values are assigned into columns) & NOT comparisons (eg joins).
So – if you or anybody else has any other information about Semantics, I’d appreciate it. I don’t know what I’m looking for there.
Drats. No, our DBA wouldn’t do that. I would have to do that. We had done some research on differences, and had tried to manage the datatype conversions in a good way – but we hadn’t learned that this was a difference between Sybase and Oracle that we’d have to deal with.
We have about 600-700 tables that have been migrated, and about the same number of views, to both a DEV and a PRD server. I was hoping to not have to revisit every table and view, to determine which ones might actually be used in joins and really needed changing.
I was really hoping that there was a system parameter that would make Oracle behave in the same way as Sybase when joining chars of different lengths, or chars to varchar2s.
Anita, I am sure that you already know this since you have done some research on this. In my opinion, if your DBA uses varchar2 for alpha numeric fields, you’ll not run into issues using these columns in your joins.
As far as a server setting goes to globally convert all char fields to varchar fileds, I’ll check with a co-worker who is an Oracle DBA and will update this thread with my findings.
Migration work bench is available for Oracle 9i. More information on this can be found here.
It seems to me that when the schemas are brought in to migration workbench, the DBA can globally change settings.
I don’t know if your team used the work bench or not.
I’ll still check with my friend tomorrow. He does consulting for Oracle and he is a very knowledgeable guy.
No, we didn’t use the workbench. The DBA team that was in power at the time didn’t want to give my userid sufficient privileges for me to use it, and they didn’t want to do the work themselves. They offered to migrate Oracle tables for me – but since I was migrating from Sybase, they let me do it all on my own, with no assistance nor suggestions.
At this point, I do not want to change each and every column. We’ll just figure out what columns will be used for joins and change them on a case-by-case basis, as we figure it out.
(BOB member since 2002-06-17)
(BOB member since 2007-02-15)
(BOB member since 2006-02-06)
(BOB member since 2004-06-02)
(BOB member since 2004-06-02)