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
Solved

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

Posted on 2001-07-13
7
393 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
The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

 
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

Suggested Solutions

Title # Comments Views Activity
diffSum example 4 50
Java exception bubble up 2 21
mysql jsp example issue 32 48
Is Applet the way to go for my drag and drop system? 8 25
Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

791 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