FORMATEANDO RESULTADOS

Cuando se crea un Archivo de PL/SQL, se puede hacer que las cosas aparezcan con un cierto formato. Uno de los comandos básicos es el SET. Este permite indicar diferentes cosas como:

SET FEEDBACK ON/OFF = Al activarlo, no dirá cuantas filas existen en un Select

SET 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

SET 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

SET LINESIZE Nº = Indica de cuanto será el largo de la línea, este valor por defecto es de 80

SET PAGESIZE Nº = Indica cuantos renglones irán por página, este valor por defecto es de 66

SET ECHO ON/OFF =

SET HEADING OFF =

SET TERMOUT OFF/ON = Hace que la salida no se vea en 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.

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

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

UNDEFINE = Saca una variable definida
 

NOTA = Puede ser muy útil 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
 

Otra variable muy útil es la SQLCASE, esta hace que lo que se busque en la BD sea en mayúscula, aún cuando el usuario la escriba en minúscula. Para eso hago:

SET SQLCASE UPPER
 

Interacción con el SQL

Para hacerlo uso el comando PROMPT para mostrar comentarios o acciones.
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 variable PROMPT ‘Ingrese el Valor’;
 
 

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
 

Mejorando los SELECT

Se puede dar formato a las salidas de un Select. De esa forma, los resultados aparecerán encabezados por el título que ponga. Para eso ponemos:
COLUMN col_name HEADING ‘titulo’

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.

Si no me gusta que debajo del título aparezca ---- puedo cambiarlo. Este ejemplo pondrá el signo =.
SET UNDERLINE =

 Puedo formatear el resultado de los números usando
COLUMN col_name FORMAT modelo

 Ej. : COLUMN salario FORMAT $99.990

Puedo modificar el ancho de una columna caracter, ya que por default sale con el ancho de la tabla. Para eso uso como  modelo el A seguido de un numero.
COLUMN nombre FORMAT A25 HEADING ‘ titulo’
 (Hace que la salida sea de 25 caracteres de ancho).

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)
 

Puedo hacer que una columna tenga el formato de otra columna, de esa forma ambas saldrán iguales en su formato. Para eso hago:
COLUMN col_name LIKE col_origen HEADING ‘titulo’
 

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, cortaq 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).

Displaying Data from Multiple Tables
 

1. 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 (+);

1. There are two kinds of SQL*Plus variables:
1. DEFINEs variables. These are always char. You can ACCEPT a value into them but they cannot be referenced in PL/SQL. An example is shown below.

DEFINE cust_name=’Fred’
SELECT * from customers where name=&cust_name;

2. VARIABLEs. These can be NUMBER, CHAR or CHAR(n). They are bind variables and can be used in PL/SQL statements: e.g.

variable msg char(255)
exec mypackage.myproc(:msg);
print msg

2. Note that the ACCEPT command also creates a DEFINE variable if it doesn’t already exist. e.g.

ACCEPT cust_name CHAR PROMPT ‘Customer Name:’ 
Customer Name: John
SELECT * from customers where name=&cust_name;

Creating Tables

1. The following command creates a table VOTER with:
1. a primary key (pk_id)
2. a unique key (unq_name)
3. a foreign key (fk_area)
4. a column check constraint (m_or_f)
5. 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.

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. Create a sequence (using defaults, starts at one, increments by 1, no maximum doesn’t cycle):

create sequence cust_seq;

4. Getting the next value from a sequence::

select cust_seq.nextval from dual;

5. Getting the most recently selected value from the sequence

select cust_seq.currval from dual;

6. 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;

7. 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:
1. SELECT, INSERT, UPDATE, DELETE (the meaning of these is obvious)
2. ALTER allows you to change the object definition
3. INDEX allows you to create an index on a table object
4. REFERENCES allows you to create a constraint that references the table (can only be granted to a user, not to a role)
5. 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.
6. 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;

1. PL/SQL is an Oracle proprietory procedural language for database processing. It has the following benefits:
1. it is a single language that can be used on the server and on the client (in forms, reports);
2. it provides the procedural control element that is lacking in SQL;
3. it allows SQL statements to be bundled together and processed in one go by the server engine this leads to better performance;
4. it is compiled when stored in the database thereby improving performance;
5. it is tightly bound to the Oracle database, supporting all the same datatypes and allowing variable typeing via the %TYPE and %ROWTYPE constructs.
6. it runs anywhere were the Oracle RDBMS runs i.e. its independent of host machine;
2. There are the following types of program unit:
1. Procedures: do not return a value, must be used as a PL/SQL statement;
2. Functions: must return a value and can be used wherever an expression is valid (note that they cannot be used on the left hand side of an assignment);
3. Packages: are Oracles nod in the direction of object-orientation. They group functions, procedures and variables into a cohesive whole. A package has a specification which identifies what a user of the package can do with the package, and a body which contains the implementation i.e. the code.
4. Anonymous block: is an unnamed piece of PL/SQL specified using the DECLARE….BEGIN….END; statements.
3. Anonoymous blocks are available on the server only. The others are available on the client (with some restrictions, depending on version) and on the server.

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

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%rowcound: 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.

