LOB Related...Please Help!!
Date: Thu, 16 Mar 2000 06:03:02 GMT
Message-ID: <qO_z4.1791$hC3.289689_at_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') andms1_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 - 07:03:02 CET