ORACLE BUILT IN Data Types

Valores simples sin componentes (num char bool date)

Scalar
Presicion se refiere a significativos
Large Object (LOB) Punteros a datos grandes (audios, imagenes, videos, etc)
Composite
(non scalar)
Items que tienen componentes accesibles como registros y colecciones

Reference Punteros a otros items

CHAR
CHAR(n BYTE|CHAR) Almacena n(max 2000) caracteres, en bytes o char (default en
NLS_LENGTH_SEMANTICS) si se crea en SYS, es en bytes
NCHAR (n) Almacena n(max 2000) caracteres permitiendo ASCII, UNICODE, que ocupan 2 o 3
bytes según el character set. (AL16UTF16 2b, UTF8 3b)
VARCHAR2(n BYTE|CHAR) Almacena hasta n caracteres. El máximo depende del valor de MAX_STRING_SIZE
(4000 si STANDARD, 32767 si EXTENDED) , en bytes o char (default en
NLS_LENGTH_SEMANTICS). Ojo no poner valores al pedo, porque en una query
Oracle prealoca el maximo de memoria.
NVARCHAR2(n) VARCHAR2 para Unicode, ASCII
LOB
CLOB Character Large Object. Hasta (4GB- 1) * (database block size)
NCLOB Character Large Object+ unicode. Hasta (4GB- 1) * (database block size)
BLOB Binario. Hasta (4GB- 1) * (database block size)
BFILE Referencia a un archivo externo hasta 4GB
LONG AND RAW
RAW No se usa mas, usar BLOB. 2000/32767( MAX_STRING_SIZE = ST / EXT)
LONG RAW No se usa mas, usar BLOB. Hasta 2 gb
LONG Hasta 2gb-1. No se usa mas, usar CLOB.
NUMBER
NUMBER(e,d) 38-position. d puede ser negativo;: 10,-2 significa redondeo a centena.
Nros +/-. Infinito. FLOAT ocupan menos.
Si pongo solo NUMBER, es un float del mayor rango.
BINARY_FLOAT 32 bits. ocupa 4 bytes. Puede tener un punto decimal donde sea o no tenerlo.
Aceptan infinito, 1.377e-20,NaN
BINARY_DOUBLE 64 bits. ocupa 8 bytes
FLOAT(n) [Link] es type number. Almacena como binario (?). si pongo
float(5), el valor no puede superar los 5 digitos binarios. (31 es el > q puede
representarse con 5) entonces 123.45 es redondeado a 120. Lo usa Oracle
internamente.
DATETIME
DATE 7-byte, representa timestamp en gregoriano, por defecto DD-MON-RR, segun
NLS_DATE_FORMAT en tabla V$PARAMETER
INTERVAL YEAR(p) TO 5-byte, almacena periodo en años y meses. por ejemplo insertamos INTERVAL '10-
MONTH 2' YEAR TO MONTH, 10 años y dos meses. P: digitos en year.
INTERVAL DAY(p1) TO 11-byte almacena periodo en dia hora minuto segundo. INTERVAL '11 10:09:08.555'
SECOND(p2) DAY TO SECOND. P1: digitos en year, p2:d igitos en seg.
TIMESTAMP(s) 7- to 11-byte representa fecha y hh, mi ,ss. si se pone presicion, es para la fraccion
de segundos(0-9), default 6. Formato según NLS_TIMESTAMP_FORMAT
TIMESTAMP WITH [LOCAL] 13-byte incluye offset para UTC
TIME ZONE LOCAL, cada uno ve horario de su sesión, data se guarda con zona de la DB
ROWID
ROWID 10-byte representa la pseudocolumna rowid
UROWID(n) Hasta 4000 bytes es rowid en tablas indexadas o non-Oracle.
XMLType(n) Hasta 4000 bytes, representa XML y existen funciones builtin para trabajar.
BOOLEAN Es PLSQL
%type Crea componente basado en formato y tipo de una columna de tabla o vista
%rowtype Crea registro basado en estructura de tabla o vista.

Tipos numéricos

+-2,147,483,648. 32 [Link] menos y las cuentas tardan menos que los NUMBER.
PLS_INTEGER Para valores mayores a esos usar INTEGER. Subtipos: NATURAL, NATURALN (not null),
POSITIVE,POSITIVEN, SIGNTYPE (-1,0,1)
Subtipo de PLS_INTEGER, mismo rango, not null. Si sabemos que nuestra variable no va a ser nula ni
SIMPLE_INTEGER superar rango es mejor utilizar esta que pls_integer.

BINARY_INTEGER Igual que pls_integer

BINARY_FLOAT Single-precision IEEE 754-format floating-point number
BINARY_DOUBLE Double-precision IEEE 754-format floating-point number
Es un Tipo numérico, no subtipo. Rango 1E-130 a 1.0E126
NUMBER(tot, dec)
Subtipos: DEC, DECIMAL, DOUBLE PRECISION, FLOAT, INT, INTEGER, NUMERIC, REAL,SMALLINT

Tipos caracteres (ya descriptos antes)

Tipos fechas e intervalos

Son de ancho fijo. Para comparacion usar función trunc. Tiene estos campos:
YEAR -4712 to 9999 (no 0), MONTH, DAY, HOUR, MINUTE, SECOND.
DATE
mas los campos de zona: TIMEZONE_HOUR/MINUTE/REGION/ABBR. Estos se extraen de
SYSTIMESTAMP, no SYSDATE
Extiende DATE y la presición son los decimales de segundos. El formato es según
TIMESTAMP(p)
NLS_TIMESTAMP_FORMAT. Hasta mil millonésima de segundo.
TIMESTAMP WITH La zona respecto a UTC expresada como -07.00 por ejemplo. El formato viene dado por
TIMEZONE NLS_TIMESTAMP_TZ_FORMAT en V$NLS_PARAMETERS

Funciones con fechas

ADD_MONTHS(x, y); Suma y meses a x.

LAST_DAY(x); Ultimo dia del mes.
MONTHS_BETWEEN(x, y); Nro meses entre x e y.
NEXT_DAY(x, d); D (1-7) Cuando es lunes despues de x: next_day(x,1).
NEW_TIME; Returns the time/day value from a time zone specified by the user.
ROUND(x [, unit]); Rounds x.
SYSDATE(); Returns the current datetime.
TRUNC(x [, unit]); Truncates x.
Returns a TIMESTAMP WITH TIME ZONE containing the current session
CURRENT_TIMESTAMP();
time along with the session time zone.
EXTRACT({ YEAR | MONTH | DAY |
HOUR | MINUTE | SECOND } |
{ TIMEZONE_HOUR | Extracts and returns a year, month, day, hour, minute, second, or time
TIMEZONE_MINUTE } | zone from x.
{ TIMEZONE_REGION | }
TIMEZONE_ABBR ) FROM x)
FROM_TZ(x, time_zone); Convierte TIMESTAMP x a TIMESTAMP WITH TIMEZONE.
LOCALTIMESTAMP(); TIMESTAMP en nuestra sesión.
SYSTIMESTAMP(); TIMESTAMP WITH TIME ZONE de la DB
 SYS_EXTRACT_UTC(x); Convierte TIMESTAMP WITH TIMEZONE x a TIMESTAMP en UTC.
TO_TIMESTAMP(x, [format]); Convierte x a TIMESTAMP.
TO_TIMESTAMP_TZ(x, [format]); Convierte x a TIMESTAMP WITH TIMEZONE.

NUMTODSINTERVAL(x, interval_unit); Convierte x an INTERVAL DAY TO SECOND.

NUMTOYMINTERVAL(x, interval_unit); Convierte x a INTERVAL YEAR TO MONTH.

TO_DSINTERVAL(x); Convierte x a INTERVAL DAY TO SECOND.

TO_YMINTERVAL(x); Convierte x a INTERVAL YEAR TO MONTH.

Lob: Large Object

