using variable in stored procedure
pradyutOct 28 2008 — edited Oct 28 2008hi,
i have a select statement that returns a numeric value.
i need to store the value in a variable and pass it to a insert statement...
for this i have created a stored procedure that accepts two variables... one for the select statement and one for the insert statement...
the stored procedure: -
---------------------------------------------------------------------------------------------------------------------------
create or replace procedure ins_state(state in varchar2, countr in varchar2)
as
var i numeric;
begin
i := select country_id from country where country_name=countr;
insert into state(country_id, state_name) values(i, state);
commit;
end ins_state; /
---------------------------------------------------------------------------------------------------------------------------
the problem is i m getting the error : -
---------------------------------------------------------------------------------------------------------------------------
ERROR at line 6: PLS-00103: Encountered the symbol "INSERT"
4. begin
5. i := select country_id from country where country_name=countr;
6. insert into state(country_id, state_name) values(i, state);
7. commit;
8. end ins_state; /
---------------------------------------------------------------------------------------------------------------------------
Any help...
Thanks
Pradyut, India