Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00932: inconsistent datatypes: expected - got CURSER
ORA-00932: inconsistent datatypes: expected - got CURSER [message #255748] Wed, 01 August 2007 11:53 Go to next message
jayz240z
Messages: 11
Registered: August 2007
Junior Member
I'm new to this forum. I hope to learn alot though. This is my first attempt at using CURSOR's. I've been struggling with the examples I've found through google and the ORACLE documentation. I've been working on this throughout the last couple of days.

My function is supposed to return a clob that consists of multiple record results for a varchar2. Reason for the clob, is the summation of each individual record will result in more then 4000characters.

I'm running this on a ORACLE 10g database.

Here is my function:

CREATE OR REPLACE FUNCTION concatenate_list (p_cursor IN  SYS_REFCURSOR)
  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;
BEGIN
    for I in get_desc 
    LOOP
    l_return := dbms_lob.APPEND(l_return,desc_text);
    END LOOP;
  RETURN l_return;
END;


Here is the error message I'm getting:
PL/SQL: ORA-00932: inconsistent datatypes: expected - got CURSER

According to Toad my problem appears to be in reguards to my line creating the Cursor:

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;


This is how I plan on using the function once it is working:


select p.programs_id, p.program_title, 
        concatenate_list(program_id) as concatinated_desc
from palomar_live.programs p
group by p.programs_id, p.program_title;


Thank you for any help you guys can provide. I'm stepping out of my comfort zone trying to talk this problem. Normally I would simply create a script in ASP or Coldfusion to handle this, but I need this solution to take place within the database. I'm also trying to expand upon my own techinical abilities.

Thank you

Jason

[Updated on: Wed, 01 August 2007 11:56]

Report message to a moderator

Re: ORA-00932: inconsistent datatypes: expected - got CURSER [message #255755 is a reply to message #255748] Wed, 01 August 2007 12:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Your parameter is of SYS_REFCURSOR type, it should be of the same type than program_id.
2/ In your loop you have to prefix desc_text with "i."
3/ Search for stragg function, it is strong, well-proved, works for more that what you want to do.

Remember you cannot return more than 4000 bytes in a SQL field.

Regards
Michel
Re: ORA-00932: inconsistent datatypes: expected - got CURSER [message #255801 is a reply to message #255748] Wed, 01 August 2007 15:32 Go to previous messageGo to next message
jayz240z
Messages: 11
Registered: August 2007
Junior Member
Michel,

Thank you for your reply. I did get the stragg function to work, however it appears there is an issue with ordering the results within the function. I need to do more reading up on using Cursors within functions. With your advice I modified my last attempt I'm still getting an error though. Here is what it now looks like:

CREATE OR REPLACE FUNCTION concatenate_list (p_cursor 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;
BEGIN
    for I in get_desc 
    LOOP
    l_return := dbms_lob.APPEND(l_return,i.desc_text);
    END LOOP;
  RETURN l_return;
END;


I'm now getting the following error:

PLS-00201: identifier 'string' must be declared

I'm sure it is how I'm trying to declair p_cursor. I have tried a couple different methods all of which through an error.

Thanks again.

Jason

Re: ORA-00932: inconsistent datatypes: expected - got CURSER [message #255834 is a reply to message #255801] Thu, 02 August 2007 00:44 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"string" is nowhere in the code you posted.

Quote:
I did get the stragg function to work, however it appears there is an issue with ordering the results within the function

Ordering? Is see no ordering in your code so it can't have any ordering problem.

Regards
Michel
Previous Topic: Dates between
Next Topic: Grouping set of data
Goto Forum:
  


Current Time: Sun Dec 11 00:25:49 CST 2016

Total time taken to generate the page: 0.09224 seconds