Use an Associative Array

Completed the Preinstallation Tasks module

Completed the Installing the Oracle9i Database module

Documentation: PL/SQL User's Guide and Reference

Using Associative Arrays

You will create a table of employee records and index the table using the employees' e-mail ID. Open a SQL*Plus session and execute the following script:

@assocarray

SET SERVEROUTPUT ON
DECLARE
  /* Index the table by a VARCHAR2 column type */
  TYPE emp_tab_type    IS TABLE
     OF employees%ROWTYPE
     INDEX BY employees.email%TYPE;
  v_emp_tab    emp_tab_type;
  /* Hold the character index here */
  v_email employees.email%TYPE;
BEGIN
  /* Populate the table */
  v_emp_tab('COLSEN').first_name := 'Christopher';
  v_emp_tab('CDAVIES').first_name := 'Curtis';
  v_emp_tab('CVISHNEY').first_name    := 'Clara';

  /* Get the first index value */
  v_email := v_emp_tab.FIRST();
  /* Display the entire table */
  WHILE v_email IS NOT NULL LOOP
    dbms_output.put_line
      (' Index: ' || v_email);
    dbms_output.put_line
      (' Name: ' || v_emp_tab(v_email).first_name);
  /* Get the next index value    */
  v_email := v_emp_tab.NEXT(v_email);
  END LOOP;
END;
/

Using the CREATE DIRECTORY Statement to manage access to directories

You will create a directory and grant read access to HR. Open a SQL*Plus session and execute the following script:

@dircreate

SET ECHO ON
CONNECT / AS SYSDBA;
CREATE OR REPLACE DIRECTORY user_dir AS 'D:\wkdir';
GRANT READ ON DIRECTORY user_dir TO hr;
CREATE OR REPLACE DIRECTORY sys_dir AS 'D:\wkdir';
GRANT READ ON DIRECTORY sys_dir TO DBA;

HR has access to the files located in the d:\wkdir directory. Subdirectory access is not available. Accessing any file within the d:\wkdir directory must follow the case sensitivity rules as required by the operating system. All other users trying to access a file within this directory receive an Oracle exception: ORA-29289: directory access denied. You can catch this exception by using the UTL_FILE package exception ACCESS_DENIED. To show how this works, logon as HR and execute the following script:

@dirtestsys

connect hr/hr@orcl.world
set serveroutput on
DECLARE
   v_fh    UTL_FILE.fILE_TYPE;
   v_buffer  VARCHAR2(4000) := 'Hello Utl_File'; 
BEGIN
   v_fh := UTL_FILE.FOPEN('SYS_DIR', 'userdata.txt', 'w');
   UTL_FILE.PUT_LINE(v_fh, v_buffer);
   UTL_FILE.FCLOSE(v_fh);
EXCEPTION
   WHEN UTL_FILE.ACCESS_DENIED THEN
     DBMS_OUTPUT.PUT_LINE('No Access!!!');
   WHEN others THEN
     DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
END;
/

Now you can test access on the USER_DIR. From your SQL*Plus session, execute the following script:

@dirtestuser

connect hr/hr@orcl.world
set serveroutput on
DECLARE
   v_fh    UTL_FILE.fILE_TYPE;
   v_buffer  VARCHAR2(4000) := 'Hello Utl_File'; 
BEGIN
   v_fh := UTL_FILE.FOPEN('USER_DIR', 'userdata.txt', 'w');
   UTL_FILE.PUT_LINE(v_fh, v_buffer);
   UTL_FILE.FCLOSE(v_fh);
EXCEPTION
   WHEN UTL_FILE.ACCESS_DENIED THEN
     DBMS_OUTPUT.PUT_LINE('No Access!!!');
   WHEN others THEN
     DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
END;
/

You can now check your working directory to see that the userdata.txt file is there.

From your SQL*Plus sessioin, execute the following script:

@rawdata

connect hr/hr@orcl.world
set serveroutput on
declare
  v_record char(80);
  c_record_size constant binary_integer := 80;
  v_raw raw(32767);
  v_raw_size constant binary_integer := 32767;
  v_handle Utl_File.File_Type;
  c_location constant varchar2(80) := 'USER_DIR';
  c_filename constant varchar2(80) := 'userdata.txt';
  procedure Put_Raw ( p_text in varchar2 ) is begin
    v_record := p_text;
    v_raw := Utl_Raw.Cast_To_Raw ( v_record );
    Utl_File.Put_Raw (
      file => v_handle,
      buffer => v_raw,
      autoflush => false );
  end Put_Raw;
  procedure Get_Raw ( p_offset in binary_integer ) is begin
    Utl_File.Fseek (
      file => v_handle,
      absolute_offset => p_offset );
    Utl_File.Get_Raw (
      file => v_handle,
      buffer => v_raw,
      len => c_record_size );
    Dbms_Output.Put_Line (
      Utl_Raw.Cast_To_Varchar2 ( v_raw ) );
  end Get_Raw;
 begin
   v_handle := Utl_File.Fopen (
   location => c_location,
   filename => c_filename,
   open_mode => 'w' /* Create for writing */,
   max_linesize => v_raw_size );
   Put_Raw ( 'Mary had a little lamb' );
   Put_Raw ( 'The cat sat on the mat' );
   Put_Raw ( 'The quick brown fox jumped over the lazy dog' );
   Utl_File.Fclose ( file => v_handle );
   v_handle := Utl_File.Fopen (
     location => c_location,
     filename => c_filename,
     open_mode => 'r' /* Re-open for reading */,
     max_linesize => v_raw_size );
   Get_Raw ( 2*c_record_size-1 );
   Get_Raw ( 0 );
   Utl_File.Fclose ( file => v_handle );
end;
/

In this case, you read the last record in the file first and the first record in the file second.

The UTL_FILE.FGETATTR procedure reads and returns the attributes of a disk file. From your SQL*Plus session, execute the following script:

@fgetattr

connect hr/hr@orcl.world
set serveroutput on
DECLARE
   v_exists    BOOLEAN;
   v_length    NUMBER;
   v_blocksize NUMBER;
BEGIN
  UTL_FILE.FGETATTR('USER_DIR', 'userdata.txt', v_exists, v_length, v_blocksize);
  IF v_exists THEN
    dbms_output.put_line('Length is: '||v_length);
    dbms_output.put_line('Block size is: '||v_blocksize);
  ELSE 
    dbms_output.put_line('File not found.');
  END IF;
END;
/

The UTL_FILE.FCOPY procedure copies a contiguous portion of a text file to a new file. The entire file is copied if the start line and end line parameters are omitted. From your SQL*Plus session session, execute the following script:

@fcopy

connect hr/hr@orcl.world
set serveroutput on
DECLARE
   v_exists    BOOLEAN;
   v_length    NUMBER;
   v_blocksize NUMBER;
BEGIN
  UTL_FILE.FCOPY('USER_DIR', 'userdata.txt', 'USER_DIR', 'copy.txt', 1, 10);
  UTL_FILE.FGETATTR('USER_DIR', 'userdata.txt', v_exists, v_length, v_blocksize);
  IF v_exists THEN
    dbms_output.put_line('Source Length is: '||v_length);
    dbms_output.put_line('Source Block size is: '||v_blocksize);
  ELSE 
    dbms_output.put_line('File not found.');
  END IF;
  UTL_FILE.FGETATTR('USER_DIR', 'copy.txt', v_exists, v_length, v_blocksize);
  IF v_exists THEN
    dbms_output.put_line('Copy Length is: '||v_length);
    dbms_output.put_line('Copy Block size is: '||v_blocksize);
  ELSE 
    dbms_output.put_line('File not found.');
  END IF;
END;
/

