How to call stored procedures into blueprism? need action and utility we use in bp?

balakrishnag

New Member
we cannot directly run stored procedures. following are two process
directly use the command in get collection or write a c# code to get input as procedure name and run it.
 

Pallavivalunj

New Member
@ritabrata7 Hi Team,
I have solved the issue by using SQL-DATA VBO, in BluePrism.
The object contains Action called as Execute is the one to call the Procedure by giving command in query- "EXEC Procedure_Name" and the work is done.
Note : don't forget to set Connection before using any execution or get collection etc object.

And to pass input parameter for Procedure you can use Execution or for getting output from procedure you have to Use Get Text/Get Number as per ur requirement.
For giving "Input parameter and getting output in Collection" the command i used was

SQL_DATA Object >> Get Collection Action >> Query>>" DECLARE @ReconAmt int &"DECLARE @BBDR int " & EXECUTE USP_BRCitiDataReconSumm &[BankBookAmt]&","&[CurrentAccount]&","&[TideAccount]&" SELECT @ReconAmt ,@BBDR"

Here, BankBookAmt, CurrentAccount,TideAccount : Input Parameter Variable
USP_BRCitiDataReconSumm : Procedure Name
ReconAmt, BBDR : Output Parameter

here u have to declare ur Output parameter first & then Execute the Command , then give input parameter in this format separating by <,> then select the output parameter which u have declared in Starting.

In my case the output was number hence i declared it as int in ur case u can do the respective change.
For collection u need not declare all column name u can simply declare the 1 Column name For.eg: " DECLARE @ReconAmt int " & EXECUTE USP_BRCitiDataReconSumm &[BankBookAmt]&","&[CurrentAccount]&","&[TideAccount]&" SELECT @ReconAmt

You will get the Whole Output without fail.
For any further Query just ping.

Thanks and Regard.
 

sonuiiml

New Member
Hi @Pallavivalunj

I did tried executing a procedure in Oracle Database using Oracle DMDA utility using the same get collection action stage but seems like there is an error. Is the structure of syntax going to be the same while calling a procedure in oracle database as well? Please let me know
 

sonuiiml

New Member
Hello everyone,

I am trying to call a stored procedure in Oracle database from blue prism. Below are the details. Any pointers anyone

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
 
Top