PLSQL Basics


UNDERSTANDING PL/SQL LANGUAGE BASICS

What Is PL/SQL?
PL/SQL is a modern, block-structured programming language. It provides several features that make developing powerful database applications very convenient. For example, PL/SQL provides procedural constructs, such as loops and conditional statements, that are not available in standard SQL.
PL/SQL code runs on the server, so using PL/SQL lets you centralize significant parts of your database applications for increased maintainability and security. It also enables you to achieve a significant reduction of network overhead in client/server applications.
What Are the Types PL/SQL Code Blocks?
There are 3 types of PL/SQL code blocks:
  • Anonymous Block - A block of codes with no name. It may contain a declaration part, an execution part, and exception handlers.
  • Stored Program Unit - A block of codes with a name. It is similar to an anonymous block. But it can take parameters and return values.
  • Trigger - A block of code that can be defined to fire based an specific event.
How To Define an Anonymous Block?
An anonymous block must have an execution part, which is a group of other PL/SQL statements enclosed in the BEGIN ... END statement. Here is a script on how to define a simple anonymous block with SQL*Plus:
SQL> set serveroutput on;

SQL> begin
  2    dbms_output.put_line('Hello world!');
  3  end;
  4  /
Hello world!

PL/SQL procedure successfully completed.
"set serveroutput on;" allows dbms_output.put_line() to work.
"/" runs the anonymous block, which print the "Hello world!" message.
How Many Anonymous Blocks Can Be Defined?
An anonymous block is stored in the user's current session without any name. So you can only define one anonymous block at any time. If you define another anonymous block, the new block will replace the previously defined block, as shown in the following script:
SQL> set serveroutput on;

SQL> begin
  2    dbms_output.put_line('Hello world!');
  3  end;
  4  /
Hello world!

PL/SQL procedure successfully completed.

SQL> begin
  2    dbms_output.put_line('This is a PL/SQL FAQ.');
  3  end;
  4  /
This is a PL/SQL FAQ.

PL/SQL procedure successfully completed.
How To Run the Anonymous Block Again?
If you have an anonymous block defined in your session, you can run it any time by using the "/" command as shown in the following script:
SQL> set serveroutput on;

SQL> begin
  2    dbms_output.put_line('This is a PL/SQL FAQ.');
  3  end;
  4  /
This is a PL/SQL FAQ.

PL/SQL procedure successfully completed.

SQL> /
This is a PL/SQL FAQ.

PL/SQL procedure successfully completed.
What Is Stored Program Unit?
A stored program unit is a named block of codes which:
  • Has a name.
  • Can take parameters, and can return values.
  • Is stored in the data dictionary.
  • Can be called by many users.
How To Create a Stored Program Unit?
If you want to create a stored program unit, you can use the CREATE PROCEDURE or FUNTION statement. The example script below creates a stored program unit:
SQL> set serveroutput on;

SQL> CREATE PROCEDURE Hello AS
  2  BEGIN
  3    DBMS_OUTPUT.PUT_LINE('Hello world!');
  4  END;
  5  /
Procedure created.
How To Execute a Stored Program Unit?
If you want to execute a stored program unit, you can use the EXECUTE statement. The example script below shows how to executes a stored program unit:
SQL> set serveroutput on;

SQL> CREATE PROCEDURE Hello AS
  2  BEGIN
  3    DBMS_OUTPUT.PUT_LINE('Hello world!');
  4  END;
  5  /
Procedure created.

SQL> EXECUTE Hello;
Hello world!
How Many Data Types Are Supported?
PL/SQL supports two groups of data types:
  • SQL Data Types - All data types used for table columns.
  • PL/SQL Special Data Types - Like BOOLEAN or PLS_INTEGER.
The script below shows some data type examples:
SQL> set serveroutput on;

SQL> DECLARE                                                       
  2    title VARCHAR(8);                                           
  3    salary NUMBER;                                              
  4    seeking_job BOOLEAN;                                        
  5  BEGIN                                                         
  6    title := 'DBA';                                             
  7    salary := 50000;                                            
  8    seeking_job := TRUE;                                        
  9    DBMS_OUTPUT.PUT_LINE('Job Title: ' || title);               
 10    DBMS_OUTPUT.PUT_LINE('Expected salary: '
 11       || TO_CHAR(salary));
 12  END;                                                          
 13  /
Job Title: DBA
Expected salary: 50000
What Are the Execution Control Statements?
PL/SQL supports three groups of execution control statements:
  • IF Statements - Conditionally executes a block of statements.
  • CASE Statements - Selectively executes a block of statements.
  • LOOP Statements - Repeatedly executes a block of statements.
  • GOTO Statements - Unconditional changes the execution flow to a specified statement.
The script below shows execution control statements:
SQL> set serveroutput on;

SQL> DECLARE                                                   
  2    total NUMBER;                                            
  3  BEGIN                                                     
  4    total := 0;                                             
  5    LOOP                                                    
  6      total := total+1;                                      
  7      IF total >= 10 THEN                                   
  8        GOTO print;                                         
  9      END IF;                                               
 10    END LOOP;                                                
 11    <>                                               
 12    DBMS_OUTPUT.PUT_LINE('Total counts: '
 13       || TO_CHAR(total));
 14  END;
 15  /
Total counts: 10
This sample script shows you how to use LOOP, IF, and GOTO statements.
How To Use SQL Statements in PL/SQL?
SQL DML (Data Manipulation Language) statements can be included in PL/SQL code blocks directly without any changes. See the script below for examples:
SQL> CREATE TABLE tip (id NUMBER(5) PRIMARY KEY,
  2    subject VARCHAR(80) NOT NULL,
  3    description VARCHAR(256) NOT NULL);
Table created.

SQL> BEGIN
  2    INSERT INTO tip VALUES(1, 'PL/SQL',
  3       'Good for beginners.');
  4    UPDATE tip SET description = 'Good for beginners.';
  5  END;
  6  /
PL/SQL procedure successfully completed.

SQL> COL subject FORMAT A12;
SQL> COL description FORMAT A24;
SQL> SELECT * FROM tip;
        ID SUBJECT      DESCRIPTION
---------- ------------ -------------------
         1 PL/SQL       Good for beginners.

SQL> DROP TABLE tip;
Table dropped.
This script example actually has 3 parts:
  • Part 1 - A single DDL (Data Definition Language) statement, CREATE TABLE, outside the PL/SQL code block.
  • Part 2 - A PL/SQL code block defined and executed to insert and updated a record.
  • Part 3 - A couple of SQL statements outside the PL/SQL code block to view the record and trop the table.
How To Process Query Result in PL/SQL?
You can run queries (SELECT statements) in a PL/SQL code blocks, and process the results a loop as shown in the following script example:
SQL> set serveroutput on;

SQL> BEGIN
  2     FOR row IN
  3         (SELECT * FROM employees WHERE manager_id = 101)
  4         LOOP
  5       DBMS_OUTPUT.PUT_LINE('Name = ' || row.last_name);
  6     END LOOP;
  7  END;
  8  /
Name = Greenberg
Name = Whalen
Name = Mavris
Name = Baer
Name = Higgins
The loop statement in this script automatically sets a temporary variable "row" each row in the result set returned from the SELECT statement.
How To Create an Array in PL/SQL?
If you want create an array data structure, you can use the collection type VARRAY. VARRAY stands for Variable Array. Here is a sample script on how to use VARRAY:
SQL> set serveroutput on;

SQL> DECLARE
  2    TYPE list IS VARRAY(3) OF VARCHAR(16);
  3    domain_list list;
  4  BEGIN
  5    domain_list := list('google','fyicenter','myspace');
  6    DBMS_OUTPUT.PUT_LINE('Second domain: '
  7       || domain_list(2));
  8  END;
  9  /