BFILE Almacenado fuera de la DB, hasta 4GB o según S.O. Accesible desde app pero ReadOnly
Dentro de la DB 8 a 128 tb. Persistentes en la DB, aplican propiedades ACID. Temporales
BLOB/CLOB/NCLOB
(var en mi app)

Composite data types (records, objects, and collections)

Son locales a menos que esten definidas en pkg y se referencia anteponiendo pkg.

Similares a una fila de una tabla. Se declaran explicitamente , basada en cursor o tabla con
%rowtype.
Primero creamos tipo, luego el registro:
TYPE Registro is RECORD (campo tipo_campo(l) [NOT NULL := algo])
Miregistro Registro.
RECORD
Se referencia con qualifier y dot notation: [Link]
Se le asigna valores desde select, fetch, otro registro (aggregate).
NESTED RECORDS: cuando uno de los campos es a su vez RECORD. y se referencia con
qualifier y dot notation adicional.
Si se define en un package, se puede referenciar con nombre_pkg.registro

Estructura unidimensional con elementos del mismo tipo. key-value. Assoc-Array|index table
Primero creamos tipo, luego la tabla:
TYPE Registro is TABLE OF (tipo_campo(ix) [NOT NULL] index by BINARY_INTEGER|VARCHAR2
No se inicializan en la declaración.
Se referencia con qualifier y dot notation y la referencia a la primary key entre paréntesis:
tabla(v_indice) o tabla(v_indice).campo si está basada en Record.
TABLE Se pueden hacer cuentas o concatenar v_indice
Se puede asignar una tabla a otra (sobreescribiendo la segunda).
ASSOC-ARRAY
Metodos: se aplican a [Link]([indice1,[indice2]])
Tabla basada en record tiene que tener campos escalares (no nested records)
INDEX BY
TABLE Pero en una tabla de la DB puedo poner una columna que sea tabla (nested table)
Si lo declaro como Constante, tengo que crear una funcion que la lleve, como un constructor.
NESTED TABLE: Si la colección no tiene index by, es una nested table o si tiene una columna
de tabla que tenga filas. indexado por number;
Al traerlas, se las pasa a una variable asignando indice desde 1. El orden puede no siempre ser
el mismo. Usando funciones set / multiset traigo datos de una columna de tipo NT.
Debe inicializarse

Estructura unidimensional con elementos del mismo tipo. Es limitada (aunque puede
extenderse) Cada elemento se le asigna un indice que arranca en 1, sin faltantes.
Primero creamos tipo, luego varray: TYPE varray1 is VARRAY(longitud) OF Tipovalor.
Al declararlo está nulo. Puede inicializarse en la declaracion. (tiene constructor)
VARRAYS
TYPE T1 IS VARRAY(3) OF NUMBER;
TYPE T2 IS VARRAY(2) OF T1; Desde 9i se puede anidar Luego se referencia VEC1(a)(b)
Vec0 T1 := T1(1,2,3);
Vec1 T2 := T2(T1);
 User Defined Types. Creamos un objeto y luego tabla de objetos.
Como Classes en otros lenguajes con sus constructores y funciones. Soportan herencia y
polimorfismo. Las manejamos como cualquier colección.
SQL UDT
CREATE TYPE hobbit IS OBJECT (...) [NOT] INSTANTIABLE [NOT] FINAL;
Pueden usarse como columnas de una tabla. Se implementan cuando definimos BODY:
CREATE TYPE BODY hobbit IS....

Manejo xml en tabla, se pasa como pará[Link] con operadores, funciones, validador.
my_xml XMLTYPE;
XMLTYPE my_xml := XMLTYPE('<root><element>value</element></root>');
SELECT ExtractValue(Value(p),'/root/element') AS elemento FROM
TABLE(XMLSequence(my_xml.extract('//root'))) p)

URI

ANY

Metodos para colecciones de datos

EXISTS(n) Returns TRUE if the nth element in a collection exists; otherwise returns FALSE.
COUNT Returns the number of elements that a collection currently contains.
LIMIT Checks the maximum size of a collection.
Returns the first (smallest) index numbers in a collection that uses the integer
FIRST
subscripts.
Returns the last (largest) index numbers in a collection that uses the integer
LAST
subscripts.
PRIOR(n) Returns the index number that precedes index n in a collection.
NEXT(n) Returns the index number that succeeds index n.
EXTEND Appends one null element to a collection.
EXTEND(n) Appends n null elements to a collection.
EXTEND(n,i) Appends n copies of the ith element to a collection.
TRIM Removes one element from the end of a collection.
TRIM(n) Removes n elements from the end of a collection.
DELETE Removes all elements from a collection, setting COUNT to 0.
Removes the nth element from an associative array with a numeric key or a nested
DELETE(n) table. If the associative array has a string key, the element corresponding to the key
value is deleted. If n is null, DELETE(n) does nothing.
DELETE(m,n) Elimina rango m..n de un ass-arr/nested table. m<n, no null. Sino no hace nada.

Collection Exceptions

COLLECTION_IS_NULL La colección está nula.

NO_DATA_FOUND No existe el índice referido.
SUBSCRIPT_BEYOND_COUNT El indice está por fuera del límite definido
SUBSCRIPT_OUTSIDE_LIMIT Valor fuera de rango
VALUE_ERROR El valor es nulo o fuera de rango, o no convertible.

Collection Pseudofunctions

Campo = varrray(16) as varchar2(100), cole = table of varchar2

CAST
Mapea una coleccion de un tipo a otro. Select cast(campo) as cole...

COLLECT Strings_nt is table of varchar2(2000);

Agrega data a una coleccion.
 SELECT dep_id,CAST (COLLECT (last_name ORDER BY hire_date) AS strings_nt) GROUP
BY dep_id

Convierte Nested Table y elimina duplicados.

SET
SELECT SET(NT) V_NT FROM...

Mapea una tabla a una coleccion. Junto con CAST podemos traer una fila como una
MULTISET columna
SELECT CAST (MULTISET (SELECT field FROM table) AS collection-type) FROM DUAL;

Mapea coleccion a una tabla (algo q pueda ser selectable) . Inverso de Multiset.
TABLE
TABLE(scope_name.collection_name) . Ver TABLE FUNCTION
Consultas

INNER JOIN
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
INNER JOIN orders -- REGISTROS DE LA PRIMERA Q ESTEN EN LA SEGUNDA
ON suppliers.supplier_id = orders.supplier_id;
LEFT OUTER JOIN
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers -- REGISTROS DE LA PRIMERA AUNQUE NO ESTEN EN SEGUNDA, IGUAL A
SELECT CON (+)
LEFT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;
RIGHT OUTER JOIN
SELECT orders.order_id, orders.order_date, suppliers.supplier_name
FROM suppliers -- REGISTROS DE LA 2DA AUNQUE NO ESTEN EN 1RA, IGUAL A SELECT
CON (+)
RIGHT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;
FULL OUTER JOIN
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers -- TODOS LOS REGISTROS DE AMBAS TABLAS , IGUAL A SELECT CON
UNION
FULL OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;
USING
SELECT [Link], [Link] -- TOMA CAMPOS DEL MISMO NOMBRE
FROM table1 JOIN table2 USING ([Link],[Link])
NATURAL JOIN
SELECT [Link], [Link] -- TOMA CAMPOS DEL MISMO NOMBRE
FROM table1
NATURAL JOIN table2
NONEQUIJOINS : cuando las tablas no tienen campo en comun, pero si una condicion
select a.x,b.y from a join b on a.x between b.a and b.b

PIVOT
SELECT * FROM ( SELECT customer_ref, product_id FROM orders)
PIVOT
(COUNT(product_id) - siempre una funcion de agrupacion.
FOR product_id IN (10, 20, 30) para estos productos )
ORDER BY customer_ref;