In Windows Explorer, if you take a look in the physical directory d:\wkdir you will see that there are two files userdata.txt and copy.txt.

The UTL_FILE.FRENAME procedure renames an existing file to a new name. You will rename the file userdata.txt to userdata2.txt in the USER_DIR directory. Execute the following SQL in SQL*Plus:

@frename

connect hr/hr@orcl.world
set serveroutput on
BEGIN
  UTL_FILE.FRENAME('USER_DIR', 'userdata.txt',
     'USER_DIR', 'userdata2.txt', TRUE);
END;
/

In Windows Explorer, you will see that the file has been renamed.

The UTL_FILE.FREMOVE procedure removes an operating system file. You will remove the userdata2.txt file from the USER_DIR directory. Execute the following script in SQL*Plus:

@fremove

connect hr/hr@orcl.world
set serveroutput on
DECLARE
  v_fh UTL_FILE.FILE_TYPE;
  v_exists BOOLEAN;
  v_length NUMBER;
  v_blocksize NUMBER;
BEGIN
  v_fh := UTL_FILE.FOPEN('USER_DIR', 'userdata2.txt', 'w');
  UTL_FILE.PUT_LINE(v_fh, 'Hello Utl_File');
  UTL_FILE.FCLOSE(v_fh);
  UTL_FILE.FREMOVE ('USER_DIR', 'userdata2.txt');
  DBMS_OUTPUT.PUT_LINE('File is removed.');
END;
/

In Windows Explorer, you will see that the file has been deleted.

You will use EXECUTE IMMEDIATE to execute a query to retrieve several employee IDs with one call to the database. From your SQL*Plus session, execute the following script:

@bulkcollect

set echo on
set serveroutput on
DECLARE
  TYPE num_tab IS TABLE OF employees.employee_id%type;
  ids num_tab;
BEGIN
  EXECUTE IMMEDIATE 
    'SELECT employee_id FROM employees WHERE salary < 2500'
  BULK COLLECT INTO ids;
  FOR i in ids.FIRST()..ids.LAST() LOOP
    DBMS_OUTPUT.PUT_LINE(ids(i));
  END LOOP;
END;
/

In Oracle9i, you can take advantage of the performance of bulk binding using dynamic SQL. Prior to Oracle9i, you could only use static SQL as the following example illustrates:

set echo on
set serveroutput on
DECLARE
  TYPE num_tab IS TABLE OF employees.employee_id%type;
  ids num_tab;
BEGIN
  SELECT employee_id 
    BULK COLLECT INTO ids
    FROM employees WHERE salary < 2500;
  FOR i in ids.FIRST()..ids.LAST() LOOP
    DBMS_OUTPUT.PUT_LINE(ids(i));
  END LOOP;
END;
/

Input bind variables of a SQL statement can be bound by the FORALL control statement and USING clause. You will use bulk binding to execute an INSERT statement. You will use EXECUTE IMMEDIATE to execute an INSERT statement passing two collection variables. The two collection variables are ids and names and they contain all the department_id and department_name values, respectively. As a result, you will create several rows using one INSERT statement with one call to the database. From your SQL*Plus session, execute the following script:

@bulkinsert

set serveroutput on
DECLARE
  TYPE num_tab IS TABLE OF departments.department_id%TYPE;
  TYPE char_tab is TABLE OF departments.department_name%TYPE;
  ids num_tab := num_tab (12,13,14,15);
  names char_tab := char_tab('R/D','IT','GL','PR');
BEGIN
  FORALL iter in 1..4
    EXECUTE IMMEDIATE
     'INSERT INTO departments (department_id, department_name) VALUES (:1, :2)'
     USING ids(iter), names(iter);
END;
/
select * from departments where department_id > 10 and department_id < 20;

The above example takes advantage of the performance of bulk binding using dynamic SQL. Prior to Oracle9i, the following example illustrates the static SQL:

