It is because, you mentioned rollback in case of an exception, but not when there is a successful call. Autonomous transaction means, it creates a separate session for itself when called and the basic purpose it was introduced was to achieve certain tasks that you could not do in a transaction, for eg. logging. Have a commit or rollback before the return statement should solve your issue.
That is a TERRIBLE thing to want to do for many reasons.
1. use of WHEN OTHERS - that completely hides the real problem from everyone. No one will ever know just what went wrong.
2. you are using PL/SQL to do a job that SQL can do
3. you are HARD CODING a query into the function. That eliminates scalability and makes maintenance more difficult.
4. that is NOT a proper use for autonomous transactions.
5. you are using a SELECT query to alter the database. That hides the true purpose of the function and makes it easy to misuse the function.
Abandon that idea and either do the job using pure SQL or use a normal procedure/function to perform the transaction.
Your exception is raised because an autonomous transaction needs to perform either a COMMIT or a ROLLBACK before it returns to the caller.
This works for me:
CREATE OR REPLACE function SCOTT.fn_clob(p_empno number)
return number is
insert into emp_copy (empno) values (p_empno);
commit; -- need a COMMIT or ROLLBACK before the function returns
when others then