SQL and PL/SQL Articles

Oracle External Tables


 The Oracle external tables feature allows us to access data in external sources as if it is a table in the database.
 External tables are read-only.
 No data manipulation language (DML) operations is allowed on an external table.
 An external table does not describe any data that is stored in the database.

V$RESERVED_WORDS & X$KWDDEF - Interesting facts!


It's a lazy Friday and I saw an interesting question in Stack Overflow about "Why are there two “null” keywords in Oracle's v$reserved_words view?" And I spent next 2 hours digging into knowing the reason. Here are my findings:

We need to understand how to interpret the view v$reserved _words. To determine whether a particular keyword is reserved in any way, check the RESERVED, RES_TYPE, RES_ATTR, and RES_SEMI columns.

Why SQL Server is confounding the Oracle Index Rebuild debate


After more than 20 years of working with Oracle databases, I have recently found myself using SQL Server for the very first time. Until now, I have been a passive observer in the My-Database-Is-Better-Than-Yours wars, so it’s a pleasant change to be able to finally contribute.

Go native! PL/SQL native compilation


Why use interpreted PL/SQL when native compiled PL/SQL is so much faster? No reason at all - except that interpreted is the default, and most DBAs never change this. They should.

How to generate trace file - SQL Trace and TKPROF in Oracle


It is a frequently asked question in almost all the Oracle forums. There have had been numerous questions/posts regarding "But how to generate the trace file?" Well, it might seem a heck of a task, however, looking it step by step will make you understand that it is actually not that difficult.

Usually, database application developers do not have the required/necessary permissions/privileges to do all the steps that I will mention below. However, most of the steps could be done by application developer. A few steps will need a DBA privilege.

ORA-06503: PL/SQL: Function returned without value


An important thing regarding function, you would agree with me that, at least once a PL/SQL developer must have heard that "A function MUST ALWAYS RETURN a VALUE of proper datatype". Having been said that a million times on various platforms, still developers make this mistake.

SQL*Plus error logging - workaround for ROLLBACK issue


In my previous blog entry SQL*Plus error logging – New feature release 11.1, in my comments I stated an issue SPERRORLOG - Issue with Rollback. Whenever ROLLBACK is issued in the session, the feature fails to log the errors. Thanks to Jacek Gebal for his blog "Oracle Thoughts". I was really impressed by the workaround.

Let's look at the issue.

SQL*Plus error logging – New feature release 11.1


One of the most important things that a developer does apart from just code development is, debugging. Isn’t it? Yes, debugging the code to fix the errors that are raised. But, in order to actually debug, we need to first capture them somewhere. As of now, any application has it’s own user defined error logging table(s).

Imagine, if the tool is rich enough to automatically capture the errors. It is very much possible now with the new SQL*PLus release 11.1

Oracle Pipelined Table Functions


Oracle Pipelined Table Functions
Basically, when you would like a PLSQL (or java or c) routine to be the «source»
of data -- instead of a table -- you would use a pipelined function.
PIPELINED functions will operate like a table.

virtual columns in 11g


virtual columns in 11g
Oracle has supported stored expressions for many years, in views and function-based indexes. Most commonly, views enable us to store and modularise computations and expressions based on their underlying tables' columns. In more recent versions (since around the 8i timeframe), we have been able to index expressions using function-based indexes. Now, with the release of 11g, Oracle enables us to store expressions directly in the base tables themselves as virtual columns.


Subscribe to RSS - SQL & PL/SQL