General VI Commands In Unix

General VI Commands In Unix

Create/Edit File
vi:
vi filename

Save Command In Unix
:w

Save Existing File Command In Unix
:w!

Save & Exit Command In Unix
:wq

Exit Command In Unix
:q

Exit without Save Command In Unix
:q!

Get Oracle Version ( SQL )

Select Database Version
select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for Solaris: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production  
 


Select Full Product Details
select * from product_component_version;

PRODUCT
----------------------------------------------------------------
VERSION
----------------------------------------------------------------
STATUS
----------------------------------------------------------------
NLSRTL
10.2.0.2.0
Production

Oracle Database 10g Enterprise Edition
10.2.0.2.0
Prod

PRODUCT
----------------------------------------------------------------
VERSION
----------------------------------------------------------------
STATUS
----------------------------------------------------------------

PL/SQL
10.2.0.2.0
Production

TNS for Solaris:
10.2.0.2.0

PRODUCT
----------------------------------------------------------------
VERSION
----------------------------------------------------------------
STATUS
----------------------------------------------------------------
Production




Select Version With Banner

select * from v$version where banner like 'Oracle%';
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod



Select Version With Banner

select * from v$version where banner like 'PL/SQL%';
BANNER
----------------------------------------------------------------
PL/SQL Release 10.2.0.2.0 - Production

Create table In Oracle

Table Creation Simple Example

CREATE TABLE Syntax:
CREATE TABLE TABLENAME (
   field1 data_type,
   field2 data_type,
   ...
   ...
   fieldn data_type
)

here,
Consider
Two Fields;
1) E_ID as number
2) E_Name as varchar(20)

CREATE TABLE Example
CREATE TABLE EMPLOYEE
   (
   E_ID NUMBER,
   E_NAME VARCHAR2(20)
   );

Drop In Oracle

Assume table name is EMPLOYEE

DROP TABLE_NAME;
DROP EMPLOYEE;

Count In Oracle / SQL

Assume table name is EMPLOYEE and it has E_NAME Field 


Count All Records In Table

SELECT COUNT(*) FROM EMPLOYEE;

Count All Records In Table with Head Name

SELECT COUNT(*) AS EMPLOYYE_COUNT FROM EMPLOYEE;

Count Column Records In Table

SELECT COUNT(E_NAME) FROM EMPLOYEE;

Count Distinct Column Records In Table

SELECT COUNT(DISTINCT E_NAME) FROM EMPLOYEE;

Count Selected Records In Table

SELECT COUNT(*) FROM EMPLOYEE WHERE CONDITION E_NAME = 'BOND';

Count Selected Records with Head Name

SELECT COUNT(*) FROM EMPLOYEE NAME_BOND WHERE CONDITION E_NAME = 'BOND';

Delete In Oracle / SQL

Assume table name is EMPLOYEE and it has E_NAME Field
Delete All Rows In Tables

DELETE TABLE_NAME;
DELETE EMPLOYEE;

Delete All Rows In Tables

DELETE FROM TABLE_NAME;
DELETE FROM EMPLOYEE;

Delete Selected Rows In Tables

DELETE FROM TABLE_NAME WHERE CONDITION;
DELETE FROM EMPLOYEE WHERE E_NAME = 'RAJU';

Delete through Select Rows In Tables

DELETE FROM (SELECT * FROM TABLE_NAME WHERE CONDITION);
DELETE FROM (SELECT * FROM EMPLOYEE WHERE E_NAME = 'RAJU');

Trim Example In Oracle PL/SQL

Trim Character
SELECT TRIM('A' FROM 'AAAHAI') FROM DUAL;
OUTPUT:
HAI

Trim Space
SELECT TRIM(' ' FROM 'I AM THIYAGARAAJ') FROM DUAL;
OUTPUT:
I AM THIYAGARAAJ

Trim Character
SELECT TRIM('A' FROM 'AAI AM THIYAGARAAJAAAA') FROM DUAL;
OUTPUT:
I AM THIYAGARAAJ

