using variable in stored procedure

pradyut

hi,
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

cdkumar

Find the correct syntax below:

create or replace procedure ins_state(state in varchar2, countr in varchar2)
as
i number;
begin
select country_id into i from country where country_name=countr;
insert into state(country_id, state_name) values(i, state);
commit;
end ins_state; /

cdkumar

Find the correct syntax below:

create or replace procedure ins_state(state in varchar2, countr in varchar2)
as
i number;
begin
select country_id into i from country where country_name=countr;
insert into state(country_id, state_name) values(i, state);
commit;
end ins_state; /

jeneesh

You are missing basics of PL/SQL. I will recommend you to learn it first.

You cannot do like

  
 l:= select...

The correct syntax is

  select ..
  into l
  ....

For the mentioned procedure you can do like..

create or replace procedure ins_state(state in varchar2, countr in varchar2)
as
begin
insert into state(country_id, state_name) 
select country_id,state from country where country_name=countr;
commit;
end ins_state;
/

pradyut

thanks ..