LIKE
SELECT ... WHERE job_id LIKE 'SA\_%' ESCAPE '\'; (EL GUION BAJO REEMPLAZA UN CARACTER)
IN, ANY, SOME
SELECT DESCRIPCION FROM TABLA WHERE CODIGO = ANY/SOME/ALL (SELECT CODIGO FROM TABLA2
WHERE...) ALGUN VALOR QUE COINCIDA
& (AMPERSAND)
SELECT.....WHERE C1 = &V1 OR C2 = &V2
EL & VA A PEDIR UN valor por cada ocurrencia. Doble Ampersand pide el valor una sola vez

Puede usarse asi:

SELECT &V1 FROM &V2 WHERE &V3...
COALESCE
Select coalesce(c1,c2,c3,c4) from tabla Devuelve el primer arg no null
NVL2
NVL2(original, ifnotnull, ifnull),
NULLIF
NULLIF(ifunequal, comparison_term),si son iguales, nada; si no, el primero
CASE
SELECT CASE substr('1234',1,3)
WHEN '134' THEN '1234 is a match', WHEN '1235' THEN '1235 is a match', WHEN concat('1','23') THEN
concat('1','23')||' is a match' ELSE 'no match' END FROM dual;
ANY, ALL
 SELECT ... WHERE salary > ALL (SELECT salary FROM employees)
< ANY: < al mayor, > ANY: > al menor,= ANY : IN,> ALL: > al mayor,< ALL: < al menor
UNION: Devuelve la combinacion de 2 queries ordenando y sacando duplicados.
UNION ALL: Devuelve la combinacion de 2 queries, sinordenar y con duplicados.
INTERSECT: Devuelve las filas comunes a las 2 queries ordenando y sacando duplicados.
MINUS: Devuelve sólo las filas del primer query que no estén en el segundo, ordenado y sin duplicados.
MERGE: Una mezcla de insert y update, cuando corresponda
MERGE INTO employees e USING new_employees n ON (e.employee_id = n.employee_id)
WHEN MATCHED THEN UPDATE SET [Link]=[Link]
WHEN NOT MATCHED THEN INSERT (employee_id, last_name) VALUES (n.employee_id, n.last_name);
INSERT ALL|FIRST: Para insertar multiples filas en multiples tablas. Puede usarse condicionales. En ese caso ALL evalua
todas las condiciones, mientras que FIRST una vez que encontro equivalencia, no evalua mas.
INSERT [ ALL | FIRST ]
WHEN condition1 THEN INTO table_1 (column_list ) VALUES (value_list)
WHEN condition2 THEN INTO table_2(column_list ) VALUES (value_list)
ELSE INTO table_3(column_list ) VALUES (value_list)
Subquery (Select * from dual si los valores son literales)

PL/SQL: Transactions (TCL: transaction control statements)

Cada vez que nos conectamos, establecemos una sesión. ACID compliance:
Atomicity: The entire sequence of actions must be either completed or aborted. The transaction cannot be partially
successful.
Consistency: The transaction takes the resources from one consistent state to another.
Isolation:A transaction's effect is not visible to other transactions until the transaction is committed.
Durability:Changes made by the committed transaction are permanent and must survive system failure.

Commit;
SET AUTOCOMMIT ON;
-- and to turn it off
SET AUTOCOMMIT OFF;
ROLLBACK [TO SAVEPOINT savepoint_name];
SAVEPOINT savepoint_name ;

SET TRANSACTION: Permite establecer una sesión como solo de lectura o lectura/escritura, hasta un
rollback o commit (aunque no haga falta). Todas las queries dentro de la transacción solo ven cambios
commiteados antes del comienzo de la transacción

Transacciones autónomas: Permite que las actividades dentro de un subprograma no afecten las
transacciones externas. Es decir COMMIT, ROLLBACK dentro de este programa no afecta a otros.
Para esto, escribimos PRAGMA AUTONOMOUS_TRANSACTION en la declarativa del programa. Antes de salir
del programa, es obligatorio finalizar la transacción.
PL/SQL: Operators / Conditions / Iterations

Apóstrofes: Si está al comienzo o final, Select ''' para que se vea uno. Si está al medio, uso ''. Si es una
concatenación, uso ''''
También uso q' para todo: q'[]' o cualquier par (),<>,!!,¡¡

Variable_name [CONSTANT] TIPO (hasta 30).

+ - * / ** = != <> ~= < <= > >=
<< >> Etiquetas (son identificadores válidos)
" " Para Case Sensitive o nombres reservados

Comparison Operators

Operator Description
LIKE Compara string o CLOB contra un patrón.
BETWEEN Se cumple si un valor está entre otros dos. x BETWEEN a AND b significa x >= a y x <= b.
IN x IN (valores). Testea si x está entre algunos de los valores.
IS NULL Devuelve TRUE o FALSE
IS [NOT] chequea si el operador esta o no vacio. se aplica a varray o table colection. Tiene que estar
EMPTY inicializado para que funcione.
The IS SET comparison operator checks whether the left operand holds a set of elements,
IS SET
and only applies when the left operand is a varray or table collection data type.

Logical Operators

Operator Description
and TRUE AND NULL = NULL, FALSE AND NULL = FALSE
or TRUE OR NULL = TRUE, FALSE OR NULL = FALSE
not Called the logical NOT Operator.

Conditions / Iterations

IF (BOOL_1)THEN ....ELSIF(BOOL _2) THEN ...ELSE.... END IF;

CASE var WHEN 'value1' THEN .... WHEN 'value2' THEN .... ELSE ....END CASE;
CASE WHEN var = 'value1' THEN .... WHEN var = 'value2' THEN .... ELSE .....END CASE;
FOR i IN (REVERSE) 0..9 LOOP EXIT WHEN CONDICION.... END LOOP
FOR i IN (SELECT item_title FROM item) LOOP EXIT WHEN CONDICION.... END LOOP
WHILE CONDICION LOOP... CONTINUE (VUELVE AL LOOP)... goto <<etiqueta>> END LOOP
FORALL :Sirve para instrucciones DML (del mismo tipo) es mas eficiente que el FOR...LOOP. Usa un índice
como subscript. Normalmente son consecutivos. Si no lo son (como al efectuar un DELETE) se usa INDICES
OF (numericos) o VALUES OF (nombre de collection).

CREATE TABLE employees_temp AS SELECT * FROM employees;

DECLARE
TYPE NumList IS VARRAY(20) OF NUMBER;
depts NumList := NumList(10, 30, 70); -- department numbers
BEGIN
FORALL i IN [Link]..[Link] -- FORALL i IN INDICES OF depts [BETWEEN a AND b]
DELETE FROM employees_temp WHERE department_id = depts(i);
COMMIT;
END;
LOBS (BLOB,CLOB,NCLOB -->SECUREFILES)
Maximum size = (4GB – 1) * db_block_size.

mas de 32k de dtos se manejan con dbms_lob package. pueden estar null, empty, populated. no se
puede cargar mas de 32k con insert, hay que usar una funcion con RETURNING INTO en el insert.

INSERT INTO table_name

( column_name1 [, column_name2 [, column_name(n+1)]] )
VALUES
( empty_clob() [, column_value2 [, column_value(n+1)]] )
RETURNING column_value1 INTO local_variable;
-----
CREATE OR REPLACE FUNCTION create_clob ( pv_input_string VARCHAR2 ) RETURN CLOB AS
/* Declare a local CLOB variable. */
lv_return CLOB;
BEGIN
/* Create a temporary CLOB in memory for the scope of the call. */
dbms_lob.createtemporary(lv_return, FALSE, dbms_lob.CALL);

/* Write the string to the empty temporary CLOB. */

dbms_lob.WRITE(lv_return, LENGTH(pv_input_string), 1, pv_input_string);

/* Return a CLOB value. */

RETURN lv_return;
END create_clob;

CREATE DIRECTORY generic AS 'C:\Windows\temp';

GRANT READ ON DIRECTORY generic TO student;

CREATE OR REPLACE PROCEDURE load_clob_from_file

( src_file_name IN VARCHAR2 , table_name IN VARCHAR2 , column_name IN VARCHAR2
, p_k_n IN VARCHAR26 , p_k_v IN VARCHAR2 ) IS

