Sorting: Character should come first

When we issue ORDER BY clause Oracle engine sorts data in an ascending order by first bringing all numeric data and then character data. As you can see below:

SELECT id FROM test ORDER BY id;
ID
---
1
2
3
A
B
C

There could be case when we want character data to come first and then numeric data. This can be achieved by simple manipulation of ORDER BY clause as follows:

SELECT id FROM test
ORDER BY DECODE(REGEXP_REPLACE(id, ‘\d+’), null, 1, 0);
ID
---
A
B
C
1
2
3

REGEXP_REPLACE is a regular expression function introduced in Oracle 10g. Here by using this function we are identifying all numeric data and replacing it with NULL. After that using DECODE function all NULLs are replaced with 1 and the remaining character data is replaced with 0. Hay this is only done in ORDER BY field NOT IN SELECT. Now we can ensure that zero (character data) obviously comes before 1 (numeric data in our case).

Loading data into Oracle from Text File

SQLLOADER is a bulk loader utility used to move data from external files into oracle database. Simple example of loading external file is given below.

First you need to create a control file which should look similar to the below example

CONTROL FILE:

OPTIONS ( SKIP=1)
LOAD DATA
INFILE 'C:\TextFile\emp.csv'
BADFILE 'C:\TextFile\emp.bad'
DISCARDFILE 'C:\TextFile\emp.dsc'

INTO TABLE "SCOTT"."EMP"
INSERT FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'TRAILING NULLCOLS
(
EMP_ID sequence(Max, 1),
EMP_NAME,
EMP_DEPT,
EMP_ADDRESS,
EMP_DOB
)

If your .csv file has headers then you need to add SKIP = 1.

You need to make sure that you have emp.csv exist in the given location. Other files like .bad and .dsc will be generated by sqlloader itself, if required.

Now save this control file on any directory (eg. c:\LoadTest\Emp.ctl)

To execute this, Type following command in the command prompt

c:\sqlldr userid=scott/tiger@orcl control=c:\LoadTest\Emp.ctl log=c:\LoadTest\Emp.log

Copying table from one database instance to another without using dblink

Copy to UserId/Pwd@MyDB

create NewTable(field1)

using select * from temp1;

Here MyDB is the TNS name of your database.

Here there is no need to create DBLink.

Querying current value of sequence without incrementing it

As we know when we use SequenceName.NextVal, sequence got increment by whatever increment number is defined while it was created.

Eg.
CREATE SEQUENCE seq1
START WITH 1
INCREMENT BY 1
CACHE 20;


If we try to issue SELECT seq1.CurrVal FROM dual without issuing se1.NextVal. It will give error stating that sequence is not yet defined in this session. But, if we use seq1.NextVal, it will increment sequence by 1 which we don’t want. So here is one easy way for getting this:

ALTER SEQUENCE seq1 NOCACHE;
SELECT last_number FROM user_sequences WHERE sequence_name = ‘seq1’;
ALTER SEQUENCE seq1 CACHE 20; --* or whatever is defined earlier

Loading data into text file from Oracle

set colsetp,
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool oradata.txt

SELECT col1  ','  col2  ','  col3
  FROM tab1
 WHERE col2 = 'XYZ';

spool off

Finding a Leap Year

We all very well know what a leap year is? A year which has 366 days is called a leap year. But how many of us know how do you know which year is a leap year? Do you know which calendar year will have 29 days in the month of February?

Most of us have a very wrong perception of a Leap year. If you ask any person how do you calculate a Leap year? His obvious reply will be divide the year by 4, if it gets divided then it is a leap year. Well that seems to be very simple, so why do we need a function to calculate a leap year?

Actually leap year is some thing more then divide by 4. First you need to check whether a year gets divided by 4, if it gets divided by 4 then ensure that it does not get divided by 100, if it does not get divided by 100 then it is a leap year. But, if it gets divided by 100 then it should also get divided by 400 in order to be a leap year.


This function takes year as an input and returns 1, if it is a leap year and 0 if it is not a leap year.


CREATE OR REPLACE FUNCTION IsLeapYear
(
p_year IN NUMBER
)
RETURN NUMBER

IS

v_chkLeapYr NUMBER;
X NUMBER;

BEGIN

--**Leap Year Calculation

SELECT DISTINCT MOD(p_year, 4) INTO v_chkLeapYr FROM DUAL;

IF v_chkLeapYr = 0 THEN
IF mod(p_year,100) = 0 THEN --* If divided by 4 and not by 100 then LEAP YEAR
IF mod(p_year,400) = 0 THEN --* If divided by 4 and 100 then it should also get divided by 400 then LEAP YEAR
X := 366;
ELSE
X := 365;
END IF;
ELSE
X := 366;
END IF;
ELSE
X := 365;
END IF;

If x = 366 Then
Return (1); --* Lear Year
Else
Return (0);
End If;


END IsLeapYear;

SQL Server: Function vs Stored Procedure

Functions are referred as UDF (User Defined Functions) in SQL Server. There where no UDF till SQL Server 7.0. UDF was first introduced in SQL Server 2000.

Some of the difference between UDF and Stored procedure given in SQL Magazine are as follows:

1) A UDF must return a value-a single result set. A stored procedure can return a value-or even multiple result sets-but doesn't have to.

2) You can use a UDF directly in a SELECT statement as well as in ORDER BY, WHERE, and FROM clauses, but you can't use a stored procedure in a SELECT statement.

3) A UDF can't use a nondeterministic function such as GETDATE(), NEWID(), or RAND(), whereas a stored procedure can use such functions. A nondeterministic function is one that can return a different result given the same input parameters.

4) A UDF can't change server environment variables; a stored procedure can.

5) A UDF always stops execution of T-SQL code when an error occurs, whereas a stored procedure continues to the next instruction if you've used proper error handling code.