SQL and PL/SQL Articles

Random Password Generator with a template


Original blog post in here.

Give the password template to this function and get random password. It acts like dbms_random.string() function but takes more than one character as a parameter.

Here is a scenario (template)
- First char must be UPPERCASE
- Second and third one must be NUMBER
- Make fourth a NON-ALPHANUMERIC character
- Fifth one must be LOWERCASE
- Sixth is a NUMBER again
- Seventh is any character

Killing users sessions in oracle


Author JP Vijaykumar Oracle DBA
Date Apr 14th 2010

A detailed discussion on redo, undo and Oracle's read consistency are
beyond the scope of this document.

A detailed discussion on setup, troubleshooting of user connections
in MTS environment is beyond the scope of this document.

Before embarking on a killing spree of Oracle sessions, let us explore
the options, limitations, dos and don'ts.

Dynamic SQL - for newbies



I have been dealing with dynamic SQL for some time. And that is something that is still a mystery for many newcomers (and experienced Oracle guys as well).
Here I am going to tell how you can use dynamic SQL. How, but more important is "why", because when creating a comment you should never describe "how" - you always should describe "why".



SQL> rem
SQL> rem Ejemplo con TRUNCATE:
SQL> rem
SQL> rem Para empezar hay que crear una tabla:
SQL> rem
SQL> create table truncate_example as select * from dba_tables
2 /

Table created.

SQL> rem La tabla tiene muchas líneas:
SQL> rem
SQL> select count(*) from truncate_example
2 /

Case sensitive object naming in PL/SQL



This is my first entry here.
I would like to criticize criticism for case sensitive object naming in Oracle and PL/SQL.

Killing Sessions In Oracle


Author JP Vijaykumar
Date Jan 27th 2009
Modified Jan 29th 2009

One of my procedure is taking long time to complete. I want to kill my job and
re-run the procedure after incorporating HINTS.

Let us explore the options to kill long running jobs.

From the v$session, I found that my session is active.

select username, osuser,sid,serial#,status from v$session where sid=57;

Performanace Issue


I have worked in a table with one timestamp field namely Actiondate. We have updated the sysdate value of inserting time to that field. It contains around 30 lakhs records.

I want to get the records which are inserted a particular day.

I run the following 2 queries..

select * from history
where trunc(actiondate) > trunc(sysdate)
order by docintno


select * from history
where trunc(actiondate) > '01-dec-2009'
order by docintno

Both are taking too much time to give the results.

SQL and PL/SQL Questions


Both SQL Trace and TKPROF help to find the statistics of an SQL statement which could be used for query optimization.

Start Oracle SQLTRACE:

In Oracle, to start an SQLTRACE for the current session, execute:


Stop Oracle SQLTRACE

In Oracle, to stop SQL tracing for the current session, execute:

ALTER SESSION SET sql_trace = false;

Read More

Proc SQL for SAS Programmers


SQL for SAS Programmers - Introduction

What is SQL?

SQL stands for Structured Query Language and was designed for development and maintenance within a Database Management System (DBMS). A DBMS consists of one or more tables of data, typically joined in a hierarchical fashion, and a series of programs for organizing the data.
Typical tasks performed with SQL code include the following:

Killing my Oracle sessions


Many a time, developers ask for procedures to kill there own sessions in the oracle db.
Many of our developers are scattered around the globe in different time zones.
During on-call rotation, developers will be calling me, round the clock, to kill their sessions.


Subscribe to RSS - SQL & PL/SQL