cancel
Showing results for 
Search instead for 
Did you mean: 

ReadyAPI freezes when trying to connect to oracle database

SOLVED
tcarey
Occasional Contributor

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
richie
Community Hero

Re: ReadyAPI freezes when trying to connect to oracle database

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
if this helped answer the post, could you please mark it as 'solved'? Also if you consider whether the title of your post is relevant? Perhaps if the post is solved, it might make sense to update the Subject header field of the post to something more descriptive? This will help people when searching for problems. Ta

View solution in original post

7 REPLIES 7
nmrao
Community Hero

Re: ReadyAPI freezes when trying to connect to oracle database

How many records it is pulling? Try using filter if not already.


Regards,
Rao.
tcarey
Occasional Contributor

Re: ReadyAPI freezes when trying to connect to oracle database

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
Community Hero

Re: ReadyAPI freezes when trying to connect to oracle database

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
if this helped answer the post, could you please mark it as 'solved'? Also if you consider whether the title of your post is relevant? Perhaps if the post is solved, it might make sense to update the Subject header field of the post to something more descriptive? This will help people when searching for problems. Ta
tcarey
Occasional Contributor

Re: ReadyAPI freezes when trying to connect to oracle database

So I tried putting the sql directly into the editable part....  but "run" button is grayed out 😭 and of course if I click on "Build Query" it locks up

Meanwhile - i created a groovy script to connect to the database to do the querying...  did not want to do that route .. as I wanted to make it user friendly for folks that are new to ReadyAPI (like myself)  

 

 

 

smartbear1.jpg

richie
Community Hero

Re: ReadyAPI freezes when trying to connect to oracle database

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
if this helped answer the post, could you please mark it as 'solved'? Also if you consider whether the title of your post is relevant? Perhaps if the post is solved, it might make sense to update the Subject header field of the post to something more descriptive? This will help people when searching for problems. Ta

View solution in original post

tcarey
Occasional Contributor

Re: ReadyAPI freezes when trying to connect to oracle database

wow.. i am going to cry.. this worked perfectly.   Thank you so much!!   We are grilling right now, I wish I can fix you a plate!

 

Thanks again!!!!

mmoser18
Frequent Contributor

Re: ReadyAPI freezes when trying to connect to oracle database

What is this "Build Query" button there for? Whenever I click that this stupid thing locks up and I have to kill it via Task Manager. 😞

 

I added a Step "JDBC Request" to my TestCase (there is no "JDBC Step" as you write, so I guess that's what you meant).

 

When I select that and configure the connection and click "Test Connection" I get "Connection tested successfully".

I also can enter some SQL statement and hit the green triangle and the query is executed.

 

But when I hit "build query" the whole thing locks up and I have to kill it. So what's the point or intended functionality of this "build query" button? I found no explanation or description of it.

New Here?
Join us and watch the welcome video:
Top Kudoed Authors