ORA - 01445 error

[ry1633]

Hi all,

I keep getting this error in my model.  Is it in at the database level or something I can address in Framework Manager?

RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-9'.
UDA-SQL-0107 A general exception has occurred during the operation "prepare".
ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table

[bdbits]

I assume you are selecting from a view. I think you need to focus on the "key-preserved table". I googled this up and it appears to depend what is included in the view, but it also seems to say it is only a problem if trying to update through the view. Not sure why it would care if you are just selecting with a rowid.

http://docs.oracle.com/cd/E11882_01/server.112/e25494/views.htm#ADMIN11783

[ry1633]

I think a related problem is that the view in question is a Mail Attachments view that has a data blob field in (that's the email attachment column - it's a data bytes/blob) field.  How does Cognos handle those and could this be contributing to my error.

[bdbits]

Good ol' blobs. We had one system with them in my last job, and they are problematic. We were on 10.1 with I think FP3, I have no idea if this has changed in 10.2. So is there by chance a composite key in the table/view? As I recall, the rule from the Cognos side is that if you were selecting from a table/view that included blobs, you had to have one-and-only-one column as the primary key on the table/view. Also, it cannot be a computed key (like rowid), it has to be a physically-stored column.

Really limiting. Really annoying. But at least at the time, there was no getting around it.

[ry1633]

yeah that's beginning to sound like it might be my problem here.  I'm checking with my dba guys now and will report back.  Is there anything in the Cognos docs that addresses data blobs in tables/views?

[bdbits]

I don't think there is much if anything in the docs. We found out about this requirement from Cognos tech support. As far as I am aware, it has not changed and is still a requirement.

[cognostechie]

That's probably an Oracle error, not Cognos. If there are two tables involved then returning ROWID would be a problem.