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 Go to next message
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 Go to previous messageGo to next message
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 #381742 is a reply to message #381738] Mon, 19 January 2009 10:43 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>I hope thaT makes it clearer!
As clear as mud.


You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you.
Re: How do I run 2 API's in the same SQL script? [message #381754 is a reply to message #381741] Mon, 19 January 2009 11:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no line 53 in what you posted.
Just fix your code.

Regards
Michel
Re: How do I run 2 API's in the same SQL script? [message #381860 is a reply to message #381741] Tue, 20 January 2009 02:38 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The error message you have posted did not come from the code you have posted.
It would help if you could show us the actual code that is producing the error message.
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 Go to previous messageGo to next message
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 #381961 is a reply to message #381959] Tue, 20 January 2009 07:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: How do I run 2 API's in the same SQL script? [message #381962 is a reply to message #381959] Tue, 20 January 2009 07:58 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
MrDuke wrote on Tue, 20 January 2009 08:49


,p_*** => 'M'



That looks invalid to me.
Re: How do I run 2 API's in the same SQL script? [message #381965 is a reply to message #381738] Tue, 20 January 2009 08:14 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you're not going to have multiple exception handlers, having multiple begins seems a bit pointless.
You can just write it as:
DELCARE
<variables>
BEGIN

procedure1;
procedure2;

COMMIT;
END;
/
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #381996 is a reply to message #381738] Tue, 20 January 2009 09:50 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you
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 Go to previous messageGo to next message
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 #382017 is a reply to message #381738] Tue, 20 January 2009 12:01 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you

It is unclear to this reader exactly what are the inputs, what are the processing requirements, or what is the expected/desired results.

Since you choose ignore Posting Guidelines, I'll choose to not waste more of my time trying to guess what might assist you.
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 Go to previous message
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
Previous Topic: Mutuating Trigger
Next Topic: Regular Expression
Goto Forum:
  


Current Time: Wed Dec 07 20:08:15 CST 2016

Total time taken to generate the page: 0.10873 seconds