Home » SQL & PL/SQL » SQL & PL/SQL » declaring variables in a script
declaring variables in a script [message #194074] Wed, 20 September 2006 13:32 Go to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
I am new to sql scripts
I would like to write an sql script

I want to insert into a table and to update some tables
here is the script
begin
v_sys_app_id NUMBER :=57770 ;

insert into fg_temp_award (select * from fg_award where agreement_no='EMW-2002-FG-15245');

update fg_temp_award set (sys_app_id,sys_org_id ,agreement_no) =
(select 57770,sys_org_id , app_num from fg_application where sys_app_id=57770) where agreement_no='EMW-2002-FG-15245';

update fg_temp_award set sys_award_id = SEQ_SYS_AWARD_ID.nextval;

commit;
end;
the script works fine without line after begin
Please tell me how to decalre variables in a script.
Re: declaring variables in a script [message #194078 is a reply to message #194074] Wed, 20 September 2006 13:46 Go to previous messageGo to next message
rigatonip
Messages: 50
Registered: December 2005
Member
You need to declare the variable before the begin statement.

declare

  v_sys_app_id NUMBER :=57770 ;

begin
insert into fg_temp_award (select * from fg_award where agreement_no='EMW-2002-FG-15245');

update fg_temp_award set (sys_app_id,sys_org_id ,agreement_no) =
(select 57770,sys_org_id , app_num from fg_application where sys_app_id=57770) where agreement_no='EMW-2002-FG-15245';

update fg_temp_award set sys_award_id = SEQ_SYS_AWARD_ID.nextval; 

commit;
end;

Re: declaring variables in a script [message #194131 is a reply to message #194074] Wed, 20 September 2006 22:34 Go to previous message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
also try reading this

http://www-db.stanford.edu/~ullman/fcdb/oracle/or-plsql.html
Previous Topic: how to print values with scripts
Next Topic: rounding to decimal places
Goto Forum:
  


Current Time: Sat Dec 10 03:03:31 CST 2016

Total time taken to generate the page: 0.14174 seconds