Second domain: fyicenter
As you can see, to define an array variable, you need to create your own array type, and use that type to define array variables.
How To Manage Transaction Isolation Level?
Transaction isolation level can be managed in a procedure by using the SET TRANSACTION and COMMIT statements. Here is a sample script on how to manage transaction isolation level:
SQL> CREATE OR REPLACE PROCEDURE HR.TOTAL_SALARY AS
  2    total_salary NUMBER(12,2);
  3  BEGIN
  4    SET TRANSACTION READ ONLY;
  5    SELECT SUM (salary) INTO total_salary FROM employees;
  6    DBMS_OUTPUT.PUT_LINE('Total salary 1: ' 
  7       || total_salary); 
  8    -- Other sessions may change salaries of some records
  9    SELECT SUM (salary) INTO total_salary FROM employees;
 10    DBMS_OUTPUT.PUT_LINE('Total salary 2: ' 
 11       || total_salary);
 12    COMMIT;
 13  END;
 14  /
 
SQL> EXECUTE TOTAL_SALARY;
Total salary 1: 691400
Total salary 2: 691400
"READ ONLY" transaction level takes a read only snapshot of the database. This allows other sessions to update the database without any locks. All queries in the session will produces identical results. So both SELECT statements in this script will return the same value guaranteed.
How To Pass Parameters to Procedures?
Store procedures or functions can take parameters. You need to define parameters while defining the procedure, and providing values to parameters while calling the procedure. The script below shows you how to do this:
SQL> CREATE OR REPLACE PROCEDURE DBA_TASK (day VARCHAR2) AS
  2  BEGIN
  3    IF day = 'MONDAY' THEN
  4      DBMS_OUTPUT.PUT_LINE('Checking log files.');
  5    ELSIF day = 'FRIDAY' THEN
  6      DBMS_OUTPUT.PUT_LINE('Rebuild indexes.');
  7    ELSE
  8      DBMS_OUTPUT.PUT_LINE('Reading some papers.');
  9    END IF;
 10  END;
 11  /
 
SQL> EXECUTE DBA_TASK('MONDAY');
Checking log files.
 
SQL> EXECUTE DBA_TASK('SUNDAY');
Reading some papers.
As you can see, procedures with parameters can make procedures more flexible.
How To Define a Procedure inside Another Procedure?
Define a procedure inside another procedure is supported by PL/SQL. The following tutorial script shows you an example:
SQL> CREATE OR REPLACE PROCEDURE HR.DBA_WEEK AS
  2    PROCEDURE DBA_TASK (day VARCHAR2) AS
  3    BEGIN
  4      IF day = 'MONDAY' THEN
  5        DBMS_OUTPUT.PUT_LINE('Checking log files.');
  6      ELSIF day = 'FRIDAY' THEN
  7        DBMS_OUTPUT.PUT_LINE('Rebuild indexes.');
  8      ELSE
  9        DBMS_OUTPUT.PUT_LINE('Reading some papers.');
 10      END IF;
 11    END;
 12  BEGIN
 13    DBA_TASK('MONDAY');
 14    DBA_TASK('TUESDAY');
 15  END;
 16  /
 
SQL> EXECUTE DBA_WEEK;
Checking log files.
Reading some papers.
Remember that procedures used inside a procedure must be defined in the declaration block.
What Do You Think about PL/SQL?
After following through the tutorials in the FAQ collection, you probably agree that PL/SQL is indeed a general purpose database programming language. PL/SQL is a natural extension of SQL. It is very useful for DBA to automate specific administration tasks or for developers to developer simple business backend 
What Is PL/SQL Language Case Sensitive?
PL/SQL language is not case sensitive:
  • Reserved words are not case sensitive. For example: CASE and Case are identical.
  • Variable names and other names are not case sensitive. For example: TOTAL_SALARY and total_salary are identical.
But values in string literals are case sensitive. For example: 'DBA' and 'dba' are different.
How To Place Comments in PL/SQL?
There are two ways to place comments into PL/SQL codes:
  • SQL Statement Style: Starts you comment any where in the line but prefixed with '--'. The comment ends at the end of the line.
  • C Language Style: Starts you comment any where in the line with '/*' and ends it with '*/' in the same line or some lines below.
Here is some example of PL/SQL comments:
BEGIN
  -- This is a comment
  /* To do:
     Need to write some codes here
  */
END;
What Are the Types PL/SQL Code Blocks?
There are 3 types of PL/SQL code blocks:
  • Anonymous Block - A block of codes with no name. It may contain a declaration part, an execution part, and exception handlers.
  • Stored Program Unit - A block of codes with a name. It is similar to an anonymous block. But it can take parameters and return values.
  • Trigger - A block of code that can be defined to fire based an specific event.
What Is an Anonymous Block?
An anonymous block is a PL/SQL code block with no name. It consists of three parts:
  • Declaration Part - Defining local variables and local procedures. Declaration part is optional.
  • Execution Part - Defining execution logic with executable statements. Execution part is required.
  • Exception Part - Defining error handling logics. Exception part is optional.
Here how a complete anonymous block should look like:
DECLARE
  -- Declaration statements
BEGIN
  -- Executable statements
EXCEPTION
  -- Error handling statements
END;
What Is a Named Program Unit?
A named program unit is a PL/SQL code block with an name. It consists of three parts:
  • Declaration Part - Defining the program unit name, calling parameters, local variables and local procedures. Declaration part is required.
  • Execution Part - Defining execution logic with executable statements. Execution part is required.
  • Exception Part - Defining error handling logics. Exception part is optional.
There are two types of named program units:
  • Procedure - Has no return values.
  • Function - Has a return value.
What Is a Procedure?
A procedure is a named program unit. It consists of three parts:
  • Declaration Part - Defining the procedure name, calling parameters, local variables and local procedures. Declaration part is required.
  • Execution Part - Defining execution logic with executable statements. Execution part is required.
  • Exception Part - Defining error handling logics. Exception part is optional.
Here how a complete procedure should look like:
PROCEDURE name (parameter_1, parameter_2) AS
  -- Declaration statements
BEGIN
  -- Executable statements
EXCEPTION
  -- Error handling statements
END;
What Is a Function?
A function is a named program unit. It consists of three parts:
  • Declaration Part - Defining the function name, calling parameters, return value type, local variables and local procedures. Declaration part is required.
  • Execution Part - Defining execution logic with executable statements. Execution part is required.
  • Exception Part - Defining error handling logics. Exception part is optional.
Here how a complete procedure should look like:
FUNCTION name (parameter_1, parameter_2) RETURN type AS
  -- Declaration statements
BEGIN
  -- Executable statements
  RETURN value;
EXCEPTION
  -- Error handling statements
END;
How To Declare a Local Variable?
A local variable can be defined in the declaration part with a declaration statement, which is a variable name followed a data type identifier. Below are some examples of declaration statements:
PROCEDURE proc_var_1 AS
  domain VARCHAR2(80);
  price REAL;
  is_for_sale CHAR;
BEGIN
  -- Executable statements
END;
How To Initialize Variables with Default Values?
There are two ways to assign default values to variables at the time of declaration:
  • Using key word DEFAULT - Appending "DEFAULT value" to the end of declaration statements.
  • Using assignment operator - Appending ":= value" to the end of declaration statements.
The script below show you some examples of declaration statements with default values:
PROCEDURE proc_var_1 AS
  domain VARCHAR2(80) := 'fyicenter.com';
  price REAL DEFAULT 999999.99;
  is_for_sale CHAR := 'N';
