I’ve been testing various custom built applications running against an 11g database. I’ve been testing quite a variety of methods that can be used for database connectivity: JDBC, perl-dbi, pro C* being quite key in the partiuclar environment. There is also a legacy application that is using Oracle Forms/Reports. Version 6. Now, this is currently running against an Oracle 10.2.0.3 database and has done so quite happilly for some time now. However, if you followed and believed Metalink note 27303.1 then you would never have even tried attempting to connect Forms/Reports to a 10g database as this runs version 8.0.5 of the Oracle client and according to the matrix on that note the latest version of the database that an 8.0.5 client should connect to is 8.1.7.
I’ve seen 8.0.5 running against, 9.2, 10.1 and 10.2 all very happily. However something has changed with 11g, and I cannot connect an 8.0.5 client to an 11g instance. When you attempt a connection via sqlplus you recieve the following error:
ORA-00248: invalid option for ALTER SESSION
So I traced the client connection and I traced the listener and I traced the database and eventually saw the alter session that was causing the problem:
(b097) nspsend: 40 61 6C 74 65 72 20 73 |@alter s| (b097) nspsend: 65 73 73 69 6F 6E 20 73 |ession s| (b097) nspsend: 65 74 20 6E 6C 73 5F 6C |et nls_l| (b097) nspsend: 61 6E 67 75 61 67 65 3D |anguage=| (b097) nspsend: 20 27 45 4E 47 4C 49 53 | 'ENGLIS| (b097) nspsend: 48 27 20 6E 6C 73 5F 74 |H' nls_t| (b097) nspsend: 65 72 72 69 74 6F 72 79 |erritory| (b097) nspsend: 3D 20 27 55 4E 49 54 45 |= 'UNITE| (b097) nspsend: 44 40 20 4B 49 4E 47 44 |D@ KINGD| (b097) nspsend: 4F 4D 27 20 6E 6C 73 5F |OM' nls_| (b097) nspsend: 63 75 72 72 65 6E 63 79 |currency| (b097) nspsend: 3D 20 27 C2 A3 27 20 6E |= '..' n| (b097) nspsend: 6C 73 5F 69 73 6F 5F 63 |ls_iso_c| (b097) nspsend: 75 72 72 65 6E 63 79 3D |urrency=| (b097) nspsend: 20 27 55 4E 49 54 45 44 | 'UNITED| (b097) nspsend: 20 4B 49 4E 47 44 4F 4D | KINGDOM| (b097) nspsend: 27 20 40 6E 6C 73 5F 6E |' @nls_n| (b097) nspsend: 75 6D 65 72 69 63 5F 63 |umeric_c| (b097) nspsend: 68 61 72 61 63 74 65 72 |haracter| (b097) nspsend: 73 3D 20 27 2E 2C 27 20 |s= '.,' | (b097) nspsend: 6E 6C 73 5F 63 61 6C 65 |nls_cale| (b097) nspsend: 6E 64 61 72 3D 20 27 47 |ndar= 'G| (b097) nspsend: 52 45 47 4F 52 49 41 4E |REGORIAN| (b097) nspsend: 27 20 6E 6C 73 5F 64 61 |' nls_da| (b097) nspsend: 74 65 5F 40 66 6F 72 6D |te_@form| (b097) nspsend: 61 74 3D 20 27 44 44 2D |at= 'DD-| (b097) nspsend: 4D 6F 6E 2D 59 59 27 20 |Mon-YY' | (b097) nspsend: 6E 6C 73 5F 64 61 74 65 |nls_date| (b097) nspsend: 5F 6C 61 6E 67 75 61 67 |_languag| (b097) nspsend: 65 3D 20 27 45 4E 47 4C |e= 'ENGL| (b097) nspsend: 49 53 48 27 20 20 6E 6C |ISH' nl| (b097) nspsend: 73 5F 73 6F 72 74 3D 20 |s_sort= | (b097) nspsend: 27 42 49 4E 05 41 52 59 |'BIN.ARY| (b097) nspsend: 27 00 00 00 00 00 00 00 |'.......| (b097) nspsend: normal exit
But now the bit that really fails:
(b097) nsprecv: reading from transport... (b097) nttrd: entry (b097) nttrd: socket 208 had bytes read=11 (b097) nttrd: exit (b097) nsprecv: 11 bytes from transport (b097) nsprecv: tlen=11, plen=11, type=12 (b097) nsprecv: packet dump (b097) nsprecv: 00 0B 00 00 0C 00 00 00 |........| (b097) nsprecv: 01 00 01 00 00 00 00 00 |........|
This sequence of bytes being recieved is repeated 3 times. I compared this with the trace of a connection to 10.2.0.3, the alter session is the same in both cases, but this connection actually receives some data:
(b07f) nsprecv: reading from transport... (b07f) nttrd: entry (b07f) nttrd: socket 208 had bytes read=410 (b07f) nttrd: exit (b07f) nsprecv: 410 bytes from transport (b07f) nsprecv: tlen=410, plen=410, type=6 (b07f) nsprecv: packet dump (b07f) nsprecv: 01 9A 00 00 06 00 00 00 |........| (b07f) nsprecv: 00 00 08 0A 00 13 00 00 |........| (b07f) nsprecv: 00 13 41 55 54 48 5F 56 |..AUTH_V| (b07f) nsprecv: 45 52 53 49 4F 4E 5F 53 |ERSION_S| (b07f) nsprecv: 54 52 49 4E 47 12 00 00 |TRING...| (b07f) nsprecv: 00 12 2D 20 36 34 62 69 |..- 64bi| (b07f) nsprecv: 74 20 50 72 6F 64 75 63 |t Produc|
Oracle support will not touch this issue as an 8.0.5 has long ago fallen out of support. I have attempted playing around with the NLS settings but to no avail I cannot seem to make it work. This will almost certainly prevent the RDBMS being upgraded to 11g for at least a year, one possibility being mooted is to create an empty 10gR2 database and have this legacy application connect in to it and then use database links to the 11g database to actually retrieve the data. It does not exactly sound optimal to me.
I guess the moral of the story is not to let your clients lag so far behind your database instance!
I guess the moral of the story is not to let your clients lag so far behind your database instance!
So true. I have been postponing several 10G upgrades because of Forms 6i. As both 8.0.5 client and 9.2 databases are desupported, I started thinking about upgrading 9.2 databases to 10.2. You say you have been using this combination in production. What is the harm in trying, right?
Years will pass if I wait for the client tools to be upgraded, given there are no client-server Forms solutions after 6i and going from 6i to Webforms is itself a major upgrade too.
Comment by Yas — March 26, 2008 @ 11:54 am |
Jason, what is the number of users, number of forms in the environment you use Forms 6i, if this information is not confidential of course?
Comment by Yas — March 26, 2008 @ 11:58 am |
Hi Yas,
Thanks for commenting.
I suppose it’s a common theme that companies have to wait for the 3rd party app to be certified on the latest release before upgrading, but these clients will never be certified.
In my environment, I have ran the 8.0.5 client with forms and been exceedingly happy with this. There have been no issues caused by this combination.
I have had no dispute with oracle support about running this, as any SR’s have involved database issues completely unrelated to forms.
We have of the order of 100 users, with around 50 forms.
Comment by jarneil — March 26, 2008 @ 1:17 pm |
Thanks for sharing that information Jason. The environment here has thousands of Forms users but I am thinking about giving it a try, after testing of course.
Comment by Yas — March 26, 2008 @ 1:47 pm |
Wow Excellent info. I am at approx 100 (+) users, and 100s of forms and 100s of “reports”.
One part of my in-house app has been converted to Forms 10g and over all..lots of work. I am still working on the “reports” .
Again, thanks for the info!
Comment by Patrick — March 26, 2008 @ 10:13 pm |
Hi Jason
I imagine that this can be made to work, albeit you’ll want to consider support carefully. Note 452783.1 is all about the recent certification of E-Business Suite 11.5.10 with Oracle Database 11g. The point being that that release of EBS uses the same, legacy, technology stack. It’s possible that all you’ll need to do is run the cr9idata.pl script in $OH/nls/data/old and set the $ORA_NLS10 environment variable as per the EBS note. That would seem like a nice potential workaround. It’s possible as well of course that there is some other ‘magic’ in the tech stack patches for oracle apps that means this isn’t viable for you.
Comment by Niall Litchfield — March 27, 2008 @ 12:28 pm |
oops intended to say that you may need to patch the developer6i files to the last ever released patchset (18) as well
Comment by Niall Litchfield — March 27, 2008 @ 12:29 pm |
Hello Niall,
Thanks for pointing out the metalink Note, I will certainly examine that and log any findings here. My first thought was to attempt a patch the 8.0.5.0 client that forms/reports seems to use, though no patchset seemed available on metalink.
Again, thanks for the tip!
Comment by jarneil — March 27, 2008 @ 2:23 pm |
[...] Arneil has been testing legacy applications and Oracle 11g. “I’ve seen [Oracle client] 8.0.5 running against, 9.2, 10.1 and 10.2 all very happily. [...]
Pingback by Log Buffer #90: a Carnival of the Vanities for DBAs — March 28, 2008 @ 5:02 pm |
Hello Niall,
Well unfortunately, I have not been able to get the suggested solution in the metalink article to work. I followed the note and installed the examples CD, which includes the cr9idata.pl script which allows you to populate the $ORACLE_HOME/nls/data/9idata. I then restarted the db with the ORA_NLS10 parameter set.
Still sqlplus fails to connect. One thing to consider is that the note is talking about forms/developer 6i but this application is using forms/reports 6.0 NOT 6i. So the patchset 18 is not applicable. But it’s not a forms/reports issue anyway as sqlplus version 8.0.5.0 itself fails to connect to the 11g database, though has no problem with connecting to a 10.2.0.3 instance.
I cannot find a patchset for 8.0.5 client.
Comment by jarneil — April 7, 2008 @ 12:37 pm |
Ah
The apps tech stack is 8.0.6
Comment by Niall Litchfield — April 10, 2008 @ 12:13 pm |