Home » Developer & Programmer » Reports & Discoverer » Oracle 10G ORA-00932: inconsistent datatypes: expected - got -
Oracle 10G ORA-00932: inconsistent datatypes: expected - got - [message #255959] Thu, 02 August 2007 10:00 Go to next message
gvenkatabbu
Messages: 11
Registered: July 2007
Junior Member
Hi,

When I am trying to execute the following Stored procedure in Oracle 10g I am getting the following error
java.sql.SQLException: ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at "DNAUSER.GETEVENTPAGESELECTION", line 24
ORA-06512: at line 1

Where as the same works fine in Oracle 9i

Is there anything I am missing here? Or any change in Oracle9i and Oracle 10g with respect to stored procedure

CREATE OR REPLACE PROCEDURE getEventPageSelection(nodeNumbers IN NUM_ARRAY,
                                         selNumQryStr IN VARCHAR2,
                                          numOfRows IN  NUMBER  ,
                                          headerCacheSize IN NUMBER,
                                          totalCount IN NUMBER,
                                          selPageQryStr IN VARCHAR2,
                                          p_results OUT EVENTPAGE) 
 AS
  selRowNum NUMBER;
  startNum NUMBER;
  endNum NUMBER;
  pageDiff NUMBER;

 BEGIN
 
 -- Walk through the Node Number List and Insert into temporary table
    FOR nodenum IN 1 .. nodeNumbers.count
    LOOP
       INSERT INTO my_nodes values (nodeNumbers(nodenum));
    END LOOP;
  
 --Executing SQL Statement to find row Nume
   IF LENGTH(selNumQryStr)  > 0 THEN
	   EXECUTE IMMEDIATE selNumQryStr INTO selRowNum;
 END IF;
 
   IF selRowNum > 0 THEN   
 	  /*As we will be fetching the header cache above the row rentention
 		    reduce the total rows by header cache */
 	  IF headerCacheSize > 0 THEN      
 	      endNum := selRowNum + numOfRows - headerCacheSize - 1;
 	      startNum := selRowNum - headerCacheSize;
 	  ELSE
 	      endNum := selRowNum + numOfRows - 1;
 	      startNum := selRowNum;
 	  END IF;
 
 	  /*This is to care of the situation where the number of rows
 	    are less than the page in that case we should get all the
 	    rows not starting the rention row */  
 	  IF (totalCount > 0 AND totalCount < endNum) THEN
 	      pageDiff := endNum - totalCount;
 	      startNum := startNum - pageDiff;
 	      endNum := endNum - pageDiff;
 	  END IF;  
       
 	  EXECUTE IMMEDIATE selPageQryStr || endNum || ') where num >= ' || startNum BULK COLLECT INTO p_results;
  END IF;	  
                     
 END;


Thanks
Venkat
Re: Oracle 10G ORA-00932: inconsistent datatypes: expected - got - [message #256006 is a reply to message #255959] Thu, 02 August 2007 13:51 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Perhaps it's better to post this question in the PL/SQL part of the forum?
Re: Oracle 10G ORA-00932: inconsistent datatypes: expected - got - [message #256020 is a reply to message #256006] Thu, 02 August 2007 14:39 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
No idea. But this red closed bracked looks strange to me.
Quote:
EXECUTE IMMEDIATE selPageQryStr || endNum || ') where num >= ' || startNum BULK COLLECT INTO p_results;
Re: Oracle 10G ORA-00932: inconsistent datatypes: expected - got - [message #256021 is a reply to message #255959] Thu, 02 August 2007 14:44 Go to previous messageGo to next message
jayz240z
Messages: 11
Registered: August 2007
Junior Member
Thank you all for your replies. Here is a version of the working code:

CREATE OR REPLACE FUNCTION palomar_live.concatenate_list (
    p_cursor in Number
    )
  RETURN  CLOB
IS
  l_return  CLOB;

cursor get_desc IS 
    SELECT to_clob(program_cat_desc_text) as desc_text 
    FROM palomar_live.PROGRAM_CAT_DESC
    WHERE programs_id =  p_cursor
    order by order_num;
BEGIN
    for I in get_desc 
    LOOP
    l_return := l_return||I.desc_text;
    END LOOP;
  RETURN l_return;
END;


I two major problems with my first post. The first delt with how I was referencing my function. The second, I was trying to use a clob function (which I couldn't get to work) when a simple || worked.

Jason
Re: Oracle 10G ORA-00932: inconsistent datatypes: expected - got - [message #256081 is a reply to message #256021] Thu, 02 August 2007 23:24 Go to previous message
gvenkatabbu
Messages: 11
Registered: July 2007
Junior Member
I found the problem. In the query I am trying select two columns instead of one. I don't how it is working in oracle 9i. Looks like Oracle 9i shall take the first column and set to selRowNum fields.

Thanks
Venkat
Previous Topic: Reports - lines are not printed
Next Topic: Qry abt Repeating Frame.
Goto Forum:
  


Current Time: Fri Dec 09 17:13:25 CST 2016

Total time taken to generate the page: 0.31000 seconds