PL/SQLProcedural Language / Structured Query Language

(Source CaoSys)

Overview

• PL/SQL Records and Tables

• Host File Access with UTL_FILE

Records

To create a record manually that contains employee information you might use the following:-

TYPE t_emp_record IS RECORD( enumber NUMBER, firstname VARCHAR2(20), lastname VARCHAR2(20));

At this point, no PL/SQL object exists, just the new datatype, so, we need to declare a variable of this type:-

l_employee t_emp_record;

Records (Continued)

This is no different to declaring a variable of any of the standard types. You can now use the new l_employee record with the dot notation to get at its elements, for example:-

l_employee.enumber := 101;l_employee.firstname := ‘John';l_employee.lastname := ‘Smith';

You can assign one record to another so long as all the elements in both records are exactly the same, for example:-

Records (Continued)

You can assign one record to another so long as all the elements in both records are exactly the same, for example:-

DECLARETYPE t_record IS RECORD( col1 NUMBER, col2 VARCHAR2(10) );

l_record1 t_record;l_record2 t_record;

BEGINl_record1.col1 := 10;l_record1.col2 := 'Test';l_record2 := l_record1;END;

Records (Continued)

Records can also be used as arguments in procedures and functions, for example:-

CREATE OR REPLACE PROCEDURE print_dept(p_dept_rec dept%ROWTYPE) IS

BEGINDBMS_OUTPUT.put_line(p_dept_rec.deptno);DBMS_OUTPUT.put_line(p_dept_rec.deptno);DBMS_OUTPUT.put_line(p_dept_rec.deptno);END;

Records (Continued)

The procedure could be invoked with:-

DECLAREl_rec dept%ROWTYPE;

BEGIN SELECT * INTO l_rec FROM dept WHERE deptno = 10; print_dept(l_rec);END;

Tables

A PL/SQL table is very similar to an array found in most third generation languages. Before a table can be used, you need to declare a new datatype that is of type table, you do this with the TYPE statement in a similar way as you create records. The syntaxof TYPE in this case is:-

TYPE table-name IS TABLE OF typeINDEX BY BINARY_INTEGER;

Tables (Continued)

Where table-name can be any valid identifier and type is any valid datatype, including any new datatypes you have created, such as a record. So, to declare a table to hold the names of employeesyou might:-

TYPE t_names IS TABLE OF emp.ename%TYPEINDEX BY BINARY_INTEGER;

Tables (Continued)

Once your new table type is declared, you must declare a variable of the new type, for example:-

names_tab t_names;

The above code will create a new variable called names_tab of type t_names. You can now use the table names_tab. You access individual rows on a PL/SQL table by using a table index (reference/subscript), similar to an array subscript. The index should appear immediately after the table name, for example, to set the elements of record one in the names_tab table:-

names_tab(1).empno := 10;names_tab(1).ename := ‘John';

Tables (Continued)

Here is another example of a less complex table, this time it is a table of a scalar datatype:-

DECLARETYPE t_names IS TABLE OF VARCHAR2(10)INDEX BY BINARY_INTEGER;names_tab t_names;

BEGINnames_tab(-10) := ‘John';names_tab(0) := ‘Mary';names_tab(250) := ‘Andrew';END;

Tables (Continued)

Memory has only been allocated for 3 rows, this is very much unlike arrays. To set the value of the 250’th row in an array, all elements preceding it must exist. Nor can an array have a negative subscript. PL/SQL tables grow dynamically in size as you create rows, very much like a database table.

Tables (Continued)

Memory has only been allocated for 3 rows, this is very much unlike arrays. To set the value of the 250’th row in an array, all elements preceding it must exist. Nor can an array have a negative subscript. PL/SQL tables grow dynamically in size as you create rows, very much like a database table.

Points of interest:

Can not reference undeclared row, as it will result in a exception.

A table object has a number of attributes which we can use to interact with it e.g. Count, last, exists etc.

Tables (Continued)

COUNT: This will return the number of rows in the table.

DECLARETYPE t_table IS TABLE OF NUMBERINDEX BY BINARY_INTEGER;number_tab t_table;l_rows NUMBER;

BEGINnumber_tab(1) := 10;number_tab(10) := 20;number_tab(87) := 5;number_tab(2500) := 100;l_rows := number_tab.COUNT;END;

In the above code, l_rows will be set to 4.

Tables (Continued)

DELETE: This is used to remove rows from a table.

DECLARETYPE t_table IS TABLE OF NUMBERINDEX BY BINARY_INTEGER;number_tab t_table;

BEGINnumber_tab(1) := 10;number_tab(10) := 20;number_tab(87) := 5;number_tab(2500) := 100;number_tab.DELETE(87);END;

DELETE can be used in three ways:-table.DELETE; will remove all rowstable.DELETE(x); remove row i.table.DELETE(x,y); remove rows i through y.

Tables (Continued)EXISTS: This is used to check whether a specified row exists or not.

DECLARETYPE t_table IS TABLE OF NUMBERINDEX BY BINARY_INTEGER;number_tab t_table;

BEGINnumber_tab(1) := 10;number_tab(10) := 20;number_tab(87) := 5;number_tab(2500) := 100;

IF number_tab.EXISTS(10) THEN DBMS_OUTPUT.put_line('Row 10 Found');END IF;IF NOT number_tab.EXISTS(100) THEN DBMS_OUTPUT.put_line('Row 100 not found');END IF;END;

