DML, DDL, DCL and TCL in SQL Server

DML

DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database.

Examples: SELECT, UPDATE, INSERT, DELETE statements

DDL

DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database.

Examples: CREATE, ALTER, DROP statements

DCL

DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.

Examples: GRANT, REVOKE statements

TCL

TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.

Examples: COMMIT, ROLLBACK statements

In C# regular expression

using System.Text.RegularExpressions;

string data="ash02389-|,jkhsd";

Regex TestValidData = new Regex("[a-zA-Z0-9-,|.; ]*");
Match TestResult = TestValidData.Match(data);
if (TestResult.ToString()==data)
{
//String valid
}
else
//String not valid
.................................

Split String in MS SQL

CREATE FUNCTION SplitString
(
-- Add the parameters for the function here
@myString varchar(500),
@deliminator varchar(10)
)
RETURNS
@ReturnTable TABLE
(
-- Add the column definitions for the TABLE variable here
[id] [int] IDENTITY(1,1) NOT NULL,
[part] [varchar](50) NULL
)
AS
BEGIN
Declare @iSpaces int
Declare @part varchar(50)

--initialize spaces
Select @iSpaces = charindex(@deliminator,@myString,0)
While @iSpaces > 0

Begin
Select @part = substring(@myString,0,charindex(@deliminator,@myString,0))

Insert Into @ReturnTable(part)
Select @part

Select @myString = substring(@mystring,charindex(@deliminator,@myString,0)+ len(@deliminator),len(@myString) - charindex(' ',@myString,0))


Select @iSpaces = charindex(@deliminator,@myString,0)
end

If len(@myString) > 0
Insert Into @ReturnTable
Select @myString

RETURN
END
GO


Select * From SplitString('Hello John Smith',' ')
select * From SplitString('Hello**John**Smith','**')

Oracle: All procedure wanna Auto Compile ....

Set heading off;
set feedback off;
set echo off;
Set lines 999;
Spool run_invalid.sql
select 'ALTER PROCEDURE ' || OBJECT_NAME || ' COMPILE;'
from DBA_OBJECTS
where STATUS = 'INVALID' AND OWNER='ATLMS2' and OBJECT_TYPE='PROCEDURE';
spool off;
set heading on;
set feedback on;
set echo on;
@run_invalid.sql

Auto database backup from oracle (.bat)

set oracle_sid=orcl
setlocal
for /f "tokens=1-4 delims=/ " %%a in ('date /t') do set DATE=%%c-%%b-%%a
for /f "tokens=1-3 delims=: " %%a in ('time /t') do (
set hours=%%a
set minutes=%%b
set ampm=%%c
)
if {%ampm%}=={AM} if {%hours%}=={12} set hours=00
if {%ampm%}=={PM} (
for /f "delims=0 tokens=*" %%a in ("%hours%") do set hours=%%a
set /a hours+= 12
)
exp atlms2/atlms2@textilefac file=d:\atlmsfac_%date%_%hours%.%minutes%_%ampm%.dmp
log=d:\atlmsfac_%date%_%hours%.%minutes%_%ampm%.log full=y feedback=10000
buffer=2000000 statistics=none
exit

Function of String Split in Oracle

create or replace function str_split(the_list varchar2,the_index number,delim varchar2 := ',')
return varchar2
is
start_pos number;
end_pos number;
begin
if the_index = 1 then
start_pos := 1;
else
start_pos := instr(the_list, delim, 1, the_index - 1);
if start_pos = 0 then
return null;
else
start_pos := start_pos + length(delim);
end if;
end if;
end_pos := instr(the_list, delim, start_pos, 1);

if end_pos = 0 then
return substr(the_list, start_pos);
else
return substr(the_list, start_pos, end_pos - start_pos);
end if;
end str_split;
/

Extra Oracle command?

1.select *from tab;
---output all table name;
2.select OWNER,NAME,TYPE from dba_source
group by OWNER,NAME,TYPE order by OWNER,NAME,TYPE;
--owner=user name
--name= package name
--type=package type(TRIGGER, PROCEDURE, FUNCTION etc)

Oracle Count(*) Return zero?

In Oracle, if a table have no records.
the select count(*) from tab_name;
return no records found.
How u return 0(zero)...........write the command?

select nvl(max(count(*)),0) from tab_namegroup by unique_key;