issue while using SQL Functions in Ready API SoapUI JDBC request

[bench]

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 🙂

[Lucian]

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?

[bench]

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 · Accepted solution]

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. 

[AlexKaras]

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?