BEGIN
  -- Executable statements
END;
How To Assign Values to Variables?
You can use assignment statements to assign values to variables. An assignment statement contains an assignment operator ":=", which takes the value specified on the right to the variable on left. The script below show you some examples of assignment statements:
PROCEDURE proc_var_2 AS
  is_done BOOLEAN;
  counter NUMBER := 0;
  message VARCHAR2(80);
BEGIN
  is_done := FASLE;
  counter := counter + 1;
  message := 'Hello world!';
END;
What Are the Arithmetic Operations?
There are 4 basic arithmetic operations on numeric values as shown in the following sample script:
PROCEDURE proc_arithmetic AS
  addition NUMBER;
  subtraction NUMBER;
  multiplication NUMBER;
  division NUMBER;
BEGIN
  addition := 7 + 8;
  subtraction := addition - 7;
  multiplication := subtraction * 5;
  division := multiplication / 8;
  -- division should contain 5 now
END;
What Are the Numeric Comparison Operations?
PL/SQL supports 6 basic numeric comparison operations as shown in the following sample script:
PROCEDURE proc_comparison AS
  res BOOLEAN;
BEGIN
  res := 1 = 2;
  res := 1 < 2;
  res := 1 > 2;
  res := 1 <= 2;
  res := 1 >= 2;
  res := 1 <> 2;
  -- more statements
END;
What Are the Logical Operations?
PL/SQL supports 3 logical operations as shown in the following sample script:
PROCEDURE proc_comparison AS
  x BOOLEAN := TRUE;
  y BOOLEAN := FALSE;
  res BOOLEAN;
BEGIN
  res = x AND y;
  res = x OR y;
  res = NOT x;
  -- more statements
END;
How Many Categories of Data Types?
PL/SQL data types are grouped into 4 categories:
  • Scalar Data Types: A scalar data type holds a single value.
  • Composite Data Types: A composite data type has internal components, such as the elements of an array.
  • LOB Data Types: A LOB data type holds a lob locator that specify the location of a large object.
  • Reference Data Types: A reference data type holds a pointer that points to another data object.
How Many Scalar Data Types Are Supported in PL/SQL?
PL/SQL supports many scalar data types divided into 4 groups:
  • Numeric Types: BINARY_DOUBLE, BINARY_FLOAT, BINARY_INTEGER, DEC, DECIMAL, DOUBLE PRECISION, FLOAT, INT, INTEGER, NATURAL, NATURALN, NUMBER, NUMERIC, PLS_INTEGER, POSITIVE, POSITIVEN, REAL, SIGNTYPE, SMALLINT.
  • Character Types: CHAR, CHARACTER, LONG, LONG RAW, NCHAR, NVARCHAR2, RAW, ROWID, STRING, UROWID, VARCHAR, VARCHAR2.
  • Boolean Types: BOOLEAN.
  • Date Types: DATE, TIMESTAMP, TIMESTAMP WITH TIMEZONE, TIMESTAMP WITH LOCAL TIMEZONE, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND.
How To Convert Character Types to Numeric Types?
You can convert character types to numeric types in two ways:
  • Explicitly by using TO_NUMBER() function.
  • Implicitly by putting character data in a numeric operation.
The sample script below shows you how to convert character types to numeric types:
PROCEDURE proc_convert_1 AS
  start_time CHAR(5);
  finish_time CHAR(5);
  elapsed_time NUMBER(5);
BEGIN
  start_time := '12052';
  finish_time := '15314';
  elapsed_time := TO_NUMBER(finish_time)
    - TO_NUMBER(start_time);
  elapsed_time := finish_time - start_time; -- same as above
END;

What Are the Execution Control Statements?
PL/SQL supports three groups of execution control statements:
  • IF Statements - Conditionally executes a block of statements.
  • CASE Statements - Selectively executes a block of statements.
  • LOOP Statements - Repeatedly executes a block of statements.
  • GOTO Statements - Unconditional changes the execution flow to a specified statement.
The script below shows some execution control statements:
DECLARE
  total NUMBER;
BEGIN
  total := 0;
  LOOP
    total := total+1;
    IF total >= 10 THEN
      GOTO print;
    END IF;
  END LOOP;
  <>
  DBMS_OUTPUT.PUT_LINE('Total counts: ' || TO_CHAR(total));