des_clob CLOB;
src_clob BFILE := BFILENAME('GENERIC',src_file_name);
des_offset NUMBER := 1;
src_offset NUMBER := 1;
src_size INTEGER;
ctx_lang NUMBER := dbms_lob.default_lang_ctx;
warning NUMBER;
stmt VARCHAR2(2000);
BEGIN
IF dbms_lob.fileexists(src_clob) = 1 AND NOT dbms_lob.isopen(src_clob) = 1 THEN
src_size := dbms_lob.getlength(src_clob);
dbms_lob.open(src_clob,DBMS_LOB.LOB_READONLY);
END IF;

stmt := 'UPDATE '||table||''SET '||column||' = empty_clob() 'WHERE '||p_k_n||' = '||''''|| p_k_v||''' ' ||
'RETURNING '||column||' INTO :locator';
EXECUTE IMMEDIATE stmt USING OUT des_clob;
dbms_lob.loadclobfromfile( dest_lob => des_clob
, src_bfile => src_clob , amount => dbms_lob.getlength(src_clob)
, dest_offset => des_offset , src_offset => src_offset
, bfile_csid => dbms_lob.default_csid , lang_context => ctx_lang , warning => warning );
dbms_lob.close(src_clob);
IF src_size = dbms_lob.getlength(des_clob) THEN
$IF $$DEBUG = 1 $THEN dbms_output.put_line('Success!'); $END
ELSE
$IF $$DEBUG = 1 $THEN dbms_output.put_line('Failure.'); $END
RAISE dbms_lob.operation_failed;
END IF;
END load_clob_from_file;
 PL/SQL: Functions - procedures

Si creamos la función solita (Standalone) se crea como AS.

La puedo declarar tambien entre el DECLARE y BEGIN
Desde 11g los parámetros se pasan por posición como siempre o por nombre (param=>valor)
CALL function_name(parameter [, parameter [, ...]]) INTO target_variable_name;
Funciones standalone, creadas a nivel esquema con CREATE... Se guarda en DB hasta DROP
Funciones creadas en un bloque son anidadas. Se declara y define al mismo tiempo o se declara
primero y se define mas adelante en el mismo bloque (Forward declaration)
SELECT * FROM ALL_OBJECTS WHERE object_type IN ('PROCEDURE' , 'FUNCTION');
SELECT text FROM ALL_SOURCE WHERE type = 'PROCEDURE' AND name = algo
USER_ERRORS view o SHOW ERRORS
En estas vistas esán las declaraciones , contenido, errores de compilacion.
ACCESSIBLE BY: PROCEDURE|FUNCTION|TRIGGER ([Link])
DETERMINISTIC: ayuda al optimizador a evitar llamadas redundantes
PARALLEL ENABLED: Uso seguro en llamadas paralelas que evaluan DML
PIPELINED: En una función que devuelve tablas, con esto lo hace fila a fila. Se pone despues de
RETURN.
SELECT * FROM TABLE(FUNCION(...)) O select * from FUNCION() si no tiene param.
{IS|USING} IS: entonces en el cuerpo tiene que usarse PIPE. USING: pongo el ADT que contiene el
start,fetch,close.

[{EDITIONABLE | NONEDITIONABLE}]: Antes de la definición. Tiene que ver con desarrollo.

AUTHID [ DEFINER | CURRENT_USER ]: En un ADT especifica el authid de func. y proc.

PL/SQL: Packages
En la especificación se declaran los items públicos. Comparte definiciones y opciones
que vimos en funciones.
Las Funciones en packages admiten overloading (cambio de tipo y nro de parámetros,
no el nombre). Si hay cursores o subprogramas, tiene que tener un cuerpo.
En el cuerpo puedo declarar items privados.
Puedo declarar cursor en la definición y definirlo (select...) en el cuerpo, asi no se ve.
En cualquiera de las dos partes se puede mapear un procedimiento externo (java,c)
con call specification.
Los valores almacenados persisten durante la vida de la llamada del bloque sevidor.,
mientras que los no serially, duran toda la sesion.
AUTHID {DEFINER|CURRENT USER}: en la especificación determina si los programas
corren con el privilegio del Definer o del usuario y como resolver la referencia a objetos
en qcuál esquema.
cuando una sesión invoca a un package, Oracle crea una instancia y lo inicializa.
Package state: son los valores de las variables, constantes y cursores. Si no hay, es
stateless.
pragma SERIALLY_REUSABLE: mejora el uso de memoria. el estado se guarda en un
area de trabajo de memoria. Oracle puede reutilizar la instancia que está en ese pool
de memoria. De lo contraria se almacena en memoria para cada usuario.
No se usan para triggers DB, o subprograma invocado por una instruccion SQL.

puedo ver detalles con DESCRIBE

PL/SQL: Cursors
 Punteros a datos que Oracle asigna en memoria (context area). No se puede ir para atras. Implicitos, se crean con cada
DML.
OPEN cursor LOOP.. FETCH cursor into.... EXIT WHEN...END LOOP... CLOSE cursor.
Un cursor puede tener parametros, como una función. CURSOR c1 (a NUMBER,b VARCHAR2) IS
Puedo usar un cursor para updatear datos. Oracle lockea esos registros hasta que el cursor se cierra o commit o rollback
FOR UPDATE [of columna1[,columna2]] WAIT seconds | NOWAIT: no espera a unlock.
UPDATE/DELETE WHERE CURRENT OF cursor :actualiza, borra el registor en cuestion.
Cursor Variable: es una variable que referencia a un cursor. No está asignada a ninguna query específica, es decir se
puede abrir para cualquier query. Y puede pasarse entre rutinas.

TYPE c1 IS REF CURSOR RETURN tabla%rowtype-- (strong type, porque está asociada a una estructura).
TYPE c2 IS REF CURSOR --es weak type o puede usarse el tipo definido SYS_REFCURSOR.
c3 c2
OPEN c2 FOR SELECT ...
RETURN c2 -- Devuelve un SYS_REFCURSOR que se asigna a variable y se procesa como cualquier cursor.

Atributos de cursores (anteponer SQL para implícitos, el nombre del cursor para explícitos)
%FOUND TRUE si una sentencia DML (o SELECT into) afectó a una o mas fila.

%NOTFOUND Opuesto a %FOUND.

%ISOPEN Siempre es FALSE para cursores implícitos

%ROWCOUNT Filas afectadas por el DML o SELECT into

%BULK_ROWCOUNT Para DML usados con FORALL

%BULK_EXCEPTIONS %BULK_EXCEPTIONS(i).ERROR_INDEX, .COUNT, .ERROR_CODE

PL/SQL: Errores y Exceptions

Al compilar, PL nos avisa de los [Link] ver la vista ??_ERRORS o SHOW ERRORS segun donde
estemos. Categorias: SEVERE,PERFORMANCE,INFORMATIONAL.
Seteando PLSQL_WARNINGS prendemos apagamos o decimos como manejar los warnings. Con alter system,
alter session o alter statements. El valor actual esta en la vista ALL_PLSQL_OBJECT_SETTINGS.
ALERT SYSTEM|SESSION|PROCEDURE X SET|COMPILE PLSQL_WARNINGS='ENABLE|DISABLE:ALL|CATEG
DBMS_WARNING , tiene subprogramas para setear o ver valores de warnings en la sesión.

PRAGMA EXCEPTION_INIT(excep,código): Se declara una excepción nueva, se le asigna ese código. la

variable excep ya tiene que estar declarada. Se maneja como cualquire excepción, la puedo levantar como
RAISE excep.

RAISE_APPLICATION_ERROR(codigo,mensaje,true/false): Levanta una excepción en packages. Luego puedo

hacer SQLERRM(codigo) para ver ese mensaje. Códigos entre -20000 y -[Link] si queremos que nos
muestre todo el camino del error o false, si queremos que el ultimo error reeemplace a los anteriores.
Puedo volver a hacer RAISE de una excepcion para que se propague a quien llamó al proc en cuestión.

