Re: PL/SQL problem.
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: arul.kumar@xxxxxx, oracle-l@xxxxxxxxxxxxx, Steve Jelfs <steve@xxxxxxxxxxxxxx>
- Date: Tue, 18 Jan 2005 11:31:11 +0100
Difficulties with procedures apart, I would also make two changes :
1. where substr(execution_time,1,10) >= to_char(trunc(ld_start_date) -1/24,
'YYYYMMDDHH24')
into
execution_time like to_char(trunc(ld_start_date) -1/24,
'YYYYMMDDHH24') || '%'
in case you would have an index on execution_time
2. I would create a view on the remote database for your full subquery
(select count(*) ...) and query this view to make certain that the join
takesplace on the remote database.
Regards,
Stephane Faroult
RoughSea Ltd
http://www.roughsea.com
On Tue, 18 Jan 2005 09:52 , Steve Jelfs <steve@xxxxxxxxxxxxxx> sent:
It may help to see the procedure you've wrapped it in- or a section at
least?
Steve
arul.kumar@xxxxxx[1] wrote:
>Gurus,
>
>Sorry for posting a pl/sql problem.=20
>
>I have this following UPDATE in a flat sql file. When I try to wrap it
>up in a stored procedure reports compilation error at "+" sign saying
>that something is missing!?
>
>
>update BREEZE_HOUR_VOLUME set
>COMPLETED =3D COMPLETED +
>(select count(*) from
>cust_orders@DBAWEB[2]_BRZ2_BYBRZ01.WORLD
>where status =3D 'COMPLETE'
>and id in
>(select cust_order_id from
>cust_order_lines@DBAWEB[3]_BRZ2_BYBRZ01.WORLD
>where substr(execution_time,1,10)
>=3D to_char(trunc(ld_start_date) -1/24, 'YYYYMMDDHH24')
>)
>)
>where CREATION_HOUR =3D 00
>and CREATION_DATE =3D trunc(ld_start_date)
>and SITE =3D 'B';
>
>
>Any clues / suggestions?
>
>Have a nice day!
>
>Thanks,
>
--
//www.freelists.org/webpage/oracle-l[4]
--- Links ---
1 javascript:parent.opencompose('arul.kumar@xxxxxx','','','')
2 javascript:parent.opencompose('cust_orders@DBAWEB','','','')
3 javascript:parent.opencompose('cust_order_lines@DBAWEB','','','')
4
modules/refer.pl?redirect=http%3A%2F%2Fwww.freelists.org%2Fwebpage%2Foracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
