Morning Aces,

I was wondering if anybody could help. We are getting the error below on our DEV environment when we run the API HR_EMPLOYEE_API.CREATE_EMPLOYEE with P_VALIDATE set to TRUE. This does not produce the same error on production and if we run it with P_VALIDATE set to FALSE the API completes successful. There are no savepoints in the calling code just the API. Thanks in advance.

ORA-01086: savepoint 'CREATE_EMPLOYEE' never established
ORA-06512: at "APPS.HR_EMPLOYEE_API", line 989
ORA-06510: PL/SQL: unhandled user-defined exception

RDBMS : 9.2.0.8.0
Oracle Applications : 11.5.10.2

Hi Mr whi5tler, and a warm welcome to the forum.

Can I venture to ask what version of the api package you have in each case?

to find this (and one assumes you have apps access to the relevant instances - or know of a tame house-trained DBA who does) you need to do:

SELECT type, text
FROM user_source
WHERE name = 'HR_EMPLOYEE_API'
--AND type = 'PACKAGE BODY'
AND text like '%$Header%';

It looks like you have some subtle version differences across the two instances.

I have seen this in 'older' versions of the APIs where the bit of code that sets a savepoint gets bypassed for some reason, but at the point where the api decides it's in 'validate only' mode, it forces an exception. Within the exception handler, it does a 'rollback to <savepoint>' - which fails because the savepoint was not set in the first place. I must confess though I am surprised to see this surface again.

Anyway, hope this helps.

Hi Clive,

Not sure you remember me but I'm Stacey the Welsh guy who used to sit with you and Bob Braund on the JPA project in Gosport.

Thanks for your help, here are the details you requested:

From DEV environment:
PACKAGE "/* $Header: peempapi.pkh 115.30 2006/03/03 05:38:37 lsilveir noship $ */
PACKAGE BODY "/* $Header: peempapi.pkb 115.63 2007/11/05 14:51:38 sidsaxen ship $ */

From PROD environment:
PACKAGE "/* $Header: peempapi.pkh 115.30 2006/03/03 05:38:37 lsilveir noship $ */
PACKAGE BODY "/* $Header: peempapi.pkb 115.63 2007/11/05 14:51:38 sidsaxen ship $ */

Both are the same Any other ideas?

Thanks again for your help.

The code in error:

exception
when hr_api.validate_enabled then
--
-- As the Validate_Enabled exception has been raised
-- we must rollback to the savepoint
--
ROLLBACK TO create_employee;

Oh hello Stacey, how's it going?

OK, that's blown my first theory right out of the water... so...

Is it the same data that's being loaded to each instance? I am wondering if there is some minor difference therein which might cause the code to branch slightly differently, and perhaps miss the 'set savepoint' bit in one case and not t'other. Out of interest, can you identify the relevant lines of code in the create_employee api whereby it decides whether or not to set the savepoint in the first place? As an aside, any particular reason you are using create_employee, and not create_gb_employee? Long shot though it is, it might be worth having a crack with that api to see if the problem goes away.

One other trick you could pull would be to put a pipe trace on the code that's calling the api (on the instance that's giving the problem of course), and then watch the story unfold from another session. If you do that, make sure you restrict it to processing one or two rows!

The employees are in Ireland, Clive!

Yeah all good thanks Clive, how are you?

As Geoff said this is for Ireland. I will see if we can do what you have said.

Do you think this might be something to do with space issues?

Code below for the savepoint issue:

begin
if g_debug then
l_proc := g_package||'create_employee';
hr_utility.set_location('Entering:'|| l_proc, 10);
end if;
--
-- Issue a savepoint.
--
savepoint create_employee;
--
--
-- Truncate the time portion from all date parameters
-- which are passed in.
--
l_hire_date := trunc(p_hire_date);
l_date_employee_data_verified := trunc(p_date_employee_data_verified);
l_date_of_birth := trunc(p_date_of_birth);
l_date_of_death := trunc(p_date_of_death);
l_receipt_of_death_cert_date := trunc(p_receipt_of_death_cert_date);
l_dpdnt_adoption_date := trunc(p_dpdnt_adoption_date);
l_original_date_of_hire := trunc(p_original_date_of_hire);
l_adjusted_svc_date := trunc(p_adjusted_svc_date);
--
begin
--
-- Start of API User Hook for the before hook of create_employee
--
hr_employee_bk1.create_employee_b
(p_hire_date => l_hire_date

I can't for the life of me think why a space issue would cause that specific problem, but it's always worth checking out. I have had some unpleasant experiences in the past from trying to initialise balances when there were space problems on some indexes.

It's not immediately obvious from the code that something bad can happen. I have an R12 instance to hand, and the API there looks fairly well behaved. However, what I can't vouch for is what if anything is happening inside your user hooks? Do you have them enabled, and if so is there anything like a rollback to the same savepoint going on in there, or a commit, or a rollback even.

If it's not that, then the only course of action open to you it to set up a trace session as mentioned above.

We're running a trace today. Hopefully this will help.

Thanks for your time Clive.

That trace is taking a heck of a time to run!

Seriously, did you get to find out what was happening in the procedure?