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 command line SQL*Plus GUI SQL Worksheet 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 Variables, procedures, functions, triggers, packages Names must be 30 characters or less Must start with letter Can contain, but not start with $,#,_ or any number Can not contain punctuation, spaces, or hyphens 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;