1. Conditions
1. The basic construct is:

IF counter = 1 THEN 
……
ELSIF counter = 2 THEN
……
ELSIF counter = 3 THEN
……
ELSE
……
END IF;

2. An IF statement must have one IF, it can have zero or more ELSIFs and a maximum of 1 ELSE.
3. The ELSE clause does not have an associated THEN.
4. 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).

LOOP
  IF counter > 100 THEN
    EXIT;
  ELSE
    counter = counter + 1;
  END IF;
END LOOP;

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.

WHILE carryon LOOP
  mypackage.tidyup(carryon)
END LOOP;

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

sum := 0;
FOR I in 1..100 LOOP
  sum = sum + i;
END LOOP;

1. Fetching rows using explicit cursors:

 
declare
  cursor c_voters is
  select v.*
  from voters v
  where dob < ‘01-JAN-40’;
  l_voters c_voters%rowtype;
begin
  open c_voters;
  for I in 1..100 LOOP
    fetch c_voters into l_voters;
    if c_voters%notfound then
      exit;
    end if;
    /* Do something useful */
    dbms_output.put_line(‘Name=’||l_voters.name);
  end loop;
  close c_voters;
end;

2. Cursor for loops: these are very convenient and simple. The example below does the same as the example except that it carries on for all the records in the table.

declare
  cursor c_voters is
    select v.*
    from   voters v
    where  dob < ‘01-JAN-40’;
begin
  for l_voters in c_voters LOOP
    /* Do something useful */
    dbms_output.put_line(‘Name=’||l_voters.name);
  end loop;
end;

1. PL/SQL uses exceptions for error handling. Exceptions were invented (long before pl/sql) as a means of tidying up the clutter introduced to code by the handling of error conditions. In many programming languages, functions and procedures are written with additional parameters indicating success/failure, error message, etc. These values have to be tested after every sub-program call. Often the code with error checking is two or three times as long as it would otherwise need to be and it suffers from being difficult to understand and maintain. Exceptions allow pl/sql to avoid this problem by de-coupling exceptions handling from the normal ‘everything works ok’ execution of the code.
2. The basic types of exception are:
1. user defined exceptions
2. 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)
  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;
  when others then
    dbms_output.put_line(SQLERRM);
    raise;
end;

4. Predefined exceptions include the following:
5. cursor_already_open
1. dup_val_on_index
2. invalid_cursor
3. invalid_number
4. login_denied
5. no_data_found
6. not_logged_on
7. program_error
8. rowtype_mismatch
9. storage_error
10. timeout_on_resource
11. too_many_rows
12. value_error
13. zero_divide
6. 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.
7. 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.

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	Have a mode: In, IN OUT, OUT.  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. Packages are Oracle’s nod in the direction of object orientation (but just a nod).
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. After compiling use: show errors; to see detailed error messages
8. 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
9. views
1. user_tab_privs holds privileges for packages!
2. user source holds the source of a package.

1. Distinction between database triggers, forms triggers, stored procedures
1. A database trigger is stored on the database. The trigger is ‘triggered’ when a DML statement is executed. There is no other way of executing the code. A database trigger fires whenever the appropriate DML statement fires whatever the source of that DML statement. i.e. it doesn’t matter whether the insert/update/delete was issued by forms, sqlplus, from pro*cobol or anything else, the trigger still fires.
2. A stored procedure is simply code stored on the database. It is only fired when an application explicitly invokes it via a pl/sql or sql statement. A stored procedure can be invoked by a database trigger.
3. An Oracle Forms trigger is nothing to do with the database. It is something which fires when an event happens in a form e.g. when-validate-field fires when a user tabs out of a field. A forms trigger which issues a DML statement may cause a database trigger to fire but the two are not related.
2. 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
3. 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.
4. When a database trigger fails, the triggering DML statement is rolled back but earlier DML statements in the transaction are not.
5. 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;

6. 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;
 
7. Disable a trigger with:

alter trigger trgcar disable;

8. Enable a trigger with:

alter trigger trgcar enable;

9. Disable all triggers on a table:

alter table car disable all triggers;

10. Enable all triggers on a table:

alter table car enable all triggers;
 
11. 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.

Top of page