Interesting Bug
Michael posted a comment on Martin’s blog, that I previously quoted, about a “hack” to add a comment on a default value definition. Here is an example of how it works (tested in 11.2.0.1):
SQL> CREATE TABLE t (
2 x VARCHAR2 (10) DEFAULT 'Hi' -- There. This will be stored too.
3 );
Table created.
Notice the comment after the default value. The comment would be ignored, right? think again:
SQL> COLUMN column_name FORMAT a14
SQL> COLUMN data_default FORMAT a40
SQL> SELECT column_name, data_default
2 FROM user_tab_columns
3 WHERE table_name = 'T';
COLUMN_NAME DATA_DEFAULT
-------------- ----------------------------------------
X 'Hi' -- There. This will be stored too.
1 row selected.
Well, basically this is bug 8546537:
When trying to add a COMMENT to a table, if the ‘)’ is on a new line, then the COMMENT is successfuly created when an error should be raised. The COMMENT also makes it to the Data Dictionary. If the ‘)’ is on the same line, an error is raised.
Interesting!
Leave a comment | Filed in Oracle | Tags: databaseComments on Constraints and Indexes in the Database, a Good Idea?
Martin Widlake thinks so:
Comments are great, you can put 4000 characters of information into the database about each table, view and column. This can be a brief description of the object, a full explanation of what a column is to hold or even a list of typical entries for a column or table. But you can’t add a comment on indexes or constraints. Why would I want to? Well, constraints and indexes should only be there for a reason and the reason is not always obvious from either the names of the columns or the name of the constraint or index, especially where you have a naming standard that forces you to name indexes and constraints after the columns they reference.
He goes on to explain the advantages:
If I was able to add comments to constraints and indexes within the database then they would there. You move the system from one platform to the other, they are there. If for any wildly unlikely reason the central documentation lets you down, the information is always there in the database and easy to check. You may not be able to track down the original design documents but you have the database in front of you, so comments in that will persist and be very easy to find.
Martin raises very good points. I believe that it would be a useful feature to have in a future database version.
2 Comments | Filed in Oracle | Tags: enhancementWhat You May Have Missed This Week: Geek Humor, Cool Developers, Database Design and More
- 11 Things every Software Developer should be doing in 2012
- BI Publisher Licensing Considerations with Oracle E-Business Suite
- Tom Kyte’s Resolutions for 2012
- 91 Ways to become the Coolest Developer in the World
- Do You Make These 5 Database Design Mistakes?
- This jaw-dropping rendering of an iPhone 4 was written completely in CSS3
- 10 programming languages that could shake up IT
- Nine Hot Skills for 2012
- 10 Really Geek Humor Cartoons
- Five Key Ingredients of Essential Test-Driven Development
You Should Attend This
Happy New Year!
One of the events I am looking forward to this year is ODTUG Kscope12. For those of you who have attended Kscope before, you know how much value you can get out of this conference. For those of you who don’t know what Kscope is, here is a quick introduction for you:
If you are a developer, architect, technical lead, or database administrator who works with Application Express, Business Intelligence, Oracle EPM; including Hyperion products, Essbase, Planning; Database Development or Fusion Middleware, Kscope12 is where you should be. It’s hard to find a conference that’s big enough to attract world renowned speakers and small enough to get the chance to share knowledge. Kscope12 is that conference.
This year, I will be presenting about data caching at Kscope12.
1 Comment | Filed in Oracle | Tags: kscope12, odtugInteresting Things You May Have Missed This Week
- 15 top quotes of 2011
- Coding standards: is consistency prettier than freedom?
- Vim: revisited
- How to Think About Estimating
- 30 free programming eBooks
- What is 1e100.net?
- Advanced Bash-Scripting Guide
- Top 50 Programming Quotes of All Time
- Correlation may not imply causation, but it sure can help us insinuate it
- Using the Oracle Developer Days VirtualBox Image
- Bitmap Index vs. B-tree Index: Which and When?
- Relational Databases and Complex Data Types
- BigData University – Learn Hadoop and other Big Data technologies
- Implementing the Enterprise Service Bus Pattern to Expose Database Backed Services
Interesting Things You May Have Missed This Week
- An Introduction To The Most Popular And Least Loved Data Language In The World
- It Is Not Necessary To Drink At Least Eight Glasses Of Water A Day To Stay Properly Hydrated
- What It Looks Like Inside Amazon.com
- 50 Problem Words and Phrases
- The 45 Most Powerful Images Of 2011
- 15 Most Massively Popular Websites You’ve Never Heard Of
- Half Life of a Tech Worker: 15 Years
- How to use Firebug on your iPad and iPhone
- Cloud Computing as a Threat to Older Tech Companies
- Nine things learned from reading The Clean Coder on how professional developers conduct themselves
- Why everyone hates the IT department
- DevOps is not an absolute. It’s a range [Video Presentation]
- Google Used To Ask These Interview Questions, But They Are So Hard They Were Banned
Five Recently Created or Updated Oracle Support Articles
- How To Create Users Like Another User In Oracle Database At SQL Command Line, Not From OEM
- Master Note For Oracle Database Auditing
- How to Monitor Password Change History
- How to Change Online Help in Apex 4.1 Which is a Link to the Online Documentation on OTN
- What is IP=FIRST in the LISTENER.ORA file?
Upgrading from Oracle E-business Suite to Fusion Apps
There is a third issue, after support cost, and availability, there is the big issue around the upgrade path. Oracle have always said that there would be an upgrade path from the latest two releases which at the time was 11.5.10 and 12.0, but now we also have 12.1 so there is only a path from 12.0 onwards. Is this fair, well I am not sure, in most cases I see the move to Fusion Apps as a replacement is not the best thing for an organisation, especially one who has not already moved to R12, so not having the option is not a problem.Leave a comment | Filed in Oracle | Tags: EBS, fusion
[...]
I think a lot of people will take Fusion but in small steps, taking the co-existence route, after perhaps adopting some technology first, over time.
Interesting Things You May Have Missed Today
- Common Errors in English Usage
- Popular myths in software development
- Video and text tutorials for a variety of computer related topics
- REST Vs SOAP web services
- Interactive web based Vim tutorial
- Overview of the Oracle NoSQL Database
- Listening to Mozart Won’t Make You Smarter
- The Most Exciting Oracle OpenWorld and JavaOne Announcements all in One Place
Why You Must Use an ORDER BY Sometimes
Suresh submitted this question on the Oracle Community forum:
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:
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.
By the way, the query in the forum post looks suspiciously Hibernated. Don’t let Jeff know 
