Solved

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

Posted on 2001-07-13
7
375 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:sineadmcglynn
7 Comments
 
LVL 15

Expert Comment

by:ozymandias
ID: 6280223
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
 
LVL 3

Expert Comment

by:shyamkumarreddy
ID: 6280632
CAn u write me a sql file which u r trying to execute

Shyam
0
 

Author Comment

by:sineadmcglynn
ID: 6280683
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 3

Expert Comment

by:shyamkumarreddy
ID: 6282461
r u trying using ref
cursor from java

Shyam
0
 
LVL 1

Expert Comment

by:saschek
ID: 6282469
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
 
LVL 5

Expert Comment

by:vemul
ID: 7669831
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
 

Accepted Solution

by:
modulo earned 0 total points
ID: 7729119
Finalized as proposed

modulo

Community Support Moderator
Experts Exchange
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

I had a project requirement for a displaying a user workbench .This workbench would consist multiple data grids .In each grid the user will be able to see a large number of data. These data grids should allow the user to 1. Sort 2. Export the …
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now