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.
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 ?
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.
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.
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]+*