Home » SQL & PL/SQL » SQL & PL/SQL » Functions problem in 9i
Functions problem in 9i [message #242054] Thu, 31 May 2007 09:03 Go to next message
akkumar81
Messages: 144
Registered: February 2007
Location: india
Senior Member

Dear friends,

I face some problems in function in plsql.My version is oracle 9i.

sql> create or replace function sample
return number
is
begin
insert into emp values('arun',78,5000);
commit;
return 1;
end;
/

sql> function is created.

sql> select * from emp;

sql> No records

How can I call this function because body contains DML statement

Expecting your replies..

Arun..




Re: Functions problem in 9i [message #242056 is a reply to message #242054] Thu, 31 May 2007 09:07 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You can call it from PL/SQL.
Declare
  v_a_number number;
Begin
  v_a_number := yourfunction;
  -- now you do something with return value
End;


MHE
Re: Functions problem in 9i [message #242058 is a reply to message #242054] Thu, 31 May 2007 09:09 Go to previous messageGo to next message
akkumar81
Messages: 144
Registered: February 2007
Location: india
Senior Member

Maheer,

Please explain briefly i can't understand sorry for my side.
Re: Functions problem in 9i [message #242061 is a reply to message #242058] Thu, 31 May 2007 09:12 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Arun,
you just created the function, you didn't execute it. There are two ways to execute a function:
1. from SQL, in a SELECT. But you can't since the function contains DML itself.
2. from PL/SQL. You simply assign the return value of the function to a variable.

MHE
Re: Functions problem in 9i [message #242062 is a reply to message #242054] Thu, 31 May 2007 09:14 Go to previous messageGo to next message
akkumar81
Messages: 144
Registered: February 2007
Location: india
Senior Member

Thanks Maheer.

Re: Functions problem in 9i [message #242069 is a reply to message #242062] Thu, 31 May 2007 09:26 Go to previous message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Who is Maheer?? The person that helped you is called Maaher. (Well, ok it's actually Maarten - but you get the point)
Previous Topic: control exceptions
Next Topic: not urgent - problem writing into text file in oracle 8i
Goto Forum:
  


Current Time: Sun Dec 11 00:40:36 CST 2016

Total time taken to generate the page: 0.25034 seconds