Home » SQL & PL/SQL » SQL & PL/SQL » 'PLS-00363: expression..' error when executing a stored procedure with in out parameters (Oracle Financials 11i, Sun Solaris platform, ORACLE RDBMS : 10.1)
| 'PLS-00363: expression..' error when executing a stored procedure with in out parameters [message #386011] |
Thu, 12 February 2009 04:03  |
MrDuke
Messages: 41
Registered: July 2007
Location: Coventry, UK
|
Member |
|
|
Hello,
I'm trying to run a PLSQL script containing an ORACLE API but its failing on compilation with the message:
'PLS-00363: expression '<expression>' cannot be used as an assignment target'.
As far as I understand its connected to my IN-OUT parameters but I can't figure out which (I must admit I'm still hiking up a steep learning curve here and I've cut and pasted someone elses example and modified it).
I would be most grateful if one of you pro's could read through my code and advise..
SET serveroutput ON SIZE 1000000 FORMAT WRAPPED
SET verify OFF
SET feedback OFF
DECLARE
error_msg varchar2(2000) := '';
l_status varchar2(10) := 'True';
l_validate_cnt number;
l_ass_count number;
l_business_group_id number := 0;
l_validate BOOLEAN DEFAULT FALSE;
l_city varchar2(30);
l_mode varchar2(20) := 'UPDATE';
l_sup_join_date date;
l_organization_id hr_organization_units.organization_id%type;
l_person_id per_all_people_f.person_id%type;
l_object_version_number per_all_assignments_f.object_version_number%type;
l_job_id per_jobs.job_id%type;
l_position_id per_positions.position_id%type;
l_location_id number := 233; -- number;
l_grade_id number := 3; -- per_grades.grade_id%type;
l_supervisor_id number := 2; -- per_all_assignments_f.supervisor_id%type;
l_assignment_status_type_id number;
l_pay_basis_id number := 33;
l_join_date date;
l_assignment_id per_all_assignments_f.assignment_id%type;
l_soft_coding_keyflex_id per_all_assignments_f.soft_coding_keyflex_id%type;
l_people_group_id per_all_assignments_f.people_group_id%type;
l_payroll_id per_all_assignments_f.payroll_id%type;
l_effective_start_date per_all_assignments_f.effective_start_date%type;
l_effective_end_date per_all_assignments_f.effective_end_date%type;
l_assignment_sequence per_all_assignments_f.assignment_sequence%type;
l_comment_id per_all_assignments_f.comment_id%type;
l_concatenated_segments varchar2(240);
l_group_name varchar2(100);
l_other_manager_warning boolean;
l_org_now_no_manager_warning boolean;
l_spp_delete_warning boolean;
l_entries_changed_warning varchar2(200);
l_tax_district_changed_warning boolean;
l_special_ceiling_step_id number;
l_no_managers_warning boolean;
l_other_manager_warnings boolean;
l_cagr_grade_def_id number;
l_cagr_concatenated_segments varchar2(100);
l_total_records number := 0;
l_success_records number := 0;
l_failure_records number := 0;
BEGIN
dbms_output.put_line('#############################################################');
dbms_output.put_line('Data Migration Of Employee Assignments :');
dbms_output.put_line('#############################################################');
dbms_output.put_line('Start Time : ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
l_person_id := null;
l_object_version_number := null;
l_organization_id := 0;
l_location_id := null;
l_grade_id := null;
l_job_id := null;
l_position_id := null;
l_supervisor_id := null;
l_assignment_status_type_id := null;
error_msg := null;
l_status := 'True';
hr_assignment_api.update_emp_asg_criteria
(
p_validate => l_validate
,p_effective_date => sysdate --to_date('11-Jan-2009','DD-MON-YYYY')
,p_datetrack_update_mode => l_mode
,p_assignment_id => l_assignment_id
,p_object_version_number => l_object_version_number +1
,p_organization_id => l_organization_id
,p_location_id => l_location_id --assignment_v.location_id
,p_grade_id => l_grade_id
,p_job_id => l_job_id
,p_position_id => l_position_id
,p_payroll_id => l_payroll_id --21--hardcoding this you should change this
-- ,p_segment1 => assignment_v.people_group
-- ,p_employment_category => assignment_v.employee_category
,p_pay_basis_id => l_pay_basis_id
,p_special_ceiling_step_id => l_special_ceiling_step_id
,p_people_group_id => l_people_group_id
,p_group_name => l_group_name
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_other_manager_warning => l_other_manager_warning
,p_spp_delete_warning => l_spp_delete_warning
,p_entries_changed_warning => l_entries_changed_warning
,p_tax_district_changed_warning => l_tax_district_changed_warning
);
hr_assignment_api.update_emp_asg
(
p_validate => l_validate --false
,p_effective_date => to_date('11-Jan-2009','DD-MON-YYYY') --assignment_v.date_of_change
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => l_assignment_id
,p_object_version_number => l_object_version_number
,p_supervisor_id => l_supervisor_id
,p_normal_hours => '7.5'
,p_frequency => 'D'
,p_time_normal_start => '09:30'
,p_time_normal_finish => '17:30'
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_comment_id => l_comment_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_concatenated_segments => l_concatenated_segments
,p_no_managers_warning => l_no_managers_warning
,p_other_manager_warning => l_other_manager_warnings
,p_cagr_grade_def_id => l_cagr_grade_def_id
,p_cagr_concatenated_segments => l_cagr_concatenated_segments
);
dbms_output.put_line('End Time : ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
dbms_output.put_line('
');
END;
/
commit;
exit;
many thanks,
Steven
[Updated on: Thu, 12 February 2009 04:19] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: 'PLS-00363: expression..' error when executing a stored procedure with in out parameters [message #386020 is a reply to message #386011] |
Thu, 12 February 2009 04:26  |
cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
1. Code tags need to go in square brackets not curly ones.
2. What are you using to run this? Because SQL+ generally tells you exactly where such errors occur:
SQL> create or replace procedure test (pa in number,
2 pb in out number) as
3 begin
4 null;
5 end;
6 /
Procedure created.
SQL> begin
2
3 test(1,
4 2);
5
6 end;
7 /
2);
*
ERROR at line 4:
ORA-06550: line 4, column 6:
PLS-00363: expression '2' cannot be used as an assignment target
ORA-06550: line 3, column 1:
PL/SQL: Statement ignored
More generally, if you're having problems calling a stored procedure you really need to give us the specification of that procedure - we've no idea which of the parameters are IN OUT.
|
|
|
|
| Re: 'PLS-00363: expression..' error when executing a stored procedure with in out parameters [message #386021 is a reply to message #386011] |
Thu, 12 February 2009 04:28 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
To get a definite answer, we'd need to see the definitions of the definitions of hr_assignment_api.update_emp_asg_criteria and hr_assignment_api.update_emp_asg
This error is caused by either:
1) Trying to assign a new value to an IN parameter in a procedure, or
2) Trying to pass a constant to a procedure as a parameter defined as IN OUT or OUT.
Examples:create or replace procedure test_098 (p in out varchar2) as
begin
null;
end;
/
begin
test_098('A');
end;
create or replace procedure test_099 (p in varchar2) as
begin
p:= 3;
end;
/
select * from user_errors;
In this case, as you're calling someone elses code, it's much more likely that you're getting case 2, and that one of the constant values that you're passing in is going to a parameter that is an IN OUT.
|
|
|
|
| Re: 'PLS-00363: expression..' error when executing a stored procedure with in out parameters [message #386245 is a reply to message #386021] |
Fri, 13 February 2009 04:51 |
MrDuke
Messages: 41
Registered: July 2007
Location: Coventry, UK
|
Member |
|
|
Many thanks for all to reading and suggested - Ive substantially changed my script now and errors with:
ORA-20001: The primary key specified is invalid
which suggests The Object Version Number, Effective Dates and the Person ID which all look fine to me.. Script now reads:
SET serveroutput ON SIZE 1000000 FORMAT WRAPPED
SET verify OFF
SET feedback OFF
DECLARE
error_msg varchar2(2000);
l_status varchar2(10);
l_validate_cnt number;
l_ass_count number;
l_validate BOOLEAN DEFAULT FALSE;
l_city varchar2(30);
l_mode varchar2(20) := 'UPDATE';
l_organization_id number;
l_effective_date date := '12-FEB-09';
l_person_id number := '29987';
l_object_version_number number := '3';
l_effective_start_date date := '12-FEB-09';
l_effective_end_date date := '12-FEB-56';
l_job_id per_jobs.job_id%type;
l_position_id per_positions.position_id%type;
l_location_id number;
l_grade_id number;
l_supervisor_id number;
l_assignment_status_type_id number;
l_pay_basis_id number;
l_join_date date;
l_soft_coding_keyflex_id number;
l_people_group_id per_all_assignments_f.people_group_id%type;
l_payroll_id per_all_assignments_f.payroll_id%type;
l_assignment_sequence per_all_assignments_f.assignment_sequence%type;
l_comment_id per_all_assignments_f.comment_id%type;
l_normal_end varchar2(15) := '17:30';
l_normal_start varchar2(15) := '09:30';
l_frequency varchar2(10) := 'D';
l_normal_hours varchar2(25) := '7.5';
l_assignment_id number := '29884';
l_gsp_post_process_warning varchar2(30);
l_entries_changed varchar2(30);
l_old_obj_ver_number per_addresses.OBJECT_VERSION_NUMBER%type;
l_segment1 varchar2(25);
l_segment2 varchar2(25);
l_segment3 varchar2(25);
l_concatenated_segments varchar2(240);
l_group_name varchar2(100);
l_other_manager_warning boolean;
l_org_now_no_manager_warning boolean;
l_spp_delete_warning boolean;
l_entries_changed_warning varchar2(200);
l_tax_district_changed_warning boolean;
l_special_ceiling_step_id number;
l_no_managers_warning boolean;
l_other_manager_warnings boolean;
l_cagr_grade_def_id number;
l_cagr_concatenated_segments varchar2(100);
l_datetrack_update_mod varchar2(10);
l_total_records number := 0;
l_success_records number := 0;
l_failure_records number := 0;
BEGIN
dbms_output.put_line('#############################################################');
dbms_output.put_line('Data Migration Of Employee Assignments :');
dbms_output.put_line('#############################################################');
dbms_output.put_line('Start Time : ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
l_person_id := null;
l_object_version_number := null;
l_organization_id := null;
l_location_id := null;
l_grade_id := null;
l_job_id := null;
l_position_id := null;
l_supervisor_id := null;
l_assignment_status_type_id := null;
error_msg := null;
l_status := 'True';
hr_assignment_api.update_emp_asg_criteria (
p_effective_date => l_effective_date ,p_datetrack_update_mode => l_datetrack_update_mod ,p_assignment_id => l_assignment_id ,p_called_from_mass_update => NULL ,p_grade_id => l_grade_id ,p_position_id => l_position_id ,p_job_id => l_job_id ,p_payroll_id => l_payroll_id ,p_location_id => l_location_id ,p_organization_id => l_organization_id ,p_pay_basis_id => l_pay_basis_id
,p_segment1 => l_segment1
,p_segment2 => l_segment2
,p_segment3 => l_segment3 ,p_employment_category => NULL ,p_concat_segments => NULL ,p_contract_id => NULL ,p_establishment_id => NULL ,p_scl_segment1 => NULL ,p_grade_ladder_pgm_id => NULL ,p_supervisor_assignment_id => NULL ,p_object_version_number => l_old_obj_ver_number ,p_special_ceiling_step_id => l_special_ceiling_step_id ,p_people_group_id => l_people_group_id ,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id ,p_group_name => l_group_name ,p_effective_start_date => l_effective_start_date ,p_effective_end_date => l_effective_end_date ,p_org_now_no_manager_warning => l_org_now_no_manager_warning ,p_other_manager_warning => l_other_manager_warning ,p_spp_delete_warning => l_spp_delete_warning ,p_entries_changed_warning => l_entries_changed ,p_tax_district_changed_warning => l_tax_district_changed_warning ,p_concatenated_segments => l_concatenated_segments ,p_gsp_post_process_warning => l_gsp_post_process_warning);
hr_assignment_api.update_emp_asg
(
p_validate => l_validate
,p_effective_date => l_effective_date
,p_datetrack_update_mode => l_datetrack_update_mod --'CORRECTION'
,p_assignment_id => l_assignment_id
,p_object_version_number => l_object_version_number
,p_supervisor_id => l_supervisor_id
,p_normal_hours => l_normal_hours --'7.5'
,p_frequency => l_frequency --'D'
,p_time_normal_start => l_normal_start --'09:30'
,p_time_normal_finish => l_normal_end --'17:30'
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_comment_id => l_comment_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_concatenated_segments => l_concatenated_segments
,p_no_managers_warning => l_no_managers_warning
,p_other_manager_warning => l_other_manager_warnings
,p_cagr_grade_def_id => l_cagr_grade_def_id
,p_cagr_concatenated_segments => l_cagr_concatenated_segments
);
dbms_output.put_line('End Time : ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
dbms_output.put_line('
');
END;
/
commit;
exit;
|
|
|
|
|
|
|
|
| Re: 'PLS-00363: expression..' error when executing a stored procedure with in out parameters [message #386292 is a reply to message #386249] |
Fri, 13 February 2009 07:52 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
| cookiemonster wrote on Fri, 13 February 2009 06:16 |
l_effective_date date := '12-FEB-09';
l_effective_start_date date := '12-FEB-09';
l_effective_end_date date := '12-FEB-56';
Using 4 digit years is always a good idea.
12-FEB-56 - is that 1956 or 2056?
|
And those aren't even DATEs. They are character strings.
SQL> l
1 declare
2 foo date := '12-FEB-09';
3 dummy number(1);
4 begin
5 select 1 into dummy from dual where foo < sysdate;
6* end;
SQL> /
declare
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 2
[Updated on: Fri, 13 February 2009 07:54] Report message to a moderator |
|
|
|
| Re: 'PLS-00363: expression..' error when executing a stored procedure with in out parameters [message #386312 is a reply to message #386011] |
Fri, 13 February 2009 09:40 |
MrDuke
Messages: 41
Registered: July 2007
Location: Coventry, UK
|
Member |
|
|
thanks for the suggestions. Yes, I've now changed the dates as well as a whole of other things. The error message now reads something Im trying to find out now..
error message: ERROR at line 1:
ORA-20001: System Error: Procedure at Step 40
Cause: The procedure has created an error at Step 40.
Action: Contact your system administrator quoting the procedure and
Step 40.
ORA-06512: at "APPS.HR_ASSIGNMENT_API", line 16009
ORA-06512: at line 79
line 79 is P_'EFFECTIVE_DATE' so its definitely connected to dates and object_version_number..
Help!
Code now looks like:
SET serveroutput ON SIZE 1000000 FORMAT WRAPPED
SET verify OFF
SET feedback OFF
DECLARE
error_msg varchar2(2000);
l_status varchar2(10);
l_validate_cnt number;
l_ass_count number;
l_validate BOOLEAN DEFAULT FALSE;
l_city varchar2(30);
l_mode varchar2(20) := 'UPDATE';
l_organization_id number;
l_effective_date date := to_date('12-FEB-2009','DD-MON-YYYY'); --'12-FEB-2009';
l_person_id number := '29987';
l_object_version_number number := '1';
l_effective_start_date date := to_date('12-FEB-2009','DD-MON-YYYY');
l_effective_end_date date := to_date('12-FEB-2059','DD-MON-YYYY');
l_job_id per_jobs.job_id%type;
l_position_id per_positions.position_id%type;
l_location_id number := '102';
l_grade_id number;
l_supervisor_id number;
l_assignment_status_type_id number;
l_pay_basis_id number;
l_join_date date;
l_soft_coding_keyflex_id number;
l_people_group_id per_all_assignments_f.people_group_id%type;
l_payroll_id per_all_assignments_f.payroll_id%type;
l_assignment_sequence per_all_assignments_f.assignment_sequence%type;
l_comment_id per_all_assignments_f.comment_id%type;
l_normal_start varchar2(15) := '09:30';
l_normal_end varchar2(15) := '17:30';
l_frequency varchar2(10) := 'W';
l_normal_hours varchar2(25) := '7.5';
l_assignment_id number := '29884';
l_gsp_post_process_warning varchar2(30);
l_entries_changed varchar2(30);
l_old_obj_ver_number number := '1';
l_segment1 varchar2(25);
l_segment2 varchar2(25);
l_segment3 varchar2(25);
l_concatenated_segments varchar2(240);
l_group_name varchar2(100);
l_other_manager_warning boolean;
l_org_now_no_manager_warning boolean;
l_spp_delete_warning boolean;
l_entries_changed_warning varchar2(200);
l_tax_district_changed_warning boolean;
l_special_ceiling_step_id number;
l_no_managers_warning boolean;
l_other_manager_warnings boolean;
l_cagr_grade_def_id number;
l_cagr_concatenated_segments varchar2(100);
l_datetrack_update_mod varchar2(10) := 'CORRECTION';
l_total_records number := 0;
l_success_records number := 0;
l_failure_records number := 0;
BEGIN
dbms_output.put_line('######################################');
dbms_output.put_line('Data Migration Of Employee Assignments :');
dbms_output.put_line('#######################################');
dbms_output.put_line('Start Time : ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
l_person_id := null;
l_object_version_number := null;
l_organization_id := null;
l_location_id := null;
l_grade_id := null;
l_job_id := null;
l_position_id := null;
l_supervisor_id := null;
l_assignment_status_type_id := null;
error_msg := null;
l_status := 'True';
hr_assignment_api.update_emp_asg_criteria (
p_effective_date => l_effective_date
,p_datetrack_update_mode => l_datetrack_update_mod
,p_assignment_id => l_assignment_id
,p_called_from_mass_update => NULL
,p_grade_id => l_grade_id
,p_position_id => l_position_id
,p_job_id => l_job_id
,p_payroll_id => l_payroll_id
,p_location_id => l_location_id
,p_organization_id => l_organization_id
,p_pay_basis_id => l_pay_basis_id
,p_segment1 => l_segment1
,p_segment2 => l_segment2
,p_segment3 => l_segment3
,p_employment_category => NULL
,p_concat_segments => NULL
,p_contract_id => NULL
,p_establishment_id => NULL
,p_scl_segment1 => NULL
,p_grade_ladder_pgm_id => NULL
,p_supervisor_assignment_id => NULL
,p_object_version_number => l_old_obj_ver_number --l_object_version_number
,p_special_ceiling_step_id => l_special_ceiling_step_id
,p_people_group_id => l_people_group_id
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_group_name => l_group_name
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_other_manager_warning => l_other_manager_warning
,p_spp_delete_warning => l_spp_delete_warning
,p_entries_changed_warning => l_entries_changed ,p_tax_district_changed_warning => l_tax_district_changed_warning
,p_concatenated_segments => l_concatenated_segments
,p_gsp_post_process_warning => l_gsp_post_process_warning);
hr_assignment_api.update_emp_asg (
p_validate => l_validate
,p_effective_date => l_effective_date
,p_datetrack_update_mode => l_datetrack_update_mod
,p_assignment_id => l_assignment_id
,p_object_version_number => l_object_version_number
,p_supervisor_id => l_supervisor_id
,p_normal_hours => l_normal_hours
,p_frequency => l_frequency
,p_time_normal_start => l_normal_start
,p_time_normal_finish => l_normal_end
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_comment_id => l_comment_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_concatenated_segments => l_concatenated_segments
,p_no_managers_warning => l_no_managers_warning
,p_other_manager_warning => l_other_manager_warnings
,p_cagr_grade_def_id => l_cagr_grade_def_id
,p_cagr_concatenated_segments => l_cagr_concatenated_segments
);
dbms_output.put_line('End Time : ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
dbms_output.put_line('
');
END;
/
commit;
exit;
|
|
|
|
| Re: 'PLS-00363: expression..' error when executing a stored procedure with in out parameters [message #386314 is a reply to message #386253] |
Fri, 13 February 2009 10:06 |
joicejohn
Messages: 327
Registered: March 2008
Location: India
|
Senior Member |
|
|
Answered already by @JRowbottom:
| JRowbottom wrote on Fri, 13 February 2009 17:19 | ORA-20001 is a user defined exception, not an oracle raised one.
If there's nothing in your code that does a RAISE_APPLICATION_ERROR then you've tripped some condition in the code that you're calling.
|
Check the procedures you are calling (for instance, hr_assignment_api.update_emp_asg_criteria) to find the reason why the error was raised.
Regards,
Jo
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Apr 19 06:46:12 CDT 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
