?
Solved

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

Posted on 2001-07-13
7
Medium Priority
?
410 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

DFW AZURE MEETUP TONIGHT FRI 6PM

We will be discussing what Azure Stack is, how does it fit into the suit of offerings that Azure has currently, and where can it fit into your organizations technology stack. We will also be discussing limitations of the platform while covering various applicable scenarios.

Question has a verified solution.

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

Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
In this post we will learn different types of Android Layout and some basics of an Android App.
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses
Course of the Month8 days, 19 hours left to enroll

764 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