I posted this in the PL/SQL forum and it was suggested that perhaps I should post here.
We've upgraded from 11i to R12. We are currently testing and updating our CEMLIS to be compatible with R12. One program that no longer works is one that adds a task to a project in PA. The parameter p_msg_data indicates missing project reference or project id.
The call, with debug is:
dbms_output.put_line('p_project_number' || p_project_number);
dbms_output.put_line('p_project_id' || p_project_id);
IF l_task_id IS NULL THEN
oe_debug_pub.ADD('pm:' || l_task_rec.pm_task_reference, 1);
oe_debug_pub.ADD('ppm:' || l_task_rec.pm_parent_task_reference
(p_api_version_number => g_api_version_number
,p_commit => g_false
,p_init_msg_list => g_false
,p_msg_count => l_msg_count
,p_msg_data => l_msg_data
,p_return_status => l_return_status
,p_pm_product_code => g_pm_product_code
,p_pm_project_reference => p_project_number
,p_pa_project_id => p_project_id
,p_pm_task_reference => l_task_rec.pm_task_reference
,p_pa_task_number => l_task_rec.pa_task_number
,p_task_name => l_task_rec.task_name
,p_task_description => l_task_rec.task_description
,p_task_start_date => l_task_rec.task_start_date
,p_pm_parent_task_reference => l_task_rec.pm_parent_task_reference
,p_billable_flag => l_task_rec.billable_flag
,p_chargeable_flag => l_task_rec.chargeable_flag
,p_attribute3 => l_task_rec.attribute3
,p_attribute4 => l_task_rec.attribute4
,p_attribute5 => l_task_rec.attribute5
,p_attribute6 => l_task_rec.attribute6
,p_attribute7 => l_task_rec.attribute7
,p_attribute8 => l_task_rec.attribute8
,p_attribute9 => l_task_rec.attribute9
,p_attribute10 => l_task_rec.attribute10
,p_allow_cross_charge_flag => l_task_rec.allow_cross_charge_flag
,p_cc_process_nl_flag => l_task_rec.cc_process_nl_flag
,p_nl_tp_schedule_id => l_task_rec.nl_tp_schedule_id
,p_pa_project_id_out => p_project_id
,p_pa_project_number_out => p_project_number
,p_task_id => l_task_id
The dbms_output indicates that the variables contain the correct values:
So, I am expecting add_task to have the correct values for p_pm_project_reference and p_pa_project_id.
I added some debug to the begining of the add_task procedure to see what value it was seeing:
dbms_output.put_line('add_task p_pm_project_reference '||p_pm_project_reference);
The dbms_output shows that inside the procedure, the values are null.
I don't understand why the values do not get passed. This still works in our 11i production instance.
How can it be that the parameters are null in the add_task procedure when values were passed in the call and this is verified by the dbms_output.
Well,may be you should check the standard API for release R12.
There is a UserGuide:Oracle® Projects APIs, Client Extensions, and Open
Part No. B25624-02
I used API pa_project_pub.create_project before,and it's no problem.
I'll test the API add_tasks and let you know asap.
To update this thread, we found that the issue has appeared because the NOCOPY hint has been added to many parameters and we were using the same variable for p_pa_project_id and p_pa_project_id_out.
In 11i, where the NOCOPY hint is not used, thre is no problem.
In R12, where the NOCOPY hint is used, there is a problem due to the way the NOCOPY hint is processed. When the API is called and the same variable is used for p_pa_project_id and p_pa_project_id_out, the memory location of the variable is flushed. Since the same variable is passed, the memory location is the same. Thus, the procedure does not see the value supplied in the variable before calling the API.
The solution for us was to use 2 different variables for the IN and OUT parameters. With this change, the value provided for p_pa_project_id is not wiped out and the task is added.
This issue could present itself for any call to a function or procedure where the same variable is used as a parameter in the call, if the NOCOPY hint is used on one or more parameters that the variable is assigned to. This is not specific to EBSO, but applies to all Oracle PL/SQL. I was able to reproduce the situation on an earlier DB version using the NOCOPY hint