Home » SQL & PL/SQL » SQL & PL/SQL » inconsistent datatypes: expected - got CLOB
inconsistent datatypes: expected - got CLOB [message #256763] Mon, 06 August 2007 09:43 Go to next message
jayz240z
Messages: 11
Registered: August 2007
Junior Member
I'm a bit lost on this one. Individually, each statement within the Create View below will work. However if I try to put both on them in with the UNION I get this message " inconsistent datatypes: expected - got CLOB ".

I'm going to try and provide as much information as to what I'm trying to accomplish as possible without making it too long. Each schema (Palomar_live, and Hawkeye) have their own function created called concatenate_list. concatenate_list takes a record id queries out related varchar2 text from another table then returns the text concatenated as a CLOB. Here is the 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;


Like I said I can create the view with just one of the queries in it without any problems. Is there a problem using CLOBS in this manor?

Here is the script to create the view that breaks.

create view curri_arc.curricusearch_programs
AS
(
select  p.program_title, 
        p.programs_id as p_id, 
        palomar_live.concatenate_list(p.programs_id) as program_desc,
        'Palomar' as school, 
        'http://www.curricunet.com/palomar/reports/program_report.cfm?programs_id='||p.programs_id as url_report,
        'Palomar_live' as datasource,
        'CA' as state,
        at.award_type_title as award_title,
        s.subject_code as subject_code
from    palomar_live.programs p,
        palomar_live.award_types at,
        palomar_live.subjects s
where   p.award_types_id = at.award_types_id(+)
AND     p.program_status_id IN (1,8)
AND     p.subjects_id = s.subjects_id
UNION
select  p.program_title, 
        p.programs_id as p_id, 
        hawkeye.concatenate_list(p.programs_id) as program_desc,
        'Hawkeye' as school, 
        'http://www.curricunet.com/hawkeye/reports/program_report.cfm?programs_id='||p.programs_id as url_report,
        'hawkeye' as datasource,
        'IA' as state,
        at.award_type_title as award_title,
        s.department_title as subject_code
from    hawkeye.programs p,
        hawkeye.award_types at,
        hawkeye.departments s
where   p.award_types_id = at.award_types_id(+)
AND     p.program_status_id IN (1)
AND     p.departments_id = s.departments_id
AND     trim(p.program_title) != 'test'

)


The Curri_arch schema has permission to create a views and permissions for both the concatenate_list functions. I'm using an oracle 10g db.

Thank you for any help on this one.

Jason
Re: inconsistent datatypes: expected - got CLOB [message #256764 is a reply to message #256763] Mon, 06 August 2007 09:47 Go to previous messageGo to next message
jayz240z
Messages: 11
Registered: August 2007
Junior Member
Ok,

It appears I have figured it out. I had to use a UNION ALL verses UNION.

Sometimes it's the simple things that get us.

Jason
Re: inconsistent datatypes: expected - got CLOB [message #256766 is a reply to message #256764] Mon, 06 August 2007 10:01 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The reason is that UNION implies removal of duplicates and you can't direclty do it on LOB.
UNION ALL keeps duplicates.

Regards
Michel
Previous Topic: sql query
Next Topic: multiple optimizer hints
Goto Forum:
  


Current Time: Sat Dec 10 07:12:03 CST 2016

Total time taken to generate the page: 0.15858 seconds