Home » SQL & PL/SQL » SQL & PL/SQL » Need help in creating materialized view for the data extracted from LONG datatype.
icon5.gif  Need help in creating materialized view for the data extracted from LONG datatype. [message #239299] Tue, 22 May 2007 00:19 Go to next message
yugamore
Messages: 23
Registered: December 2006
Junior Member
Hi All,
I have column in a table with LONG data type. I could not read text in this column directly as it gave an error:
ORA-00932: inconsistant datatype:expected number got LONG.

I used following function to extract data from the LONG datatype then:

Create or Replace Function Response_memo_Text return varchar2
as
DECLARE
long_var LONG;
var_var VARCHAR2(32767);
cursor memo_cur is
SELECT surveyid, respondentid, responsememo
FROM response
where responsememo is not null;
memo_rec memo_cur%ROWTYPE;
BEGIN
open memo_cur;
loop
fetch memo_cur into memo_rec;
exit when memo_cur%notfound;
var_var := substr(memo_rec.responsememo,1,32767);
insert into responsmemotemp(surveyid, respondentid, responsememo_text)
values(memo_rec.surveyid, memo_rec.respondentid, var_var);
commit;
end loop;
close memo_cur;
END;

I actually need this text from LONG column for reporting purpose and can not use function there. Can I create a Materialized view to extract data from LONG column, If yes how can I do it?

[Updated on: Tue, 22 May 2007 01:53]

Report message to a moderator

Re: Need help in creating materialized view for the data extracted from LONG datatype. [message #239351 is a reply to message #239299] Tue, 22 May 2007 01:56 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I don't really understand your procedure. You declare a LONG var, but I can't see where you use it.

If responsememo is a LONG, then you could:

clob_var CLOB;
...
...
clob_var := memo_rec.responsememo;
var_var := substr(clob_var,1,32767);


Ross Leishman
Re: Need help in creating materialized view for the data extracted from LONG datatype. [message #239352 is a reply to message #239351] Tue, 22 May 2007 02:03 Go to previous messageGo to next message
yugamore
Messages: 23
Registered: December 2006
Junior Member
Hi,
Sorry about that 'long_var', it is not used anywhere.. Yes responsememo has LONG datatype, but I cannot use CLOB as you mentioned.
As I have mentioned earlier, i need to use the text from LONG column into cognos report.
The frame work model for these reports does not support LONG or CLOB data types, hence I need to extract data into varchar2.
Re: Need help in creating materialized view for the data extracted from LONG datatype. [message #239640 is a reply to message #239352] Tue, 22 May 2007 22:37 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
That's why I moved the substring your var_var, which is VARCHAR2, not CLOB.

Ross Leishman
Previous Topic: EXECUTE IMMEDIATE Statement
Next Topic: Kindly help me out !!!
Goto Forum:
  


Current Time: Sat Dec 10 03:10:15 CST 2016

Total time taken to generate the page: 0.08967 seconds