This discussion is archived
6 Replies Latest reply: May 7, 2012 4:24 AM by Nicolas.Gasparotto RSS

Impact of LONG to BLOB migration on PeopleSoft

user273366 Newbie
Currently Being Moderated
Hello

We are running on Oracle PeopleSoft 8.8 with Oracle database 10g R2 (10.2.0.3) on Solaris 5.10 server.
We would like to migrate all tables which are having LONG(RAW) columns to BLOB, however these databases are being used by PeopleSoft v8.8.
Out concern is, what would be the impact of this change? Would there be any issues with the PeopleSoft queries/SQL's running in the database?
If the answer is YES, then please advice for which type/kind of statements or in what areas would we see any issues?
In case of any queries, kindly let me know .

Note: Any redirection to useful documentations would be useful. Thanks.

Thanks
Suddhasatwa.
  • 1. Re: Impact of LONG to BLOB migration on PeopleSoft
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    I think the LONG datatype is not in use anymore since application 9.0 and probably since PTools 8.48.
    As long as you are running on older Peoplesoft version, you should continue to use LONG datatype instead of LOB. By the way, LONG would become CLOB (not BLOB).
    Changing them can make you out of support (modification of standards), and will make the DDDAUDIT/SYSADUIT out of sync between PSoft meta-model and Oracle db. Moreover, one of the consequence will also be that eveytime you will (re)build a table, it will put LONG again and you will have to modify it manually.
    If your Peopletools version does not allow it, it's rather painful to do it.

    But what's the concern of use LONG datatype in your Peoplesoft environment ?

    Nicolas.
  • 2. Re: Impact of LONG to BLOB migration on PeopleSoft
    user273366 Newbie
    Currently Being Moderated
    Hello

    Thanks for the inputs.
    I tried to test one conversion, and using the to_lob() function on the database, it is getting converted to BLOB on my database (test).
    The issue with LONG(RAW) datatype is: we are seeing row chaining and very high amount of unused space in many tables in the database, consuming up to 50G of space unnecessarily.
    W.r.t to rebuilding of tables: please advice when this issue occurs (as you have mentioned above). We refresh our test instances from Production,so if we change the LONG columns to LOB/BLOB in production, the same would be reflected to the target database(s) as well.
    I have manually ran tests by peopleSoft queries, and they look to be running fine.
    Please highlight any other issues we might face with such conversion.

    Thanks.
    Suddhasatwa.
  • 3. Re: Impact of LONG to BLOB migration on PeopleSoft
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    Whenever you'll migrate a project containing such table with that BLOB datatype, you will rebuild the project on target and will be face this issue.
    Moreover, and I really insist, if you make such modification on standard tables you'll be out-of-support. And even on standard tables, I'm not sure neither if it will be supported. I don't know exactly the consequences of using a datatype which is not supposed to be used, but I wouldn't do that.

    And for the row chaining of the LONG RAW, it's not a Peoplesoft problem (you're right, it becomes BLOB, I misread and thought that was LONG).
    You should rather see on database side how you have to fix that issue, not on Peoplesoft application.

    Ultimately, upgrade to a more recent application/Peopletools. Or if nothing help and you don't trust what said above, raise a case to the support to hear from them what they suggest.

    Nicolas.
  • 4. Re: Impact of LONG to BLOB migration on PeopleSoft
    user273366 Newbie
    Currently Being Moderated
    Many Thanks for the above inputs.
    I would investigate from the DB perspective and update you. Thanks.
  • 5. Re: Impact of LONG to BLOB migration on PeopleSoft
    user273366 Newbie
    Currently Being Moderated
    Hello.

    Please advice if there are any other impacts of LONG RAW to BLOB Migration in a PeopleSoft environment, other than the one(s) mentioned above.
    Thanks.
  • 6. Re: Impact of LONG to BLOB migration on PeopleSoft
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    I found a Metalink note which does not say otherwise than I said above (it's supported only from Appl 9.0+ and PTools 8.48+) :
    +E-ORA: Supportability of CLOB and BLOB datatypes [ID 641070.1]+*

    Nicolas.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points