|
|
 Blogs
Toad World blogs are a mix of insightful how-tos from Quest experts as well as their commentary on experiences with new database technologies. Have some views of your own to share? Post your comments! Note: Comments are restricted to registered Toad World users.
 Do you have a topic that you'd like discussed? We'd love to hear from you. Send us your idea for a blog topic.
Mar
15
Written by:
StevenFeuersteinTW
Tuesday, March 15, 2011 11:35 AM
I discovered something curious today. Check out the following block; it runs without any compilation or runtime error:
DECLARE
l_char CHAR (1);
CURSOR x
IS
SELECT dummy INTO l_char FROM DUAL;
BEGIN
OPEN x;
FETCH x INTO l_char;
CLOSE x;
END;
Can anyone see why I find it odd and curious? I have sent a note to the PL/SQL Product Manager asking what he thinks. What do you think?
Cheers, Steven
4 comment(s) so far...
Re: Curious PL/SQL Code
The strange thing is that you define an into clause in the cursor. It doesn;t do anything though, but the variable you use MUST be declared or the code will not compile.
I think it is odd that you would even try this ;-). I think the PL/SQL Engine removes the into clause from the cursor, but the compiler doesn't. That is odd. IMHO either they should both use it, or both ignore it.
Kindest regards,
Patrick Barel
By Patch on
Wednesday, March 16, 2011 6:21 AM
|
Re: Curious PL/SQL Code
In terms of it being odd that I would try this, that's easy: I didn't "try" it, I cut-and-pasted it. I had an implicit query in my executable section and needed to switch to an implicit cursor. So I moved the cursor up there, compiled and then noticed the INTO clause.
I checked in with Bryn Llewellyn, PL/SQL Product Manager, and he had this to say:
===========================================
Remember the golden rule: try something in SQL (via SQL*Plus) before assuming that you're seeing a PL/SQL curiosity.
So you might have said that this SQL statement:
select 'Curiosity' "?" into :b from Dual
runs without error in SQL*Plus, and prints out this:
?
---------
Curiosity
To which I'd've said this:
Yes, odd isn't it? But this is an example of an embedded SQL statement. See this extract from 11.2 SQL Language Reference (in the section Types of SQL Statements):
>
In other words, the into is syntactically legal but has no effect when submitted as an ordinary SQL statement. And of course, this is what the PL/SQL compiler does on your behalf when it parses the cursor statement.
Like many things, there must have been a reason for allowing this, taken by people who are no longer with us; but it's too late to change it now.
===========================================
Thanks, Bryn!
By StevenFeuersteinTW on
Wednesday, March 16, 2011 8:09 AM
|
Re: Curious PL/SQL Code
Hi,
It's not related to this code though, I wanted to ask, some time back you developed Qute for PL/SQL code unit testing. Did you discontinue the product or was it renamed to something else. I could find URL for Qute, and neither am I able to find any discontinuation news from you...
By Muscle on
Sunday, March 20, 2011 3:10 PM
|
Re: Curious PL/SQL Code
Yep, not related at all, but that's OK! :-) Qute the caterpillar has transformed itself into an amazing and wonderful butterfly named Quest Code Tester for Oracle.
That is, the automated testing tool for PL/SQL now sold by Quest is dramatically better and more powerful than that early incarnation (Qute).
Check it out - I am certain you will be impressed by all it can do for you.
SF
By StevenFeuersteinTW on
Monday, March 21, 2011 5:08 AM
|
|
|