Re: update with subselect

One fairly standard trick for handling SQL that is beyond the ken of the pl/sql parser is to put it into quotes and use

    execute immediate ' ... ';

It looks as if it would work in this case.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

Tom wrote in message ...


>Hi everybody,

>

>I've an update-stmt that works fine in sql-plus, but if i try to

>compile it inside an procedure it fails with the following error:

>

>PLS-00103: Encountered the symbol "SELECT" when expecting one of

>the following:

>( - + mod not null others <an identifier>

><a double-quoted delimited-identifier> <a bind variable> avg

>count current exists max min prior sql stddev sum variance

>execute forall time timestamp interval date

><a string literal with character set specification>

><a number> <a single-quoted SQL string>

>

>update-stmt:

>

>update

>   rep_account rep_account1

>set

>   rep_account1.percentage =

>     rep_account1.debit /

>        ( select distinct

>            rep_account2.debit

>         from

>            rep_account rep_account2

>         where

>                rep_account2.rcv_internalid =

>rep_account1.rcv_internalid

>            and rep_account2.pos_id         =

>pkg_gloconst.pos_accountbalance )

>      * 100

>where

>       rep_account1.rcv_internalid   = ext_rcv_row.internalid

>   and rep_account1.pos_id          in ( pkg_gloconst.pos_ccy_balance,

>

>pkg_gloconst.pos_accountbalance_all )

>;

>

>

>any ideas? thnx in advance

>Tom