set serveroutput on
DECLARE
  TYPE num_tab IS TABLE OF departments.department_id%TYPE;
  TYPE char_tab is TABLE OF departments.department_name%TYPE;
  ids num_tab := num_tab (12,13,14,15);
  names char_tab := char_tab('R/D','IT','GL','PR');
BEGIN
  FORALL iter in 1..4
    INSERT INTO departments (department_id, department_name) VALUES 
     (ids(iter), names(iter));
END;
/
select * from departments where department_id > 10 and department_id < 20;
       

Only DML statements have OUT binds. When bulk-binding, these must be bound by the RETURNING BULK COLLECT INTO clause. You will use native dynamic SQL to execute an UPDATE statement by passing a variable v_sal for the value to be used for the update. All the updated salaries are then returned, using a collection variable saltab. From your SQL*Plus session, execute the following script:

@bulkupdate

set serveroutput on
DECLARE
  TYPE num_tab IS TABLE OF employees.employee_id%TYPE;
  sql_str VARCHAR2(200);
  saltab num_tab;
BEGIN
  sql_str := 'UPDATE employees SET salary = 10000
              WHERE department_id = 100
              RETURNING employee_id INTO :1';
  EXECUTE IMMEDIATE sql_str
    RETURNING BULK COLLECT INTO saltab;
  FOR i in saltab.FIRST..saltab.LAST LOOP
    DBMS_OUTPUT.PUT_LINE(saltab(i)); 
  END LOOP;
END;
/

The Oracle9i code is shown above. To produce the same results prior to Oracle9i, the code would be as follows:

set serveroutput on
DECLARE
  TYPE num_tab IS TABLE OF employees.employee_id%TYPE;
  sql_str VARCHAR2(200);
  saltab num_tab;
BEGIN
  UPDATE employees SET salary = 10000
    WHERE department_id = 100
    RETURNING employee_id
    BULK COLLECT INTO saltab;
  FOR i in saltab.FIRST..saltab.LAST LOOP
    DBMS_OUTPUT.PUT_LINE(saltab(i)); 
  END LOOP;
END;
/

Starting with Oracle9i, to handle exceptions encountered during a BULK BIND operation, you can add the keyword SAVE EXCEPTIONS to your FORALL statement. Previously, if any one row failed during the FORALL loop, the loop execution was terminated. SAVE_EXCEPTIONS allows the loop to continue processing and is required if you want the loop to continue. From your SQL*Plus session, execute the following script:

@bulkexception


set serveroutput on
DECLARE
  TYPE NumList IS TABLE OF NUMBER;
  num_tab     NumList := NumList(10,0,11,30,0,199,2,0,1);
  bulk_errors EXCEPTION;
  PRAGMA      EXCEPTION_INIT (bulk_errors, -24381 );
BEGIN
  FORALL i IN num_tab.FIRST..num_tab.LAST
  SAVE EXCEPTIONS
  DELETE FROM employees WHERE salary > 500000/num_tab(i);
EXCEPTION WHEN bulk_errors THEN
  DBMS_OUTPUT.PUT_LINE('Number of errors is: '
                        || SQL%BULK_EXCEPTIONS.COUNT);
  FOR j in 1..SQL%BULK_EXCEPTIONS.COUNT
  LOOP
    DBMS_OUTPUT.PUT_LINE (
      TO_CHAR(SQL%BULK_EXCEPTIONS(j).error_index) || 
      ' / ' ||
      SQLERRM(-SQL%BULK_EXCEPTIONS(j).error_code) );
  END LOOP;
END;
/

In this case, 5 errors were identified, however, the delete statement was run for each of the 9 values identified in the num_tab table.

In previous versions of Oracle database, you could only bulk-fetch from a cursor into multiple collections of column types. You will select all departments that are not at location 1700 into a PL/SQL table of records. From your SQL*Plus session, execute the following script:

@rec_select

set serveroutput on
DECLARE
  TYPE v_dept_table_typ IS TABLE OF departments%ROWTYPE
      INDEX BY BINARY_INTEGER;
  v_dept v_dept_table_typ;
