topic Calling a stored procedure written in PL/SQL in Oracle database from blue prism in Product Forum

Calling a stored procedure written in PL/SQL in Oracle database from blue prism

sonuiiml — Tue, 12 Jan 2021 16:07:00 GMT

Hi Team,

I am trying to call a procedure in Oracle after establishing connection through Oracle MDA utility but no luck yet. I can run normal SQL queries and get desired result.

Any pointers anyone?

------------------------------
Susamay Halder Consultant
Consultant
Bruce Power
+1(437)217-1086
------------------------------

RE: Calling a stored procedure written in PL/SQL in Oracle database from blue prism

sonuiiml — Tue, 12 Jan 2021 16:13:00 GMT

@ewilson any thoughts?

------------------------------
Susamay Halder Consultant
Consultant
Bruce Power
+1(437)217-1086
------------------------------

RE: Calling a stored procedure written in PL/SQL in Oracle database from blue prism

GeoffHirst — Wed, 13 Jan 2021 13:31:00 GMT

Hi Susamay,

I am sorry you are experiencing an issue with the Oracle MDA asset. Please could you advise if you are using the very latest version 6, this was updated very recently. Are you getting any error message returned? If you could supply details of this it might help to determine what is happening.

regards

Geoff Hirst

------------------------------
Geoff Hirst
Partner Pre Sales
Blue Prism
------------------------------

RE: Calling a stored procedure written in PL/SQL in Oracle database from blue prism

sonuiiml — Wed, 13 Jan 2021 14:51:00 GMT

Hi @GeoffHirst

Thanks Geoff
Well my company uses blue prism version 6 and while using this tool., Eric made a custom code which suits version 6. Below is the link to the previous discussion I had
https://community.blueprism.com/communities/community-home/digestviewer/viewthread?MessageKey=f3e6a94b-7602-4101-8520-adc40759c969&CommunityKey=3743dbaa-6766-4a4d-b7ed-9a98b6b1dd01&tab=digestviewer#bm1556d6e7-92e0-41d7-9523-56fdbbf57a41

Below is the procedure I am trying to run from Get collection stage . It works perfectly fine while running from SQL developer

DECLARE vcEmpId VARCHAR2(6):= '506060'; vcAsOfDt VARCHAR2(10) := '2021-01-07'; nEmpTBHrs NUMBER := 0 ;nRtnCode NUMBER := 0;

BEGIN
tempus.tempus_schedule2.GetTBRelativeToShiftAsOfDate (
vcEmpId,
vcAsOfDt,
nEmpTBHrs,
nRtnCode);
dbms_output.put_line('nEmpTBHrs: '||to_char(nEmpTBHrs));
dbms_output.put_line('nRtnCode: '||to_char(nRtnCode));
END;

Here the inputs are vcEmpId and vcAsOfDt

and the outputs are nEmpTBHrs,nRtnCode.

While executing this code as given above It gibes the below error

ORA-06550: line 1, column 400:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

; <an identifier> <a double-quoted delimited-identifier>
The symbol ";" was substituted for "end-of-file" to continue.

Do I need to change some syntax in the procedure? Please let me know

Thanks

------------------------------
Susamay Halder Consultant
Consultant
Bruce Power
+1(437)217-1086
------------------------------

RE: Calling a stored procedure written in PL/SQL in Oracle database from blue prism

GeoffHirst — Wed, 13 Jan 2021 15:15:00 GMT

Susamay,

Apologies, I wasn't clear. There has been an update to the Oracle MDA asset and its now on version 6. However, I don't think this will address your issue. Oracle isn't my core database and I am unsure why it would respond the way it is doing. However, I will investigate and revert back to you.

regards

Geoff Hirst

------------------------------
Geoff Hirst
Partner Pre Sales
Blue Prism
------------------------------

RE: Calling a stored procedure written in PL/SQL in Oracle database from blue prism

sonuiiml — Thu, 14 Jan 2021 17:36:00 GMT

Hi @GeoffHirst

I was able to solve this. I made a separate code stage which included some tweaking of the C# code as given in below link
.net - Calling Oracle stored procedure from C#? - Stack Overflow
Thanks

------------------------------
Susamay Halder Consultant
Consultant
Bruce Power
+1(437)217-1086
------------------------------