|
|
 |
Blogs
Toad and Database Commentaries |
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.
|
|
|
|
|
|
Location: Blogs
Steven Feuerstein's Blog
|
|
| StevenFeuersteinTW |
Monday, November 16, 2009 8:54 AM |
I recently published the following puzzle in the ToadWorld newsletter:
Which of the following queries return the names of programs (without duplication) defined in the currently connected schema whose source contains a call to DBMS_OUTPUT.PUT_LINE (assume that this program name does not appear inside comments)?
A. SELECT name FROM USER_DEPENDENCIES
WHERE referenced_name = 'DBMS_OUTPUT.PUT_LINE'
B. SELECT name from ALL_SOURCE
WHERE owner = USER
AND name = 'DBMS_OUTPUT.PUT_LINE'
C. SELECT DISTINCT name from ALL_SOURCE
WHERE INSTR (text, 'DBMS_OUTPUT.PUT_LINE') > 0
D. SELECT DISTINCT name from USER_SOURCE
WHERE INSTR (UPPER (text), 'DBMS_OUTPUT.PUT_LINE') > 0
I believed that (D) was correct. Certainly, A, B and C do not do the trick (click here to see my explanation). But I recently received the following email from Wim de Lange of the Netherlands:
"None of the answers below is correct. (D) could be the solution, but DBMS_OUTPUT.PUT_LINE could be spelled as DBMS_OUTPUT . PUT_LINE (spaces around the dot) and that code will still compile and run. So the spaces are destroying the solution here."
Wim is absolutely correct. You could put spaces between the package name, dot and subprogram name, and the PL/SQL compiler will have no trouble with that code.
Thanks, Wim, for your very close reading of my puzzle, and for reminding me that programming can be a very subtle and nuanced craft. Oh, and also that it is so difficult to come up with all the test cases for a program.
And just to show you that I am not the least bit upset about someone finding an error in my code and writing, Wim is hereby declared another winner of that contest and will receive a Toad World t-shirt in addition to the randomly selected four winners. |
|
| Permalink |
Trackback |
Comments (3)
|
By ThomasKyte on
Monday, November 16, 2009 9:48 AM |
ops$tkyte%ORA11GR2> create or replace procedure demo
2 as
3 begin
4 "DBMS_OUTPUT"."PUT_LINE"( 'hello world' );
5 end;
6 /
would thwart it as well - as would
wrap iname=test.sql oname=test.wrap - just wrap the code
as would
ops$tkyte%ORA11GR2> create or replace synonym dbms_output_mine for dbms_output
2 /
Synonym created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace procedure demo
2 as
3 begin
4 DBMS_OUTPUT_mine."PUT_LINE"( 'hello world' );
5 end;
6 /
as would
ops$tkyte%ORA11GR2> create or replace procedure demo
2 as
3 begin
4 DBMS_OUTPUT.
5
6
7 "PUT_LINE"( 'hello world' );
8 end;
9 /
Procedure created.
and of course:
ops$tkyte%ORA11GR2> create synonym "dbms_output.put_line" for dual
2 /
Synonym created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace procedure demo
2 as
3 begin
4 for x in (select * from "dbms_output.put_line")
5 loop
6 null;
7 end loop;
8 end;
9 /
Procedure created.
would return a false postive....
and then again
ops$tkyte%ORA11GR2> create or replace procedure demo
2 as
3 begin
4 execute immediate replace( 'begin doutput.put_line( ''hello world'' ); end;',
5 'doutput', 'dbms_output' );
6 end;
7 /
Procedure created.
would be missed
;) It is MUCH harder than it looks at first |
|
|
By StevenFeuersteinTW on
Monday, November 16, 2009 11:34 PM |
OK, Tom, you get a t-shirt as well.
No, seriously, thanks for pointing this out. And in my, ahem, defense, the point of the quiz is to point out that you can use data dictionary views to analyze your code. While a developer can certainly make it all but impossible to uncover all instances of a particular program usage (unless you use PL/Scope, a new and exciting Oracle11g feature), most developers aren't playing games like that!
Regards, SF
|
|
|
By FernandoUrea on
Monday, December 14, 2009 3:54 AM |
Hello
one solution can be the use of regular expression function regexp_like
per example
SELECT DISTINCT name from USER_SOURCE WHERE regexp_like (text, 'DBMS_OUTPUT[[:space:]]*\.[[:space:]]*PUT_LINE', 'imn')
Regards |
|
|
|
|
|
|
|
|
|
|