FORMATEANDO RESULTADOS
Usar la opcion SET con las siguientes opciones:

DEFINE = Me mostrará todos los valores almacenados y en que estado se encuentran.

ECHO ON/OFF = Muestra/Oculta lo que se va haciendo

FEEDBACK ON/OFF = Muestra/Oculta la cantidad de filas retornadas por uan query

HEADING OFF/ON = Si esta en OFF, no aparece el encabezado de cada columna

LINESIZE Nº = Cant. de caracteres por linea, este valor por defecto es de 80

NUMWIDTH Nº = Se lo puede usar para modificar el ancho por defecto que poseen todas las columnas numéricas. Este valor por defecto es 10

NUMFORMAT ...... = Le doy un formato a todos los números por igual. Algunos formatos son:
                  999,999.99 Pondrá , y . Si aparece un null, pondrá null
                  $9990  Pondrá 0 si viene un null. El signo $ aparecerá con todos los números

PAGESIZE Nº = Cant. de filas por pagina, este valor por defecto es de 66

SCAN = Pone la sustitucion de variables en ON u OFF

SHOW ALL o COMANDO = Muestra en que estado está configurado un comando o todos ellos.

SQLCASE UPPER = Hace que lo que se busque en la BD sea en mayúscula, aún cuando el usuario la escriba en minúscula.

TRIMSPOOL OFF/ON = Cuando hacemos un spool a un archivo, al definir el LINESIZE, si pusimos muchos tambien nos vendran varios espacios en blanco al final de cada linea. Para evitar eso, ponemos a TRIMSPOOL en ON.

TERMOUT OFF/ON = Muestra/Oculta la salida de un script a la pantalla. Generalmente se lo pone en OFF al principio del archivo, y se lo pone en ON inmediatamente después del SPOOL, para que se vean los resultados.

RECSEP ON/OFF = Por default el parametro RECSEP esta seteado en WRAP. Esto hace que despues de cada "wrapped record" se insertara el caracter en que se haya seteado RECSEPCHAR, que por defecto es un espacio en blanco. Poniendolo en OFF se eliminara la linea en blanco entre los registros.
The recsep command has basically two functions:
    1.  SQL> set recsep off (returns all rows single-spaced)
    2.  SQL> set recsep each
    (inserts a linefeed between each row/double-spaced)

UNDEFINE = Saca una variable definida

UNDERLINE = Sirve para modificar lo que aparece debajoi del titulo de cada columna (el default es ---).Este ejemplo pondrá el signo =.

VERIFY ON/OFF = Muestra/Oculta las lineas reemplazadas

NOTA = Poner antes que ningún comando las instrucciones siguientes, esto hace que si había algo en la memoria, se lo resetee.
                CLEAR BREAKS
                CLEAR COMPUTES
 

Interacción con el SQL
Con PROMPT  muestro comentarios o acciones a la salida del programa.
Usaré el ACCEPT para cargarle valores a una variable; también puedo usar el & 2 veces, de esa manera no pregunta varias veces por un mismo valor.
Cuando uso estos, en mi Select lo debo usar con un & adelante y entre comillas simples. Si no quiero que salgan comentarios, antes del ACCEPT pongo ECHO OFF y VERIFY OFF.
Ej:
 accept mes  number prompt 'Ingrese Mes a Calcular: '
 
 

Títulos de Reportes
A los reportes o salidas de Información, le puedo poner un título abajo y arriba. Para eso uso:
Para el superior:
TTITLE (center, left o right) ‘mi Titulo’
TTITLE center ‘Un titulo’ skip 1 right ‘Otro Título’  skip 2

O sea que se pueden poner varios, pero todos deben estar definidos al mismo tiempo. Además le puedo agregar el comando SKIP, el cual me permite dejar renglones en blanco.

Para el inferior:
 BTITLE con las mismas opciones.

También  puedo definir varios títulos con variables. Ejemplo:
  DEFINE LINE1 = ‘Primera línea al centro‘
  DEFINE LINE2 = ‘Segunda línea a la izquierda ‘
  DEFINE LINE3 = ‘Tercera línea a la derecha ‘
  TTITLE CENTER LINE1 SKIP 1 LEFT LINE2 SKIP1 RIGHT LINE3

Para mostrar los números de paginas uso la función SQL.PNO. Ejemplo:
  TTITLE LEFT ‘Reporte Mensual’ RIGHT ‘PAGINA:’ FORMAT 999 SQL.PNO SKIP 2

Para suprimir los Títulos, hago:
  TTITLE OFF
  BTITLE OFF
 
 

Formateando Resultados
COLUMN col_name HEADING ‘titulo’ FORMAT a15

Formatea la salida de esa columna que aparezca en el select. Si necesito un título más largo, entonces puedo usar el | (pipe) dentro de mi título. Esto hará que lo que esté después del | aparezca en otro renglón.

