How to execute PL/SQL Stored Procedure?

[VicP (Vic Platon)]

How to execute a stored procedure using PL/SQL in UiPath?. I have a database in Oracle Database Developer and tag in to a stored procedure to execute.

command type: storedProcedure
sql: "greetings "

here is a sample Procedure I’ve created:
CREATE OR REPLACE PROCEDURE greetings
AS
BEGIN
dbms_output.put_line(‘Hello World!’);
END;
/

below error using “EXEC greetings” and “greetings”

[kirankumar.mahanthi1 (Kirankumar Mahanthi)]

Hi @VicP ,

Nice to connecting with you again.

i hope the mentioned procedure working in SSMS.

Are you using Execution type as stored procedure in the Execute Query activity.

And also please remove the semicolon in the End statement and try executing it.

please refer the below links for your reference.

How to execute stored procedure in uipath?

Use Execute query activity under Database. If it is not available install the UiPath.Database.Acitivities package.Use configure connection and connect to DB. In properties Fill the SP name against SQL. In Parameter pass input values with direction IN. Choose command type as Store procedure You can Store the return output in a DT variable if needed. [image]

Regards,
Kirankumar.

[VicP (Vic Platon)]

Hi @kirankumar.mahanthi1 , yes I’m using execute query activity. i’ve also tried removing semi colon it is throwing this error.
the stored procedure though workins fine in oracle sql developer

Execute Non Query: ERROR [42000] [Oracle][ODBC][Ora]ORA-00900: invalid SQL statement

[Palaniyappan (Forum Leader)]

@VicP

command type: storedProcedure
sql: "greetings "

This looks completely fine

There is an syntax error in statement

Did we try the same statement in a sql database server
Check there once for the error and pass the same here in uipath

As an alternative place the script in a txt file and use READ TEXT FILE activity and get the output as string named strinput

Now pass that variable with sql property

The reason is when you are passing the command as it is the single quotes May throw error

@VicP

[VicP (Vic Platon)]

Hi @Palaniyappan thank you for helping me out.
the script above works perfectly fine in Oracle SQL Developer no error at all, however when it is executed in uiPath it throws some error. i also tried placing it into a text file and pass into a string variable using the execute query and non execute query activity but no luck.

[Palaniyappan (Forum Leader)]

@VicP

CREATE OR REPLACE PROCEDURE greetings
AS
BEGIN
dbms_output.put_line(‘Hello World!’);
END;
/

I don’t find any error with the above statement
Try removing that forward slash at last and run it
Or

May be let’s do one thing
Let’s create this procedure in your dB server
And call that procedure here in uipath using the database activity

@VicP