Home » SQL & PL/SQL » SQL & PL/SQL » Insert statements from Stored Function
Insert statements from Stored Function [message #35897] Mon, 22 October 2001 22:36 Go to next message
Sushil Chopra
Messages: 3
Registered: October 2001
Junior Member
I am trying to execute insert statement from a stored function. It gives me error ora 06571 - function does not gaurantee not to update database.

how is it possible to execute insert/update from stored function?

----------------------------------------------------------------------
Re: Insert statements from Stored Function [message #35901 is a reply to message #35897] Tue, 23 October 2001 01:24 Go to previous messageGo to next message
Milan Kumar Barui
Messages: 16
Registered: October 2001
Junior Member
Hi Sushil,
there are two things to consider here :
1) Are you running this function from a sql statement? if yes, then don't run in that way , because it contains update statement.
make a file containing:
declare
b number;
begin
b := func(parameter);
return b;
end;
You must return something from function, if you don't then write procedure , not function.

2) Is that function a part of package? if yes,
then create the function again with the required pragma , include the 'Write No Database State' (WNDS) argument in the argument list of the pragma.

Give me the feedback.
Milan
Oracle DBA

----------------------------------------------------------------------
Re: Insert statements from Stored Function [message #35904 is a reply to message #35901] Tue, 23 October 2001 02:06 Go to previous messageGo to next message
Sushil Chopra
Messages: 3
Registered: October 2001
Junior Member
Thanks for the reply, Milan.

Actually I need to call this function as part of SQL i.e. Select function(a)from dual; I need to implement it in Java program and thus it is easy to be do it if it is a stored function rather than procedure.

Also when i tried to put it as part of package and included the pragma you mentioned then it execution time it gave me error that declaration is different from the functionality.

----------------------------------------------------------------------
Re: Insert statements from Stored Function [message #35906 is a reply to message #35901] Tue, 23 October 2001 02:30 Go to previous message
Milan Kumar Barui
Messages: 16
Registered: October 2001
Junior Member
Sushil,
You can not call a function from select statement if the function contains any update or insert statement. If you want to update data from java program, then it's better to write database procedure or even function for this and call from java program using callablestatement.
Thnaks
Milan

----------------------------------------------------------------------
Previous Topic: SQLPLUS > inserting multiple lines
Next Topic: wrong number or types of arguments in call to 'PUT_LINE'
Goto Forum:
  


Current Time: Sat Apr 04 17:41:00 CDT 2020