Para capturar errores en DECLARE, conviene encerrar la sección en otro BEGIN EXCEPTION END.

SQLCODE,SQLERRM(codigo): Nos da codigo y mensaje de error. No pueden usarse en una instrucción SQL
Conviene usar (a menos que estemos en un FORALL)
DBMS_UTILITY.FORMAT_ERROR_STACK: Nos da el nro de error
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE: Nos muestra proceso y linea de donde viene el error

Adicionalmente UTL_CALL_STACK tiene funciones y metodos para el manejo de subprogramas y errores.

BULK OPERATIONS
BULK COLLECT: trae muchas filas en un solo fetch. Trae todo a una collection index by PLS_INTEGER. Evita
idas y vueltas entre PLSQL y SQL.
SELECT .... BULK COLLECT INTO (Table) [LIMIT 1000].
Si no encuentrala colección está vacía, arranca en 1, es densa y solo enteros. No s einicializa ni extiende (si
lo lleno en un varray).
Luego se trabaja con como cursores o FORALL

OPEN A1
FETCH A1 BULK COLLECT INTO TABLA LIMIT 1000 EXIT WHEN [Link] = 0;
FOR x in 1..[Link] LOOP tabla(x).campo .......END LOOP
CLOSE A1

PL/SQL: Triggers
PL blocks que se ejecutan antes, despues o en lugar de un evento. Se guardan en LONG asi q max 32k. Se
ejecutan automaticamente. A nivel tabla, vistas, esquemas o DB.
Eventos: DML, DDL, logon, startup, servererror. 12 max por tabla.
Se administran con ALTER TRIGGER nombre [COMPILE|ENABLE|DISABLE] y también a nivel tabla ALTER
TABLE tabla [ENABLE|DISABLE] ALL TRIGGERS. DROP TRIGGER nombre.

Triggers se guardan en USER_OBJETS / USER_TRIGGERS.

Se disparan BEFORE, AFTER del evento general o por cada fila con FOR EACH ROW.
Se puede poner PRECEDES|FOLLOWS nombre_t para indicar el orden de disparo de triggers. Precedes solo
para triggers crossedition.
Se usa :[Link],:[Link] para referenciar esos valores, o REFERENCING OLD as x | NEW as y. Estos
valores se llaman correlation names o pseudorecords
DML: INSERT,DELETE,UPDATE
DDL: ddl,alter,create,drop,grant,logon,logoff,rename, revoke,truncate
SYSTEM: startup,shutdown,servererror (no aplica en ora 1034,1403,1422,1423,4030)
No se puede usar commit, rollback o ddl
Se pueden usar event attributes (variables que tienen info)
Se pueden combinar o condicionar:
CREATE TRIGGER t BEFORE INSERT OR UPDATE OF campo ON schema|database|tabla
BEGIN CASE WHEN INSERTING|UPDATING [('columna')]|DELETING|condición THEN .... END CASE
En condición puedo referenciar new, old (sin los :)
INSTEAD OF: Para vistas, row-level, no condicional. Puede leer valores old/new, pero no cambiarlos. Si se usa
NESTED TABLE, opera en los elementos de la columna anidada.
COMPOUND: puede dispararse en varios puntos, caa sección tiene sus instrucciones , excepciones. también
puede tener codigo en comun. Se usan por ejemplo para acumular filas que puedan ser insertadas despues
con bulk, o para evitar los errores de tablas mutantes. BEFORE|AFTER STATEMENT|EACH ROW

Metadata disponible sobre eventos que ORA_IS_CREATING_NESTED_TABLE

dispararon el trigger ORA_IS_DROP_COLUMN
ORA_IS_SERVERERROR
ORA_CLIENT_IP_ADDRESS ORA_LOGIN_USER
ORA_DATABASE_NAME ORA_PARTITION_POS
ORA_DES_ENCRYPTED_PASSWORD: si se crea o altera ORA_PRIVILEGE_LIST
un usuario ORA_REVOKEE()
ORA_DICT_OBJ_NAME: nombre del objeto en donde ORA_SERVER_ERROR
ocurrió la DDL ORA_SERVER_ERROR_DEPTH
ORA_DICT_OBJ_NAME_LIST(): lista de objetos que son ORA_SERVER_ERROR_MSG
modificados en el evento ORA_SERVER_ERROR_NUM_PARAMS
ORA_DICT_OBJ_OWNER ORA_SERVER_ERROR_PARAM
ORA_DICT_OBJ_OWNER_LIST() ORA_SQL_TXT
ORA_DICT_OBJ_TYPE ORA_SYSEVENT
ORA_GRANTEE ORA_WITH_GRANT_OPTION
ORA_INSTANCE_NUM SPACE_ERROR_INFO
ORA_IS_ALTER_COLUMN(col): boolean
DYNAMIC NATIVE SQL (DNS)
Para generar y correr SQL en runtime. Hay dos maneras DNS, DBMS_SQL (Ver Oracle Packages).
DNS usa EXECUTE IMMEDIATE o OPEN-FOR, FETCH, CLOSE.
En general se usa DNS pero si no sabemos bien los valores a procesar, nombres de tablas, etcc. se usa el
paquete. Tambien hay cosas que solo se hacen con el paquete.
Se pueden usar placeholders, que tienen que tener su valor correspondiente. No se puede usar NULL como
bind variable, aunque si una var no inicializada. Las instrucciones pueden se run bloque entero, no puede
tener exceptions.

EXECUTE IMMEDIATE 'statement';

EXECUTE IMMEDIATE 'statement :x1,:x2,:x3' USING [IN OUT] a,b,c;
Si la instruccion representa un bloque anonimo, el nombre de las variables, si se repiten, son significantes.
EXECUTE IMMEDIATE 'BEGIN statement :x1,:x2,:x2,:x1' USING [IN OUT] a,b; --con eso alcanza.
Si la ejecución devuelve mas de una fila, usamos un REF CURSOR...
x := 'SELECT * from tabla where campo = :valor';
OPEN cur FOR x USING 'mivalor'; .. y después como siempre.
Como SQL primero hace el analisis de la instrucción (parse) y luego reemplaza las variables (bind) no se
puede SELECT .....FROM :A, no se puede;

RETURNING INTO: Especifica las variables dondes se almacenan los resultados devueltos por una instrucción.
Static: I,U,D; Dyn: ExIm. Solo si devuelve una fila, a menos que sea con bulk collect. Aparece en la
instrucción y en 'USING...'
x := 'UPDATE A SET B=:1 WHERE ID=:2 RETURNING columna [bulk collect] INTO :3';
EXECUTE IMMEDIATE X USING a,b RETURNING INTO c;

dbms_assert: valida para evitar injections

COMPOSITE TABLE COLLECTION

Puede ser Objetos o Objeto que tiene una coleccion anidada. SELECT *
(multilevel) FROM TABLE(
CREATE OR REPLACE SELECT CAST(COLLECT(
TYPE prominent_object IS OBJECT ( N V2(20) , a V2(10)); people_object( 'Men' ,
CREATE OR REPLACE prominent_object('Aragorn','3rd Age') )
TYPE people_object IS OBJECT ( race VARCHAR2(10) , ) AS people_table ) FROM dual);
exemplar PROMINENT_OBJECT);
CREATE OR REPLACE FUNCION COLLECT: Toma como argumento
TYPE people_table IS TABLE OF people_object; columna y genera nested table del select
que hace. se tiene que ver con CAST
CAST convierte un tipo en otro.

ASSOCIATIVE ARRAYS (Index-by Table)

No pueden ser multinivel. son plsql, no pueden devolverse en funciones ni formar parte de
columnas, son de uso programático.

TYPE T1 IS TABLE OF element_type [NOT NULL] INDEX BY BINARY_INTEGER|VARCHAR2(size);

No se puede inicializar como v_a('a','b') pq tiene indice. V_A(indice) = valor. No tienen que ser
secuenciales, puede ser cualquier entero. Cuando se trabaja con Objetos, se carga por indice con
constructor, se referencia por el nombre del campo:

