Skip navigation.

SQL & PL/SQL

SQL and PL/SQL Articles

Impact on Packages - Assign permissions directly vs Assign permission via Roles

Document depicts how Roles and Privileges behave for Packages

DB Version: 10.2.0.4

Users: TESTUSR (Table Owner)
TESTUSR_PKG (Package Onwer)
TESTUSR_APP (Application Owner)

Roles: TESTUSR_APP_ROLE
TESTUSR_PKG_ROLE

Table: TEST_TBL
Package: TEST_PKG

Table Text:
create table test_tbl(a number);

Package Text:
CREATE OR REPLACE PACKAGE test_pkg AS -- spec
PROCEDURE insert_test_tbl (
a NUMBER);
PROCEDURE delete_test_tbl (
a NUMBER);
END test_pkg;

CREATE OR REPLACE PACKAGE BODY test_pkg AS -- body
PROCEDURE insert_test_tbl (
a

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.

I’m pleased to report that – as a software developer – the skills map pretty well between the databases.

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.

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".

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".

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.

Oracle Pipelined Table Functions

Oracle Pipelined Table Functions
________________________________________
Overview
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.
A PL/SQL function may be used in a data warehouse database to transform large amounts of data. This might also involve massaging the data in a series of transformations, each performed by different functions.

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.

Finding islands – 4 methods in Oracle

Finding islands are classic problems in PL/SQL. The basic concept is that you have some sort of numbers, like these: 1, 2, 3, 5, 6, 8, 9, 10, 15, 20, 21, 22, 23, 25, 26. The islands problem involves identifying ranges of existing values.