PL/SQL

[NitinJain]

Hello,

I am getting the shown error for the following select statement.

select createDocument ('namespaceUri', 'qualifiedName', NULL) from dual;

ORA-06571: Function APPENDCHILD does not guarantee not to update database

createDoument is a stored function with the following code:

CREATE OR REPLACE FUNCTION createDocumentType (arg_qualifiedName VARCHAR, arg_publicId VARCHAR, arg_systemId VARCHAR) RETURN NUMBER IS

DOCUMENT_TYPE_NODE_TYPE_ID CONSTANT NUMBER := 10;
newDocumentTypeId NUMBER;
BEGIN
newDocumentTypeId := createNode (NULL, DOCUMENT_TYPE_NODE_TYPE_ID);

INSERT INTO t_DocumentType ( id, name, publicId, systemId, internalSubset)
VALUES (newDocumentTypeId, arg_qualifiedName, arg_publicId, arg_systemId, NULL);

RETURN newDocumentTypeId;

END createDocumentType;


Regards.

 

[vikramkalsi]

Hi,
There are some Restrictions on PL/SQL Functions when called from SQL. Read the following excerpt -

-----------START-----------------

Stored functions in SQL offer tremendous power. As you might expect, however, power introduces the possibility of abuse and the need for responsible action (e.g., side effects). General recommendation for a function is that it should be narrowly focused on computing and returning a value.

But a recommendation is not enough when it comes to database integrity: in order to guard against nasty side effects and unpredictable behavior, the Oracle Server makes it impossible for your stored function in SQL to take any of the following actions:

Purity-level: The stored function may not modify database tables. It cannot execute an INSERT, DELETE, or UPDATE statement.

-----------END-----------------

More on "Purity Level" can be found in the Oracle PL/SQL guide. -Vikram Kalsi
----------------------------------------
We learn most when we have to invent - Piaget

 

[NitinJain]

Thankyou Vikram for the reply. Is there any other way to handle this situation as it can be done in Postgres using

select createDocument ('namespaceUri', 'qualifiedName', NULL) \g /dev/null

Regards

 

[sem]

why do you use SELECT? As I see you just need to EXECUTE.
declare
retval number;
begin
retval := createDocument ('namespaceUri', 'qualifiedName', NULL);
end;

If you need to see result, in sql*plus it may be done by declaring bind variable:

var retval number
exec :retval :=
createDocument ('namespaceUri', 'qualifiedName', NULL);
print retval


or using dbms_output:
set serverout on
exec dbms_output.put_line(createDocument ('namespaceUri', 'qualifiedName', NULL)
)

 

[vikramkalsi]

Yes Nitin,
there is a way - by using autonomous transactions. Consider procedure t1p and function t1f -

create or replace procedure t1p as
PRAGMA AUTONOMOUS_TRANSACTION;
begin
insert into temp_table values (51,'51');
commit;
end;

create or replace function t1f return varchar2 as
begin
--Call procedure defined as Autonomous from the function
t1p;
return 'DONE';
end;

Now,
SQL> select t1f from dual;

T1F
-------------------------------------------------
DONE

But beware that the procedure is autonomous i.e. the changes will be committed even if the function t1f issues a rollback.

Hope this helps. -Vikram Kalsi
----------------------------------------
We learn most when we have to invent - Piaget

 

[NitinJain]

Thanks everyone for the prompt replys.

Regards

Nitin