Puedo cargar un cursor en un array que sea rowtype con array(x) := cursor;

TYPE suit_object IS OBJECT ( campo VARCHAR2(7));

TYPE suit_table IS TABLE OF suit_object INDEX BY BINARY_INTEGER;
lv_suit SUIT_TABLE;
---
lv_suit(1) := suit_object('Club');
dbms_output.put_line(lv_suit(1).campo);

CREATE OR REPLACE Crea un asoc. arr. q usa un objeto como base.

TYPE PO IS OBJECT(n1 V2(20),N2 V2(10)); valido en plsql. se puede asignar el resultado de
DECLARE un constructor a cada elemento, por ser un
TYPE pt IS TABLE OF PO INDEX BY INTEGER; objeto. si fuera un record type, habria que
lv_array PT; asignar fila a fila o campo a campo
BEGIN
lv_array(-1) := PO('Bard ','3rd Age');
IF lv_array.EXISTS(-1) THEN ALGO END IF;
END

/* Define a symmetrical record data type. */

TYPE PR IS RECORD ( id INTEGER , element PO );
TYPE PT IS TABLE OF PR INDEX BY PLS_INTEGER;
lv_array PT;
BEGIN
lv_array(1).id := 1; /* The initial element uses 1 as an index value. */
lv_array(1).element := prominent_object('Bilbo Baggins','3rd Age');
lv_array(2).id := 2;
lv_array(2).element := prominent_object('Frodo Baggins','3rd Age');

FOR i IN 1..lv_array.COUNT LOOP

IF lv_array.EXISTS(i) THEN ALGO END IF;
END LOOP;
END;
DECLARE
TYPE emp_copy_nt IS TABLE OF employees%ROWTYPE;
l_emps emp_copy_nt;
BEGIN
SELECT * BULK COLLECT INTO l_emps FROM employees;
END;
BUILT IN FUNCTIONS

CONVERT, convierte de un charset a otro. CONVERT(text,'AL32UTF8','UTF8')

EXTRACT saca un dato de una fecha EXCTRACT (HOUR,DAY,MONTH FROM --DATE--)

