Solved

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

Posted on 2001-07-13
7
386 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 …
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

773 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