Puedo hacer que si un resultado es muy largo, el mismo ocupe mas de un renglón. Para ello agrego al final de mi comando COLUMN las distintas opciones:
    WORD_WRAPPED = Continúa una palabra en otro renglón
    WRAPPED =
    TRUNC = La trunca (si es mas larga que mi formato)

Para ver como están los atributos que le puse a las columnas uso el comando
COLUMN col_name

Y para resetearlo uso:
COLUMN col_name CLEAR

El formato del comando COLUMN es el siguiente:

COLUMN nom_col HEADING ‘mi titulo’ NEW_VALUE variable_que_va_al_ttitulo NOPRINT NULL texto_a_mostrar_caso_de_null OLD_VALUE variable_a_btitulo WORD_WRAPPED
           WRAPPED
     TRUNC

El NOPRINT con el NEW_VALUE hace que una variable se "esconda" para que luego la pueda imprimir en otro lado (por ejemplo en un título).
Ej. :
  COLUMN today NEW_VALUE xtoday NOPRINT

Esto crea una nueva variable xtoday que contendrá el valor de la variable today (se supone que este valor se repite constantemente), le digo que NO la muestre en el Select. Si esa variable la pienso usar en el Título de abajo, por cuestión de velocidad, conviene ponerle OLD_VALUE en vez de NEW_VALUE
 

Corte por Columna
Para que no se me repitan valores cuando hago una consulta con el order by (por ejemplo el código de departamento), puedo ponerle un break, de esa forma si un valor viene repetido, no lo pone. Uso:

BREAK ON break_column
Esto hace que cuando cambie el ítem, corta y vuelve a poner los títulos de las columnas.

Si quiero que antes de que venga un nuevo valor, me deje un espacio en blanco o una hoja en blanco, entonces hago:
BREAK ON break_column SKIP (N_renglones o PAGE)

Para ver todos los BREAKS que puse pongo
BREAK

Y para sacar todos pongo:
CLEAR BREAKS
 
 

Cálculos
Uno de los comandos mas importantes es el COMPUTE. Este me permite sacar totales parciales por un campo particular.  Su formato es:

BREAK ON break_column (acá puedo ponerle las opciones de renglones)
COMPUTE función OF col_a_aplicar_funcion ON col_de_agrupamiento

En el COMPUTE puedo poner varias columnas, NO las debo separar por comas. Ej:
COMPUTE sum OF col1 col2 col3 ON campo o REPORT
 

Si quiero distintos subtotales entonces los pondré en el BREAK desde el mayor group hasta el menor. Ej:
 BREAK ON company ON division ON Departamento SKIP 1

Si hago
 BREAK ON REPORT ON col1
  COMPUTE SUM OF ...... ON .......... REPORT
Se calcularán no sólo los subtotales por grupo, sino que se agregará un renglón al final de todo con el valor total de todo.
 

Las distintas opciones del BREAK ON son:
 COL =
 ROW = Fuerza BREAK con cada fila
 PAGE = Cuando se llena una página
 REPORT = Cuando fin del reporte

Las distintas opciones del SKIP son:
 ‘n filas’ = Salta esa cantidad de renglones
 PAGE = Salta hasta una nueva página

Las distintas opciones del COMPUTE son:
 SUM – MIN – MAX – AVG – STD
VAR – COUNT – NUM (como el count, pero cuenta los nulos)
 

Single Row Functions

Type	Function	Returns
Number	abs(n)	Absolute value of n. i.e. removes the sign.
	ceil(n)	Smallest integer >=n
	floor(n)	Largest integer <=n
	mod(n,m)	Remainder of n divided by m
	power(n,m)	n raised to the power m
	round(n[,m])	n rounded to m digits to the right of the decimal point. m = 0 by default.
	sign(n)	-1 if n<0 0 if n=0 1 if n>09
	sqrt(n)	Ö n
	trunc(n[,m])	as round but there is no rounding up.
Character	chr(n)	the character corresponding to the decimal value e.g. In the ascii character set chr(10) is a new line character.
	concat(s1,s2)	s1||s2 (used for portability where the ‘|’ character may cause problems)
	initcap(s)	s with the first letter of each word in upper case
	lower(s)	s with all letters lower case
	lpad(s,n[,c])	s left-padded with c’s to a size of n
	ltrim(s1[,s2])	s1 with letters in s2 trimmed from left until a letter not in s2 is found.
	replace(s1,s2[,s3])	s1 with all instances of s2 replaced by s3
	rpad(s,n[,c])	as lpad but to the right
	rtrim(s1,n,[s2)	as ltrim but to the right
	substr(s1,n,m)	the m character long substring of s1 beginning with character n
	translate(s1,s2,s3)	s1 but with each character in s2 replaced by the corresponding letter from s3.
	upper(s)	s with all letters upper case.
Character returning number	ascii(c)	returns the decimal value of c in the character set of the database (note that this may not be ascii!!)
	instr(s1,s2[,n[,m]])	returns the character position of the mth occurrence of the string s2 in s1 beginning with the nth character.
	length(s1)	the length of s1
Date	add_months(d,n)	the date n days after d
	last_day(d)	the date of the last day of the month which d is in
	months_between(d1,d2)	number of months between d1 and d2; this is expressed as a floating point number calculated on the basis if 31 days per month.
	next_day(d, day)	e.g. if day=Monday then returns the date of the first Monday after d.
	round(d,f)	returns d rounded to the nearest f (e.g. ‘Day’, ‘Month’, etc)
	sysdate	the current database date and time
	trunc(d,f)	as round but truncates rather than rounds.
Conversion	to_char(d,f) to_char(n,f)	Converts a date or a number to characters.
	to_date(s1,f)	Converts a character string in format f to a date
	to_number(c,f)	Converts a character string in format f to a number
Other	greatest(e1,e2….)	Whichever of e1, e2, etc is the greatest
	least(e1,e2,….)	Whichever of e1, e2, etc is the least
	nvl(e1, e2)	e1 unless it is null in which case e2
	user	the current database username
Decode	decode	decode is not strictly a function but rather a bit of non-standard SQL syntax (for that reason its available in SQL but not in PL/SQL).

Simulating IF/THEN/ELSE logic
There is no IF command in SQL*Plus. However, you can use some of the techniques shown above along with the DECODE operator to simulate an IF command.
In this example, the user is asked to enter the number of a script to run. The user’s answer is evaluated and the appropriate script is run. If the user entered an incorrect value, the badscript.sql script is run to display an error message.
Prompt ’1 - script1.sql’
prompt ’2 - script2.sql’
prompt ’3 - script3.sql’
accept script_number prompt - ’Enter the number of the script to run: ’
@formatoff
column script_name noprint new_value script_name_var
select decode ( &script_number,
        1, ’script1.sql’,
        2, ’script2.sql’,
        3, ’script3.sql’,
        ’badscript.sql’) script_name
from dual;
@formaton
@&script_name_var
 

Copy Command
Probably the easiest way to copy data from one table to another is to use
INSERT INTO table1 SELECT * FROM table2;

This method doesn’t work well with large tables because this command is processed as a single unit of work. Since you can’t have ORACLE do a commit in the middle of the insert, you have to have a rollback segment large enough to handle the complete transaction. It also won’t work if the source table has a LONG datatype.

You could write a store procedure that opens a cursor, does the inserts, and does a commit after a certain number of rows. This can get tedious if you have a large number of columns in the table, since you will have to refer to each one in the INSERT command.
The SQL*Plus COPY command allows you continue to use simple SQL to define the data that you are copying. It also allows you to take occasional COMMITS during the copy operation.

One really nice thing about COPY is that it will work with tables that have LONG datatypes. You can’t use CREATE TABLE or INSERT to move data from one table to another if one of the columns is LONG. When using COPY, the SET LONG command tells SQL*Plus how much of the LONG data to copy.

The commit frequency is controlled by the ARRAYSIZE and COPYCOMMIT settings in your SQL*Plus session. The ARRAYSIZE setting determines how many rows are in a ’batch’. The COPYCOMMIT setting determines how many batches to process before doing a commit.

The COPY command requires that at least one of the tables be a remote table. It expects a SQL*Net connect string to identify the remote database. To copy one local table to another local table, simply specify the connect string for the local database.

The COPY command allows you to CREATE, REPLACE, INSERT, or APPEND the destination table. CREATE will create the table. REPLACE removes any existing rows before inserting new data. INSERT requires that the table exists, but that it is empty. APPEND adds rows and does nothing with any existing rows.

The following example uses the COPY command to create the local table mytable2 as a copy of the local table mytable1. A commit is taken after every 1000 rows. Note that since COPY is a SQL*Plus command, not a SQL command, you must use a ’-’ to continue the command to multiple lines. Also, the COPY command is not stored in your edit buffer, so you’ll have to retype the command if you make mistakes (or save the command in a script file).
set arraysize 10
set copycommit 100
set long 3000
copy from myusername/mypassword@mydb create mytable2 using select * from mytable1 ;

Array fetch/bind size is 10. (arraysize is 10)
Will commit after every 100 array binds. (copycommit is 100)
Maximum long size is 3000. (long is 3000)
Table MYTABLE2 created.
 

Displaying Data from Multiple Tables
An outer join forces a query to return a row even if the row in one table has no matching row in the other. i.e. it will return at least as many rows as the corresponding equijoin and probably more (since that’s the reason for writing it in the first place). e.g. the following query lists one row for each order pplus one row for each customer who has not placed any orders:

select c.name, o.order_date
from   customers c, orders o
where  o.cust_id = c.id (+);

Creating Tables
The following command creates a table VOTER with:

• a primary key (pk_id)
• a unique key (unq_name)
• a foreign key (fk_area)
• a column check constraint (m_or_f)
• a table check constraint (dor_after_dob)

create table VOTER 
id    number(6)    constraint pk_id primary key,
name  varchar2(30) not null constraint unq_name unique,
sex   varchar2(1)  not null constraint m_or_f check (sex in (‘M’,’F’)),
dob   date         not null,
area  varchar2(5)  not null constraint fk_area references areas(code),
dor   date,
constraint dor_after_dob check (dor > dob)
);

Altering Tables and Constraints

1. Add a column:

alter table voters add ( post_code varchar2(7));

2. Add a constraint (column syntax): the only constraint you can add using this syntax is a NOT NULL constraint. Note that this will only work if the affected column is populated for every row.

alter table voters modify (post_code not null varchar2(7));

3. Add a constraint (table syntax):

alter table voters add (constraint name_not_fred check (name != ‘Fred’));

4. Disable a constraint:

alter table voters disable constraint name_not_fred;

5. Enable a constraint:

alter table voters enable constraint name_not_fred;

6. Remove a constraint:

alter table voters drop constraint name_not_fred;

7. Amend a column:

alter table voters modify (post_code varchar2(8));

8. drop a table (the cascade constraints clause is optional; it drops all foreign key constraints which refer to this table):

drop table voters cascade constraints;

9. Delete all rows using one of the commands below:

truncate table voters;
delete from voters;

10. Rename a table, view, sequence or private synonym using the RENAME command (note: NOT public synonyms or columns). Grants, indexes, integrity constraints are automatically transferred. Other objects that depend on the renamed object are made invalid e.g. procedures, functions, packages, views, synonyms.

rename wrinklies to senior_voters;

11. Add comments on the following objects only: table, view, snapshot, comment:

COMMENT ON COLUMN voters.sex 
IS ' Trans-sexuals have the sex on their birth certificate';

12. Comments can be viewed in the data dictionary views: user_tab_comments (tables and views), user_col_comments, all_tab_comments, all_col_comments.

Creating Sequences

1. Sequences are database objects that generate unique integers. They are typically used to provide unique identifiers for rows in a table. If you get a number from a sequence and the number is not used, or the transaction is rolled back, then there will be a gap in your numbers. For this reason, sequences are suitable when there must not be any missing numbers, for example, in generating invoice numbers.
2. Create a sequence (numbers start at 1000, increment by 5, maximum is 5000 and then cycles back to 1000);

create sequence cust_seq 
start with 1000 increment by 5 maxvalue 5000 cycle;

3. Getting the next value from a sequence::

select cust_seq.nextval from dual;

4. Getting the most recently selected value from the sequence

select cust_seq.currval from dual;

5. Modify a sequence. The alter sequence command has al of the same options as create sequence except START WITH. To re-start a sequence you must drop it and ten re-create it.:

alter sequence cust_seq increment 10;

6. Remove a sequence:

drop sequence cust_seq;

Controlling User Access

1. Database objects are owned by users whose access is password protected. Users can GRANT other users access to their objects – this can include granting another user the right to pass on the grant to third parties.
2. A typical security setup in a simple production system might be is as follows:
1. A single user (the schema owner) owns the database objects;
2. The schema owner grants privileges to a number of database roles e.g. clerk, supervisor, manager. Each of these will have different privileges depending on the access needed to carry out the job. The following objects privileges are available:
• SELECT, INSERT, UPDATE, DELETE (the meaning of these is obvious)
• ALTER allows you to change the object definition
• INDEX allows you to create an index on a table object
• REFERENCES allows you to create a constraint that references the table (can only be granted to a user, not to a role)
• EXECUTE allows you to execute a package, procedure or function. Note that you do not need privileges on the tables that are accessed by the package, procedure or function. e.g. If you have EXECUTE on a package that inserts into the VOTERS table and the owner of the package has been granted INSERT on VOTERS then by using the package you can insert into the VOTERS table, even if you cannot do so directly from SQL.
• WITH GRANT OPTION: allows the grantee to pass on the privileges (cannot be granted to a role, oly a user).
3. Individuals are GRANTed ROLES thereby allowing them the access they need.
3. A role is created by:

create role MANAGER_ROLE;

4. The schema owner grants object privileges to the role:

grant select, insert, update, delete on VOTERS to MANAGER_ROLE;
grant select on STATS to MANAGER_ROLE;

5. A user is created and granted the requisite roles as follows:

create user fredg identified by xyz123
grant create session to fredg -- lets fredg connect to the db
grant MANAGER_ROLE to fredg;

6. Amend a users password with:

alter user fredg identified by changedpassword;

7. Privileges are revoked using the REVOKE command:

revoke select on stats from MANAGER_ROLE;

8. Create a private synonym (works only for user who creates the synonym):

create synonym VOTERS for herbieg.voters;

9. Create a public synonym (works for every user, requires CREATE PUBLIC SYNONYM privilege):

create public synonym VOTERS for herbieg.voters;

Developing a simple PL/SQL block

/*
 * A multi-line comment
 */
declare
  l_counter NUMBER := 12;
  Fixed_value  CONSTANT NUMBER(6) := 999999;
  Var_Value    NUMBER(4,2);
  l_string VARCHAR2(10) := ‘Dummy’;
  isittrue BOOLEAN := FALSE;
begin
  l_counter := length(l_string);
  l_string  := ‘Dummy’||’2’;
  isittrue  := TRUE; -- A simple comment.
end;

%ROWTYPE and %TYPE
Used to inherit the data types from the underlying table
Avoids having PL/SQL fail if a column is modified, e.g. CHAR(2) to CHAR(10)
ROWTYPE is whole row and TYPE is for a single column

DECLARE
    CURSOR get_customer IS
       SELECT * FROM customer;
    cust_row  get_customer%ROWTYPE;
BEGIN
      OPEN   get_customer;
      FETCH get_customer INTO cust_row;
      DBMS_OUTPUT.PUT_LINE(cust_row.name);
      CLOSE get_customer;
END;
/

DECLARE
    CURSOR get_customer IS
       SELECT name FROM customer;
    cust_name  customer.name%TYPE;
BEGIN
      OPEN   get_customer;
      FETCH get_customer INTO cust_name;
      DBMS_OUTPUT.PUT_LINE(cust_name);
      CLOSE get_customer;
END;
/
 

Interacting with Oracle

1. Implicit cursors are used to execute simple insert, update, delete statements and select statements of the kind you typically use in SQL*Plus. You don’t declare a cursor, open it, fetch it and close it; Oracle does it all for you behind the scenes. Note that implicit SELECT statements used in pl/sql must include the INTO clause (there must be somewhere to put the answer).
2. Explicit cursors involve you explicitly declaring, opening, fetching and closing the cursor – see later section.
3. When you’ve executed an implicit cursor you can test the outcome using the following attributes which need to be tested after the statement is executed:
1. sql%found: TRUE if statement affected or retrieved more than zero rows; FALSE otherwise
2. sql%notfound: the opposite of sql%found
3. sql%rowcount: the number of rows affected or retrieved
4. sql%isopen: always returns FALSE since the cursor has been automatically closed

/*
 * A multi-line comment 
 */ 
declare 
  l_voter voters%rowtype; 
begin 
  select * into   l_voter 
     from   voters v 
     where  rownum = 1; 
  if SQL%FOUND then  
    update stats s 
      set times_executed = times_executed + 1 
      where year = ‘1999’; 
  end if; 
end;

4. Controlling transactions in pl/sql is essentially the same as controlling transactions anywhere else in Oracle. You start a transaction by making an amendment. You can commit or rollback as you see fit. You can also define named savepoints which you can explicitly roll back to.

Controlling Flow in PL/SQL Blocks

1. Conditions
• The basic construct is:

IF counter = 1 THEN 
    DBMS_OUTPUT.PUT_LINE('counter is 1') ;
ELSIF counter = 2 THEN
    DBMS_OUTPUT.PUT_LINE('counter is 2') ;
ELSIF counter = 3 THEN
    DBMS_OUTPUT.PUT_LINE('counter is 3') ;
ELSE
    DBMS_OUTPUT.PUT_LINE('counter is something else') ;
END IF;

• An IF statement must have one IF, it can have zero or more ELSIFs and a maximum of 1 ELSE.
• IF statements can be nested to any depth.
2. Iteration
1. LOOP with BREAK: the loop continues until a BREAK statement is executed (or some other means of ending the loop such as a GOTO or until an exception is raised).

DECLARE
   x NUMBER ;
BEGIN
   x :=0;
   LOOP
      x:=x+1;
      EXIT WHEN (x > 10) ;
      INSERT INTO TEST2 (COL21) VALUES (x) ;
   END LOOP;
   COMMIT ;
END ;

2. WHILE-LOOP: the loop executes while a condition is met; the code in the loop can be executed zero times if the condition is not satisfied at the start.

DECLARE
   x NUMBER ;
BEGIN
   x :=10 ;
   WHILE x > 0 
   LOOP
      INSERT INTO TEST2 (COL21) VALUES (x) ;
      x:=x-1 ;
   END LOOP ;
   COMMIT ;
END ;

3. FOR-LOOP: executes a fixed number of iterations

DECLARE
  x NUMBER ;
BEGIN
  x := 0 ;
  FOR i IN 1..10 
  LOOP
    x := x+1 ;
    INSERT INTO test2 (col21) values (x) ;
  END LOOP ;
  COMMIT ;
END ;

Processing Queries by Using Explicit Cursors

1. Standard Cursors:

DECLARE
    CURSOR get_customer IS
       SELECT name FROM customer;
    cust_name  customer.name%TYPE;
BEGIN
  OPEN   get_customer;
  FETCH get_customer INTO cust_name;
    DBMS_OUTPUT.PUT_LINE(cust_name);
  CLOSE get_customer;
end;

 
2. Fetching rows using a loop:

DECLARE
   CURSOR c1 IS 
     SELECT col11,col12 FROM test1 ;
BEGIN
  FOR cur1 IN c1 LOOP
    INSERT INTO test2 (col21,col22) VALUES (cur1.col11, cur1.col12);
  END LOOP ;
  COMMIT ;
END ;
/

3. Cursor with Parameters

DECLARE
CURSOR c1 ( ws_col number ) IS
    SELECT col11, col12 FROM test1
         WHERE col11 > c1.ws_col;
--
tmp_rec c1%rowtype ;
--
BEGIN
  OPEN c1(3) ;
  LOOP
     FETCH c1 INTO tmp_rec ;
     EXIT WHEN c1%NOTFOUND ;
     INSERT INTO test2 ( col21, col22 ) VALUES ( tmp_rec.col11, tmp_rec.col12 ) ;
  END LOOP;
  CLOSE c1 ;
COMMIT ;
END ;
/

4. Managing Cursor Status

BEGIN
  IF SQL%FOUND THEN 
    NULL ;
  END IF ;
  --
  IF SQL%ISOPEN THEN 
    NULL ;
  END IF ;
  --
  IF SQL%FOUND THEN 
    NULL ;
  END IF ;
  --
  IF SQL%ROWCOUNT = 2 THEN 
    NULL ;
  END IF ;
END ;
/

Error Handling

1. PL/SQL uses exceptions for error handling.
2. The basic types of exception are:
• user defined exceptions
• pre-defined exceptions
3. The following code defines, raises and handles an exception. It also shows the use of the raise statement on its own in an exception handler to re-raise the exception and pass it to the calling routine.

procedure doubletill_over100 (p_in IN OUT NUMBER)
-- define an exception
too_big EXCEPTION;
begin
  if p_in > 128 then
    raise too_big;
  else
    p_in := p_in * 2;
  end if;
exception
  when too_big then
    p_in = 1;
    other statements;
  when others then
    DBMS_OUTPUT.PUT_LINE('Unkown error');
    dbms_output.put_line(SQLERRM);
    raise;
end;

4. Predefined exceptions include the following:
• cursor_already_open
• dup_val_on_index
• invalid_cursor
• invalid_number
• login_denied
• no_data_found
• not_logged_on
• program_error
• rowtype_mismatch
• storage_error
• timeout_on_resource
• too_many_rows
• value_error
• zero_divide
5. Exceptions propagate back up through the calling sequence until they find an exception handler to handle them. For example, if a calls b which calls c which calls d and d raises a no_data_found exception then the system will pass control to the first exception handler it finds that handles NO_DATA_FOUND or OTHERS. If there is no such handler in d, then the system looks in c, then in b, then in a. If ultimately there is no handler found then an ‘unhandled exception’ message is returned and the program terminates.
6. A maximum of one error handler will be executed in a program unit. If a further exception is raised in the exception handler, then that exception is handled at the next level up.

Develop PL/SQL Program Units

Developing Stored Procedures and Functions

	Procedure	Function
Syntax	create or replace procedure myproc (   p_vat_rate IN NUMBER DEFAULT 17.5,   p_price IN OUT NUMBER,   p_vat OUT NUMBER) is price_invalid EXCEPTION; begin   if p_price <= 0.0 then     raise price_invalid;   end if;   p_vat := (p_vat_rate/100) * p_price;   p_price := p_price + p_vat; exception   when price_invalid then     raise_application_error(-20000, ’Price must be positive’);   when others then     raise_application_error(-20999, ’Unexpected error’); end;	create or replace function vat (   vat_rate IN NUMBER,   price IN NUMBER) is l_vat NUMBER; begin   l_vat := (vat_rate/100) * price;   return l_vat; end;
sqlplus	variable price number variable vat number exec myproc(15,:price,:vat)   or   exec myproc(p_vat=>:vat, p_price=>:price);	variable price number variable rate number exec :price := :price + vat(:rate,:price);   or   select vat(15, p.price)   from prices p   where p.id=123;
Parameters	An IN parameter may be used in the program unit but not changed. e.g. it may not appear on the left hand side of an assignment.  An OUT can be assigned to but may not be used/looked at. It may appear on the left hand side of an assignment but not the right hand side. It cannot appear in the condition of an if statement or while/for loop.  An IN OUT is effectively unrestricted – it can be used wherever a variable declared locally in the program unit can be used.
Notes	The sqlplus execute (or exec) statement is a shorthand for begin … .end;  Generally, it is best to use the ‘create or replace’ statement as this preserves grants on the program unit.  Parameters and variables can be defined using the Oracle datatypes (number, varchar2, etc) or by reference to database objects e.g.  sale_price product.price%type; -- defines a simple variable  my_product product%rowtype; -- defines a record  raise_application_error allows the programmer to pass an error code/message to the calling environment e.g. sqlplus or forms. The error code must be in the range –20000 to –20999. The calling program unit can use pragma exception_init as follows:  declare   invalid_price EXCEPTION;   pragma exception_init(-20000,invalid_price);   l_price NUMBER := 10;   …… begin   myproc(p_vat=>16, p_price=>l_price) exception   when invalid_price then     handle the error …. end; If a function/procedure fails with an unhandled exception, Oracle rolls back DML statements to the start of the program unit and then passes the exception to the calling program unit, sql*plus, or whatever else called the program unit.

Developing and using packages
An example package is shown below.

create or replace package pckcar as
  number_of_gets number := 0;
  procedure upd (pId in number, pDesc in varchar2);
  function  getNumberOfUpds return number;
  function  getManufacturer (pId number) return varchar2;
  pragma    restrict_references (getManufacturer, WNDS);
end;
/

create or replace package body pckcar as
  number_of_upds number := 0;
 
  procedure inc_upd is
  begin
    number_of_upds := number_of_upds + 1;
  end;
 
  function getNumberOfUpds return number is
  begin
    return number_of_upds;
  end;
 
  procedure upd (pId in number, pDesc in varchar2) is
  begin
    update car set description = pDesc
    where id = pId;
    inc_upd;
  end;
 
  function getManufacturer (pId number) return varchar2 is
  lName maker.name%type;
  begin
    select m.name into lName
    from maker m, car c
    where m.id = c.fk_id
    and c.id = pId;
    number_of_gets := number_of_gets + 1;
    return lName;
  end;
 
end;
/

1. Packages group together related procedures, functions and variables. Variables in a package persist for the duration of a session.
2. You need a package specification (create or replace package) and a package body (create or replace package body). The specification defines what the user of the package can see and use. It therefore follows that…
1. Variables, functions, procedures declared in the package specification are public. Program units can be executed by anyone who has EXECUTE privilege on the package. Variables can be accessed directly using package_name.variable notation. In the example, number_of_gets, upd(), getManufacturer(), getNumberOfUpds() are public.
2. Anything which is not declared in the package specification is private and can only be accessed (looked at, amended) by the package code itself. In the example, number_of_upds and inc_upd() are private.
3. A user who had execute privilege on the package pckcar could issue the following commands:
1. pckcar.number_of_gets := 100;
2. pckcar.upd(24);
3. name := pckcar.getManufacturer(24);
4. ngets := pckcar.getNumberOfUpds;
5. select id, pckcar.getManufacturer(id) from car;
4. .but not these commands:
1. nupds := pckcar.number_of_upds := 100;
2. inc_upd;
5. If a function within a package is to be used in select statements, it must first guarantee to be free of side effects. This is achieved using the pragma restrict_references as shown above for getManufacturer(). WNDS guarantees that the function ‘writes no database state’ i.e. that it does not update the database. This is sufficient for it to be used in the select list. However, as defined getManufacturer() could not be used in an order by statement because it updates a package variable. To allow it to be used in an order by we would have to remove the line of code which updates number_of_gets and change the pragma to:

pragma restrict_references (getManufacturer, WNDS, WNPS);

6. Compile a package which is already on the database, even if only the package body is marked as invalid, by:

alter package pckcar compile;

7. Oracle supplied packages include:
1. dbms_job: to schedule jobs
2. dbms_lock: lets you request a lock, release a lock etc.
3. dbms_output: to output to sqlplus
4. dbms_pipe: lets two database sessions communicate
5. dbms_sql: to issue dynamic sql statements
6. dbms_system: lets you, for example, enable trace for a session
7. dbms_utility.analyze_schema(‘schemaname’, ‘ESTIMATE_ROWS’): to analyze all objects in a schema
8. dbms_utility.compile_schema(‘schemaname’): to compile all objects in a schema
8. views
1. user_tab_privs holds privileges for packages!
2. user source holds the source of a package.

Running Packaged Procedures
CREATE OR REPLACE
  PROCEDURE RUN_PACKS IS
  BEGIN
     FIN_PACK.UPDATE_SAL (999999, 67500);
      DBMS_OUTPUT.PUT_LINE(FIN_PACK.AVG_SAL);
  END;

In SQL*Plus
set serveroutput on
EXECUTE run_packs.UPDATE_SAL(999999,67500);
 

Developing Database Triggers

• There are two kinds of trigger:
1. statement level: fires once for the insert statement (or the update statement or the delete statement)
2. row level: fires once for every row inserted/updated/deleted by the statement
• You need no specific privileges to execute a database trigger. If you can carry out a DML operation then the associated trigger will fire (whether you want it to or not). All you need is the appropriate privilege on the table you are amending.
• When a database trigger fails, the triggering DML statement is rolled back but earlier DML statements in the transaction are not.

 
• A statement level trigger is created with the following syntax:

CREATE OR REPLACE TRIGGER AUDCAR 
BEFORE INSERT OR DELETE OR UPDATE ON CAR
DECLARE
  action varchar2(1);
BEGIN
  IF INSERTING THEN
    action=’I’;
  ELSIF UPDATING THEN
    action=’I’;
  ELSIF DELETING THEN
    action=’I’;
  END IF;
  insert into action_audits values (‘CAR’,action,user);
END;

• A row level trigger is created with the following syntax:

CREATE OR REPLACE TRIGGER TRGCAR
BEFORE INSERT OR DELETE OR UPDATE OF ID, DESCRIPTION
ON CAR
FOR EACH ROW
WHEN (nvl(new.description,’X’) != ‘Focus’)
DECLARE
BEGIN
  IF INSERTING THEN
    update stats set number_of_cars = number_of_cars + 1;
  ELSIF UPDATING THEN
    IF :new.description != :old.description THEN
      update stats set number_of_changes = number_of_changes + 1;
    END IF;
  ELSIF DELETING THEN
    update stats set number_of_cars = number_of_cars - 1;
  END IF;
END;
 
• Disable a trigger with:

alter trigger trgcar disable;

• Enable a trigger with:

alter trigger trgcar enable;

• Disable all triggers on a table:

alter table car disable all triggers;

• Enable all triggers on a table:

alter table car enable all triggers;
 
• Mutating tables are a major problem with database triggers.
1. In general you are much more likely to get a mutating table with a row level trigger than with a statement level trigger.
2. If you have a row level database trigger on the CAR table, then that trigger cannot access the CAR table. So for example, you cannot have ‘SELECT description from CAR…’ in the trigger or in any code called from the procedure (you can’t just hide it away in a procedure). If you try to access the CAR table then the trigger will fail with a mutating table error.
3. More problematical than this is the fact that you cannot manipulate data in any table which has a foreign key which references the trigger table. For example, we have CAR_HISTORY table which holds an audit trail of changes to the CAR table. CAR_HISTORY has a foreign key defined which references CAR. In our row level trigger on CAR we try to create a CAR_HISTORY record ® mutating trigger.

Built in Packages Available
- DBMS_OUTPUT displays to the screen
- DBMS_PIPE communicates between sessions
- DBMS_LOCK manages program locks
- DBMS_JOB allows you to schedule jobs
- UTL_FILE reads from and writes tp operating system files
- DBMS_SQL for dynamic SQL and DDL

PL/SQL Tables
Used to perform array processing, must contain Primary Key
DECLARE
     TYPE cust_name IS TABLE OF  customer.name%TYPE
            INDEX BY BINARY_INTEGER;
     STORED_NAME    cust_name;
BEGIN
     SELECT name INTO stored_name(1) FROM customer
      WHERE rownum = 1;
      DBMS_OUTPUT.PUT_LINE(stored_name(1));
END;

Tables using %ROWTYPE
Allows a PL/SQL table to inherit a tables definition

DECLARE
     TYPE cust_rec IS TABLE OF  customer.name%ROWTYPE
            INDEX BY BINARY_INTEGER;
     STORED_CUST    cust_rec;
BEGIN
     SELECT * INTO stored_cust(1) FROM customer
      WHERE rownum = 1;
      DBMS_OUTPUT.PUT_LINE(stored_name(1).name);
END;
 

PL/SQL Table Methods
Assists with table handling
table_name.method(parameters) e.g. stored_cust.EXISTS(1)

BEGIN
     SELECT * INTO stored_cust(1) FROM customer
      WHERE rownum = 1;
      IF stored_cust.EXISTS(1)
         THEN
            DBMS_OUTPUT.PUT_LINE(stored_name(1).name);
      END IF;
END;

PL/SQL Table Methods
Methods available includes

stored_rec.COUNT        -- Number of elements in the table
stored_rec.FIRST           -- Goes to first record
stored_rec.LAST            -- Goes to last record
stored_rec.PRIOR (n)     -- Returns the preceding index number
stored_rec.NEXT (n)      -- Returns the next index number
stored_rec.TRIM            -- Removes the last element in table
stored_rec.TRIM(n)       -- Removes the last n elements in table
stored_rec.DELETE           -- Deletes all elements from table
stored_rec.DELETE(n)      -- Deletes the nth elements from table
stored_rec.DELETE(m, n) -- Deletes elements in range m to n
stored_rec.EXTEND          -- Appends one null row to table
stored_rec.EXTEND(n)      -- Appends n null rows to table
stored_rec.EXTEND(n, I)   -- Appends n copies of the I row to table

Top of page