Oracle 8.1.7 + WebSphere 3.5.0 = ORA-01007: variable not in select list

Hello,

can anyone please tell me if its not possible to connect to an Oracle 8.1.7 database from JSPs running on WebSphere 3.5.0, all running on NT?

The JSPs run fine on the same version of WebSphere when connecting to an 8.0.5 oracle database using classes111.zip. However when I try to move to 8.1.7 and classes12.zip I get an SQL error when I try to retrieve an oracle ref cursor as a resultset:

ORA-01007: variable not in select list

The JSP calls both stored procedures and functions and seems to be able to retrieve data from the procedures OK. However when I try to read from a cursor I get the oracle error above.

I don't think there is a problem with the actual stored functions as they can be executed from SQL Plus on the 8.1.7 database and the expected results obtained. Also the same functions and JSPs behave correctly when using 8.0.5

Any help or clues would be appreciated.

Thanks,
Sinead
sineadmcglynnAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
moduloConnect With a Mentor Commented:
Finalized as proposed

modulo

Community Support Moderator
Experts Exchange
0
 
ozymandiasCommented:
I am running WAS 3.5 and Oracle 8.1.7 no problems.
However, I do have WAS Fix Pack 4 installed, so actually I am running WAS 3.5.4.

You could try applying th fix pack :

http://www-4.ibm.com/software/webservers/appserv/efix.html
0
 
shyamkumarreddyCommented:
CAn u write me a sql file which u r trying to execute

Shyam
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
sineadmcglynnAuthor Commented:
The sql functions are generally of the form:

CREATE OR REPLACE FUNCTION GetUserGroups
(aUserId IN LGNCC_USERS.USERID%TYPE)
RETURN REP.CURSORTYPE
IS
     MYCURSOR REP.CURSORTYPE;
BEGIN
     OPEN MYCURSOR FOR
          SELECT      g.Name
          FROM      USERGROUP ug, GROUPS g
          WHERE     ug.userID = aUserID
          AND      ug.groupID = g.groupID
          ORDER BY g.Name;
     RETURN MYCURSOR;
     
     IF MYCURSOR%ISOPEN
     THEN
            CLOSE MYCURSOR;
     END IF;

EXCEPTION WHEN OTHERS
     THEN
          IF MYCURSOR%ISOPEN
          THEN
                 CLOSE MYCURSOR;
          END IF;
end GetUserGroups;
/

where the REP package defines a ref cursor type.

All the functions compile and execute from SQL Plus so I don't think the problem is in the SQL code, rather in how I'm retrieving results from it.

Thanks,

s

0
 
shyamkumarreddyCommented:
r u trying using ref
cursor from java

Shyam
0
 
saschekCommented:
Try to make shure all your JSPs were recompiled when using Oracle's classes12.zip!
(Edit the files or just set the date to tuday's date.)

BTW: Did it work with 8.1.7 and classes111.zip?
-- Sven

0
 
vemulCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
- To be PAQ'ed and points NOT refunded
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

vemul
Cleanup Volunteer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.