Forum Discussion

tcarey's avatar
tcarey
Occasional Contributor
5 years ago

ReadyAPI freezes when trying to connect to oracle database

Initial Test Connection works fine using oracle thin driver  - however when I try to build a query... it just gets stuck and eventually ReadyAPI freezes and I have to end it from task manager.

 

Any suggestion on how I can build a query from oracle database?

 

  • richie's avatar
    richie
    5 years ago
    Hi tcarey,

    According to the screenshot you supplied it looks like youre using a Datasource step (type JDBC). Don't.

    Use the separate 'JDBC step' instead. This will allow you to run the SQL without any problems and maintains the ease of use for non coders.

    Cheers

    Rich
  • nmrao's avatar
    nmrao
    Champion Level 3
    5 years ago
    How many records it is pulling? Try using filter if not already.
    • tcarey's avatar
      tcarey
      Occasional Contributor
      5 years ago

      trying to just pull one record..

      here is my sql statement below ... just simple query to get 1 active account_number but it just freezes up. 

       

      SELECT DISTINCT c.account_number
      FROM hz_cust_accounts c , ra_customer_trx_all t
      WHERE 1=1
      AND t.bill_to_customer_id = c.cust_account_id
      and t.creation_date > sysdate - 30
      AND c.status = 'A'
      order by dbms_random.value() FETCH FIRST 1 ROW ONLY

       

       

      • richie's avatar
        richie
        Community Hero
        5 years ago
        Hey tcarey

        If you run the same query via Oracle's sqlplus or another db interrogation tool, does the single record get retrieved almost immediately? Using "distinct" can slow down the execution of a query by up to 75-80% in certain situations (in your query it appears you have a tablescan, then a sort (relating to the distinct) and then a final sort (equating to your "order by"). That's a lot of work which would also be slowed down further if youre columns youre querying aren't indexed or arent indexed efficiently.

        Have you tried executing the simplest query in soapui? e.g.

        SELECT SYSDATE FROM DUAL;

        If that retrieves the results without hanging in soapui then id suggest optimising your query more efficiently as if querying dual works fine then the problem is the query youre executing, not soapui.

        Oh. One final thing. Rather than use querybuilder, id just type thr sql directly into the editable field rather than using the querybuilder function as its extra overhead and not necessary.

        Cheers

        Rich