RE: PL/SQL problem.

  • From: <arul.kumar@xxxxxx>
  • To: <steve@xxxxxxxxxxxxxx>
  • Date: Tue, 18 Jan 2005 10:36:09 -0000

Sorry!. This is the procedure:
------------------------------------------------------------------------
----------

CREATE OR REPLACE Procedure Update_BY_Breeze_Orders (Start_Date Varchar,
End_Date Varchar)
AS

ld_start_date date              :=3D
Trunc(To_Date(Start_Date,'DD-MON-YYYY'));
ld_end_date date                        :=3D
Trunc(To_Date(End_Date,'DD-MON-YYYY'));

Begin

UPDATE <something>

commit;

Execute Immediate 'UPDATE breeze_hour_volume ' ||
'   SET completed =3D completed + ( SELECT COUNT(*) FROM
cust_orders@xxxxxxxxxxxxxxxxxxxxxxxxx ' ||=20
'                                                        WHERE status =3D
''COMPLETE''                                                     ' ||
'                                                AND ID IN (
' ||
'                                                       SELECT
cust_order_id
' ||
'
FROM cust_order_lines@xxxxxxxxxxxxxxxxxxxxxxxxx          ' ||
'                                                        WHERE SUBSTR
(execution_time, 1, 10) =3DTO_CHAR (     ' ||ld_start_date||' - (1 /
24),''YYYYMMDDHH24'')))'||
' WHERE creation_hour =3D TO_NUMBER (TO_CHAR (
' ||ld_start_date||', ''HH24''))' ||
'   AND creation_date =3D TRUNC (ld_start_date)
' ||
'   AND site =3D ''B''; =20

Exception

When Others Then
DBMS_Output.Put_Line('While
processing'||To_Char(ld_start_date,'dd-mon-yyyy hh24')||': '||SQLERRM);
Raise;=09

End;
/

---------------
Error Message:
---------------

PLS-00103: Encountered the symbol "UPDATE" when expecting one of the
following:

   . ( * @ % & =3D - + ; < / > at in mod not rem return returning
   <an exponent (**)> <> or !=3D or ~=3D >=3D <=3D <> and or li

------------------------------------------------------------------------
----------

Alternatively,

Without Execute Immediate..

...some update..
commit;

update BREEZE_HOUR_VOLUME set
           COMPLETED =3D          COMPLETED +
                                (select count(*) from
cust_orders@xxxxxxxxxxxxxxxxxxxxxxxxx
                                 where status =3D 'COMPLETE'
                                 and id in
                                    (select cust_order_id from
cust_order_lines@xxxxxxxxxxxxxxxxxxxxxxxxx
                                       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';

...something else!...

While compiling reports (pointing out at the SELECT after '+' sign):

PLS-00103: Encountered the symbol "SELECT" when expecting one of the
following:=20

   ( - + mod not null others <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> avg
   count c


Thanks!


-----Original Message-----
From: Steve Jelfs [mailto:steve@xxxxxxxxxxxxxx]=20
Sent: 18 January 2005 09:53
To: Kumar,A,Arul,XGF3C C
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: PL/SQL problem.


It may help to see the procedure you've wrapped it in- or a section at=20
least?

Steve


arul.kumar@xxxxxx wrote:

>Gurus,
>
>Sorry for posting a pl/sql problem.=3D20
>
>I have this following UPDATE in a flat sql file. When I try to wrap it=20
>up in a stored procedure reports compilation error at "+" sign saying=20
>that something is missing!?
>
>
>update BREEZE_HOUR_VOLUME set
>           COMPLETED =3D3D          COMPLETED +
>                                (select count(*) from=20
>cust_orders@xxxxxxxxxxxxxxxxxxxxxxxxx
>                                 where status =3D3D 'COMPLETE'
>                                 and id in
>                                    (select cust_order_id from=20
>cust_order_lines@xxxxxxxxxxxxxxxxxxxxxxxxx
>                                       where=20
>substr(execution_time,1,10) =3D3D to_char(trunc(ld_start_date) -1/24,
'YYYYMMDDHH24')
>                                    )
>                                )
>where CREATION_HOUR =3D3D 00
>  and CREATION_DATE =3D3D trunc(ld_start_date)
>  and SITE          =3D3D 'B';
>
>
>Any clues / suggestions?
>
>Have a nice day!
>
>Thanks,
>

--
//www.freelists.org/webpage/oracle-l

Other related posts: