| Problems creating an object VIEW that returns multiple rows from a BLOB datatype [message #235435] |
Sat, 05 May 2007 14:49  |
ebliebe
Messages: 2
Registered: October 2006
Location: Oakland, CA
|
Junior Member |
|
|
I'm trying to create an object VIEW on the following base table:
CREATE TABLE MSG_TEXT_COMP
(
TRN_DATE DATE NOT NULL,
TRN_NUMBER NUMBER(8) NOT NULL,
TRN_TIMESTAMP NUMBER(23) NOT NULL,
TEXT_INFO BLOB DEFAULT empty_blob() NOT NULL
)
TEXT_INFO contains a compressed JAVA class that in turn contains multiple lines of text. The intent of the VIEW is to return those multiple lines. The view that I have created produces the following error: "ORA-01427: single-row subquery returns more than one row". I'm seeking suggestions as to how to create a view that overcomes this error.
Here's the background. A line of text has the following object definition:
CREATE OR REPLACE
TYPE MSG_TEXT_REC IS OBJECT
(
TRN_DATE DATE,
TRN_NUMBER NUMBER(8),
TRN_TIMESTAMP NUMBER(23),
TEXT_TYPE VARCHAR2(1 BYTE),
SEQUENCE_NO NUMBER(10),
MESSAGE_TEXT VARCHAR2(80 BYTE)
);
A simple collection of MSG_TEXT_REC would be:
CREATE OR REPLACE
TYPE MSG_TEXT$SET IS TABLE OF MSG_TEXT_REC;
A Java stored procedure has been developed to extract the data. The FUNCTION
wrapper to the stored procedure looks like:
CREATE OR REPLACE FUNCTION GET_TEXT$RECORD (
trn_date DATE,
trn_number NUMBER
)
RETURN MSG_TEXT$SET
AS LANGUAGE JAVA
NAME 'jspDecompress.GetMsgText.getTEXT_RECArray(oracle.sql.DATE, oracle.sql.NUMBER)
return oracle.sql.ARRAY';
The following SQL will return the multiple lines of text:
select * from table (select GET_TEXT$RECORD('7-JUN-07', 100) from dual);
However, this type of SQL will not work through a DBLink. Hence the reason to create a VIEW or some other means.
My attempt to create a view led me to:
CREATE OR REPLACE VIEW GET_TEXT_COMP_VW
OF MSG_TEXT_REC
WITH OBJECT IDENTIFIER (trn_date) AS
select * from table ( select wtx.GET_TEXT$RECORD(TRN_DATE, TRN_NUMBER)
from MSG_TEXT_COMP )
However, when executing the following SQL this error occured:
select * from GET_TEXT_COMP_VW;
ORA-01427: single-row subquery returns more than one row
Finally, if a VIEW will not work, then I would be interested in suggestions for any other type of SQL construction that will overcome the DBLink limitation.
|
|
|
|
|
|
| Re: Problems creating an object VIEW that returns multiple rows from a BLOB datatype [message #236771 is a reply to message #235680] |
Thu, 10 May 2007 23:49  |
ebliebe
Messages: 2
Registered: October 2006
Location: Oakland, CA
|
Junior Member |
|
|
Thanks for the reference to AskTom. I looked at his example and also reviewed Functional tables and Pipelining. I implemented a simple functional table and tried to put a VIEW wrapper around it and it failed. I got the same "ORA-01427: single-row subquery returns more than one row" error.
UsingTom's emp_etl function, found at: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:19481671347143 as a guide, I created the following:
CREATE OR REPLACE GET_TEXT_INFO (p_cursor in sys_refcursor)
RETURN MSG_TEXT_TABLE
PIPELINED
as
l_rec MESSAGE_TEXT_T%rowtype;
begin
loop
fetch p_cursor into l_rec;
pipe row (
MSG_TEXT_REC (l_rec.TRN_DATE, l_rec.TRN_NUMBER,
<reference all the other fields....> )
);
end loop;
return;
end;
Then I put together the following SQL to test it:
select * from table (
get_text_info (
cursor (
select * from table (
select get_text$record(<a date>, <a number>) from dual
))));
And the SQL worked. It worked just like:
select * from table (select get_text$record(<a date>, <a number>) from dual);
I appeared to me that all I created was functional table wrapped around another functional table. A function that called a JSP. And since that function returned a collection, it too had to be reference by "select * from table".
So, when I tried to create a VIEW of the table with the BLOB, as follows:
create or replace view get_msg_comp_vw
of MSG_TEXT_REC
with object identifier (trn_date, trn_number)
as
select * from table (
get_text_info (
cursor (
select * from table (
select get_text$record(trn_date, trn_number) from msg_text_comp
))))
/
and executed the following SQL:
select * from get_msg_comp_wv;
I got the same ORA-01427 error. Clearly I'm missing something basic about constructing a VIEW.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
