Auto select query bug

[hbar]

Surely someone has posted this little guy:

Say you have a SQL window, and the topmost bit of code is a block of some sort. If you attempt to auto-select a query below that block, it throws this error:

PLS-00103: Encountered the symbol "SELECT"

Here is an example. You try it! It's fun!

BEGIN
dbms_output.put_line('test');
END;

SELECT *
FROM dual;

Auto-select the dual query to see what I mean.

Thanks,

 

[Robert_G]

Hi hbar

Think you're missunderstanding the SQL window.
It's more like a wrapper for _ONE_ statement.
So if you put there something like ...

begin
  DBMS_Output.Put_Line('...');
end;

... it will execute this as an anonymous block.
If you want to display some data, than you can exec ONE SQL Statement:

SELECT *
FROM   Scott.Emp

But you can't put that as one statement to oracle:

BEGIN
dbms_output.put_line('test');
END;

SELECT *
FROM dual;

You'd mix SQL with PL/SQL and your poor little ora DB doesn't know what to do.
I even don't know if the SQL window will except an anonymous block, since it's purpose is to display the result of a SELECT statement.

If you wish to something like that you can put it into a testwindow:

begin
  DBMS_Output.Put_Line('test');

  open :oCur for
    SELECT *
    FROM   Scott.Emp;
end;

Just set the bind variable oCur to type RefCursor and it will refer to your query result.

 

[hbar]

No, actually, I don't believe I have misunderstood it at all, but it seems you have. If I put two queries in an SQL window like this:

select sysdate
from dual;

select sysdate + 1
from dual;

and then hit F8, the query that my cursor is currently in will execute. However, if there is a block at the top of the window like this:

Begin
dbms_output.put_line('now do you get it?');
End;

select sysdate
from dual;

select sysdate + 1
from dual;

I no longer get to make use of the auto-select query feature. By your response, you may have missed my point. And I'll pretend that ref cursor bit was leftovers from your clipboard that you didn't intend to paste in

 

[Robert_G]

You meant the Command Window! (Hoping this time, I got you right )
Same thing: You can't put an usual SELECT within an anonymous block.
But you can close the block and run the SELECT afterwards.

By your response, you may have missed my point. And I'll pretend that ref cursor bit was leftovers from your clipboard that you didn't intend to paste in

There wasn't much sense in this Ref cursor snippet.
I didn't know what you wanted to do, so I thought you want to get query result at a certain timepoint within your code run time.

 

[Joachim Rupik]

Robert,

Please, go to Tools/Preferences/Window Types/SQL Window and check the "AutoSelect statement" then it will work in SQL Window. When that option is not selected you must manually select the whole statement code and then press F8.

hbar,

Probably it is a bug. When you place anonymous block as a last statement you will see, when you press F8, that anonymous block is not completely selected, but auto-select feature for other statements will work!

Greetings,
Joachim Rupik

 

[hbar]

Originally posted by jrupik:
Robert,

Please, go to Tools/Preferences/Window Types/SQL Window and check the "AutoSelect statement" then it will work in SQL Window. When that option is not selected you must manually select the whole statement code and then press F8.

hbar,

Probably it is a bug. When you place anonymous block as a last statement you will see, when you press F8, that anonymous block is not completely selected, but auto-select feature for other statements will work!

Greetings,
Joachim Rupik

Thanks, Joachim! I had a feeling this thread was about to get painful.

So now that I have corroboration that it is a bug, what can be done to fix it?

 

[Marco Kalter]

Currently you can only select the select statement to execute it. We'll fix the AutoSelect bug for 6.0.4.

 

[Robert_G]

Doh! Sorry for that...

 

[Joachim Rupik]

Marco,

In my copy of PLDEV I can select (manually) an anonymous block either, and then I can execute it. Only AutoSelect feature works not properly.

Joachim Rupik