PL/SQL

Views

Patrick Barel on 05 June 2013 with 0 comments

Success always occurs in private and failure in full public view. – Unknown When working with views you have several options to hide some of the data. Of course there is the possibility not to select certain columns of data in the view. This way, the user of the view doesn’t even have to know this column even exists. You… Continue Reading →

Developing your First Oracle Cloud Application

Morten Egan on 09 May 2013 with 5 comments

Prerequisites A local database, for development SQL Developer version 3.2.20.09.87 or higher (Download) Wallet Manager Getting started To get started, we first need an account on the Oracle cloud. So go to Oracle Cloud, login with your OTN account and request a trial period. You will need to enter a credit card for verification, but it will not get charged… Continue Reading →

SQL’s MEDIAN Function

Jonathan Gennick on 09 April 2013 with 0 comments

Article #3 in my ongoing series covering SQL statistic functions in Oracle Database is now up. The topic is the median: MEDIAN: For When You Don’t Really Mean It Median is useful in typifying a data set when the data might be skewed, or in the presence of extreme outliers. For example, the U.S. Census Bureau reports median household income… Continue Reading →

PL/SQL Subtypes

Patrick Barel on 03 April 2013 with 5 comments

Technology is dominated by two types of people: those who understand what they do not manage, and those who manage what they do not understand – Putt’s Law We are always working with data and their datatypes. In the tables we constrain the data by their datatype. For instance, we create a price column of type NUMBER(4,2) telling the database… Continue Reading →

Jonathan Lewis helps to optimize Red Gate’s Oracle tools

Neil Anderson on 28 January 2013 with 0 comments

Speed, performance, optimization: time is of the essence when you’re waiting for an operation to complete. Our Oracle tools — Schema Compare, Data Compare and now Source Control for Oracle —all need a database model filled with schema objects and their properties. They also need to figure out object dependencies so they can do their jobs with accuracy — all the… Continue Reading →

Automating and delegating datapump imports with PL/SQL

Steve Harville on 20 December 2012 with 0 comments

We recently bought another company and we are in the process of integrating our systems with theirs. One requirement was to import their daily business data into our Business Intelligence system for reporting. We agreed they would provide an Oracle datapump export file for us to import. We wanted to automate this and have a non-privileged application owner perform the… Continue Reading →

Code Instrumentation

Jan Leers on 04 December 2012 with 5 comments

Whenever we execute a procedure, it’s out of our hands. We expect it to do what it was designed to do, but what if it doesn’t? What if it takes a lot more time then expected? All kind of questions start to arise: Is it almost finished, should I wait just a few more minutes? Is it trapped in an… Continue Reading →

Generating XML from SQL and PL/SQL – Part 2

Gert Poel on 29 November 2012 with 16 comments

In part one of “Generating XML from SQL and PL/SQL“, I explained how to generate XML from SQL. In this second part I’ll show you how you can generate XML from table data in PL/SQL. The datatype to hold XML in PL/SQL or in the database is XMLTYPE. So you can use the generated XML in PL/SQL, store it in… Continue Reading →

Edition Based Redefinition – Part 1

Patrick Barel on 25 October 2012 with 0 comments

A process cannot be understood by stopping it. Understanding must move with the flow of the process, must join it and flow with it. – Frank Herbert, Dune (First Law of Mentat) One of the ‘killer’ features of the Oracle Database version 11g Release 2 is probably Edition Based Redefinition. This functionality allows for near zero downtime when performing an… Continue Reading →

PL/SQL Exceptions Propagation during Variable Declaration

Martin Giffy DSouza on 11 September 2012 with 0 comments

It’s always good to know how any language handles and propagates exceptions, Oracle PL/SQL being no different. They’re plenty of examples online about raising and handling exceptions on the web, but one thing you may not have realized is how PL/SQL propagates exceptions that occur in the variable declaration section of a procedure. In the first example I created a… Continue Reading →