SQL Server- Encryption/Decryption (functions, procedures, triggers and views)

I have made some functions, procedures, triggers and views in SQL Server 2008 its level was encrypted by using “WITH ENCRYPTION”. I was looking to secure my functions, procedures, triggers and views secure so that no one would be able to use it.

After having some sort of working, I came to know that if the procedure or user defined function created “WITH ENCRYPTION”, there is no possible ways to decrypt it by SQL Server commands.

Well, if anyone wants to decrypt it then you will have to use the freeware “Optillect SQL Decryptor”. It will decrypt your functions, procedures, triggers and views.

Just download “Optillect SQL Decryptor” , connect it by using your desired connection and decrypt it whatever you want.

Have a nice Day..!

Some Useful Data Dictionary VIEWS

Table spaces


dba_tablespacesuser_tablespaces 

Tablespace Quotas

dba_ts_quotas
user_ts_quotas
Data Files

dba_data_files
v$backup_datafile
v$datafile
v$datafile_copy
v$datafile_header
Free Space

dba_free_space
Segments

dba_segments
v$segment_statistics
Extents

dba_extents
Blocks

v$database_block_corruption

Groups

dba_tablespace_groups
SYSAUX Tablespace

v_$sysaux_occupants
Temp Tablespace

dba_temp_files
Undo Tablespace

dba_rollback_segs
dba_undo_extents
v$rollstat
v$undostat
Transportable Tablespaces

transport_set_violations

Connect Oracle10g Database with DotNet Framework using C#

Namespaces


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

Button Click Event to Display Data on DataGridView


private void button1_Click(object sender, EventArgs e)
{
string connectionString = "provider=MSDAORA;data source=ORCL;user id=SCOTT;password=TIGER";
OleDbConnection myOleDbConnection = new OleDbConnection(connectionString);
OleDbDataAdapter da = new OleDbDataAdapter("select * from emp",myOleDbConnection );
DataSet ds = new DataSet();
da.Fill(ds, "emp");
dataGridView1.DataSource = ds.Tables["EMP"].DefaultView;
}

DROP TABLE - PURGE

Oracle Database 10g gives you the extra feature to drop your table and put them in the Recycle bin, This feature was introduced in 10g and it's not in earlier versions. Once you drop the table you can use Flashback query to retrieve that table from the Recycle bin.
If you want to remove the table permanently from the database also from Recycle bin then you have to use PURGE (just like shift+delete key in Windows).
And you cannot roll back Drop Table and Purge Clause.

Syntax given below.

Drop Table Table_name Purge;
It'll permanently remove and clears your storage space.

Drop Table Table_name
It will drop the table and send that table into the Recycle Bin.

Flashback Table Table_name to Before drop;
It will retrieve the Table from the Recycle Bin.

And also you can use this query to retrieve from Recycle Bin.

Select original_name, operation, droptime, from recyclebin;
Where Original_name is the table_name
Operation is drop that you did perform
Droptime, At what time you dropped the table.

To Clear the RecycleBin,
PURGE RECYCLEBIN;

Syed Sharique Imam: Different Types of Joins-

Syed Sharique Imam: Different Types of Joins-: "Joins A range of new join syntax are available that comply with the ANSI/ISO SQL: 1999 standards. CROSS JOIN The CROSS JOIN produces a ca..."

Different Types of Joins-

Joins

A range of new join syntax are available that comply with the ANSI/ISO SQL: 1999 standards.

CROSS JOIN

The CROSS JOIN produces a cartesian product.

ANSI/ISO Syntax

select e.ename, d.dname
from emp e cross join dept d;

Existing Syntax
select e.ename, d.dname
from emp e , dept d ;

NATURAL JOIN

The NATURAL JOIN performs a join for all columns with matching names in the two tables.

ANSI/ISO Syntax

select e.ename, d.dname
from emp e natural join dept d;

Existing Syntax

select e.ename, d.dname
from emp e , dept d
where e.deptno = d.deptno;

JOIN ... USING

