Tom is not not a fan of public synonyms, here is why:
- public synonyms pollute the namespace.
- public synonyms can lead to security issues.
- public synonyms can lead to a maintenance headache.
- public synonyms are public – no one owns them.
So, instead of public synonyms… Continue reading…2 Comments | Filed in Oracle | Tags: sql
SQL injection attacks are becoming significantly more popular amongst hackers, according to recent data. Between Q1 2012 and Q2 2012, there has been an estimated 69 percent increase of this attack type.
SQL injection can be easily avoided. How? Just use bind variables. Here is a quote from Tom Kyte:
If you use bind variables, you cannot be SQL Injected – this is true for PL/SQL, for Java, for any and all languages. If you use bind variables you cannot be SQL Injected – period. It is that simple, really and truly.
Tom also links to an excellent paper on this subject written by Bryn Llewellyn: How to write SQL injection proof PL/SQL [PDF].
It baffles me how such a simple and easy solution to such a big security problem is not implemented in the code base of all of these big companies that have recently been hacked.Comments Off | Filed in Oracle, Security | Tags: sql
I think if Oracle had extended its ANSI SQL support beyond SELECT to the other DML commands the argument over whether to use the new syntax would have died out years ago.
It would have helped for sure. I also think that if you work in an environment where ANSI SQL is not the “standard” it is hard to adopt it as your main approach to writing queries.Comments Off | Filed in Oracle | Tags: join, sql
Apparently somewhere around the version 10 of sqlplus they changed the autotrace command to use DBMS_XPLAN.DISPLAY to show the plan of a query run with set autotrace on. But, I’ve never taken advantage of this feature because I’m using a 9.2 version of the Oracle client on my laptop.Comments Off | Filed in Oracle | Tags: sql
I will be presenting a free Webinar, brought to you by ODTUG, on Tuesday, July 17, 9:00 AM – 10:00 AM PDT. I will be talking about and demonstrating SQL and PL/SQL caching techniques that will considerably improve the performance of your queries and programs. The Webinar will cover the following five features:
Click here to register.
The webinar went very well. I thank all of you who attended and ODTUG for hosting it.
Click here to download a zip file containing the following files:
1. Set based processing will likely be much faster than row based processing. Our experiment of processing 100K rows showed row based processing was 3700 times slower than set based processing. Not twice as slower or even 10 times slower… 3700 times slower!Comments Off | Filed in Oracle | Tags: pl/sql, sql
2. Compared to set based processing, row based processing times degrade much quicker than set based processing. That is, row based processing does not scale nearly as well as set based processing. We saw this in that the linear trend line for row based processing was 0.00259 compared to 0.00000 for set based processing.
ALTER TABLE sys.dual ADD (dummy2 VARCHAR2(1));
You never, never never never, as in never ever – touch any of the SYS tables – not to insert into them, not to delete from them, not to update them and especially not to alter them.Comments Off | Filed in Oracle, Tips | Tags: sql
in oracle 10 g , the below query results in alphabetical order , there is no order by caluse. but when i am running the same query in oracle 11g , it gives me in random order. what is the problem here?
People, it is really simple: You want an ordered result set? use ORDER BY.
Tom Kyte wrote about it in this blog post and in his books:
Comments Off | Filed in Oracle | Tags: sql
Do not ever count on the order of rows from a query unless you have an ORDER BY statement on your query!
The only way to retrieve data from the database in some sorted order is to include an ORDER BY on your query. There is no substitute for ORDER BY.
This is a guest post by David Clement, a veteran database engineer, a coworker and a friend of mine. You can find him online at davidclement.org.
Here is an interesting feature that’s worth knowing about because of the potential for strange bugs. When you run a SQL script in the Unix shell, if you use a user-defined exception, you have to take care to prevent failure and success from returning the same result.
The following code snippet shows a normal way to return an error code to the Unix shell from a SQL script. I saved this as return_error_code.sql:3 Comments | Filed in Oracle, Tips | Tags: gotcha, linux, pl/sql, shell, sql, unix