LOB Related...Please Help!!

From: Deepa Kamat <dkamat_at_questone.com>
Date: Thu, 16 Mar 2000 06:02:31 GMT
Message-ID: <WN_z4.1790$hC3.289531@dfiatx1-snr1.gtei.net>

Hi all,
I would like to know if there is
anyway to retrieve the data directly.
Basically, I wanted to know if it is possible to retrieve the data when the table is of the form
create table( a varchar(10),b number(5));

into a CLOB data type .This is because the data to be retrieved is in bulk.

I'm not very sure if I'm on the right track.So,could you please guide me on this issue.

Another problem I have is that if I execute a function in PL/SQL which performs some
computation and returns the result,is there anyway of getting bulk data through the function
as a return parameter.

I am attaching the function as :

create or replace function formula_data return number
is
retval number(10);
begin
select Sum(msval1) * sum(msval2) into retval from
(select ms1_tab.pd_id prodval,ms1_tab.mkt_id Mktval,ms1_tab.mth_id
Monthval,logisticsystem.ls_id_val lsval,ms1_tab.salesprice msval1,0 msval2 from ms1_tab,logisticsystem
where
(logisticsystem.ls_id_val = 0)

and (ms1_tab.pd_id>=0 and ms1_tab.pd_id<=225)
and (ms1_tab.mkt_id >= 0 and ms1_tab.mkt_id <= 66)
and (ms1_tab.mth_id >= to_date('JAN1990','MONYYYY') and

ms1_tab.mth_id<=to_date('DEC1994','MONYYYY')) union
select
ms2_tab.pd_id,ms2_tab.mkt_id,ms2_tab.mth_id,ms2_tab.ls_id,0,ms2_tab.salesqty msval2
from ms2_tab where
(ms2_tab.pd_id>=0 and ms2_tab.pd_id<=90)
and (ms2_tab.mkt_id >= 0 and ms2_tab.mkt_id <= 60) and (ms2_tab.mth_id >= to_date('JAN1990','MONYYYY') and ms2_tab.mth_id <=to_date('DEC1994','MONYYYY'))
and (ms2_tab.ls_id=0))
group by prodval,Mktval,Monthval,lsval; return(retval);
end;
/

ms1_tab:

 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------
------------------------
 PD_ID                                                            NUMBER(10)
 MKT_ID                                                         NUMBER(10)
 MTH_ID                                                         DATE
 SALESPRICE                                               NUMBER(10)

and ms2_tab:

 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------
---
 PD_ID                                                          NUMBER(10)
 MKT_ID                                                       NUMBER(10)
 MTH_ID                                                      DATE
 LS_ID                                                           NUMBER(10)
 SALESQTY                                                NUMBER(10)

logisticsystem:

 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------
---
 PD_ID                                                          NUMBER(10)
 MKT_ID                                                      NUMBER(10)
 MTH_ID                                                      DATE
 LS_ID                                                           NUMBER(10)
 SALESQTY                                                 NUMBER(10)

Note: The ranges specified in the above function are dynamic and will be passed as
parameters to the fuction.

Now, I want to retrieve the result of this function in chunks. I tried to retrieve the data directly by binding huge arrays but it is taking a real long time.
So,I was trying some way of speeding up the query. Can you please help me out with this too.

Thanks and regards,
Deepa Received on Thu Mar 16 2000 - 00:02:31 CST