Oracle & SQLPlus

The best ways to run all files from a folder in SqlPLus
Create a file and store all of the file names from the folder:
ls -l > list.sql
Edit and set @ before each file name
vi list.sql
1,$s/^/@/
sqlpus ich/foo@mydb
@list.sql

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)

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 (…)