Toad World® Forums

SQL Editor Result


#1

Hi,

Not sure whether it’s a problem but since I upgraded straight from version 4.x it does not seem I can get 5.x to behave the same way. While running long queries in 4.x I could press ‘Stop’ and ‘view the result generated so far’. If I do the same in 5.x it does not give me this option and generates ORA-01013: user requested cancel of current operation immediately

Regards,

Vadim


#2

Hi Vadim,

Thanks for this goood catch. Sadly, it is a regression from previous versions. I will create change request to fix this bug on your behalf

Rgds,
Andrew


#3

what is the chance ti fix it for a final release ? It’s a very useful feature


#4

Regards
Sekhar

Hi Vadim ,

After further investigation , we are not sure that this is a regression problem.It seems to depend on the type of query being run and the timing of hitting the stop button.

Senario: 1) If you query from a table ,which exists in your own schema and contains a lot of data .For Eg : If you stop after 0.50 sec of execution , you may be prompted by a dialog in NAV SQL editor stating that “View Results Generated So Far?”.If you press Yes here on this dialog , then the partial results will be displayed in Data grid .

Senario: 2) If you run a query for Eg : select * from all_tab_columns .Then if you hit the stop button after 0.50 sec of execution time, you may get " ORA-01013: user requested cancel of current operation" , because by the time you hit the button , it is not prepared yet the execution plan or might not retrieved any results from tables.But on the same query , if you hit stop after 1.52 sec of execution time, you will see the partial results.Could you please try to run your query with little longer and hit stop button and see how it behaves ?.

Andrew and me we tested on different versions of databases using different queries and we came to the above conclusion.Please provide us any model query , if you can. Thank you very much for your time and feedback.



#5

Sekhar,

Do not think it’s related to DB version or to timing. My database is located in Lausangne so we’ve got a bit of latency here. What I noticed though that the only way to get “View Results Generated So Far” is to press Stop when the data is already retrieved from DB and Nav is in the process of populating the grid. In other cases Stop will not give the option to view interim result. I think you really need to compare Nav 4.x agains Nav 5.x.

Vadim


#6

Hi Vadim,

We have checked again from 4x all through upto 5.5 Beta, we see the same behaviour (as per Sekhar’s previous note). We are confident that this is not a (regression) issue.

Could you please kindly verify that in 4x an 5.5 behaves in different ways?

Thanks and regards,
Bruce


#7

Guys,

I have to disappoint you but your confidence did not hold my testing. Do the following

create table test (col1 varchar2(20))
/
begin
for c1 in 1…1000000 loop
insert into test values('Hello - ’ || to_char(c1));
end loop;
commit;
end;
/

Try ‘Stop’ select * from test from SQL Editor in 4.5 and in 5.5 (build 707)

My Result:

4.5 - displays 'View result retrieved so far ?" and when you press ‘OK’ it shows result in the grid. You can also read at the bottom 'Rows 1 of 200 (Partial)

5.5 - generates ORA-01013: user requested cancel of current operation

Have a nice day

Vadim


#8

Hi Vadim,

Thanks for sending us the info/update. Sorry to hear about it .

We are getting on it right now. Will get back to you shortly.

Thanks and regards,
Bruce


#9

Hi!
In my opinion, “view results generated …” could be displayed when sqlnav is getting results from db, means fetching data into grid component, then when you click stop sqlnav can stop fetch and presents results. When you press stop while preparng and executing statement, operation will be canceled. Watch that this is ORA error not sqlnav . I thing is posibility that in 4.x you have fetch all option enabed and single select fetching all rows and then when you cliick stop you have “view…”, but in 5.x you don not have this enabled and time to retrieve records is to short to hit stop while fetching, and you have standard Oracle error. This error occurs when user press stop in the middle of database transaction (session didn’t start returning query). Maybe there is posibility that in 4.x query gets big part of rows into grid, and 5x gets only about 250 records, and this is reason of different messages.

Regards Piter


#10

Bruce,

I think I figured it out. I have different clients installed. The default one is Client 10g. The difference in behaviour occurs only if you use 10g client for both Navs. As soon as you switch to 8i client it works as expected. Everything bogs down to OCI version. Regards,
Vadim


#11

Hi Vadim,

Nice catch here. Thanks for the update and info. You are the champ.

It is the Oracle client related isssues here as per your note.

Thanks again for your feedback/info on this.

Will leave it to Roman’s good hand and see if there is anything we can do in Nav to handle 10g client’ behaviour in the same way as other clients.

Thanks and regards,
Bruce


#12

In other way when you have fev separate sqls in single sqleditor, and hit stop afther one of sqls - message view results generated so far is displayed.


#13

Piter,

When you use Oracle 10 client It does not matter how many SQLs you execute it does not work. It’s clearly a regression and quite a big one but related to client version. I can personally get around the problem by forcing 8i client but not many people in the real world have the luxury of having several clients installed. If the problem was related to Oracle 7 client I would have put it to rest but because it’s 10g it would be nice to fix.

Vadim


#14

Vadim,

Have you tried the /+FIRST_ROWS/ hint? I’ve just thought that it may somehow affect the way results are fetched.

Roman


#15

Roman,

Clearly /+FIRST_ROWS/ does not change anything. I am still unable to get partial result via ‘Stop’ in client 10g. Were you able to reproduce ?

Vadim


#16

Hi Vadim,

I would suggest you try the work around for this:

  • Do not turn on the fetch all option in SQL Editor.
  • Execute the sql which Nav will give you a few hundred rows.
  • Then you can get the rest of the rows by click on the last record button on the toolbar of the data grid.
  • There will be a progress bar with cancel button where you can stop when needed.
    Thanks and regards,
    Bruce

#17

Bruce,

Thanks a lot for your suggestion but I am just reporting a regression in functionality which probably needs to be looked at. Your workaround will work but it requires 10 shortcuts as opposed to 1 previously. There are many ways you can achieve the same result but you always use the most efficient way.

Regards,

Vadim


#18

Hi Vadim,

We just would like to make sure that you could use 10g client with the suggested workaround for now until we could find a solution for this. Yeh, know that the workflow would not be as nice as we would like .

We will definitely try to address this for you. However, I have already discussed with Roman on this, it will be a very high risk late changes for 5.5 release to deal with Oracle client files… Roman will tackle this and will get this fix for you in an early beta build post 5.5.

Thanks again for your feedback and your input .

Regards,
Bruce


#19

Bruce,

As long as it’s not put to rest.

Vadim


#20

Hi Vadim,
We will not certainly let this off the radar .
Jaime has raised CR 188136 on your behalf. We will tackle this for your and will try to get this to you in an early beta build post 5.5. We plan to release the 1st post 5.5 Beta build late Sept/Early Oct 06.

Thanks and regards,
Bruce