Stored Procedure Error ORA-06550: line 1, column 7: PL/SQL: Statement ignored

rishub over 7 years ago

Hi,

I have been trying to use execute stored procedure smart service but when I followed the same use case mentioned in Documentation for Stored Procedure with SP_BOOKLIST, while using the smart Service in Process I got the error:

ORA-06550: line 1, column 7:PLS-00306: wrong number or types of arguments in call to 'SP_LISTBOOKS'ORA-06550: line 1, column 7:PL/SQL: Statement ignored

When I am following the same in function as per documentation, I am getting the error:

Interface Definition: Expression evaluation error : Invalid index: Cannot index property '_foundation' of type Text into type List of Variant:

Attached is the code, I used in interface Rule

=with(
local!spResult: fn!executestoredprocedure(
"jdbc/OracleDataSource",
"SP_LISTBOOKS",
{
{name: "title_search", value: "%pig%"}
}
),
if(local!spResult.success,
local!spResult.result,
local!spResult.error
)
)

I also checked permission in Oracle DB & the connection type is "Basic" & role is "Default".

Can anyone please guide where I am getting it wrong & how can I fix it?

Thanks

Rishu

Discussion posts and replies are publicly visible

Parents

0 Ramesh Gudipati over 7 years ago

rishub Can you check with number of parameters the SP is having and also name of the input parameter should match with the input parameter of the Stored Procedure that you are calling.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 rishub over 7 years ago in reply to Ramesh Gudipati

rameshg73 I used the same code that Appian has added in Documentation so I am not sure if it is related to parameters or sth related to code.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 rishub over 7 years ago in reply to rishub

Ramesh Gudipati
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Ramesh Gudipati over 7 years ago in reply to rishub

Can you send the name of the SP and also the input and output parameters of the SP. This helps me to give the solution.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 rishub over 7 years ago in reply to Ramesh Gudipati

Ramesh Gudipati :: I ran the following script in Oracle:

CREATE TABLE "book" (
"title" varchar2(50),
"author" varchar2(20),
"quantity" int,
"price" float
);
INSERT INTO "book" VALUES ('Peppa Pig: Fun at the Fair', 'Collectif', 23, 3.74);
INSERT INTO "book" VALUES ('Panda Goes to the Olympics', 'Judith Simanovsky', 7, 4.99);
INSERT INTO "book" VALUES ('Topsy and Tim Visit London', 'Jean Adamson', 15, 3.29);
CREATE OR REPLACE PROCEDURE sp_ListBooks (
title_search IN varchar2,
total_cost OUT float,
books_cursor OUT SYS_REFCURSOR
)
AS
BEGIN
OPEN books_cursor FOR SELECT "title", "author", "quantity", "price" FROM "book" WHERE "title" LIKE title_search;
SELECT SUM("quantity" * "price") INTO total_cost FROM "book" WHERE "title" LIKE title_search;
END;
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Reply

0 rishub over 7 years ago in reply to Ramesh Gudipati

Ramesh Gudipati :: I ran the following script in Oracle:

CREATE TABLE "book" (
"title" varchar2(50),
"author" varchar2(20),
"quantity" int,
"price" float
);
INSERT INTO "book" VALUES ('Peppa Pig: Fun at the Fair', 'Collectif', 23, 3.74);
INSERT INTO "book" VALUES ('Panda Goes to the Olympics', 'Judith Simanovsky', 7, 4.99);
INSERT INTO "book" VALUES ('Topsy and Tim Visit London', 'Jean Adamson', 15, 3.29);
CREATE OR REPLACE PROCEDURE sp_ListBooks (
title_search IN varchar2,
total_cost OUT float,
books_cursor OUT SYS_REFCURSOR
)
AS
BEGIN
OPEN books_cursor FOR SELECT "title", "author", "quantity", "price" FROM "book" WHERE "title" LIKE title_search;
SELECT SUM("quantity" * "price") INTO total_cost FROM "book" WHERE "title" LIKE title_search;
END;
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Children

0 Ramesh Gudipati over 7 years ago in reply to rishub

Can you refer to the documentation. As per the documentation, in Oracle even the schema name should be mentioned in the SP name.
forum.appian.com/.../summary

Please refer to the attached PDF.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 rishub over 7 years ago in reply to Ramesh Gudipati

Ramesh Gudipati I did try it with Schema name as well but it did not work. Below is the expression & error I got:

Expression:

=with(
local!spResult: fn!executestoredprocedure(
"jdbc/OracleDataSource",
"XXXXSWPR_UT.SP_LISTBOOKS",
{
{name: "title_search", value: "%pig%"}
}
),
if(local!spResult.success,
local!spResult.result,
local!spResult.error
)
)

Result:

Interface Definition: Expression evaluation error : Invalid index: Cannot index property '_foundation' of type Text into type List of Variant
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 rishub over 7 years ago in reply to rishub

ericw I guess you have worked on this POC. Can you please shed some light on where I am getting it wrong?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 rishub over 7 years ago in reply to rishub

eliav Can you please tell how did you figure out the solution?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
+1 Ramesh Gudipati over 7 years ago in reply to rishub

rishub Dased on the error message, i think the SP got executed successfully. The error might be during the saving of the result into the variable. Can you please check if the SP ran successfully or not.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Reject Answer

Cancel
0 rishub over 7 years ago in reply to Ramesh Gudipati

Ramesh Gudipati Thanks a lot. The issue was on input as I was input field name was in Capital letters in Oracle & in input I was taking it in small letter.

Also, I am getting the response : [success:true,error:,parameters:[BOOKS_CURSOR:[quantity:23,author:Collectif,price:3.74,title:Peppa Pig: Fun at the Fair]; [quantity:7,author:Judith Simanovsky,price:4.99,title:Panda Goes to the Olympics]],result:] But I need to save the values of BOOKS_CURSOR in Books CDT which has quantity, author,price & title fields. Can you please guide how can I do it?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Ramesh Gudipati over 7 years ago in reply to rishub

Create a CDT with the same column names that you are returning from SP. The you can collect the result into that CDT. As you are getting quantity,author,price,title, create a CDT with these fields with same name and type.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel