Defining available Oracle connections

 

 

tnsnames.ora - Contains info to connect to Oracle databases from the client

File contained in $ORACLE_HOME/network/admin directory

 

File format is

 

ORCL=                                             < net service name (alias)

   (DESCRIPTION=

        (ADDRESS=(PROTOCOL=TCP)

                             (HOST=server)      <host computer name or IP addr

                             (PORT= nnnn)       <port number on server

          )

    (CONNECT_DATA=

          (SERVER=DEDICATED)

          (SERVICE_NAME=orcl)           <Service name or SID

     )

   )

 

The net service name is an alias and code be anything, eg. ‘testsys’

 

The service name must match that of list of instances that the data server is listening for on the port specified.


Testing the connection

Tnsping orcl

 

Connect to different schema or instance while in SQL*Plus

Sql> connect user_id@other_instance     will be prompted for password

Use conn for short

 

 

SQL*Plus

An interface to the database

 

(Note: JDeveloper is better environment for PL/SQL development but is downloaded separately)

 

There are 4 different ways to get to SQL*Plus

  1. command line
  2. SQL*Plus GUI
  3. SQL Worksheet
  4. iSQL*Plus

 

1. From command line to get command line sql*plus prompt

> sqlplus user_id/password@oracle_instance  

> sqlplus user_id@oracle_instance  (to be prompted for password)

 

oracle_instance (if defaults used is orcl) if defined in tnsnames.

 

2. For SQL*Plus GUI – use program  sqlplusw at os prompt.

 

3. SQL Worksheet can recall past statements and do explain on SQL

 

4. iSQL*Plus is and internet version that can diplay multibyte characters

 

To change environment settings  edit $ORACLE_HOME/sqlplus/admin/glogin.sql

SQL*Plus commands

HELP index  to get list

 

To run connects of buffer use forward-slash  ‘/’

Temporarily get back to host by using

Unix   !host_command             eg. !ls   for file list

Windows  host host_command

 

 

Executing

Log in to SQL*Plus and type @filename   (include path if not in filename directory)

 

Comments

--   Inline

/* */ Multiline

 

 

           

PL/SQL

Basic PL/SQL program

A bock is basic structure for PL/SQL code

DECLARE                     -- optional 

   Variables                    -- local variables optional

BEGIN                          -- Mandatory, note no ‘;’ end

    Some code;               -- Minimum is ‘Null;’

EXCEPTION                  -- Optional

    Some code;

END;                            - Mandatory

Anonymous blocks

A PL/SQL program that

1. Is not given a name and is not stored in the database

2. Can’t call themselves but can call other programs

3. A script probably in some text file

4.Has an explicit DECLARE statement if variables need to be defined.

 

Example

SET SERVEROUTPUT ON      --  set defaults so don’t need this?

DECLARE

  v_count PLS_INTEGER :=0

 v_type  employees.emplid_type%TYPE;   -- var defined sames as field

Cursor  emplid_type_cur is

   Select emplid_type, count(*)

     From employees

    Group by emplid_type

    Order by emplid_type

 

BEGIN

DBMS_OUTPUT.ENABLE(10000);

OPEN emplid_type

LOOP Fetch emplid_type_cur

      INTO v_type, v_count

  EXIT WHEN emplid_type%NOTFOUND

   DBMS_OUTPUT.PUT_LINE(‘Employee type: ‘ || v_type ||

                                                 ‘ count: ‘ || v_count);

 END LOOP;

 

CLOSE emplid_type;

 

EXCEPTION

    WHEN OTHERS THEN

             DBMS_OUTPUT.PUT_LINE(SQLERRM);

 END;

 

Execute via

/     -- to execute if in SQL*PLUS

@filename.sql if at prompt

 

 

Named blocks

A PL/SQL block given a name

1. Can be procedure, function or trigger.

2. Have a fourth section called a HEADER that specifies if procedure or function, plus definitions of values passed or (if function) value returned.

3. A DECLARE statement is not explicitly needed before declaring variables.

 

CREATE OR REPLACE PROCEDURE my_procedure as

    v_emplname  employees.emplname%TYPE;

    v_hiredate      employees.hiredate%TYPE;

    v_dept            departments.dept%TYPE;

  ….

 

 CREATE OR REPLACE FUNCTION my_function as

 

  CREATE OR REPLACE TRIGGER my_trigger

       AFTER UPDATE OF some_column

       ON some_table

       FOR EACH ROW

    WHEN (OLD.some_column != NEW.some_column)

   BEGIN

   END;

 

 

List compile errors

If in SQL*PLUS

SHOW ERRORS

 

 

Nested Blocks

DECLARE       -- assuming anonymous block of code

  …

BEGIN

  BEGIN

    …;

     EXCEPTION

           ….

  END;

  BEGIN

    …;

     EXCEPTION

           ….

  END;

END;

 

Identifiers

Identifiers are

  1. Variables, procedures, functions, triggers, packages
  2. Names must be 30 characters or less
  3. Must start with letter
  4. Can contain, but not start with $,#,_ or any number
  5. Can not contain punctuation, spaces, or hyphens
  6. NOT case sensitive  - Myvar is same as myvar

 

Literals

Character – ‘c’     1 char surrounded by single quotes

Strings – defined by multiple chars. Can be defined

 Mystring := ‘A glide L:D or 40:1’;

 Mystring := ‘ Eric’’s glider’ ;          -- 2 single quotes

 Mystring := g’[Eric’s glider’];    -- use any char not in string, ie []

 

 

PL/SQL Data Types

This are NOT Oracle data types!

 

Character type:

CHAR – fixed length    CHAR(10)

VARCHAR – ANSI standard varchar. Oracle recommends VARCHAR2

VARCHAR2 – Variable length up to 32K (Database varchar can only store  up to 4K)

LONG  - variable length up to 32K (32760) (Don’t use)

LONG RAW – variable length binary up to 32K bytes (Don’t use)

NCHAR – fixed length national character data. Same as CHAR but uses character set specified by National Character Set

NVARCHAR2 – variable length NCHAR
RAW – fixed length binary up to 32K. (Oracle database can only store up to 2K)

ROWID – Row id (every record in Oracle table as a unique binary rowed). Supports physical rowids, not logical rowids

UROWID – Supports both physical and logical rowids. Recommend by Oracle over ROWID

 

Number types:

BINARY_DOUBLE – double precision floating point

BINARY_FLOAT – single precision floating point

BINARY_INTEGER - -2147483647 to +2147483647

NUMBER – Supports both integer and floating point. Max precision is 38, Scale can range from -84 to 127.  NUMBER(5), NUMBER(5,2)

PLS_INTEGER – Same as BINARY_INTEGER and recommended rather than BINARY_INTEGER

 

BOOLEAN – ‘TRUE’, ‘FALSE’, or NULL

 

Date/Time:

TIMESTAMP

TIMESTAMP with TIMEZONE

TIMESTAMP with LOCAL TIMEZONE

 

Intervals:

INTERVAL YEAR TO MONTH

     TO_TIMESTAMP(‘09/11/2001’, ‘DD/MM/YYYY’) + INTERVAL ‘5-6’ YEAR TO MONTH – add 5 years and 6 months to date

 

INTERVAL DAY TO SECOND

     TO_TIMESTAMP(‘09/11/2001’, ‘DD/MM/YYYY’) + INTERVAL ’10 12:0:0:0’ YEAR TO MONTH – add 10 days, 12 hours to date

 

Composite:

Records

Nested Tables

Index-By Tables

Varrays

 

 

Reference:

(Access to memory structures)

REF CURSOR – Provides access to cursor. Use SYS_REFCURSOR to return cursor from procedure

REF – Used with Object types (a pointer to an object)

 

Large objects

LOB –access up to 4GB (prior to 10g, 8 -128 terabytes) Data can be manipulated piecewise

 

Variable Declaration

My_var [CONSTANT] type [NOT NULL]  [:= value]

 

v_pi CONSTANT NUMBER(3,2) := 3.14;

v_emplid employees.emplid%TYPE;      -- define same as emplid in employees

v_employees EMPLOYEES%ROWTYPE;   -- define with same fields as employees table, eg;

    v_employees.emplid

    v_employees.name

 

Variable scope

Variables are local to the block in which they are defined, or subblocks, unless overrided with variable in subblock with same name.

 

Value assignment to variable

my_emplid  employees.emplid%TYPE

 

BEGIN

   My_emplid := ‘12345’;         -- assign a value to variable

   Select emplname

   From employees

   Where emplid = :my_emplid;    -- use variable in SQL

END;

 

Concatenation

Use || 

my_var := ‘My name is: ‘ || name_var;

 

Conditional flow

Conditional – IF THEN

IF condition     -- condition can evaluate to TRUE, FALSE or NULL

   THEN
           …

   ELSE

           …

END IF;

 

 

Multiple IF THEN ELSIF

IF condition

    THEN

        …

ELSEIF

        …

THEN

     …

  ELSE

      …

END IF;

 

Simple CASE

CASE expression

    WHEN test1 THEN …;

    WHEN test2  THEN …;

  END CASE;

 

Searched CASE

CASE

   WHEN condition1 THEN …;

   WHEN condition2 THEN …;

   ELSE …;

 END CASE;

 

Loops

Simple LOOP

LOOP

    …;

   EXIT [WHEN condition]    -- optional to break out of loop

END LOOP

 

Numeric LOOP

FOR counter IN start_var .. end_var       -- Note ‘..’ is used, no ‘;’ end

LOOP                                                      -- No ‘;’ end

   …

END LOOP;

 

While LOOP

WHILE condition

LOOP

   …;

END LOOP;

 

 

Labels/GOTO

Labels can be defined with <<label_name >>

 

Loops can be given an optional label

BEGIN

 <<first_loop>>

  FOR counter IN 1 .. 10

  LOOP

          …;

   END LOOP  first_loop;      -- Ending loop with name is optional

 

BEGIN

    …;

    GOTO proc_exit;

    …;

 <<proc_exit>>                -- No ending ‘;’

    …

END;