Home » SQL & PL/SQL » SQL & PL/SQL » Problems creating an object VIEW that returns multiple rows from a BLOB datatype
Problems creating an object VIEW that returns multiple rows from a BLOB datatype [message #235435] Sat, 05 May 2007 14:49 Go to next message
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 #235680 is a reply to message #235435] Mon, 07 May 2007 11:54 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Try this...
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:23325542868374
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 Go to previous message
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.



Previous Topic: PLS-00103: Encountered the symbol "CREATE"
Next Topic: PLS-00306: wrong number or types of arguments in call to
Goto Forum:
  


Current Time: Thu Dec 08 16:23:37 CST 2016

Total time taken to generate the page: 0.06112 seconds