Loading ...
Sorry, an error occurred while loading the content.

RE: PeopleSoft DBA Forum Psft Application migration from SQL Server to Oracle 10g--> Issues with LONG data type

Expand Messages
  • David Kurtz
    The limit is still 2000 characters (because early versions of PT8.4 were certified with Oracle 8i - the limit on VARCHAR2 was increased to 4000 from Oracle
    Message 1 of 8 , Nov 10, 2006
    • 0 Attachment
      The limit is still 2000 characters (because early versions of PT8.4 were
      certified with Oracle 8i - the limit on VARCHAR2 was increased to 4000 from
      Oracle 9i).

      regards
      _________________________
      David Kurtz
      Go-Faster Consultancy Ltd.
      tel: +44 (0)7771 760660
      fax: +44 (0)7092 348865
      mailto:david.kurtz@...
      web: www.go-faster.co.uk
      Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
      The PeopleSoft DBA Blog: http://psftdba.blogspot.com
      PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

      > -----Original Message-----
      > From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com]On
      > Behalf Of Graham Bowden
      > Sent: 10 November 2006 19:02
      > To: psftdba@yahoogroups.com
      > Subject: Re: PeopleSoft DBA Forum Psft Application migration from
      > SQL Server to Oracle 10g--> Issues with LONG data type
      >
      >
      > Consider the possibility of using PeopleSoft Long with a limit on
      > the number
      > of characters. I have not tested this with more recent versions,
      > but if you
      > create a PeopleSoft Long and specify a maximum of 2000 char it creates a
      > varchar2(2000) instead (do not know whether the 2000 limit has been
      > increased now Oracle can take more).
      >
      > If there is not really very long data in the column (just more
      > than 254), it
      > might help.
      >
      > Graham Bowden
      > Springboard Solutions
      > ----- Original Message -----
      > From: "David Kurtz" <info2@...>
      > To: <psftdba@yahoogroups.com>
      > Sent: Friday, November 10, 2006 10:57 AM
      > Subject: RE: PeopleSoft DBA Forum Psft Application migration from
      > SQL Server
      > to Oracle 10g--> Issues with LONG data type
      >
      >
      > > You cannot use CLOBs in your applicaiton. It is supported and
      > it doesn't
      > > work.
      > > If you convert LONGs to CLOBs you get runtime errors in the PeopleSoft
      > > application (I stopped further testing this when I got errors in AE).
      > >
      > > Your approach will migrate the data, but then that part of your
      > > application will then not work.
      > > You need to correct your original customisation that introduced multiple
      > > longs per table.
      > >
      > > regards
      > > _________________________
      > > David Kurtz
      > > Go-Faster Consultancy Ltd.
      > > tel: +44 (0)7771 760660
      > > fax: +44 (0)7092 348865
      > > mailto:david.kurtz@...
      > > web: www.go-faster.co.uk
      > > Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
      > > The PeopleSoft DBA Blog: http://psftdba.blogspot.com
      > > PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
      > >
      > >> -----Original Message-----
      > >> From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com]On
      > >> Behalf Of Manoj Kumar
      > >> Sent: 09 November 2006 08:18
      > >> To: psftdba@yahoogroups.com
      > >> Subject: Re: PeopleSoft DBA Forum Psft Application migration from
      > >> SQL Server to Oracle 10g--> Issues with LONG data type
      > >>
      > >>
      > >> Thanks very much for ur time....
      > >>
      > >> What we are thinking is the following option.SInce u mentioned we
      > >> may get runtime error ,thout of posting our way of approach. Can
      > >> u please have look ?
      > >>
      > >> ====================================================
      > >> * Identify the tables with LONG columns before exporting the database.
      > >>
      > >> * Export those tables using PeopleSoft Data mover from SQL Server
      > >> database.
      > >>
      > >> * Create those records definitions manually in AppDesigner in the
      > >> Oracle target database.
      > >>
      > >> * While building that record, choose 'Build Script File' only and
      > >> manually modify the datatype from LONG to CLOB in the script.
      > >>
      > >> * Create those table definitions in Oracle 10g using Sql+ (with
      > >> ?clob? data types instead of Long Char)
      > >>
      > >> * Import the data into the table using data mover.
      > >> ================================================
      > >> Will this method work? Do we need to take care of any other points..
      > >>
      > >>
      > >> Regards
      > >> Manoj
      > >>
      > >> ----- Original Message ----
      > >> From: David Kurtz <info2@...>
      > >> To: psftdba@yahoogroups.com
      > >> Sent: Thursday, 9 November, 2006 9:28:21 AM
      > >> Subject: RE: PeopleSoft DBA Forum Psft Application migration from
      > >> SQL Server to Oracle 10g--> Issues with LONG data type
      > >>
      > >>
      > >> You are right that Oracle doesn't permit more than one LONG column per
      > >> table. Otherwise, when you try to create it you get:
      > >> ORA-01754: a table may contain only one column of type LONG
      > >>
      > >> It is very unfortunate that PeopleSoft uses LONGs and not CLOBs. LONGs
      > >> impose a number of restrictions. It is an example of PeopleSoft not
      > >> adopting new database standards. CLOBs were introduced (I believe) in
      > >> Oracle 8i, Tools 8.1 was first certified on 8.1.6 so PeopleSoft could
      > >> have
      > >> made the change to Clobs (but probably didn't because PT8.0 was
      > >> certified on
      > >> Oracle 8.0).
      > >>
      > >> Worse still, there isn't anything you can do about it. I have
      > >> experimented
      > >> with converting the columns to CLOBs myself, but you get runtime
      > >> errors. It
      > >> doesn't work (and even if it did, it wouldn't be supported).
      > >>
      > >> This is not an import/export problem, its a design problem. It
      > >> will not be
      > >> a vanilla part of the application because PeopleSoft also
      > conform to the
      > >> 1
      > >> Long/table rule (although Application Designer does not enforce
      > >> it), so this
      > >> will be caused by a customisation. Its been a long time since
      > I went on
      > >> a
      > >> PeopleTools course, but I seem to remember being told about 1 long per
      > >> record limit.
      > >>
      > >> I think you need to recustomise you source system so that you
      > only have 1
      > >> long column per table. Long character fields in PeopleSoft
      > with between
      > >> 1
      > >> and 2000 characters are created as VARCHAR2 columns in Oracle.
      > >> Then migrate
      > >> the application changes and the problem table.
      > >>
      > >>
      > >> regards
      > >> _________________________
      > >> David Kurtz
      > >> Go-Faster Consultancy Ltd.
      > >> tel: +44 (0)7771 760660
      > >> fax: +44 (0)7092 348865
      > >> mailto:david.kurtz@...
      > >> web: www.go-faster.co.uk
      > >> Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
      > >> The PeopleSoft DBA Blog: http://psftdba.blogspot.com
      > >> PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
      > >>
      > >> > -----Original Message-----
      > >> > From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com]On
      > >> > Behalf Of Manoj Kumar
      > >> > Sent: 09 November 2006 02:15
      > >> > To: psftdba@yahoogroups.com
      > >> > Subject: PeopleSoft DBA Forum Psft Application migration from SQL
      > >> > Server to Oracle 10g--> Issues with LONG data type
      > >> >
      > >> >
      > >> > Hi Everybody,
      > >> >
      > >> > We are encountering some issues with migrating PS App in SQL
      > >> > server to Oracle 10g.
      > >> >
      > >> > We tried to import Data Mover export(sql server) and tried to
      > >> > import in to Oracle 10g with 8.22 tools.Since it is converting
      > >> > all text data type(sql server) columns to LONG(Oracle)
      > throwing error.
      > >> > Oracle doesn?t support more than 1 LONG column in a table. I?m
      > >> > aware that we can change the LONG to CLOB once you are in an
      > Oracle DB.
      > >> >
      > >> > We tested with a single table export with 1 text fields(in sql
      > >> > server) and did import to Oracle 10g but it is converting it to
      > >> > LONG data type(with Tools 8.22). But how to take care of this
      > >> > PSFT migration if the table is of a Sql server database and has
      > >> > more than 1 LONG columns? If it is not possible to import using
      > >> > Data mover, then any other alternatives are there? (other than
      > >> > Oracle Migration Work Bench?)
      > >> >
      > >> >
      > >> > Even if we migrate using any other method, is it required to
      > >> > synchronize (build/alter) that table between the AppDesigner
      > >> and Database?
      > >> >
      > >> > Any help would be greatly appreciated.
      > >> >
      > >> > Thanks & Regards
      > >> > Manoj
      > >> >
      > >> >
      > >> >
      > >> > __________________________________________________________
      > >> > Yahoo! India Answers: Share what you know. Learn something new
      > >> > http://in.answers.yahoo.com/
      > >> >
      > >> >
      > >> >
      > >> > PeopleSoft for the Oracle DBA is published by Apress - see
      > >> http://www.psftdba.com
      > >> The PeopleSoft DBA Forum is managed by http://www.go-faster.co.uk
      > >>
      > >> Yahoo! Groups Links
      > >>
      > >>
      > >>
      > >>
      > >>
      > >>
      > >>
      > >>
      > >>
      > >>
      > >> PeopleSoft for the Oracle DBA is published by Apress - see
      > >> http://www.psftdba.com
      > >> The PeopleSoft DBA Forum is managed by http://www.go-faster.co.uk
      > >>
      > >> Yahoo! Groups Links
      > >>
      > >>
      > >>
      > >>
      > >>
      > >> __________________________________________________________
      > >> Yahoo! India Answers: Share what you know. Learn something new
      > >> http://in.answers.yahoo.com/
      > >>
      > >>
      > >>
      > >> PeopleSoft for the Oracle DBA is published by Apress - see
      > >> http://www.psftdba.com
      > >> The PeopleSoft DBA Forum is managed by http://www.go-faster.co.uk
      > >>
      > >> Yahoo! Groups Links
      > >>
      > >>
      > >>
      > >>
      > >>
      > >
      > >
      > >
      > >
      > >
      > >
      > > PeopleSoft for the Oracle DBA is published by Apress - see
      > > http://www.psftdba.com
      > > The PeopleSoft DBA Forum is managed by http://www.go-faster.co.uk
      > >
      > > Yahoo! Groups Links
      > >
      > >
      > >
      > >
      > >
      >
      >
      >
      >
      >
      > PeopleSoft for the Oracle DBA is published by Apress - see
      > http://www.psftdba.com
      > The PeopleSoft DBA Forum is managed by http://www.go-faster.co.uk
      >
      > Yahoo! Groups Links
      >
      >
      >
      >
      >
    Your message has been successfully submitted and would be delivered to recipients shortly.