The USING clause is used if several columns share the same name, but you do not wish to join using all of these common columns. The columns listed in the USING clause cannot have any qualifiers in the statement, including the WHERE clause.


ANSI/ISO Syntax

select e.ename, e.job, d.dname
from emp e join dept d
using (deptno)

Existing Syntax

select e.ename, e.job, d.dname
from emp e , dept d
where e.deptno = d.deptno

Multiple Joins

Multiple Joins are those where more than two tables are joined

ANSI/ISO Syntax

select first_name, last_name, department_name, city
from employees e join departments d on (e.department_id = d.department_id)
join locations l on (d.location_id = l.location_id)

Existing Syntax

select first_name, last_name, department_name, city
from employees e , departments d , locations l
where e.department_id = d.department_id
and d.location_id = l.location_id

OUTER JOIN

There are three variations on the outer join. The LEFT OUTER JOIN returns all the rows from the table on the left side of the join, along with the values from the right hand side, or NULLs if a matching row doesn't exist. The RIGHT OUTER JOIN does the reverse of this. Finally, the FULL OUTER JOIN returns all rows from both tables, filling in any blanks with nulls.


ANSI/ISO Syntax

select l.city, d.department_name
from locations l
left outer join
departments d
on (l.location_id = d.location_id)


Existing Syntax

select l.city, d.department_name
from locations l , departments d
where l.location_id = d.location_id(+)


ANSI/ISO Syntax

select l.city, d.department_name
from locations l
right outer join
departments d
on (l.location_id = d.location_id)


Existing Syntax

select l.city, d.department_name
from locations l , departments d
where l.location_id(+) = d.location_id


ANSI/ISO Syntax

select l.city, d.department_name
from locations l
full outer join
departments d
on (l.location_id = d.location_id)


Existing Syntax

No Equivalent!

Oracle - Date Time Format

SQL> SELECT TO_CHAR (SYSDATE, 'DD-MON-RRRR HH24:MI:SS') FROM DUAL;

