query running at sql but not in plsql

[satishpc]

hi,

assuming i have 2 tables temp1 and temp2 both having fields
t1 & t2

if i write a query at the sql prompt

update temp1 a set t2 = t1*( select t2 from temp2 b where b.t1 = a.t1);

it works properly but when i put it in a procedure

create or replace procedure test as
begin
update temp1 a set t2 = t1*( select t2 from temp2 b where b.t1 = a.t1);
end;

it gives the foll error.
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/30 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>


can anyone please suggest a solution

satish

[Highlander]

You can not write DML statment using sql in pl/sql, you have to use dynamic sql.

See Oracle doc (execute immediate, dmbs_sql)

Good luck.

[Highlander]

Sorry I missled you by mistake, try this

create or replace procedure test as
begin
update temp1 a set t2 = ( select t2 from temp2 b where b.t1 = a.t1);
end;


This will update temp1.t2 with temp2.t2 where t1 = t2.

[tamilselvan]

Your procedure should be
create or replace procedure test as
begin
update temp1 a set a.t2 = ( select a.t1*b.t2
from temp2 b
where b.t1 = a.t1)
where a.t1 in ( Select c.t1 from temp2 c ) ;
commit;
end;
/

Note: If you don't add WHERE clause for the updating table, all the rows will be updated. In ANSI SQL, the column being updated is set to NULL before the value is written on to that column. Hence, restricting the rows is must.

[Edited by tamilselvan on 08-13-2001 at 01:38 PM]

[satishpc]

hi,

it worked. thanks a lot. i have another query.

can i use olap functions in procedures. when i create a cursor

create cursor cr_sch_dt (tdate date) is
select cmast.sch_code, brok_code, cmast.isc_code, cust_type, count(1) no_cust from
(Select cust_code, sch_code, brok_code, isc_code, trxn_date,
rank() over(partition by cust_code, sch_code order by trxn_date desc) prank
from test.eis_trans where trxn_type = 'P' and trxn_date <= tdate) rdet,
eis_cust_mast cmast
where prank = 1 and rdet.cust_code = cmast.cust_code
group by cmast.sch_code, brok_code, cmast.isc_code, cust_type;

it gives me the foll error
LINE/COL ERROR
-------- -----------------------------------------------------------------
182/15 PLS-00103: Encountered the symbol "(" when expecting one of the
following:
, from

which points out to the "(" of "Over". how can i write this statement ?

satish

[jmodic]

No, AFAIK you can't use analytical functions in PL/SQL in 8i. I guess you can use them in 9i, but I've not try it yet...

[chrisrlong]

You have to use dynamic SQL to utilize Analytical functions (or CASE statements) in PL/SQL in 8i. Thank you, Oracle ;)

- Chris