cancel
Showing results for 
Search instead for 
Did you mean: 

issue while using SQL Functions in Ready API SoapUI JDBC request

SOLVED
bench
New Contributor

issue while using SQL Functions in Ready API SoapUI JDBC request

Hi,

Do you know if its possible to use an existing SQL functions that already created in the oracle DB on JDBC request?

in my case im trying to create a validation test step that calling SQL function that returning TRUE/FALSE in case test passed accordingly.

once running the query in oracle DB directly im reciving an output

but once running same query on same DB connection using JDBC request its fails with the bellow log info

  • 2018-08-28 11:03:46.670 - Error getting response; java.sql.SQLException: ORA-00911: invalid character

im using the connection driver: oracle.jdbc.driver.OracleDriver

i will be thankfull to get your advise 🙂

1 ACCEPTED SOLUTION

Accepted Solutions
Lucian
Community Hero

Re: issue while using SQL Functions in Ready API SoapUI JDBC request

Kind of out of ideas except for the fact that you need to not end the query with a semicolon. So instead of

select EF_CHECK_EVENT_ID(Event_ID) from dual;

you could try

select EF_CHECK_EVENT_ID(Event_ID) from dual

Sorry pal. 



If my answer helped please click on the 'Accept as Solution' button.

View solution in original post

4 REPLIES 4
Lucian
Community Hero

Re: issue while using SQL Functions in Ready API SoapUI JDBC request

hmmm.. what is the exact query that you are doing? What does the query return? Is there a way to look into the db log?



If my answer helped please click on the 'Accept as Solution' button.
bench
New Contributor

Re: issue while using SQL Functions in Ready API SoapUI JDBC request

hi,

Thank you for your reply,

The query i'm using to call the function is (this query works in DB but not in JDBC request):

select EF_CHECK_EVENT_ID(Event_ID) from dual;

 

its return string: 'TRUE'/'FALSE' PSB create function syntax

 

CREATE OR REPLACE FUNCTION EF_CHECK_EVENT_ID (Event_id_in NUMBER)

RETURN VARCHAR2

 IS

   Counter NUMBER;

   Return_value VARCHAR2 (5);

 BEGIN

  --function is checking if the event id persisted in the DB in RE table

  SELECT COUNT (*) INTO Counter FROM abpappo1.ape1_rated_event A WHERE Event_id_in = A.Event_id;

  IF Counter = 0 THEN Return_value := 'FALSE';

    ELSE Return_value := 'TRUE';

  END IF;

 RETURN Return_value;

 END EF_CHECK_EVENT_ID ;

 

 

 

Lucian
Community Hero

Re: issue while using SQL Functions in Ready API SoapUI JDBC request

Kind of out of ideas except for the fact that you need to not end the query with a semicolon. So instead of

select EF_CHECK_EVENT_ID(Event_ID) from dual;

you could try

select EF_CHECK_EVENT_ID(Event_ID) from dual

Sorry pal. 



If my answer helped please click on the 'Accept as Solution' button.

View solution in original post

AlexKaras
Community Hero

Re: issue while using SQL Functions in Ready API SoapUI JDBC request

Hi,

 

May it be something wrong with the actual value of the Event_ID parameter? Extra space or some other (invisible) character at the end?
What if you log complete text of the query (with substituted parameter) and the length of the parameter value?

 

Regards,
  /Alex [Community Hero]
____
[Community Heroes] are not employed by SmartBear Software but
are just volunteers who have some experience with the tools by SmartBear Software
and a desire to help others. Posts made by [Community Heroes]
may differ from the official policies of SmartBear Software and should be treated
as the own private opinion of their authors and under no circumstances as an
official answer from SmartBear Software.
The [Community Hero] signature is used with permission by SmartBear Software.
https://community.smartbear.com/t5/custom/page/page-id/hall-of-fame
================================
New Here?
Join us and watch the welcome video:
Announcements
Top Kudoed Authors