Tables (Continued)FIRST and LAST: These are used to find the index of the first and last rows in a table.

DECLARETYPE t_table IS TABLE OF NUMBERINDEX BY BINARY_INTEGER;number_tab t_table;l_first BINARY_INTEGER;l_last BINARY_INTEGER;

BEGINnumber_tab(1) := 10;number_tab(10) := 20;number_tab(87) := 5;number_tab(2500) := 100;l_first := number_tab.FIRST;l_last := number_tab.LAST;

END;In the above code, l_first wil be set to 1 and l_last will be set to 2500.

Records and Tables Review

PL/SQL are generally very much under-used. They are very powerful constructs and greatly enhance the functionality of PL/SQL. Many programs need to have some kind of temporary storage area, normally used to hold intermediate data which needs to be processed in some way, a great deal of developers create this temporary storage using database tables, while this offers some advantages, such as the ability to restart a failed process from where it last was, PL/SQL tables offer advantages too; such a vastly increased performance, PL/SQL tables are much faster to workwith than database tables, as everything is done in memory.

You also have the advantage of not having to create and maintain a temporary table. The only real disadvantage is slightly more complex code.

Host File Access

UTL_FILE

Host File Access (Cont)

Reading from and writing to host files is a common task for PL/SQL. PL/SQL itself does not directly support this kind of functionality, but it can be done using an Oracle supplied package, UTL_FILE.

UTL_FILE is a server side package for writing to host files on the server, there is another package, TEXT_IO.

Host File Access (Cont)

UTL_FILE has the following functions and procedures:

FOPEN Used to open a fileFCLOSE Used to closed a fileFCLOSE_ALL Close all open filesIS_OPEN Check if a file is openFFLUSH Output from UTL_FILE buffered, this procedure is usedto ensure the buffer has been written to the file.PUT Write to filePUT_LINE Write to fileNEW_LINE Write to filePUTF Write to fileGET_LINE Read from a file

Host File Access (Cont)

FOPEN: Use this function to open a file.

FUNCTION fopen( path IN VARCHAR2, filename IN VARCHAR2, mode IN VARCHAR2)RETURN FILE_TYPE;

PATH the directory containing the file FILENAME the actual filename MODE the Open Mode, this can be one of:

'r' : Read from file'w' : Write to file'a' : Append to file

Host File Access (Cont)

The return type is the File Handle which is used to reference the open file is other functions. It’s type is UTL_FILE.file_type.

DECLAREl_handle UTL_FILE.file_type;

BEGINl_handle := UTL_FILE.fopen( '/u01/app', 'my_file.dat', 'r');. . .

The above code will open the file/u01/app/my_file.dat for reading.

Host File Access (Cont)PUT & PUT_LINE: This procedure is used to write data to a file.

UTL_FILE.put(handle,buffer);

Where handle is the handle of an already open file and buffer the string to be written. PUT does not append a newline character to the buffer; you should use PUT_LINE or NEW_LINE for this. PUT_LINE is almost the same as PUT except that it automatically add’s a newline character to the end of buffer.

DECLAREl_handle UTL_FILE.file_type;

BEGINl_handle := UTL_FILE.fopen( '/u01/app', 'my_file.dat', 'w');UTL_FILE.put(l_handle,'This line is written');UTL_FILE.fclose(l_handle);END;

Host File Access (Cont)PUTF: This procedure is used to write data to a file.

UTL_FILE.putf(handle,format,arg1,arg2,arg3,arg4,arg5);

Where handle is the handle of an already open file. The parameters arg1 to arg5 are optional and are string that are written to the file in a format specified by format.format is any string of text which can contain special codes used to print the strings in arg1 to arg5. The %s code is replaced in the string with is corresponding arg parameter. You can also use the code \n to add a newline character. It is similar toC’s printf function.

DECLAREl_handle UTL_FILE.file_type;BEGINl_handle := UTL_FILE.fopen( '/u01/app', 'my_file.dat', 'w');UTL_FILE.putf(l_handle,'arg1=%s\n',’John');UTL_FILE.fclose(l_handle);END;

Host File Access (Example)Below is a procedure that can be used to dump out the contents of the dept table to a file.

CREATE OR REPLACE PROCEDURE dump_deptIS

CURSOR dept_cur IS SELECT deptno , dname , loc FROM dept;

l_handle UTL_FILE.file_type; l_path VARCHAR2(50) := '/u01/app'; l_filename VARCHAR2(20) := 'dept.dat'; l_record VARCHAR2(200);

Host File Access (Example)BEGIN-- Open filel_handle := UTL_FILE.fopen( l_path, l_filename, 'w');

-- Get all dept rowsFOR r_dept IN dept_curLOOPl_record := TO_CHAR(r_dept.deptno)||'|'||r_dept.dname||'|'||TO_CHAR(r_dept.loc);

-- Write row to fileUTL_FILE.put_line(l_handle,l_record);END LOOP;UTL_FILE.fflush(l_handle);UTL_FILE.fclose(l_handle);EXCEPTIONWHEN UTL_FILE.invalid_path THENDBMS_OUTPUT.put_line('Invalid Path');WHEN UTL_FILE.write_error THENDBMS_OUTPUT.put_line('Write Error');END;