Home » RDBMS Server » Server Administration » Oracle migration problem from 8i to 9i - URGENT
Oracle migration problem from 8i to 9i - URGENT [message #64086] Mon, 13 December 2004 20:21 Go to next message
Deepali Butaney
Messages: 4
Registered: December 2004
Junior Member
We have a Oracle/PowerBuilder application. After migrating from Oracle 8.1.7 to 9.2.0, data of all columns with datatype CHAR were not displayed in the Client (PB) application. We changed the datatype from CHAR to VARCHAR2. In this case queries with multiple outer joins fail. There are two options to make this work

1) The query works by adding a Trim function to one of the outer join 

2) by creating a copy of the same table and then firing the query on the copy of the table.

If you know anything about this problem, or any alternate solution please let me know. Thanks.

 
Re: Oracle migration problem from 8i to 9i - URGENT [message #64087 is a reply to message #64086] Mon, 13 December 2004 21:50 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Why not run a single update once ( as part of your migration )? Newly inserted data or updated Varchar2 data shouldn't produce the padding.

MHE
Re: Oracle migration problem from 8i to 9i - URGENT [message #64088 is a reply to message #64087] Mon, 13 December 2004 22:04 Go to previous messageGo to next message
Deepali Butaney
Messages: 4
Registered: December 2004
Junior Member
Hi Maaher, Thanx for the reply. If we migrate the database, create a backup of the table, drop all tables, recreate the tables and then import the data, it works. But the client will not accept such a solution which involves dropping of all the tables in the database and recreating them.
Can you suggest any alternate soln.
Once again thanx for taking time off to reply to my query
Re: Oracle migration problem from 8i to 9i - URGENT [message #64091 is a reply to message #64088] Tue, 14 December 2004 02:00 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Look at this example:
SQL> create table t(id number, val char(25));

Table created.

SQL> [insert statements]

SQL> select id
  2       , val
  3       , length(val) l
  4    from t
  5  /

        ID VAL                                L
---------- ------------------------- ----------
         1 ONE                               25
         2 TWO                               25
         3 THREE                             25
         4 FOUR                              25
         5 FIVE                              25
         6 SIX                               25
         7 SEVEN                             25
         8 EIGHT                             25
         9 NINE                              25
        10 TEN                               25

10 rows selected.

SQL> alter table t modify (val varchar2(25))
  2  /

Table altered.

SQL> update t set val = rtrim(val);

10 rows updated.

SQL> select id
  2       , val
  3       , length(val) l
  4    from t
  5  /

        ID VAL                                L
---------- ------------------------- ----------
         1 ONE                                3
         2 TWO                                3
         3 THREE                              5
         4 FOUR                               4
         5 FIVE                               4
         6 SIX                                3
         7 SEVEN                              5
         8 EIGHT                              5
         9 NINE                               4
        10 TEN                                3

10 rows selected.
And there you go. No table drop is needed. Just an alter table and an update.

MHE
Re: Oracle migration problem from 8i to 9i - URGENT [message #64092 is a reply to message #64091] Tue, 14 December 2004 02:13 Go to previous messageGo to next message
Deepali Butaney
Messages: 4
Registered: December 2004
Junior Member
Hey Maaher, I really appreciate your response. Unfortunately we have already tried this option that you mentioned and it does not work. We migrated the application and fired explicit updates on the table columns with Trim exactly the way you mentioned but the joins in SQL's still dont work. The only 2 options that worked, out of all that we tried are
1) Dropping the tables and recreate and import data - Client wont accept it
2) Adding an explicit Trim in each and every query in our application - Too much effort, will take days to do it.
Thanx buddy. Appreciate your effort and time.
Re: Oracle migration problem from 8i to 9i - URGENT [message #64093 is a reply to message #64086] Tue, 14 December 2004 03:35 Go to previous messageGo to next message
Sanjay Bajracharya
Messages: 279
Registered: October 2001
Location: Florida
Senior Member
I really have not worked with PB (combined with Oracle)...still. I also did migrate from 8.1.7 to 9.2.0 about a year ago and did not see anything like this at all

Question... how are you connecting the PB app to Oracle ? Is it native or ODBC ? Asking this, if ODBC, did you find if there are any upgraded drivers ? or did you upgrade the Oracle client too ?? I would try to use the 9.2 client with the 9.2 RDBMS.

Good luck.
--
Sanjay
Re: Oracle migration problem from 8i to 9i - URGENT [message #64094 is a reply to message #64092] Tue, 14 December 2004 03:36 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Have you considered NULL values? As it is possible that a CHAR column contains nothing but blanks, it will be trimmed to a zero-length string (= NULL in Oracle's logic ). It's a long shot, but you never know...

MHE
Re: Oracle migration problem from 8i to 9i - URGENT [message #64102 is a reply to message #64093] Wed, 15 December 2004 18:18 Go to previous messageGo to next message
Deepali Butaney
Messages: 4
Registered: December 2004
Junior Member
Sanjay, thanx for the reply. The problem is not with PowerBuilder. Even if we run the SQL in SQL Plus or any other tool, we get the same problem.

Maaher, Thanx once again. The problem is not with null values. The columns do have proper data.
Re: Oracle migration problem from 8i to 9i - URGENT [message #64403 is a reply to message #64086] Sun, 23 January 2005 13:21 Go to previous message
Dan Goodwyn
Messages: 1
Registered: January 2005
Junior Member
For my review..
Previous Topic: How can I find out why a job did not execute ?
Next Topic: loss of controlfile
Goto Forum:
  


Current Time: Fri Jan 10 04:03:48 CST 2025