Home » SQL & PL/SQL » SQL & PL/SQL » How do I run 2 API's in the same SQL script? (Oracle Financials 11i, Sun Solaris platform, ORACLE RDBMS : 10.1)
How do I run 2 API's in the same SQL script? [message #381738] |
Mon, 19 January 2009 10:22  |
MrDuke
Messages: 41 Registered: July 2007 Location: Coventry, UK
|
Member |
|
|
to whom..
I need help on how to write a sql [or is it a PL/SQL?] script to run 2 API's (application programming interfaces source code interface from Financials) together. Code below goes basically:
DECLARE
Variables
BEGIN
hr_employee_api.create_gb_employee(.......); --API 1
hr_person_address_api.create_person_address(...); --API 2
dbms_output.put_line ('Address Updation/Insertion has been successful!');
END;
/
commit;
exit;
I know it'll need error handling but I just thought Id get it working first..
any help is greatly appreciated...
Steven
|
|
|
Re: How do I run 2 API's in the same SQL script? [message #381741 is a reply to message #381738] |
Mon, 19 January 2009 10:38   |
MrDuke
Messages: 41 Registered: July 2007 Location: Coventry, UK
|
Member |
|
|
..I should have perhaps said also that each API has a BEGIN & END thus providing the problem. When I try to run it it errors at the line between the API's:
ORA-06550: line 53, column 1:
PLS-00103: Encountered the symbol "HR_PERSON_ADDRESS_API" when expecting one of
the following:
:= . ( % ;
The symbol ":=" was substituted for "HR_PERSON_ADDRESS_API" to continue.
ORA-06550: line 59, column 22:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
) , * & = - + < / > at in is mod remainder not rem
..I need to somehow put each of them inside something so they can be then ran individually (they both run fine on their own) but within a script..
I hope thaT makes it clearer!
many thanks
Steven
|
|
|
|
|
|
Re: How do I run 2 API's in the same SQL script? [message #381959 is a reply to message #381738] |
Tue, 20 January 2009 07:49   |
MrDuke
Messages: 41 Registered: July 2007 Location: Coventry, UK
|
Member |
|
|
thanks for the comments, Ive now set it up with the code in the structure:
declare
begin
begin
...
hr_api.create_emp() ;
end ; -- api 1 call
begin
...
hr_api.emp_address() ;
end ; -- api 2 call
end ;
..its still falling over at the line where the first API ends
and the second the begins.
======================== error message =====================
DECLARE
*
ERROR at line 1:
ORA-06550: line 65, column 22:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
) , * & = - + < / > at in is mod remainder not rem
<an exponent (**)> or != or ~= >= <= <> and or like
between || multiset member SUBMULTISET_
The symbol ", was inserted before ";" to continue.
Commit complete.
======================== error message =====================
======================== actual code =====================
-- SL's HRMS New Employee's Personal and address details Migration--
SET SERVEROUTPUT ON SIZE 100000
DECLARE
/*Following Variables declared to hold Values returned by the API (Out Parameters) */
ip_p_address_id NUMBER;
ip_p_object_version_number NUMBER;
l_employee_number varchar2(20):='00019834';
l_person_id number:= 134571;
l_validate BOOLEAN DEFAULT FALSE;
l_assignment_id number;
l_per_object_version_number number;
l_asg_object_version_number number;
l_per_effective_start_date date;
l_per_effective_end_date date;
l_full_name varchar2(40);
l_per_comment_id number;
l_assignment_sequence number;
l_assignment_number varchar2(20);
l_name_combination_warning boolean;
l_assign_payroll_warning boolean;
l_address_id NUMBER;
l_object_version_number NUMBER;
--dbms_output.put_line('Starting to import the Employee records for today: ' || SYSDATE);
--dbms_output.put_line('');
BEGIN
BEGIN
hr_employee_api.create_gb_employee
(
p_validate => l_validate --FALSE
,p_hire_date => SYSDATE
--,p_hire_date => to_date('11-Jul-2001','DD-MON-YYYY')
,p_business_group_id => 0
,p_first_name => 'Jack'
,p_middle_names => NULL --'Sell'
,p_last_name => 'Beats'
,p_*** => 'M'
,p_ni_number => 'WP924591B'
,p_employee_number => l_employee_number
,p_person_id => l_person_id
,p_assignment_id => l_assignment_id
,p_per_object_version_number => l_per_object_version_number
,p_asg_object_version_number => l_asg_object_version_number
,p_per_effective_start_date => l_per_effective_start_date
,p_per_effective_end_date => l_per_effective_end_date
,p_full_name => l_full_name
,p_per_comment_id => l_per_comment_id
,p_assignment_sequence => l_assignment_sequence
,p_assignment_number => l_assignment_number
,p_name_combination_warning => l_name_combination_warning
,p_assign_payroll_warning => l_assign_payroll_warning);
END;
BEGIN
hr_person_address_api.create_person_address
(
p_validate => l_validate,
p_effective_date => SYSDATE,
p_pradd_ovlapval_override => NULL,
p_validate_county => NULL,
p_person_id => 134571;
p_primary_flag => 'Y',
p_style => 'GB_GLB',
p_date_from => SYSDATE,
p_date_to => NULL,
p_address_type => NULL,
p_comments => NULL,
p_address_line1 => 'Waterloo Rd',
p_address_line2 => 'Cockfield',
p_address_line3 => 'Darlo',
p_town_or_city => 'Saarrfff Laaarrnnnddaann',
p_region_1 => NULL,
p_region_2 => NULL,
p_region_3 => NULL,
p_postal_code => 'NE13 6DF',
p_country => 'GB',
p_telephone_number_1 => '0122 26453148',
p_telephone_number_2 => NULL,
p_telephone_number_3 => NULL,
p_addr_attribute_category => NULL,
p_addr_attribute1 => NULL,
p_addr_attribute2 => NULL,
p_addr_attribute3 => NULL,
p_addr_attribute4 => NULL,
p_addr_attribute5 => NULL,
p_addr_attribute6 => NULL,
p_addr_attribute7 => NULL,
p_addr_attribute8 => NULL,
p_addr_attribute9 => NULL,
p_addr_attribute10 => NULL,
p_addr_attribute11 => NULL,
p_addr_attribute12 => NULL,
p_addr_attribute13 => NULL,
p_addr_attribute14 => NULL,
p_addr_attribute15 => NULL,
p_addr_attribute16 => NULL,
p_addr_attribute17 => NULL,
p_addr_attribute18 => NULL,
p_addr_attribute19 => NULL,
p_addr_attribute20 => NULL,
p_add_information13 => NULL,
p_add_information14 => NULL,
p_add_information15 => NULL,
p_add_information16 => NULL,
p_add_information17 => NULL,
p_add_information18 => NULL,
p_add_information19 => NULL,
p_add_information20 => NULL,
p_party_id => NULL,
p_address_id => ip_p_address_id,
p_object_version_number => ip_p_object_version_number);
END;
dbms_output.put_line ('Address Updation/Insertion has been successful!');
dbms_output.put_line ('');
--DBMS_OUTPUT.put_line (SQLERRM);
end;
/
commit;
exit;
======================== actual code =====================
..the structure must need changing again.. hmmm...
thanks, Steven
|
|
|
|
|
|
Re: How do I run 2 API's in the same SQL script? [message #381992 is a reply to message #381738] |
Tue, 20 January 2009 09:31   |
MrDuke
Messages: 41 Registered: July 2007 Location: Coventry, UK
|
Member |
|
|
the s*x field is fine - as I said earler, if I run each API seperately they run OK loading the record into ORACLE, so its only the coding thats letting me down now!
I'll try rejigging to the structure suggested (each API in its own Procedure) ..not that Ive done tried this before.. any examples for guidance..?
many thanks..
|
|
|
Re: How do I run 2 API's in the same SQL script? [message #381995 is a reply to message #381738] |
Tue, 20 January 2009 09:49   |
MrDuke
Messages: 41 Registered: July 2007 Location: Coventry, UK
|
Member |
|
|
wait a minute - can you do this? create 2 procedures in the same script? Or do you mean create them in different files and call them in a PL SQL script?
If its the latter then it won't work as they have to be in the same file so the person_id (which seems to be generated by ORACLE when the employee is imported) is used in the second part when the address fields are imported.. hmmmm...
|
|
|
|
Re: How do I run 2 API's in the same SQL script? [message #382006 is a reply to message #381738] |
Tue, 20 January 2009 10:53   |
MrDuke
Messages: 41 Registered: July 2007 Location: Coventry, UK
|
Member |
|
|
Thanks for the comments, I've successfully loaded in 1 employee record together with their address details altogether. The structure worked a treat (with some minor variable changes and error handling added)..
DELCARE
<variables>
BEGIN
procedure1;
procedure2;
COMMIT;
END;
/
Now Ive got that done, the plan is to create a UNIX script to create a temp table, load the records in, and run the PL SQL script here to update ORACLE Financials.. cheers... Steven.
|
|
|
|
Re: How do I run 2 API's in the same SQL script? [message #382154 is a reply to message #381738] |
Wed, 21 January 2009 03:17  |
MrDuke
Messages: 41 Registered: July 2007 Location: Coventry, UK
|
Member |
|
|
mr blackswan,
sincerest apologies if anyone was confused by my postings, as far as I can I believe I am following guidelines. I admit I'm not the most technical type in the world which shows in my descriptions I only know as much as I need to get by.. I will make any future postings simpler, clearer and with concise and definite questions and requirements.
regards
Steven
|
|
|
Goto Forum:
Current Time: Mon Feb 17 21:31:09 CST 2025
|