Home » SQL & PL/SQL » SQL & PL/SQL » calling function inside a procedure
calling function inside a procedure [message #191412] Wed, 06 September 2006 04:39 Go to next message
selvakumar_82
Messages: 138
Registered: October 2005
Location: chennai
Senior Member
Hi friends,


i am using function inside a procedure

but it showing error:any kinldy check the code and tell me.

To Create a Funtion :
create or replace function fn_return_itemcode (v_item_name varchar2)
return varchar2
is
v_item_code item_master.item_code%type;
begin
select item_code into v_item_code from item_master where item_name = v_item_name;
return (v_item_code);
end fn_return_itemcode;

I am using this function in my procedure :

Proc Code is:
create or replace procedure sales_insert(v_item_name varchar2,i_qty number,i_rate number)
is
v_item_code varchar2(10);
cnt_item_code number;
begin
v_item_code :='fn_insert_itemcode(v_item_name)';
select count(*) into cnt_item_code from stock where item_code = v_item_code and qty > i_qty;
if ( cnt_item_code > 0 ) then
insert into sales_items values (v_item_code,i_qty,i_rate);
update stock set qty = qty-i_qty where item_code = v_item_code;
end if;
end sales_insert;


In Function calling i used like this also:
select fn_insert_itemcode(v_item_name) into v_item_code from dual;

and

v_item_code :='select fn_insert_itemcode(v_item_name) from dual';

still it s showing error,

kinldy tell me the solution for this problem.

thanx and Regards
Selva.R
Re: calling function inside a procedure [message #191433 is a reply to message #191412] Wed, 06 September 2006 06:23 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
What CRUCIAL piece of information have you left out. I mean CRUCIAL.
Re: calling function inside a procedure [message #191442 is a reply to message #191412] Wed, 06 September 2006 06:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why the quotes around the function-call? That makes it a literal string
Re: calling function inside a procedure [message #191479 is a reply to message #191433] Wed, 06 September 2006 08:59 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
JSI2001 wrote on Wed, 06 September 2006 07:23

What CRUCIAL piece of information have you left out. I mean CRUCIAL.

Oooh-Oooh, Mr. Kotter. The error message?
Re: calling function inside a procedure [message #191484 is a reply to message #191479] Wed, 06 September 2006 09:23 Go to previous message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Yes young Division, that is correct. Go to the head of the class.

Laughing
Previous Topic: Use of bulk bind
Next Topic: error while using sqlldr
Goto Forum:
  


Current Time: Fri Dec 02 19:02:09 CST 2016

Total time taken to generate the page: 0.34926 seconds