Oracle & SQLPlus

SPOOL Command

SPOOL FILENAME Store query results in file
SPOOL OFF Turn off spooling SPOOL OUT sends file to printer

By default, SQL*PLUS doesn’t read what a PL/SQL programm has written with dbms_output. With set serveroutput on, this behavior is changed.

SET serveroutput ON
SET serveroutput OFF
SET serveroutput on size n

IF i=0 THEN Bala Bala
ELSE
dbms_output.put_line( ‘Something Faild!’ );
END IF;

Oracle Datatypes

Number and Numeric Subtypes DEC, DECIMAL FLOAT This ANSI datatype will be accepted by Oracle – It stores zero, positive, and negative floating-point numbers INTEGER This ANSI datatype will be accepted by Oracle – it is actually a synonym for NUMBER(38)

Fixed-point NUMBER NUMBER(p,s) precision p = length of the number in digits scale s = places after the decimal point, or (for negative scale values) significant places before the decimal point. Integer NUMBER NUMBER(p) This is a fixed-point number with precision p and scale 0. Equivalent to NUMBER(p,0) Floating-Point NUMBER NUMBER floating-point number with decimal precision 38ROWID Hexadecimal string representing the unique address of a row in its table. UROWID Hex string representing the logical address of a row of an index-organized table CHAR(size) Fixed length character data of length size bytes. NCHAR(size) Fixed length national character set data of length size bytes. CLOB Character Large Object BLOB Binary Large Object XMLType

Character Datatypes
VARCHAR2:
variables store variable-length character strings. When you declare a variable-length string, you must also specify a maximum length for the string, which can range from 1 to 32767 bytes. The general format for a VARCHAR2 declaration is:
VARCHAR2 ();
as in:
DECLARE
small_string VARCHAR2(4);
line_of_text VARCHAR2(2000);
LONG Character data of variable length (A bigger version the VARCHAR2 datatype)
TIMESTAMP
DATE Valid date range
The standard date format is DD-MMM-YY as in 01-JAN-99.

LAST_DAY
ADD_MONTHS
MONTHS_BETWEEN(’01-NOV-99′,’01-OCT-99′)
SYSDATE

Relational Operators:
There are six relational operators in Oracle, they are
= Equal
<> or != Not Equal
< Less than
<= Less than or equal to
> Greater than
>= Greater than or equal to
like ”
in (‘…’,'…’, …)
between ‘A%’ and ‘D%’
exists (…)