Forum Stats

  • 3,781,426 Users
  • 2,254,516 Discussions
  • 7,879,688 Comments

Discussions

using variable in stored procedure

pradyut
pradyut Member Posts: 3
edited Oct 28, 2008 8:06AM in SQL & PL/SQL
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

Best Answer

  • cdkumar
    cdkumar Member Posts: 131 Blue Ribbon
    Accepted Answer
    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; /

Answers

  • cdkumar
    cdkumar Member Posts: 131 Blue Ribbon
    Accepted Answer
    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
    jeneesh Member Posts: 7,168
    edited Oct 28, 2008 4:55AM
    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;
    /
This discussion has been closed.