SQL & PL/SQL
DB Version: 10.2.0.4
Users: TESTUSR (Table Owner)
TESTUSR_PKG (Package Onwer)
TESTUSR_APP (Application Owner)
create table test_tbl(a number);
CREATE OR REPLACE PACKAGE test_pkg AS -- spec
PROCEDURE insert_test_tbl (
PROCEDURE delete_test_tbl (
CREATE OR REPLACE PACKAGE BODY test_pkg AS -- body
PROCEDURE insert_test_tbl (
Why publishing this under SQL instead of RDBMS Server?
The necessaty to create supporting indexes for foreign keys has been explained in other articles.
However writing a foolproof query to identify missing FK indexes is not so straightforward as it might look. I used to do it PLSQL, but this has proven not to scale very well and taking quite some elapsed time and system resources in databases with many thousands of tables.
Another example of what I think of as "the self-tuning database". Setting optimizer_dynamic_sampling=11 can fix many performance problems, without the DBA needing to use his brain at all.
Excellent exposition of a very effective SQL tuning metodology
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.
So, how do I create an external table?
To create an external table in Oracle we use the same CREATE TABLE DDL, but we specify the type of the table as external by an additional clause - ORGANIZATION EXTERNAL.
It's a lazy Friday and I saw an interesting question in Stackoverflow 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.
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.
I’m pleased to report that – as a software developer – the skills map pretty well between the databases.
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.
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.
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".