BEGIN
  SELECT *
    BULK COLLECT INTO v_dept
    FROM departments
    WHERE location_id <> 1700
    ORDER BY department_id;
  FOR i IN 1..v_dept.COUNT LOOP
    dbms_output.put_line ('Row #: ' || i);
    dbms_output.put_line (' ID: ' || v_dept(i).department_id);
    dbms_output.put_line (' Name: ' || v_dept(i).department_name);
    dbms_output.put_line (' Mgr: ' || v_dept(i).manager_id);
    dbms_output.put_line (' Loc: ' || v_dept(i).Location_id || CHR(10));
  END LOOP;
END;
/

In Oracle9i Database Release 2 you can also select or fetch rows into a PL/SQL table of records, instead of using a separate PL/SQL table for each SQL column. From your SQL*Plus session, execute the following script:

@rec_bulkfetch

set serveroutput on
DECLARE
  TYPE v_dept_table_typ IS TABLE
    OF departments%ROWTYPE
    INDEX BY BINARY_INTEGER;
  v_dept v_dept_table_typ;
  dept_c SYS_REFCURSOR;
BEGIN
  OPEN dept_c for
     SELECT *
       FROM departments
       WHERE location_id <> 1700
       ORDER BY department_id;
  FETCH dept_c BULK COLLECT INTO v_dept;
  CLOSE dept_c;
  FOR i IN 1..v_dept.COUNT LOOP
    dbms_output.put_line
      ('Row #:  ' || i);
    dbms_output.put_line
      ('  ID:   ' || v_dept(i).department_id);
    dbms_output.put_line
      ('  Name: ' || v_dept(i).department_name);
    dbms_output.put_line
      ('  Mgr:  ' || v_dept(i).manager_id);
    dbms_output.put_line
      ('  Loc:  ' || v_dept(i).location_id || CHR(10));
  END LOOP;
END;
/

You will INSERT a row using a PL/SQL record. From your SQL*Plus session, execute the following script:

@rec_insert

DECLARE
  v_dept departments%ROWTYPE;
BEGIN
  v_dept.department_id   := 999;
  v_dept.department_name := 'DBA';
  v_dept.location_id     := 1500;
  INSERT INTO departments VALUES v_dept;
END;
/
select * from departments where department_id = 999;

Now you will update that same row with a PL/SQL record. From your SQL*Plus session, execute the following script:

@rec_update

DECLARE
  v_dept departments%ROWTYPE;
BEGIN
  SELECT * INTO v_dept
    FROM departments WHERE department_id = 999;  
  v_dept.department_name := 'IT DBA';
  v_dept.manager_id      := 103;
  v_dept.location_id     := 1700;
  UPDATE departments
    SET ROW = v_dept
    WHERE department_id = v_dept.department_id;
END;
/
select * from departments where department_id = 999;

You can include a RETURNING clause in an INSERT, UPDATE, or DELETE statement to return column values from the affected row into a PL/SQL record variable. This eliminates the need to select the row after an insert or update, or before a delete. You can use this clause only when operating on exactly one row. From your SQL*Plus session, execute the following script:

@rec_delete

set serveroutput on
DECLARE
  v_dept departments%ROWTYPE;
BEGIN
  DELETE
    FROM departments
    WHERE department_id = 999
    RETURNING department_id,
              department_name,
              manager_id,
              location_id
      INTO v_dept;
    DBMS_OUTPUT.PUT_LINE ('ID:  ' || v_dept.department_id);
    DBMS_OUTPUT.PUT_LINE ('Name:  ' || v_dept.department_name);
    DBMS_OUTPUT.PUT_LINE ('Mgr:  ' || v_dept.manager_id);
    DBMS_OUTPUT.PUT_LINE ('Loc:  ' || v_dept.location_id);
END;
/

Use an Associative Array