Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

using variable in stored procedure

pradyutOct 28 2008 — edited Oct 28 2008
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
This post has been answered by cdkumar on Oct 28 2008
Jump to Answer

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 25 2008
Added on Oct 28 2008
3 comments
1,155 views