Trim Space
SELECT TRIM(' ' FROM '        I AM THIYAGARAAJ   ') FROM DUAL;
OUTPUT:
I AM THIYAGARAAJ

While Loop In Oracle

Syntax WHILE Loop In Orcale
WHILE Contitions
   Loop Statements
END LOOP

While Loop Start With 1 to 10
DECLARE
i NUMBER :=0;
BEGIN
WHILE i < 10 LOOP
i:= i+1;
DBMS_OUTPUT.PUT_LINE('Current Number :'||i);
END LOOP;
END;
/

While Loop Start With 10 to 17
DECLARE
i NUMBER :=10;
BEGIN
WHILE i < 17 LOOP
i:= i+1;
DBMS_OUTPUT.PUT_LINE('Current Number :'||i);
END LOOP;
END;
/

While Loop Start & End With variables value
DECLARE
StartValue NUMBER := 10;
EndValue   NUMBER := 20;
BEGIN
WHILE StartValue < EndValue LOOP
StartValue := StartValue + 1;
DBMS_OUTPUT.PUT_LINE('Current Number :'||StartValue);
END LOOP;
END;
/

For Loop In Oracle

Syntax For Loop In Orcale
FOR Contitions
   Loop Statements
END LOOP

Loop Start With 1 to 10
BEGIN
FOR i IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE('Current Number :'||i);
END LOOP;
END;
/

Loop Start With 10 to 17
BEGIN
FOR i IN 10..17 LOOP
DBMS_OUTPUT.PUT_LINE('Current Number :'||i);
END LOOP;
END;
/

Loop Start & End With variables value
DECLARE
StartValue NUMBER := 10;
EndValue   NUMBER := 20;
BEGIN
FOR i IN StartValue .. EndValue LOOP
DBMS_OUTPUT.PUT_LINE('Current Number :'||i);
END LOOP;
END;
/

Get Date & Time From System In Orcale

Get Date From System In Oracle
SELECT SYSDATE FROM DUAL;

Get Date From System In Oracle
SELECT TO_CHAR(SYSDATE,'YYYY MM DD') FROM DUAL;

Get Time From System In Oracle
SELECT TO_CHAR(SYSDATE,'HH MI SS') FROM DUAL;

Get Date From System with Heading In Oracle
SELECT TO_CHAR(SYSDATE,'YYYY MM DD') TODAY_DATE FROM DUAL;

Get Time From System with Heading In Oracle
SELECT TO_CHAR(SYSDATE,'HH MI SS') NOW_TIME FROM DUAL;

Get Time(24 Hours) From System with Heading In Oracle
SELECT TO_CHAR(SYSDATE,'HH24 MI SS') NOW_TIME FROM DUAL;

Get Time(12 Hours) From System with Heading In Oracle
SELECT TO_CHAR(SYSDATE,'HH MI SS') NOW_TIME FROM DUAL;

Get Date & Time(12 Hours) From System with Heading In Oracle
SELECT TO_CHAR(SYSDATE,'YYYY MM DD HH24 MI SS') DAT_TIME FROM DUAL;

Create Sequence On Oracle

You can create for auto increment number/ID creation. Oracle generates sequence of number depend upon our code boundary.

Syntax:

CREATE SEQUENCE SEQUENCE_NAME

        MINVALUE VALUE        ( Assign Minimum Value )

        MAXVALUE VALUE        ( Assign Maximum Value )

        START WITH VALUE    ( Assign Start Value )

        INCREMENT BY VALUE;    ( Assign Increment Value )

For example:

CREATE SEQUENCE DUMMY_SEQUENCE

    MINVALUE 1

    MAXVALUE 10000

    START WITH 1

    INCREMENT BY 1

    CACHE 20;

Usage:

    DUMMY_SEQUENCE.NEXTVAL

Inside Query

INSERT INTO DUMMY_PERSONAL_DTLS

(DUMMY_ID,DUMMY_FNAME,DUMMY_LANME)

VALUES

(DUMMY_SEQUENCE.NEXTVAL,'RAJINI','THIYAGARAAJ');