Forum Discussion

bench's avatar
bench
New Contributor
7 years ago

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 :-)

  • Lucian's avatar
    Lucian
    7 years ago

    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. 

  • Lucian's avatar
    Lucian
    Community Hero
    7 years ago

    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's avatar
      bench
      New Contributor
      7 years ago

      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's avatar
        Lucian
        Community Hero
        7 years ago

        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.