Oracle 11g New Features – PL/SQL enhancements Part-II

This is the concluding part of the 2 part series on PL/SQL enhancements in 11g. Here are few more useful enhancements in PL/SQL :-

1. Usage of Named and mixed notation with PL/SQL subprograms in a SQL statement

Prior to 11g, to use a sub-program (e.g. function) in a SELECT statement you have to provide the real parameters in positional notation. Let’s look at an example using both 10g and 11g to understand it better.

Here is a small piece of code which calculates the years of employment of an employee :-
CREATE OR REPLACE FUNCTION cal_employment_duration (
empid (more...)

Oracle 11g New Features – PL/SQL enhancements Part-I

I was going through the enhancements made for PL/SQL in Oracle 11g and learn that there are quite a few. I am going to post on these new features in two parts. Here goes the first part.

1. Usage of sequences in PL/SQL expressions

Allows you to assign a sequence value in an expression that is, you do not need to use a SQL query to generate sequence value and assign it to variable. Here is an example :-

SELECT seq_name.nextval INTO variable FROM dual; –> this was how we used to generate sequence values inside PL/SQL

From 11g (more...)

11g RAC Database hanged or appears to hanged

11g RAC Database hanged or appears to hanged

Around lunch time 1:00 PM database hanged. When we tried to login as sysdba
our sqlplus session also hanged. we not able to login on either of node of RAC.
Thought to kill all the remote connection , so we will able to login

ps -aef |grep LOCAL=NO |awk '{ print $2}' |xargs kill -9

When your database hanged and you not able to login, we have used following procedure
to create hang analysis dump

sqlplus /nolog

SQL> set _prelim on
SQL> connect / as sysdba
SQL> oradebug setmypid
Statement processed.
SQL> (more...)