END;
This script should print this:
Total counts: 10
`
How To Use "IF" Statements on Multiple Conditions?
If you have multiple blocks of codes to be executed based on different conditions, you can use the "IF ... ELSIF" statement. Here is a sample script on IF statements:
DECLARE
  day VARCHAR2;
BEGIN
  day := 'SUNDAY';
  IF day = 'THURSDAY' THEN
    DBMS_OUTPUT.PUT_LINE('Checking log files.');
  ELSIF day = 'TUESDAY' THEN
    DBMS_OUTPUT.PUT_LINE('Helping developers.');
  ELSIF day = 'FRIDAY' THEN
    DBMS_OUTPUT.PUT_LINE('Rebuild indexes.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Reading some papers.');
  END IF;
END;
This script should print this:
Reading some papers.
How To Use "WHILE" Statements?
If you have a block of codes to be executed repeatedly based a condition, you can use the "WHILE ... LOOP" statement. Here is a sample script on WHILE statements:
DECLARE
  total NUMBER;
BEGIN
  total := 0;
  WHILE total < 10 LOOP
    total := total+1;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Total counts: ' || TO_CHAR(total));
END;
This script should print this:
Total counts: 10
How To Use "FOR" Statements?
If you have a block of codes to be executed repeatedly over a range of values, you can use the "FOR ... LOOP" statement. Here is a sample script on FOR statements:
DECLARE
  total NUMBER := 0;
BEGIN
  FOR i IN 1..10 LOOP
    total := total + i;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Total: ' || TO_CHAR(total));
END;
Note that temporary variable "i" used in the FOR loop needs no declaration. This script should print this:
Total: 55
What Is NULL in PL/SQL?
NULL is a reserved key word and it stands for two things in PL/SQL:
  • NULL is an executable statement, and means doing nothing.
  • NULL is a data balue, and means no value.
The following sample script shows you examples of using NULL keyword:
DECLARE
  next_task CHAR(80);
BEGIN
  next_task := NULL; -- NULL value

  IF next_task IS NOT NULL THEN
     DBMS_OUTPUT.PUT_LINE('I am busy.');
  ELSE
     DBMS_OUTPUT.PUT_LINE('I am free.');
  END IF;
  IF next_task IS NULL THEN
     NULL; -- NULL statement
  ELSE
     DBMS_OUTPUT.PUT_LINE('... working on ' || next_task);
  END IF;
END;
This script should print this:
I am free.
How To Test NULL Values?
There ate two special comparison operators you can use on NULL values:
  • "variable IS NULL" - Returns TRUE if the variable value is NULL.
  • "variable IS NOT NULL" - Return TRUE if the variable value is not NULL.
The following sample script shows you examples of comparing NULL values:
DECLARE
  next_task CHAR(80);
BEGIN
  next_task := NULL;

  IF next_task IS NOT NULL THEN
     DBMS_OUTPUT.PUT_LINE('I am busy.');
  ELSE
     DBMS_OUTPUT.PUT_LINE('I am free.');
  END IF;
  IF next_task IS NULL THEN
     NULL;
  ELSE
     DBMS_OUTPUT.PUT_LINE('... working on ' || next_task);
  END IF;
END;
Note that "variable = NULL" is not a valid operation. This script should print this:
I am free.
================================================================================================================================================================================================================

Introduction to Command-Line SQL*PLUS Client Tool
What Is SQL*Plus?
SQL*Plus is an interactive and batch query tool that is installed with every Oracle Database Server or Client installation. It has a command-line user interface, a Windows Graphical User Interface (GUI) and the iSQL*Plus web-based user interface.
SQL*Plus has its own commands and environment, and it provides access to the Oracle Database. It enables you to enter and execute SQL, PL/SQL, SQL*Plus and operating system commands to perform the following:
  • Format, perform calculations on, store, and print from query results
  • Examine table and object definitions
  • Develop and run batch scripts
  • Perform database administration
You can use SQL*Plus to generate reports interactively, to generate reports as batch processes, and to output the results to text file, to screen, or to HTML file for browsing on the Internet. You can generate reports dynamically using the HTML output facility of SQL*Plus, or using the dynamic reporting capability of iSQL*Plus to run a script from a web page.
How To Start the Command-Line SQL*Plus?
If you Oracle server or client installed on your windows system, you can start the command-line SQL*Plus in two ways:
1. Click Start > All Programs > Oracle ... > Start SQL Command Line. The SQL*Plus command window will show up with a message like this:
SQL*Plus: Release 10.2.0.1.0 - Production on Tue ...

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL>
2. Click Start > Run..., enter "cmd" and click OK. A Windows command window will show up. You can then use Windows commands to start the command-line SQL*Plus as shown in the tutorial exercise below:
>cd c:\oraclexe\app\oracle\product\10.2.0\server\
>.\bin\sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Tue ...

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL>
How To Get Help at the SQL Prompt?
Once SQL*Plus is started, you will get a SQL prompt like this: SQL>. This where you can enter commands for SQL*Plus to run.
To get help information at the SQL prompt, you can use the HELP command as shown in the following tutorial example:
SQL> HELP INDEX

Enter Help [topic] for help.

 @             COPY         PAUSE       SHUTDOWN
 @@            DEFINE       PRINT       SPOOL
 /             DEL          PROMPT      SQLPLUS
 ACCEPT        DESCRIBE     QUIT        START
 APPEND        DISCONNECT   RECOVER     STARTUP
 ARCHIVE LOG   EDIT         REMARK      STORE
 ATTRIBUTE     EXECUTE      REPFOOTER   TIMING
 BREAK         EXIT         REPHEADER   TTITLE
...
 COMPUTE       LIST         SET         XQUERY
 CONNECT       PASSWORD     SHOW

SQL> HELP CONNECT

 CONNECT
 -------

 Connects a given username to the Oracle Database. When you
 run a CONNECT command, the site profile, glogin.sql, and
 the user profile, login.sql, are processed in that order.
 CONNECT does not reprompt for username or password if the
 initial connection does not succeed.

 CONN[ECT] [{logon|/|proxy} [AS {SYSOPER|SYSDBA}]]

 where logon has the following syntax:
     username[/password][@connect_identifier]
What Information Is Needed to Connect SQL*Plus an Oracle Server?
If you want to connect your SQL*Plus session to an Oracle server, you need to know the following information about this server:
  • The network hostname, or IP address, of the Oracle server.
  • The network port number where the Oracle server is listening for incoming connections.
  • The name of the target database instance managed by the Oracle server.
  • The name of your user account predefined on in the target database instance.
  • The password of your user account predefined on in the target database instance.
What Is a Connect Identifier?
A "connect identifier" is an identification string of a single set of connection information to a specific target database instance on a specific Oracle server.
Connect identifiers are defined and stored in a file called tnsnames.ora located in $ORACLE_HOME/network/admin/ directory. Here is one example of a "connect identifier" definition:
FYI_XE =
  (DESCRIPTION =
    (ADDRESS =
      (PROTOCOL = TCP)
      (HOST = dba.fyicenter.com)
      (PORT = 1521)
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )
The above "connect identifier" defines "TNS_XE" with the following connection information:
  • The network hostname: dba.fyicenter.com.
  • The network port number: 1521.
  • The name of the target database instance: XE.
How To Connect a SQL*Plus Session to an Oracle Server?
In order to connect a SQL*Plus session to an Oracle server, you need to:
1. Obtain the connection information from the Oracle server DBA.
2. Define a new "connect identifier" called "FYI_XE" in your tnsnames.org file with the given connection information.
3. Run the CONNECT command in SQL*Plus as shown in the tutorial exercise below:
>cd c:\oraclexe\app\oracle\product\10.2.0\server\
>.\bin\sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Tue ...
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> CONNECT fyi/retneciyf@FYI_XE;
Connected.

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
---------
05-MAR-06
What Happens If You Use a Wrong Connect Identifier?
Of course, you will get an error, if you use a wrong connect identifier. Here is an example of how SQL*Plus react to a wrong connect identifier:
SQL> CONNECT fyi/retneciyf@WRONG;
ERROR:
ORA-12154: TNS:could not resolve the connect identifier
   specified

Warning: You are no longer connected to ORACLE.
What you need to do in this case:
  • Check the CONNECT command to make sure that the connect identifier is entered correctly.
  • Check the tnsnames.ora file to make sure that the connect identifier is defined correctly.
  • Check the tnsnames.ora file to make sure that there is no multiple definitions of the same connect identifier.
  • Check your files system to see if you have multiple copies of tnsnames.ora in different Oracle home directories, because you installed multiple versions of Oracle. If you do have multiple copies, make sure your SQL*Plus session is picking up the correct copy of tnsnames.ora.
What To Do If DBA Lost the SYSTEM Password?
If the DBA lost the password of the SYSTEM user account, he/she can go to the Oracle server machine, and run SQL*Plus on server locally with the operating system authentication method to gain access to the database. The tutorial exercise below shows you how:
(Terminal server to the Oracle server machine)
(Start SQL*Plus)
SQL>CONNECT / AS SYSDBA
Connected.

SQL> ALTER USER SYSTEM IDENTIFIED BY ssap_iyf;
User altered.
Notice that the (/) in the CONNECT command tells SQL*Plus to use the current user on local operating system as the connection authentication method.
What Types of Commands Can Be Executed in SQL*Plus?
There are 4 types of commands you can run at the SQL*Plus command line prompt:
1. SQL commands - Standard SQL statements to be executed on target database on the Oracle server. For example: "SELECT * FROM fyi_faq;" is a SQL command.
2. PL/SQL commands - PL/SQL statements to be executed by the Oracle server. For example: "EXECUTE DBMS_OUTPUT.PUT_LINE('Welcome to dba.fyicenter.com')" runs a PL/SQL command.
SQL*Plus commands - Commands to be executed by the local SQL*Plus program itself. For example: "SET NULL 'NULL'" is a SQL*Plus command.
OS commands - Commands to be executed by the local operating system. For example: "HOST dir" runs an operating system command on the local machine.
How To Run SQL Commands in SQL*Plus?
If you want to run a SQL command in SQL*Plus, you need to enter the SQL command in one or more lines and terminated with (;). The tutorial exercise below shows a good example:
SQL> SELECT 'Welcome!' FROM DUAL;

'WELCOME
--------
Welcome!

SQL> SELECT 'Welcome to FYIcenter.com tutorials!'
  2  FROM DUAL
  3  ;

'WELCOMETOFYICENTER.COMTUTORIALS!'
-----------------------------------
Welcome to FYIcenter.com tutorials!
How To Run PL/SQL Statements in SQL*Plus?
If you want to run a single PL/SQL statement in SQL*Plus, you need to use the EXECUTE command as shown in the following tutorial example:
SQL> SET SERVEROUTPUT ON

SQL> EXECUTE DBMS_OUTPUT.PUT_LINE('Welcome to FYIcenter!')
Welcome to FYIcenter!

PL/SQL procedure successfully completed.
How To Change SQL*Plus System Settings?
SQL*Plus environment is controlled a big list of SQL*Plus system settings. You can change them by using the SET command as shown in the following list:
  • SET AUTOCOMMIT OFF - Turns off the auto-commit feature.
  • SET FEEDBACK OFF - Stops displaying the "27 rows selected." message at the end of the query output.
  • SET HEADING OFF - Stops displaying the header line of the query output.
  • SET LINESIZE 256 - Sets the number of characters per line when displaying the query output.
  • SET NEWPAGE 2 - Sets 2 blank lines to be displayed on each page of the query output.
  • SET NEWPAGE NONE - Sets for no blank lines to be displayed on each page of the query output.
  • SET NULL 'null' - Asks SQL*Plus to display 'null' for columns that have null values in the query output.
  • SET PAGESIZE 60 - Sets the number of lines per page when displaying the query output.
  • SET TIMING ON - Asks SQL*Plus to display the command execution timing data.
  • SET WRAP OFF - Turns off the wrapping feature when displaying query output.
How To Look at the Current SQL*Plus System Settings?
If you want to see the current values of SQL*Plus system settings, you can use the SHOW command as shown in the following tutorial exercise:
SQL> SHOW AUTOCOMMIT
autocommit OFF

SQL> SHOW HEADING
heading ON

SQL> SHOW LINESIZE
linesize 80

SQL> SHOW PAGESIZE
pagesize 14

SQL> SHOW FEEDBACK
FEEDBACK ON for 6 or more rows

SQL> SHOW TIMING
timing OFF

SQL> SHOW NULL
null ""

SQL> SHOW ALL
appinfo is OFF and set to "SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autorecovery OFF
autotrace OFF
blockterminator "." (hex 2e)
cmdsep OFF
colsep " "
compatibility version NATIVE
concat "." (hex 2e)
copycommit 0
COPYTYPECHECK is ON
define "&" (hex 26)
describe DEPTH 1 LINENUM OFF INDENT ON
echo OFF
...
What Are SQL*Plus Environment Variables?
Behaviors of SQL*Plus are also controlled a some environment variables predefined on the local operating system. Here are some commonly used SQL*Plus environment variables:
  • ORACLE_HOME - The home directory where your Oracle client application is installed.
  • PATH - A list of directories where SQL*Plus will search for executable or DLL files. PATH should include $ORACLE_HOME\bin.
  • SQLPLUS - The directory where localization messages are stored. SQLPLUS should be set to $ORACLE_HOME\sqlplus\mesg
  • TNS_ADMIN - The directory where the connect identifier file, tnsnames.ora is located. TNS_ADMIN should be set to $ORACLE_HOME/network/admin.
How To Generate Query Output in HTML Format?
If you want your query output to be generated in HTML format, you can use the "SET MARKUP HTML ON" to turn on the HTML feature. The following tutorial exercise gives you a good example:
SQL> connect HR/retneciyf

SQL> SET MARKUP HTML ON

SQL> SELECT FIRST_NAME, LAST_NAME, HIRE_DATE

  2  FROM EMPLOYEES WHERE FIRST_NAME LIKE 'Joh%';



















FIRST_NAME

LAST_NAME

HIRE_DATE

John

Seo

12-FEB-98

John

Russell

01-OCT-96


What Is Output Spooling in SQL*Plus?
The output spooling a nice feature of the command-line SQL*Plus tool. If the spooling feature is turned on, SQL*Plus will send a carbon copy of the everything on your screen to a specified local file.
Output spooling is used mostly for quick dump of data to local files. Here are the commands to turn on and off output spooling in SQL*Plus:
  • SPOOL fileName - Turning on output spooling with the specified file.
  • SPOOL OFF - Turning off output spooling and close the spool file.
How To Save Query Output to a Local File?
Normally, when you run a SELECT statement in SQL*Plus, the output will be displayed on your screen. If you want the output to be saved to local file, you can use the "SPOOL fileName" command to specify a local file and start the spooling feature. When you are done with your SELECT statement, you need to close the spool file with the "SPOOL OFF" command. The following tutorial exercise gives you a good example:
SQL> connect HR/retneciyf

SQL> SET HEADING OFF
SQL> SET FEEDBACK OFF
SQL> SET LINESIZE 1000
SQL> SPOOL \temp\employees.lst
SQL> SELECT * FROM EMPLOYEES;
......
SQL> SPOOL OFF
You should get all records in employees.lst with fixed length fields.
What Is Input Buffer in SQL*Plus?
Input buffer is a nice feature of the command-line SQL*Plus tool. It allows you to revise a multiple-line command and re-run it with a couple of simple commands. By default, input buffer is always turned on in SQL*Plus. The last SQL statement is always stored in the buffer. All you need is to remember to following commonly used commands:
  • LIST - Displays the SQL statement (the last executed SQL statement) in the buffer.
  • RUN - Runs the SQL statement in the buffer again. ";" is a quick command equivalent to RUN.
  • CLEAR BUFFER - Removes the SQL statement in the buffer.
  • INPUT line - Adds a new line into the buffer.
  • APPEND text - Appends more text to the last line in the buffer.
  • DEL - Deletes one line from the buffer.
  • CHANGE /old/new - Replaces 'old' text with 'new' text in the buffer.
How To Revise and Re-Run the Last SQL Command?
If executed a long SQL statement, found a mistake in the statement, and you don't want enter that long statement again, you can use the input buffer commands to the correct last statement and re-run it. The following tutorial exercise gives you a good example:
SQL> connect HR/retneciyf

SQL> SELECT FIRST_NAME, LAST_NAME, HIRE_DATE
  2  FROM EMPLOYEE WHERE FIRST_NAME LIKE 'Joh%';
FROM EMPLOYEE WHERE FIRST_NAME LIKE 'Joh%'
     *
ERROR at line 2:
ORA-00942: table or view does not exist

SQL> LIST
  1  SELECT FIRST_NAME, LAST_NAME, HIRE_DATE
  2* FROM EMPLOYEES WHERE FIRST_NAME LIKE 'Joh%'

SQL> CHANGE /EMPLOYEE/EMPLOYEES/
  2* FROM EMPLOYEES WHERE FIRST_NAME LIKE 'Joh%'

SQL> RUN
(Query output)

SQL> INPUT ORDER BY FIRE_DATE
SQL> LIST
  1  SELECT FIRST_NAME, LAST_NAME, HIRE_DATE
  2  FROM EMPLOYEE WHERE FIRST_NAME LIKE 'Joh%'
  3* ORDER BY HIRE_DATE

SQL> RUN
(Query output)

SQL> CLEAR BUFFER
buffer cleared

SQL> LIST
SP2-0223: No lines in SQL buffer.
How Run SQL*Plus Commands That Are Stored in a Local File?
If you have a group of commands that you need to run them repeatedly every day, you can save those commands in a file (called SQL script file), and using the "@fileName" command to run them in SQL*Plus. If you want to try this, create a file called \temp\input.sql with:
SELECT 'Welcome to' FROM DUAL;
SELECT 'FYIcenter.com!' FROM DUAL;
Then run the "@" command in SQL*Plus as:
SQL> connect HR/retneciyf

SQL> @\temp\input.sql

'WELCOMETO
----------
Welcome to


'FYICENTER.COM
--------------
FYIcenter.com!
How To Use SQL*Plus Built-in Timers?
If you don't have a stopwatch/timer and want to measure elapsed periods of time, you can SQL*Plus Built-in Timers with the following commands:
  • TIMING - Displays number of timers.
  • TIMING START [name] - Starts a new timer with or without a name.
  • TIMING SHOW [name] - Shows the current time of the named or not-named timer.
  • TIMING STOP [name] - Stops the named or not-named timer.
The following tutorial exercise shows you a good example of using SQL*Plus built-in timers:
SQL> TIMING START timer_1

(some seconds later)
SQL> TIMING START timer_2

(some seconds later)
SQL> TIMING START timer_3

(some seconds later)
SQL> TIMING SHOW timer_1
timing for: timer_2
Elapsed: 00:00:19.43

(some seconds later)
SQL> TIMING STOP timer_2
timing for: timer_2
Elapsed: 00:00:36.32

SQL> TIMING
2 timing elements in use
What Is Oracle Server Autotrace?
Autotrace is Oracle server feature that generates two statement execution reports very useful for performance tuning:
  • Statement execution path - Shows you the execution loop logic of a DML statement.
  • Statement execution statistics - Shows you various execution statistics of a DML statement.
To turn on the autotrace feature, the Oracle server DBA need to:
  • Create a special table called PLAN_TABLE.
  • Create a special security role called PLUSTRACE.
  • Grant PLUSTRACE role your user account.
·         How To Set Up Autotrace for a User Account?
·         If an Oracle user wants to use the autotrace feature, you can use the tutorial as an example to create the required table PLAN_TABLE, the required security role PLUSTRACE, and grant the role to that user:
·         SQL> CONNECT HR/retneciyf
·          
·         SQL> @\oraclexe\app\oracle\product\10.2.0\server
·           \RDBMS\ADMIN\UTLXPLAN.SQL
·         Table (HR.PLAN_TABLE) created.
·          
·         SQL> CONNECT / AS SYSDBA
·          
·         SQL> @C:\oraclexe\app\oracle\product\10.2.0\server
·            \SQLPLUS\ADMIN\PLUSTRCE.SQL
·          
·            SQL> drop role plustrace;
·            Role (PLUSTRACE) dropped.
·            
·            SQL> create role plustrace;
·            Role (PLUSTRACE) created.
·            
·            SQL> grant plustrace to dba with admin option;
·            Grant succeeded.
·          
·         SQL> GRANT PLUSTRACE TO HR;
·         Grant succeeded.
·         Remember that PLAN_TABLE table must be created under the user schema HR.
·         How To Get Execution Path Reports on Query Statements?
·         If your user account has autotrace configured by the DBA, you can use the "SET AUTOTRACE ON EXPLAIN" command to turn on execution path reports on query statements. The tutorial exercise bellow shows you a good example:
·         SQL> CONNECT HR/retneciyf
·          
·         SQL> SET AUTOTRACE ON EXPLAIN
·          
·         SQL> SELECT E.LAST_NAME, E.SALARY, J.JOB_TITLE
·           2  FROM EMPLOYEES E, JOBS J
·           3  WHERE E.JOB_ID=J.JOB_ID AND E.SALARY>12000;
·          
·         LAST_NAME             SALARY JOB_TITLE
·         ----------------- ---------- -----------------------------
·         King                   24000 President
·         Kochhar                17000 Administration Vice President
·         De Haan                17000 Administration Vice President
·         Russell                14000 Sales Manager
·         Partners               13500 Sales Manager
·         Hartstein              13000 Marketing Manager
·          
·         6 rows selected.
·          
·         Execution Plan
·         -----------------------------------------------------------
·         Plan hash value: 3851899397
·          
·         -----------------------------------------------------------
·         Id|Operation           |Name     |Rows|Bytes| Cost|Time   
·           |                    |         |    |     |/%CPU|
·         -----------------------------------------------------------
·          0|SELECT STATEMENT    |         |  59| 2832|  4/0|00:00:01
·          1| NESTED LOOPS       |         |  59| 2832|  4/0|00:00:01
·         *2|  TABLE ACCESS FULL |EMPLOYEES|  59| 1239|  3/0|00:00:01
·          3|  TABLE ACCESS      |JOBS     |   1|   27|  1/0|00:00:01
·           |     BY INDEX ROWID |         |    |     |     |
·         *4|   INDEX UNIQUE SCAN|JOB_ID_PK|   1|     |  0/0|00:00:01
·         -----------------------------------------------------------
·          
·         Predicate Information (identified by operation id):
·         ---------------------------------------------------
·            2 - filter("E"."SALARY">12000)
·            4 - access("E"."JOB_ID"="J"."JOB_ID")
·         How To Get Execution Statistics Reports on Query Statements?
·         If your user account has autotrace configured by the DBA, you can use the "SET AUTOTRACE ON STATISTICS" command to turn on execution statistics reports on query statements. The tutorial exercise bellow shows you a good example:
·         SQL> CONNECT HR/retneciyf
·          
·         SQL> SET AUTOTRACE ON STATISTICS
·          
·         SQL> SELECT E.LAST_NAME, E.SALARY, J.JOB_TITLE
·           2  FROM EMPLOYEES E, JOBS J
·           3  WHERE E.JOB_ID=J.JOB_ID AND E.SALARY>12000;
·          
·         LAST_NAME             SALARY JOB_TITLE
·         ----------------- ---------- -----------------------------
·         King                   24000 President
·         Kochhar                17000 Administration Vice President
·         De Haan                17000 Administration Vice President
·         Russell                14000 Sales Manager
·         Partners               13500 Sales Manager
·         Hartstein              13000 Marketing Manager
·          
·         6 rows selected.
·          
·         Statistics
·         ----------------------------------------------------------
·                   0  recursive calls
·                   0  db block gets
·                  16  consistent gets
·                   0  physical reads
·                   0  redo size
·                 720  bytes sent via SQL*Net to client
·                 384  bytes received via SQL*Net from client
·                   2  SQL*Net roundtrips to/from client
·                   0  sorts (memory)
·                   0  sorts (disk)
·                   6  rows processed
 
 
Understanding SQL Basics
What Is SQL?
SQL, SEQUEL (Structured English Query Language), is a language for RDBMS (Relational Database Management Systems). SQL was developed by IBM Corporation.
How Many Categories of Data Types?
Oracles supports the following categories of data types:
  • Oracle Built-in Datatypes.
  • ANSI, DB2, and SQL/DS Datatypes.
  • User-Defined Types.
  • Oracle-Supplied Types.
What Are the Oracle Built-in Data Types?
There are 20 Oracle built-in data types, divided into 6 groups:
  • Character Datatypes - CHAR, NCHAR, NVARCHAR2, VARCHAR2
  • Number Datatypes - NUMBER, BINARY_FLOAT, BINARY_DOUBLE
  • Long and Row Datatypes - LONG, LONG RAW, RAW
  • Datetime Datatypes - DATE, TIMESTAMP, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND
  • Large Object Datatypes - BLOB, CLOB, NCLOB, BFILE
  • Row ID Datatypes - ROWID, UROWID
What Are the Differences between CHAR and NCHAR?
Both CHAR and NCHAR are fixed length character data types. But they have the following differences:
  • CHAR's size is specified in bytes by default.
  • NCHAR's size is specified in characters by default. A character could be 1 byte to 4 bytes long depending on the character set used.
  • NCHAR stores characters in Unicode.
What Are the Differences between CHAR and VARCHAR2?
The main differences between CHAR and VARCHAR2 are:
  • CHAR stores values in fixed lengths. Values are padded with space characters to match the specified length.
  • VARCHAR2 stores values in variable lengths. Values are not padded with any characters.
What Are the Differences between NUMBER and BINARY_FLOAT?
The main differences between NUMBER and BINARY_FLOAT are:
  • NUMBER stores values as fixed-point numbers using 1 to 22 bytes.
  • BINARY_FLOAT stores values as single precision floating-point numbers.
What Are the Differences between DATE and TIMESTAMP?
The main differences between DATE and TIMESTAMP are:
  • DATE stores values as century, year, month, date, hour, minute, and second.
  • TIMESTAMP stores values as year, month, day, hour, minute, second, and fractional seconds.
What Are the Differences between INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND?
The main differences between INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND are:
  • INTERVAL YEAR TO MONTH stores values as time intervals at the month level.
  • INTERVAL DAY TO SECOND stores values as time intervals at the fractional seconds level.
What Are the Differences between BLOB and CLOB?
The main differences between BLOB and CLOB are:
  • BLOB stores values as LOB (Large OBject) in bitstreams.
  • CLOB stores values as LOB (Large OBject) in character steams.
What Are the ANSI Data Types Supported in Oracle?
The following ANSI data types are supported in Oracle:
  • CHARACTER(n) / CHAR(n)
  • CHARACTER VARYING(n) / CHAR VARYING(n)
  • NATIONAL CHARACTER(n) / NATIONAL CHAR(n) / NCHAR(n)
  • NATIONAL CHARACTER VARYING(n) / NATIONAL CHAR VARYING(n) / NCHAR VARYING(n)
  • NUMERIC(p,s)
  • DECIMAL(p,s)
  • INTEGER / INT
  • SMALLINT
  • FLOAT
  • DOUBLE PRECISION
  • REAL
How To Write Text Literals?
There are several ways to write text literals as shown in the following samples:
SELECT 'FYICenter.com' FROM DUAL -- The most common format
FYICenter.com

SELECT 'It''s Sunday!' FROM DUAL -- Single quote escaped
It's Sunday!

SELECT N'Allo, C''est moi.' FROM DUAL -- National chars
Allo, C'est moi.

SELECT Q'/It's Sunday!/' FROM DUAL -- Your own delimiter
It's Sunday!
How To Write Numeric Literals?
Numeric literals can coded as shown in the following samples:
SELECT 255 FROM DUAL -- An integer
255

SELECT -6.34 FROM DUAL -- A regular number
-6.34

SELECT 2.14F FROM DUAL -- A single-precision floating point
2.14

SELECT -0.5D FROM DUAL -- A double-precision floating point
-0.5
How To Write Date and Time Literals?
Date and time literals can coded as shown in the following samples:
SELECT DATE '2002-10-03' FROM DUAL -- ANSI date format
03-OCT-02

SELECT TIMESTAMP '1997-01-31 09:26:50.124' FROM DUAL
31-JAN-97 09.26.50.124000000 AM
-- This is ANSI format
How To Write Date and Time Interval Literals?
Date and time interval literals can coded as shown in the following samples:
SELECT DATE '2002-10-03' + INTERVAL '123-2' YEAR(3) TO MONTH
  FROM DUAL
  -- 123 years and 2 months is added to 2002-10-03
03-DEC-25

SELECT DATE '2002-10-03' + INTERVAL '123' YEAR(3) FROM DUAL
  -- 123 years is added to 2002-10-03
03-OCT-25

SELECT DATE '2002-10-03' + INTERVAL '299' MONTH(3) FROM DUAL
  -- 299 months years is added to 2002-10-03
03-SEP-27

SELECT TIMESTAMP '1997-01-31 09:26:50.124'
  + INTERVAL '4 5:12:10.222' DAY TO SECOND(3) FROM DUAL
04-FEB-97 02.39.00.346000000 PM

SELECT TIMESTAMP '1997-01-31 09:26:50.124'
  + INTERVAL '4 5:12' DAY TO MINUTE FROM DUAL
04-FEB-97 02.38.50.124000000 PM

SELECT TIMESTAMP '1997-01-31 09:26:50.124'
  + INTERVAL '400 5' DAY(3) TO HOUR FROM DUAL
07-MAR-98 02.26.50.124000000 PM

SELECT TIMESTAMP '1997-01-31 09:26:50.124'
  + INTERVAL '400' DAY(3) FROM DUAL
07-MAR-98 09.26.50.124000000 AM

SELECT TIMESTAMP '1997-01-31 09:26:50.124'
  + INTERVAL '11:12:10.2222222' HOUR TO SECOND(7) FROM DUAL
31-JAN-97 08.39.00.346222200 PM

SELECT TIMESTAMP '1997-01-31 09:26:50.124'
  + INTERVAL '30.12345' SECOND(2,4) FROM DUAL
31-JAN-97 09.27.20.247500000 AM 
How To Convert Numbers to Characters?
You can convert numeric values to characters by using the TO_CHAR() function as shown in the following examples:
SELECT TO_CHAR(4123.4570) FROM DUAL
123.457
 
SELECT TO_CHAR(4123.457, '$9,999,999.99') FROM DUAL
     $4,123.46
 
SELECT TO_CHAR(-4123.457, '9999999.99EEEE') FROM DUAL
 -4.12E+03
How To Convert Characters to Numbers?
You can convert characters to numbers by using the TO_NUMBER() function as shown in the following examples:
SELECT TO_NUMBER('4123.4570') FROM DUAL
4123.457
 
SELECT TO_NUMBER('     $4,123.46','$9,999,999.99') FROM DUAL
4123.46
 
SELECT TO_NUMBER(' -4.12E+03') FROM DUAL
-4120
How To Convert Dates to Characters?
You can convert dates to characters using the TO_CHAR() function as shown in the following examples:
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') FROM DUAL;
  -- SYSDATE returns the current date
07-MAY-2006 
 
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD') FROM DUAL;
2006/05/07 
 
SELECT TO_CHAR(SYSDATE, 'MONTH DD, YYYY') FROM DUAL;
MAY       07, 2006
 
SELECT TO_CHAR(SYSDATE, 'fmMONTH DD, YYYY') FROM DUAL;
May 7, 2006
 
SELECT TO_CHAR(SYSDATE, 'fmDAY, MONTH DD, YYYY') FROM DUAL;
SUNDAY, MAY 7, 2006
How To Convert Characters to Dates?
You can convert dates to characters using the TO_DATE() function as shown in the following examples:
SELECT TO_DATE('07-MAY-2006', 'DD-MON-YYYY') FROM DUAL;
07-MAY-06
 
SELECT TO_DATE('2006/05/07 ', 'YYYY/MM/DD') FROM DUAL;
07-MAY-06
 
SELECT TO_DATE('MAY       07, 2006', 'MONTH DD, YYYY')
  FROM DUAL;
07-MAY-06
 
SELECT TO_DATE('May 7, 2006', 'fmMONTH DD, YYYY') FROM DUAL;
07-MAY-06
 
SELECT TO_DATE('SUNDAY, MAY 7, 2006', 
  'fmDAY, MONTH DD, YYYY') FROM DUAL;
07-MAY-06
How To Convert Times to Characters?
You can convert dates to characters using the TO_CHAR() function as shown in the following examples:
SELECT TO_CHAR(SYSDATE, 'HH:MI:SS') FROM DUAL;
04:49:49
 
SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS.FF') FROM DUAL;
  -- Error: SYSDATE has no fractional seconds
  
SELECT TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS.FF9') FROM DUAL;
16:52:57.847000000                     
 
SELECT TO_CHAR(SYSDATE, 'SSSSS') FROM DUAL;
  -- Seconds past midnight
69520

How To Convert Characters to Times?
You can convert dates to characters using the TO_CHAR() function as shown in the following examples:
SELECT TO_CHAR(TO_DATE('04:49:49', 'HH:MI:SS'), 
  'DD-MON-YYYY HH24:MI:SS') FROM DUAL;
  -- Default date is the first day of the current month
01-MAY-2006 04:49:49
 
SELECT TO_CHAR(TO_TIMESTAMP('16:52:57.847000000', 
  'HH24:MI:SS.FF9'), 'DD-MON-YYYY HH24:MI:SS.FF9') 
  FROM DUAL;
01-MAY-2006 16:52:57.847000000
 
SELECT TO_CHAR(TO_DATE('69520', 'SSSSS'), 
  'DD-MON-YYYY HH24:MI:SS') FROM DUAL;
01-MAY-2006 19:18:40
What Is NULL?
NULL is a special value representing "no value" in all data types. NULL can be used on in operations like other values. But most opertations has special rules when NULL is involved. The tutorial exercise below shows you some examples:
SET NULL 'NULL'; -- Make sure NULL is displayed
 
SELECT NULL FROM DUAL;
N
-
N
U
L
L
 
SELECT NULL + NULL FROM DUAL;
 NULL+NULL
----------
NULL
  
SELECT NULL + 7 FROM DUAL;
    NULL+7
----------
NULL
 
SELECT NULL * 7 FROM DUAL;
    NULL*7
----------
NULL
 
 
SELECT NULL || 'A' FROM DUAL;
N
-
A
 
SELECT NULL + SYSDATE FROM DUAL;
NULL+SYSD
---------
NULL
How To Use NULL as Conditions?
If you want to compare values against NULL as conditions, you should use the "IS NULL" or "IS NOT NULL" operator. Do not use "=" or "<>" against NULL. The sample script below shows you some good examples:
SELECT 'A' IS NULL FROM DUAL;
  -- Error: Boolean is not data type. 
  -- Boolean can only be used as conditions
  
SELECT CASE WHEN 'A' IS NULL THEN 'TRUE' ELSE 'FALSE' END
  FROM DUAL;
FALSE
 
SELECT CASE WHEN '' IS NULL THEN 'TRUE' ELSE 'FALSE' END
  FROM DUAL;
TRUE
 
SELECT CASE WHEN 0 IS NULL THEN 'TRUE' ELSE 'FALSE' END
  FROM DUAL;
FALSE
 
SELECT CASE WHEN NULL IS NULL THEN 'TRUE' ELSE 'FALSE' END
  FROM DUAL;
TRUE
 
SELECT CASE WHEN 'A' = NULL THEN 'TRUE' ELSE 'FALSE' END
  FROM DUAL;
  -- Do not use "="
FALSE
 
SELECT CASE WHEN 'A' <> NULL THEN 'TRUE' ELSE 'FALSE' END
  FROM DUAL;
  -- Do not use "<>"
FALSE
 
SELECT CASE WHEN NULL = NULL THEN 'TRUE' ELSE 'FALSE' END
  FROM DUAL;
  -- Do not use "="
FALSE
How To Concatenate Two Text Values?
There are two ways to concatenate two text values together:
  • CONCAT() function.
  • '||' operation.
Here is some examples on how to use them:
SELECT 'FYI' || 'Center' || '.com' FROM DUAL;
FYICenter.com

SELECT CONCAT('FYICenter','.com') FROM DUAL;
FYICenter.com
How To Increment Dates by 1?
If you have a date, and you want to increment it by 1. You can do this by adding the date with a date interval. You can also do this by adding the number 1 directly on the date. The tutorial example below shows you how to adding numbers to dates, and take date differences:
SELECT TO_DATE('30-APR-06') + 1 FROM DUAL;
  -- Adding 1 day to a date
01-MAY-06

SELECT TO_DATE('01-MAY-06') - TO_DATE('30-APR-06')
  FROM DUAL;
  -- Taking date differences
1

SELECT SYSTIMESTAMP + 1 FROM DUAL;
  -- The number you add is always in days.
08-MAY-06

SELECT TO_CHAR(SYSTIMESTAMP+1,'DD-MON-YYYY HH24:MI:SS.FF3')
  FROM DUAL;
-- Error: Adding 1 to a timestamp makes it a date.
How To Calculate Date and Time Differences?
If you want to know how many years, months, days and seconds are there between two dates or times, you can use the date and time interval expressions: YEAR ... TO MONTH and DAY ... TO SECOND. The tutorial exercise below gives you some good examples:
SELECT
  (TO_DATE('01-MAY-2006 16:52:57','DD-MON-YYYY HH24:MI:SS')
   -
   TO_DATE('31-JAN-1897 09:26:50','DD-MON-YYYY HH24:MI:SS'))
  YEAR(4) TO MONTH FROM DUAL;
  -- 109 years and 3 months
109-3 

SELECT
  (TO_DATE('01-MAY-2006 16:52:57','DD-MON-YYYY HH24:MI:SS')
   -
   TO_DATE('31-JAN-1897 09:26:50','DD-MON-YYYY HH24:MI:SS'))
  DAY(9) TO SECOND FROM DUAL;
  -- 39901 days and some seconds
39901 7:26:7.0 

SELECT
  (TO_TIMESTAMP('01-MAY-2006 16:52:57.847',
  'DD-MON-YYYY HH24:MI:SS.FF3') -
   TO_TIMESTAMP('31-JAN-1897 09:26:50.124',
  'DD-MON-YYYY HH24:MI:SS.FF3'))
  YEAR(4) TO MONTH FROM DUAL;
  -- 109 years and 3 months
109-3 

SELECT
  (TO_TIMESTAMP('01-MAY-2006 16:52:57.847',
  'DD-MON-YYYY HH24:MI:SS.FF3') -
   TO_TIMESTAMP('31-JAN-1897 09:26:50.124',
  'DD-MON-YYYY HH24:MI:SS.FF3'))
  DAY(9) TO SECOND
  FROM DUAL;
  -- 39901 days and some fractional seconds
39901 7:26:7.723000000

How To Use IN Conditions?
An IN condition is single value again a list of values. It returns TRUE, if the specified value is in the list. Otherwise, it returns FALSE.
Some examples are given in the script below:
SELECT CASE WHEN 3 IN (1,2,3,5) THEN
  'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE

SELECT CASE WHEN 3 NOT IN (1,2,3,5) THEN
  'TRUE' ELSE 'FALSE' END FROM DUAL;
FALSE  

SELECT CASE WHEN 'Y' IN ('F','Y','I') THEN
  'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE
How To Use LIKE Conditions?
LIKE condition is also called pattern patch. There 3 main rules on using LIKE condition:
  • '_' is used in the pattern to match any one character.
  • '%' is used in the pattern to match any zero or more characters.
  • ESCAPE clause is used to provide the escape character in the pattern.
The following script provides you some good pattern matching examples:
SELECT CASE WHEN 'FYICenter.com' LIKE '%Center%'
  THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE  

SELECT CASE WHEN 'FYICenter.com' LIKE '%CENTER%'
  THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
  -- Case sensitive by default
FALSE

SELECT CASE WHEN 'FYICenter.com' LIKE '%Center_com'
  THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE  

SELECT CASE WHEN '100% correct' LIKE '100\% %' ESCAPE '\'
  THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE  
How To Use Regular Expression in Pattern Match Conditions?
If you have a pattern that is too complex for LIKE to handle, you can use the regular expression pattern patch function: REGEXP_LIKE().
The following script provides you some good examples:
SELECT CASE WHEN REGEXP_LIKE ('FYICenter.com', '.*fyi.*',
  'i') THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE

SELECT CASE WHEN REGEXP_LIKE ('FYICenter.com', '.*com$',
  'i') THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE

SELECT CASE WHEN REGEXP_LIKE ('FYICenter.com', '^F.*','i')
  THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE



No comments:

Subscribe to: Posts (Atom)