TO_CHAR(SYSDATE,'DD-
--------------------
01-FEB-2011 15:24:12

SQL> ED
Wrote file afiedt.buf

1* SELECT SYSDATE, 'DD-MON-RRRR HH24:MI:SS' FROM DUAL
SQL> /

SYSDATE 'DD-MON-RRRRHH24:MI:SS
--------- ----------------------
01-FEB-11 DD-MON-RRRR HH24:MI:SS


1* SELECT TO_CHAR(SYSDATE,'DD-MON-RR') FROM DUAL
SQL> /

TO_CHAR(S
---------
01-FEB-11

SQL> ED
Wrote file afiedt.buf

1* SELECT TO_CHAR(SYSDATE,'DD-MON-RR') "SYSTEM DATE" FROM DUAL
SQL> /

SYSTEM DA
---------
01-FEB-11

SQL> ED
Wrote file afiedt.buf

1* SELECT TO_CHAR(SYSDATE,'DD-MON-RR') "SYS DATE" FROM DUAL
SQL> /

SYS DATE
---------
01-FEB-11

1* SELECT SYSDATE, HIREDATE, MONTHS_BETWEEN(SYSDATE, HIREDATE) "MONTH-B/W" FROM EMP
SQL> /

SYSDATE HIREDATE MONTH-B/W
--------- --------- ----------
01-FEB-11 17-DEC-80 361.504772
01-FEB-11 20-FEB-81 359.407998
01-FEB-11 22-FEB-81 359.343482
01-FEB-11 02-APR-81 357.988643
01-FEB-11 28-SEP-81 352.149934
01-FEB-11 01-MAY-81 357
01-FEB-11 09-JUN-81 355.762837
01-FEB-11 19-APR-87 285.440256
01-FEB-11 17-NOV-81 350.504772
01-FEB-11 08-SEP-81 352.795095
01-FEB-11 23-MAY-87 284.311224
01-FEB-11 03-DEC-81 349.956385
01-FEB-11 03-DEC-81 349.956385
01-FEB-11 23-JAN-82 348.311224

14 rows selected.

SQL> ED
Wrote file afiedt.buf

1* SELECT SYSDATE, HIREDATE, ROUND(MONTHS_BETWEEN(SYSDATE, HIREDATE),0) "MONTH-B/W" FROM EMP
SQL> /

SYSDATE HIREDATE MONTH-B/W
--------- --------- ----------
01-FEB-11 17-DEC-80 362
01-FEB-11 20-FEB-81 359
01-FEB-11 22-FEB-81 359
01-FEB-11 02-APR-81 358
01-FEB-11 28-SEP-81 352
01-FEB-11 01-MAY-81 357
01-FEB-11 09-JUN-81 356
01-FEB-11 19-APR-87 285
01-FEB-11 17-NOV-81 351
01-FEB-11 08-SEP-81 353
01-FEB-11 23-MAY-87 284
01-FEB-11 03-DEC-81 350
01-FEB-11 03-DEC-81 350
01-FEB-11 23-JAN-82 348

14 rows selected.

SQL> ED
Wrote file afiedt.buf

1 SELECT SYSDATE, HIREDATE, ROUND(MONTHS_BETWEEN(SYSDATE, HIREDATE),0) "MONTH-B/W",
2* (SYSDATE-HIREDATE)/30 "MONTHSSS" FROM EMP
SQL> /

SYSDATE HIREDATE MONTH-B/W MONTHSSS
--------- --------- ---------- ----------
01-FEB-11 17-DEC-80 362 366.788296
01-FEB-11 20-FEB-81 359 364.621629
01-FEB-11 22-FEB-81 359 364.554963
01-FEB-11 02-APR-81 358 363.254963
01-FEB-11 28-SEP-81 352 357.288296
01-FEB-11 01-MAY-81 357 362.288296
01-FEB-11 09-JUN-81 356 360.988296
01-FEB-11 19-APR-87 285 289.654963
01-FEB-11 17-NOV-81 351 355.621629
01-FEB-11 08-SEP-81 353 357.954963
01-FEB-11 23-MAY-87 284 288.521629
01-FEB-11 03-DEC-81 350 355.088296
01-FEB-11 03-DEC-81 350 355.088296
01-FEB-11 23-JAN-82 348 353.388296

14 rows selected.

SQL> ED
Wrote file afiedt.buf

1 SELECT SYSDATE, HIREDATE, ROUND(MONTHS_BETWEEN(SYSDATE, HIREDATE),0) "MONTH-B/W",
2* ROUND((SYSDATE-HIREDATE)/30,0) "MONTHSSS" FROM EMP
SQL> /

SYSDATE HIREDATE MONTH-B/W MONTHSSS
--------- --------- ---------- ----------
01-FEB-11 17-DEC-80 362 367
01-FEB-11 20-FEB-81 359 365
01-FEB-11 22-FEB-81 359 365
01-FEB-11 02-APR-81 358 363
01-FEB-11 28-SEP-81 352 357
01-FEB-11 01-MAY-81 357 362
01-FEB-11 09-JUN-81 356 361
01-FEB-11 19-APR-87 285 290
01-FEB-11 17-NOV-81 351 356
01-FEB-11 08-SEP-81 353 358
01-FEB-11 23-MAY-87 284 289
01-FEB-11 03-DEC-81 350 355
01-FEB-11 03-DEC-81 350 355
01-FEB-11 23-JAN-82 348 353

14 rows selected.

SQL> ED
Wrote file afiedt.buf

1 SELECT SYSDATE, HIREDATE, ROUND(MONTHS_BETWEEN(SYSDATE, HIREDATE),0) "MONTH-B/W",
2* ROUND((SYSDATE-HIREDATE)/30,0) "MONTHSSS" FROM EMP
SQL> /

SYSDATE HIREDATE MONTH-B/W MONTHSSS
--------- --------- ---------- ----------
01-FEB-11 17-DEC-80 362 367
01-FEB-11 20-FEB-81 359 365
01-FEB-11 22-FEB-81 359 365
01-FEB-11 02-APR-81 358 363
01-FEB-11 28-SEP-81 352 357
01-FEB-11 01-MAY-81 357 362
01-FEB-11 09-JUN-81 356 361
01-FEB-11 19-APR-87 285 290
01-FEB-11 17-NOV-81 351 356
01-FEB-11 08-SEP-81 353 358
01-FEB-11 23-MAY-87 284 289
01-FEB-11 03-DEC-81 350 355
01-FEB-11 03-DEC-81 350 355
01-FEB-11 23-JAN-82 348 353

1* SELECT SYSDATE, NEXT_DAY(SYSDATE,'WEDNESDAY') FROM DUAL
SQL> /

SYSDATE NEXT_DAY(
--------- ---------
01-FEB-11 02-FEB-11

1* SELECT SYSDATE, LAST_DAY(SYSDATE) FROM DUAL
SQL> /

SYSDATE LAST_DAY(
--------- ---------
01-FEB-11 28-FEB-11

SQL> -- LAST_DAY,, RETURNS LASTDAY OF THE MONTH ----
SQL> SELECT SYSDATE, ADD_MONTHS(SYSDATE, 2) FROM DUAL;

SYSDATE ADD_MONTH
--------- ---------
01-FEB-11 01-APR-11

1* SELECT SYSDATE, ADD_MONTHS(SYSDATE, -2) FROM DUAL
SQL> /

SYSDATE ADD_MONTH
--------- ---------
01-FEB-11 01-DEC-10

SQL> SELECT '12' * 5, '01-FEB-11' FROM DUAL;

'12'*5 '01-FEB-1
---------- ---------
60 01-FEB-11

SQL> ED
Wrote file afiedt.buf

1* SELECT '12' * 5, '01-FEB-11', '3*5' FROM DUAL
SQL> /

'12'*5 '01-FEB-1 '3*
---------- --------- ---
60 01-FEB-11 3*5

SQL> ED
Wrote file afiedt.buf

1* SELECT '12' * 5, '01-FEB-11', 3*'5' FROM DUAL
SQL> /

'12'*5 '01-FEB-1 3*'5'
---------- --------- ----------
60 01-FEB-11 15

SQL> -- CONVERSION FUNCTION,,, 'IGNORES SINGLE CODE'-----
SQL> -- ALSO CALLED IMPLICIT CONVERSION --

1* select SYSDATE, TO_CHAR(SYSDATE, 'D, DD, DDD, DAY, DY, W, WWW') "FORMATS" FROM DUAL
SQL> /

SYSDATE FORMATS
--------- ----------------------------------
01-FEB-11 3, 01, 032, TUESDAY , TUE, 1, 051

SQL> ED
Wrote file afiedt.buf

1* select SYSDATE, TO_CHAR(SYSDATE, 'D, DD, DDD, DAY, DY, W, WWW') "FORMATS" FROM DUAL
SQL> /

SYSDATE FORMATS
--------- ----------------------------------
01-FEB-11 3, 01, 032, TUESDAY , TUE, 1, 051

SQL> ED
Wrote file afiedt.buf

1* select SYSDATE, TO_CHAR(SYSDATE, 'D, DD, DDD, DAY, DY, W, WWW, MM, MON, MONTH, RM') "FORMATS" FROM DUAL
SQL> /

SYSDATE FORMATS
--------- ------------------------------------------------------------
01-FEB-11 3, 01, 032, TUESDAY , TUE, 1, 051, 02, FEB, FEBRUARY , II

SQL> ed
Wrote file afiedt.buf

1* select SYSDATE, TO_CHAR(SYSDATE, 'DD-MM-RRRR SCC,CC') "FORMATS" FROM DUAL
SQL> /

SYSDATE FORMATS
--------- -----------------
01-FEB-11 01-02-2011 21,21

SQL> ED
Wrote file afiedt.buf

1* select SYSDATE, TO_CHAR(SYSDATE, 'DD-MM-RRRR SCC AD,CC B.C.') "FORMATS" FROM DUAL
SQL> /

SYSDATE FORMATS
--------- -------------------------
01-FEB-11 01-02-2011 21 AD,21 A.D.

SQL> -- TIME FORMAT---