New year 2008

Self Motivation

Oracle - Useful functions

/Create Table using As
CREATE TABLE New_Table_Name AS SELECT * FROM Table_Name WHERE Column1 >100;

SQL> SELECT 5/2 FROM DUAL
2 /

5/2
---------
2.5

SQL> SELECT -5/2 FROM DUAL
2 /

-5/2
---------
-2.5

SQL> SELECT ABS(-5/2) FROM DUAL
2 /

ABS(-5/2)
---------
2.5

SQL> SELECT ROUND(5/2) FROM DUAL;

ROUND(5/2)
----------
3

SQL> SELECT ROUND(-5/2) FROM DUAL;

ROUND(-5/2)
-----------
-3

SQL> SELECT CEIL(5/2) FROM DUAL;

CEIL(5/2)
---------
3

SQL> SELECT FLOOR(5/2) FROM DUAL;

FLOOR(5/2)
----------
2

SQL> SELECT TO_CHAR(SYSDATE) FROM DUAL;

TO_CHAR(S
---------
18-DEC-07

SQL> SELECT TO_DATE('18-Dec-07') FROM DUAL;

TO_DATE('
---------
18-DEC-07

SQL> SELECT TO_NUMBER('143') FROM DUAL;

TO_NUMBER('143')
----------------
143

SQL> SELECT UPPER('bALA') FROM DUAL;

UPPER('BALA')
--------------------------------
BALA

SQL> SELECT LOWER('sANdyA') FROM DUAL;

LOWER('SANDYA')
--------------------------------
sandya

SQL> SELECT SUBSTR('Bala krishnan',6,5) FROM DUAL;

SUBSTR('BALAKRISHNAN',6,5)
--------------------------------
krish

SQL> SELECT SUBSTR('Bala Krishnan',6) FROM DUAL;

SUBSTR('BALAKRISHNAN',6)
--------------------------------
Krishnan

SQL> SELECT NVL(NULL,'0') FROM DUAL;

NVL(NULL,'0')
--------------------------------
0

SQL> SELECT DECODE(NULL,NULL,0,1) FROM DUAL;

DECODE(NULL,NULL,0,1)
---------------------
0

SQL> SELECT LPAD(5,10,'*') FROM DUAL;

LPAD(5,10,'*')
-----------------
*********5

SQL> SELECT RPAD(6,10,'*') FROM DUAL;

RPAD(6,10,'*')
-----------------
6*********

SQL> SELECT LEAST(1,5,2,7) , GREATEST(1,5,2,7) FROM DUAL;
O/P
----
1 7

Oracle some tricks

// Other than SQL statements are not allowed in Oracle procedures. Only trick is Execute immediate
Execute immediate “DDL Commnds such as truncae table and crete table”

//ISQLW editor – to stop the execution of the query which takes long time.
ALT + F, C

//Debug tips
SET SERVER OUTPUT ON SIZE 100000;
DBMS_OUTPUT.Put_line('This is Bala');
SET SERVER OUTPUT OFF;

//GUI Tool for Oracle
TOAD and its free

//To Clear screen
CL SCR

//concatenation operator
SELECT 'Bala ' ' Loves ' 'Sandya' FROM DUAL

//To go to command prompt from a set of statements
/ or .

//To run the last statement or Execute the statements
RUN

//DECODE - If matches then this else that...

SQL>SELECT DECODE('&INP','r','Red color','g','Green color','b','Blue color','Default color') FROM DUAL
Enter value for inp: g
DECODE('G','R','REDCOLOR','G'
--------------------------------
Green color
Enter value for inp: b
DECODE('B','R','REDCOLOR','G'
--------------------------------
Blue color
Enter value for inp: sd
DECODE('SD','R','REDCOLOR','G
--------------------------------
Default color

//NVL - If Null Value Then

SQL> SELECT NVL('&inp','Null text') FROM DUAL;

Enter value for inp:
NVL('','NULLTEXT')
--------------------------------
Null text

Enter value for inp: Bala
NVL('BALA','NULLTEXT')
--------------------------------
Bala

//Cursor
DECLARE CURSOR CURPF IS
 SELECT * FROM Table1
VarCPF CURPF%ROWTYPE;
BEGIN
 FOR VarCPF IN CURPF LOOP
  UPDATE Table2 SET Col1 = VarCPF.col1
  WHERE Col2 = VarCPF.col2;
 END LOOP;
END;


TEST USER
=========
SCOTT
TIGER
DBTEST

LOGIN AS SYSADMIN
=================
sys
*****
DBASE AS SYSDBA


Create and Grant permission
======================

SQL> CREATE USER baladba IDENTIFIED BY ****;

User created.

SQL> GRANT RESOURCE TO baladba;

Grant succeeded.

SQL> GRANT DBA TO baladba;

Grant succeeded.

SQL> GRANT CONNECT TO baladba;

Grant succeeded.

SQL> SELECT * FROM TAB;


//EXECUTE IMMEDIATE

SSQL := ' SELECT SUM('C2.CLASS  ;
ssql := ssql ')  into :p1 from EMP_TAX where payyear = :p2 and emp_no = :p3 ';
EXECUTE IMMEDIATE SSQL into p1 USING '2007','1117';

//Identify min value from different arrays

SELECT MIN(HRA) INTO EXHRA FROM
(SELECT HRA_ITD HRA FROM DUAL
UNION ALL
SELECT HRA_ACT HRA FROM DUAL
UNION ALL
SELECT COREPAY2 HRA FROM DUAL);

//Spool
SQL> SPOOL C:\BALA.SQLSQL> SELECT 10 FROM DUAL;
10--------- 10
SQL> SPOOL OFF

//NLS - Configure for National Language Support
http://www.exzilla.net/docs/nls/oracleNLS01.php
http://download.oracle.com/docs/cd/B19188_01/doc/B15921/nls.htm
http://download-uk.oracle.com/docs/cd/B14117_01/olap.101/b10339/x_monitor018.htm

E-Books Software

Good .net, xml, web services and more..

Links

http://www.vijaymukhi.com
http://www.free-ebooks-download.org
http://www.programmerworld.net/dotnet/books.htm
http://www.dotnetspider.com/qa/Question3052.aspx