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