COALESCE(a,b,c,d..= devuelve el primero no nulo.

GREATEST / LEAST: Compara dos escalares y deveulve mayor o menor

NANVL(mal,bien) es como nvl pero para numeros que sos NaN

SYS_CONTEXT('USERENV','PROP') Devuelve valores de entorno o ambiente

ACTION Identifies the position in the module. You use the dbms_application_info
package to set the value
AUDITED_CURSORID Returns the cursor ID of the SQL statement that triggered an audit
event. It is not a valid value when you’re using fine-grain auditing, in
which case it returns a null
AUTHENTICATED_IDENTITY Returns the authenticated identity in a format that differs by type of
authentication, like Kerberos, SSL, password, OS, Radius, proxy, or
SYSDBA/SYSOPER
AUTHENTICATION_DATA Containsvalue to authenticate the user, which may be an X.503
certificate
AUTHENTICATION_METHO Returns the authenticated method, like Kerberos, SSL, password,OS,
D Radius, proxy, or background process.
BG_JOB_ID Returns the current session identifier established by a background DB
process
CLIENT_IDENTIFIER Returns an identifier set by calling the SET_IDENTIFIERprocedure from
the dbms_session package, the OCI_ATTR_CLIENT_IDENTIFIER attribute,
or the setClientIdentifier method of the Java class
[Link]
CLIENT_INFO Returns a 64-byte character string set by calling the SET_CLIENT_INFO
procedure of the dbms_application_info package

.
.
CURRENT_BIND Returns bind variables or fine-grain auditing.
CURRENT_EDITION_NAME Returns the edition in use by the current session.
CURRENT_EDITION_ID Returns the identifier of the edition in use by the current session.
CURRENT_SCHEMA Returns the current schema name, which you can change by
calling the ALTER SESSION SET CURRENT_SCHEMA statement.
CURRENT_SCHEMAID Returns the current schema identifier, which you can change by
calling the ALTER SESSION SET CURRENT_SCHEMA statement.
CURRENT_SQL or
CURRENT_SQLn
Returns the first 4KB of the current SQL statement that triggered
fine-grain auditing. You use CURRENT_SQLn (where n is an
integer) to get the next 4KB of the current SQL statement.
CURRENT_SQL_LENGTH Returns the byte length of the SQL statement that triggered
a fine-grain auditing event.
DB_DOMAIN Returns the database initialization parameter of the same name
when it is set.
DB_NAME Returns the database initialization parameter of the same name
when it is set.
DB_UNIQUE_NAME
Returns the database initialization parameter of the same name
ENTRYID
Returns the current audit entry number. This sequence value is
shared between regular and fine-grain auditing and cannot be
used in distributed scope.
ENTERPRISE_IDENTITY
Returns the user’s enterprise-wide identity, which is an OID value
set as the DN value.
FG_JOB_ID Returns the current session identifier when established by
 a foreground database process.
GLOBAL_CONTEXT_MEMORY
Returns the number being used in the SGA by the globally
accessed context.
GLOBAL_UID Returns the current session identifier when established by
a background database process.
HOST Returns the machine hostname value.
IDENTIFICATION_TYPE Returns the method used to establish the current session, as follows:
LOCAL when identified by password
EXTERNAL when identified externally
GLOBAL SHARED when identified globally
GLOBAL PRIVATE when identified globally by DN
INSTANCE Returns the identification number of the current instance.
INSTANCE_NAME Returns the name of the current instance.
IP_ADDRESS Returns the IP address for the server or virtual machine running
the instance.
ISDBA Returns true when the current user has DBA privileges and returns false when they do
not.
LANG Returns the ISO abbreviation for the language name.
LANGUAGE Returns the language and territory currently in use and the character set
separated by a period.
MODULE Returns the application name set by the SET_MODULE procedure in the
dbms_application_info package.
NETWORK_PROTOCOL Returns network protocol value for a connection.
NLS_CALENDAR Returns the current session’s calendar.
NLS_CURRENCY Returns the current session’s currency.
NLS_DATE_FORMAT Returns the current session’s default date format.
NLS_DATE_LANGUAGE Returns the current session’s language for expressing dates.
NLS_SORT Returns the current session’s linguistic sort basis or the default BINARY.
NLS_TERRITORY Returns the current session’s territory.
OS_USER Returns the operating system user account that initiated the current database
session.
POLICY_INVOKER Returns the invoker of row-level security (RLS) policy function.
PROXY_ENTERPRISE_IDENTITY
Returns the Oracle Internet Directory DN when the proxy user is
an enterprise user.
PROXY_GLOBAL_UID Returns the global user identifier from the Oracle Internet
Directory for Enterprise User Security (EUS) proxy users, or null
for all other proxy users.
PROXY_USER Returns the user name of the database user who opened the
current session on behalf of the SESSION_USER.
PROXY_USERID Returns the user identifier of the database user who opened the
current session on behalf of the SESSION_USER.
SERVER_HOST Returns the server hostname.
SERVICE_NAME Returns the service hostname.
SESSION_EDITION_NAME Returns the edition in use by the current session.
SESSION_EDITION_ID Returns the edition identifier in use by the current session.
SESSION_USER Returns the schema for Enterprise users, and the database user
name by which the current session is authenticated.
SESSION_USERID Returns the database user identifier by which the current session
is authenticated.
SESSIONID Returns the auditing session identifier.
SID Returns the session number, which is different from the session
identifier.
STATEMENTID Returns the number of the SQL statement audited in a given
session. This attribute cannot be used in distributed scope.
TERMINAL Returns the server hostname

when it is set.

TABLE Nos permite trata colecciones como filas columnas en un select.

SELECT * FROM TABLE(street_list('4000 Warner Blvd','Suite 701'));

TREAT permite instanciar y poner en memoria objets type de una tabla.

Para colecciones

CARDINALITY (algo) es como el count pero para colecciones y listas

COLLECT te permite tomar datos de un objet type en un varray o [Link].
wrap files
Para encriptar archivos. los packages y demas deben tener separadas las especificaciones y las
implementaciones. No para triggers. Pueden compilarse despues.
wrap iname=[Link] oname=[Link]

resultado := dbms_ddl.wrap(ddl=> fuente,lb=>1,ub=>[Link]); luego concateno

execute immediate result;

USER_OBJECTS: nos muestra la validez o invalidez (a raiz de cambios) de los objetos.

SQL LOADER

.ctl

LOAD DATA
INFILE 'path\FILE'
TRUNCATE
INTO TABLE table
FIELDS TERMINATED BY ","
(col1, col2..., col n)

SQLLDR user=id/pass control='ctl'

 SYS_CONTEXT: Nos devuelve el valor de un parámetro asociado a un namespace. Se
usa en SQL y PL/SQL, localmente. Esos parámetros pueden ser creados con
DBMS_SESSION.set_context
sys_context('[Link]',longitud); --longitud si sabemos que puede
devolver >256bytes.
Orcle ya tiene estos namespaces:

USERENV: la sesión actual.

SYS_SESSION_ROLES: Indica si un rol está habilitado para la sesion. Oracle evalua al
usuario actual.

Parameter Return Value

ACTION Identifies the position in the module (application name) and

is set through the DBMS_APPLICATION_INFO package or OCI.

IS_APPLICATION_ROOT Identifies whether or not an application is the application

root.

IS_APPLICATION_PDB Identifies whether or not a container is an Application PDB .

AUDITED_CURSORID Returns the cursor ID of the SQL that triggered the audit. This
parameter is not valid in a fine-grained auditing environment.
If you specify it in such an environment, then Oracle
Database always returns null.

AUTHENTICATED_IDENTITY Returns the identity used in authentication. In the list that

follows, the type of user is followed by the value returned:
Kerberos-authenticated enterprise user: kerberos principal
name
Kerberos-authenticated external user : kerberos principal
name; same as the schema name
SSL-authenticated enterprise user: the DN in the user's PKI
certificate
SSL-authenticated external user: the DN in the user's PKI
certificate
Password-authenticated enterprise user: nickname; same as
the login name
Password-authenticated database user: the database
username; same as the schema name
OS-authenticated external user: the external operating
system user name
Radius-authenticated external user: the schema name
Proxy with DN : Oracle Internet Directory DN of the client
Proxy with certificate: certificate DN of the client
Proxy with username: database user name if client is a local
database user; nickname if client is an enterprise user.
SYSDBA/SYSOPER using Password File: login name
SYSDBA/SYSOPER using OS authentication: operating system
user name
Password-authenticated OCI IAM user: IAM user name; same
as the login name
IAM token-authenticated enterprise user: IAM user name

AUTHENTICATION_DATA Data being used to authenticate the login user. For X.503
certificate authenticated sessions, this field returns the
context of the certificate in HEX2 format.
Note: You can change the return value of
the AUTHENTICATION_DATA attribute using
the length parameter of the syntax. Values of up to 4000 are
accepted. This is the only attribute of USERENV for which
Oracle Database implements such a change.
Parameter Return Value

AUTHENTICATION_METHOD Returns the method of authentication. In the list that follows,

the type of user is followed by the method returned:
Password-authenticated enterprise user, local database user,
or user with the SYSDBA or SYSOPER administrative privilege
using a password file; proxy with username using password:
PASSWORD
Password-authenticated enterprise user, OCI IAM user, local
database user, or user with the SYSDBA or SYSOPER
administrative privilege using a password file; proxy with
username using password: PASSWORD_GLOBAL
OCI IAM token-authenticated enterprise user: TOKEN_GLOBAL
Kerberos-authenticated enterprise user or external user (with
no administrative privileges): KERBEROS
Kerberos-authenticated enterprise user (with administrative
privileges): KERBEROS_GLOBAL
Kerberos-authenticated external user (with administrative
privileges): KERBEROS_EXTERNAL
SSL-authenticated enterprise or external user (with no
administrative privileges): SSL
SSL-authenticated enterprise user (with administrative
privileges): SSL_GLOBAL
SSL-authenticated external user (with administrative
privileges): SSL_EXTERNAL
Radius-authenticated external user: RADIUS
OS-authenticated external user or use with the SYSDBA or
SYSOPER administrative privilege: OS
Proxy with certificate, DN, or username without using
password: NONE
Background process (job queue slave process): JOB
Parallel Query Slave process: PQ_SLAVE
For non-administrative connections, you can
use IDENTIFICATION_TYPE to distinguish between external
and enterprise users when the authentication method is
PASSWORD, KERBEROS, or SSL. For administrative
connections, AUTHENTICATION_METHOD is sufficient for the
PASSWORD, SSL_EXTERNAL, and SSL_GLOBAL authentication
methods.

BG_JOB_ID Job ID of the current session if it was established by an Oracle

Database background process. Null if the session was not
established by a background process.

CDB_DOMAIN CDB_DOMAIN is the DB_DOMAIN of the CDB and is the same

for all the PDBs associated with it.

CDB_NAME If queried while connected to a multitenant container

database (CDB), returns the name of the CDB. Otherwise,
returns null.

CLIENT_IDENTIFIER Returns an identifier that is set by the application through

the DBMS_SESSION.SET_IDENTIFIER procedure, the OCI
attribute OCI_ATTR_CLIENT_IDENTIFIER, or Oracle Dynamic
Monitoring Service (DMS). This attribute is used by various
database components to identify lightweight application
users who authenticate as the same database user.

CLIENT_INFO Returns up to 64 bytes of user session information that can

be stored by an application using
the DBMS_APPLICATION_INFO package.

CLIENT_PROGRAM_NAME The name of the program used for the database session.
Parameter Return Value

CON_ID If queried while connected to a CDB, returns the current

container ID. Otherwise, returns 0.

CON_NAME If queried while connected to a CDB, returns the current

container name. Otherwise, returns the name of the
database as specified in the DB_NAME initialization
parameter.

CURRENT_BIND The bind variables for fine-grained auditing. You can specify
this attribute only inside the event handler for the fine-
grained auditing feature.

CURRENT_EDITION_ID The identifier of the current edition.

CURRENT_EDITION_NAME The name of the current edition.

CURRENT_SCHEMA The name of the currently active default schema. This value
may change during the duration of a session through use of
an ALTER SESSION SET CURRENT_SCHEMA statement. This
may also change during the duration of a session to reflect
the owner of any active definer's rights object. When used
directly in the body of a view definition, this returns the
default schema used when executing the cursor that is using
the view; it does not respect views used in the cursor as
being definer's rights.
Note: Oracle recommends against issuing the SQL
statement ALTER SESSION SET CURRENT_SCHEMA from
within all types of stored PL/SQL units except logon triggers.

CURRENT_SCHEMAID Identifier of the currently active default schema.

CURRENT_SQL CURRENT_SQL returns the first 4K bytes of the current SQL

CURRENT_SQLn that triggered the fine-grained auditing event.
The CURRENT_SQLn attributes return subsequent 4K-byte
increments, where n can be an integer from 1 to 7,
inclusive. CURRENT_SQL1 returns bytes 4K to
8K; CURRENT_SQL2 returns bytes 8K to 12K, and so forth.
You can specify these attributes only inside the event handler
for the fine-grained auditing feature.

CURRENT_SQL_LENGTH The length of the current SQL statement that triggers fine-
grained audit or row-level security (RLS) policy functions or
event handlers. You can specify this attribute only inside the
event handler for the fine-grained auditing feature.

CURRENT_USER The name of the database user whose privileges are

currently active. This may change during the duration of a
database session as Real Application Security sessions are
attached or detached, or to reflect the owner of any active
definer's rights object. When no definer's rights object is
active, CURRENT_USER returns the same value
as SESSION_USER. When used directly in the body of a view
definition, this returns the user that is executing the cursor
that is using the view; it does not respect views used in the
cursor as being definer's rights. For enterprise users, returns
schema. If a Real Application Security user is currently active,
returns user XS$NULL.
See Also: Oracle Database 2 Day + Security Guide for more
information on user XS$NULL
Parameter Return Value

CURRENT_USERID The identifier of the database user whose privileges are

currently active.

DATABASE_ROLE The database role using the SYS_CONTEXT function with

the USERENV namespace. The role is one of the
following: PRIMARY, PHYSICAL STANDBY, LOGICAL STANDBY,
SNAPSHOT STANDBY.

DB_DOMAIN Domain of the database as specified in

the DB_DOMAIN initialization parameter.

DB_NAME Name of the database as specified in

the DB_NAME initialization parameter.

DB_SUPPLEMENTAL_LOG_LEV If supplemental logging is enabled, returns a string

EL containing the list of enabled supplemental logging levels.
Possible values
are: ALL_COLUMN, FOREIGN_KEY, MINIMAL, PRIMARY_KEY, PR
OCEDURAL, and UNIQUE_INDEX. If supplemental logging is
not enabled, returns null.

DB_UNIQUE_NAME Name of the database as specified in

the DB_UNIQUE_NAME initialization parameter.

DBLINK_INFO Returns the source of a database link session. Specifically, it

returns a string of the form:
SOURCE_GLOBAL_NAME=dblink_src_global_name,
DBLINK_NAME=dblink_name,
SOURCE_AUDIT_SESSIONID=dblink_src_audit_sessionid
where:
dblink_src_global_name is the unique global name of the
source database
dblink_name is the name of the database link on the source
database
dblink_src_audit_sessionid is the audit session ID of the
session on the source database that initiated the connection
to the remote database using dblink_name

DRAIN_STATUS Displays the draining status for the current session. If the
session is a candidate for drain, returns DRAINING, else
returns NONE.

ENTRYID The current audit entry number. The audit entryid sequence
is shared between fine-grained audit records and regular
audit records. You cannot use this attribute in distributed SQL
statements. The correct auditing entry identifier can be seen
only through an audit handler for standard or fine-grained
audit.

ENTERPRISE_IDENTITY Returns the user's enterprise-wide identity:

For enterprise users: the Oracle Internet Directory DN.
For external users: the external identity (Kerberos principal
name, Radius schema names, OS user name, Certificate DN).
For local users and SYSDBA/SYSOPER logins: NULL.
The value of the attribute differs by proxy method:
For a proxy with DN: the Oracle Internet Directory DN of the
client
For a proxy with certificate: the certificate DN of the client for
external users; the Oracle Internet Directory DN for global
users
For a proxy with username: the Oracle Internet Directory DN
Parameter Return Value

if the client is an enterprise users; Null if the client is a local

database user.
For OCI IAM users: the Oracle Cloud Identifier (OCID).

FG_JOB_ID If queried from within a job that was created using

the DBMS_JOB package: Returns the job ID of the current
session if it was established by a client foreground process.
Null if the session was not established by a foreground
process.
Otherwise: Returns 0.

GLOBAL_CONTEXT_MEMORY Returns the number being used in the System Global Area by
the globally accessed context.

GLOBAL_UID Returns the global user ID (GUID) from Active Directory for
Centrally Managed Users (CMU) logins, or from Oracle
Internet Directory for Enterprise User Security (EUS) logins.
Returns null for all other logins.

HOST Name of the host machine from which the client has
connected.

IDENTIFICATION_TYPE Returns the way the user's schema was created in the
database. Specifically, it reflects the IDENTIFIED clause in
the CREATE/ALTER USER syntax. In the list that follows, the
syntax used during schema creation is followed by the
identification type returned:
IDENTIFIED BY password: LOCAL
IDENTIFIED EXTERNALLY: EXTERNAL
IDENTIFIED GLOBALLY: GLOBAL SHARED
IDENTIFIED GLOBALLY AS DN: GLOBAL PRIVATE
GLOBAL EXCLUSIVE for exclusive global user mapping.
GLOBAL SHARED for shared user mapping.
NONE when the schema is created with no authentication.

INSTANCE The instance identification number of the current instance.

INSTANCE_NAME The name of the instance.

IP_ADDRESS IP address of the machine from which the client is connected.

If the client and server are on the same machine and the
connection uses IPv6 addressing, then ::1 is returned.

IS_APPLY_SERVER Returns TRUE if queried from within a SQL Apply server in a

logical standby database. Otherwise, returns FALSE.

IS_DG_ROLLING_UPGRADE Returns TRUE if a rolling upgrade of the database software in

a Data Guard configuration, initiated by way of
the DBMS_ROLLING package, is active. Otherwise,
returns FALSE.

ISDBA Returns TRUE if the user has been authenticated as having

DBA privileges either through the operating system or
through a password file.

LANG The abbreviated name for the language, a shorter form than
the existing 'LANGUAGE' parameter.
Parameter Return Value

LANGUAGE The language and territory currently used by your session,

along with the database character set, in this form:
language_territory.characterset

LDAP_SERVER_TYPE Returns the configured LDAP server type, one

of OID, AD(Active Directory), OID_G, OPENLDAP.

MODULE The application name (module) set through

the DBMS_APPLICATION_INFO package or OCI.

NETWORK_PROTOCOL Network protocol being used for communication, as specified

in the 'PROTOCOL=protocol' portion of the connect string.

NLS_CALENDAR The current calendar of the current session.

NLS_CURRENCY The currency of the current session.

NLS_DATE_FORMAT The date format for the session.

NLS_DATE_LANGUAGE The language used for expressing dates.

NLS_SORT BINARY or the linguistic sort basis.

NLS_TERRITORY The territory of the current session.

ORACLE_HOME The full path name for the Oracle home directory.

OS_USER Operating system user name of the client process that

initiated the database session.

PLATFORM_SLASH The slash character that is used as the file path delimiter for
your platform.

POLICY_INVOKER The invoker of row-level security (RLS) policy functions.

PROXY_ENTERPRISE_IDENTITY Returns the Oracle Internet Directory DN when the proxy

user is an enterprise user.

PROXY_USER Name of the database user who opened the current session
on behalf of SESSION_USER.

PROXY_USERID Identifier of the database user who opened the current

session on behalf of SESSION_USER.

SCHEDULER_JOB Returns Y if the current session belongs to a foreground job

or background job. Otherwise, returns N.

SERVER_HOST The host name of the machine on which the instance is

running.

SERVICE_NAME The name of the service to which a given session is

connected.

SESSION_DEFAULT_COLLATIO The default collation for the session, which is set by

Parameter Return Value

N the ALTER SESSION SET DEFAULT_COLLATION ... statement.

SESSION_EDITION_ID The identifier of the session edition.

SESSION_EDITION_NAME The name of the session edition.

SESSION_USER The name of the session user (the user who logged on). This
may change during the duration of a database session as
Real Application Security sessions are attached or detached.
For enterprise users, returns the schema. For other users,
returns the database user name. If a Real Application
Security session is currently attached to the database
session, returns user XS$NULL.
See Also: Oracle Database 2 Day + Security Guide for more
information on user XS$NULL

SESSION_USERID The identifier of the session user (the user who logged on).

SESSIONID The auditing session identifier. You cannot use this attribute
in distributed SQL statements.

SID The session ID.

STATEMENTID The auditing statement identifier. STATEMENTID represents

the number of SQL statements audited in a given session.
You cannot use this attribute in distributed SQL statements.
The correct auditing statement identifier can be seen only
through an audit handler for standard or fine-grained audit.

TERMINAL The operating system identifier for the client of the current
session. In distributed SQL statements, this attribute returns
the identifier for your local session. In a distributed
environment, this is supported only for
remote SELECT statements, not for remote INSERT, UPDATE,
or DELETE operations. (The return length of this parameter
may vary by operating system.)

UNIFIED_AUDIT_SESSIONID If queried while connected to a database that uses unified

auditing or mixed mode auditing, returns the unified audit
session ID.
If queried while connected to a database that uses traditional
auditing, returns null.