Comparing PL-SQL with TSQL

Recently I came across a project where I need to connect to oracle database for fetching my result set. Having always involved in microsoft technology stack projects, and very familier with the T-SQL, I find working with PL-SQL wasnt that different. Although I had to google for some function names corresponding to sql functions I was comfortable with.

functions comparison between PL-SQL and T-SQL

Function	T-SQL	PL-SQL
Current date	GetDate() GetUTCDate()	Sysdate Current_date
Current timestamp	SysDateTime	Current_TimeStamp systimestamp
Get maximum value of the column	Max()	max()
Get year from date field	Year(DOB)	to_char(DOB, ‘yyyy’)
Get Month from the date field	Datename(mm,DOB) Other formats m, month	to_char(DOB, ‘MON’) Other formats can be MM, MONTH, mon
Get Day of week	Datename(dw, DOB)	to_char(DOB, ‘DAY’)
Specific Date format	SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]	To_char(DOB, ‘dd/mm/yyyy’)
Finding index of (Location of text)	CHARINDEX ( expressionToFind ,expressionToSearch [ , start_location ] ) e.g. CHARINDEX('@','someone@somewhere')	Instr(expressionToSearch, expressionToFind [, start_position [, nth_appearance ] ] ) e.g Instr('someone@somewhere', '@')

Finding tables with given column name

Not in all projects, enough Knowledge transfer is provisioned to understand complete database architecture. In such scenarios, we end up exploring the database tables and columns to understand their relationships or to just find out if such column exists and in which table. In SQL server, we have tables called sys.Tables and sys.Columns and by writing a simple query like below, it can be done.

USE AdventureWorks
GO
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%EmployeeID%'
ORDER BY schema_name, table_name;

After googling and altering the query, I came up with the PL-SQL query as below:

select a.table_name, column_name,DATA_TYPE,DATA_LENGTH from all_tab_columns a,USER_ALL_TABLES u
where a.TABLE_NAME=u.TABLE_NAME
and column_name like 'FAMILY_TYPE%'

[Note: default T-SQL like clause works as case-insensative, while with PL-SQL like clause works as case-sensative]

First Sunday of October month (Daylight saving ending date)

In SQL server, this requires a bit of query while in PL-SQL a simple function is available

Declare @D Datetime
Set @D = DateTime(‘01/10/2013’)
Select DateAdd(day, (8-DatePart(weekday,
DateAdd(Month, 1+DateDiff(Month, 0, @D), 0)))%7,
DateAdd(Month, 1+DateDiff(Month, 0, @D), 0))

With in PL-SQL

Select next_day(to_date('01/10/2013','dd/mm/yyyy'), 'Friday') from dual