Package gb_large

Package gb_large_object

Common Business interface for the LARGE OBJECT API (gb_large_object).

This API helps manage the storage and retrieval of Large Objects (LOBs), which can include, for example, PDF files and graphics files in a variety of formats.

Data Type	Description
LOB	A generic term associated with a large object. LOBs are stored in a way that optimizes space and provides efficient access. These large objects include the following data types: BLOB, CLOB, BFILE.
BLOB	A BLOB is unstructured binary data that is accessed and referenced by using a locator, which is stored in the database table and points to the BLOB data, which is outside the table.
CLOB	A CLOB is character data that is accessed and referenced by using a locator, which is stored in the database table and points to the CLOB data, which is outside the table.
BFILE	A BFILE is a file stored in an operating system file outside of the Oracle database. A BFILE object contains a logical pointer to a BFILE, not the BFILE itself.

There are four ways for storing Large Objects via the API:

1) Store BLOB.
The application program obtains a BLOB locator. This may come from another table, or the application may have created a temporary LOB with data loaded from the file system. The application uses the p_blob parameter to pass the BLOB to the API, leaving the binary file locator parameter (p_bfile) NULL. The API creates the row in the table, then copies the input BLOB into the BLOB in the new record.

2) Store known binary file locator (BFILE).
The application program obtains a binary file locator, either from another table, or by taking a logical directory name and a file name and setting a local binary file to the result of bfilename. For example: mylocal_bfile := bfilename('THE_DIRECTORY','sample.pdf'). The application then passes the binary file locator to the API's p_create procedure using mylocal_bfile as the value of the p_bfile parameter. In this case the p_blob, p_directory, and p_filename parameters are null, and p_loadfromfile is set to 'V' (to validate and store) or 'N' (to store without validation). The API then creates the GORBLOB row storing the binary file locator in the record.

3) Store binary file using directory and filename.
The application program obtains a logical directory name, and a file name. It passes this to p_create using the p_directory and p_filename parameters. (For example, p_directory is 'THE_DIRECTORY', p_filename is 'sample.pdf'). In this case the p_blob and p_bfile parameters are null, and p_loadfromfile is set to 'V' (to validate and store) or 'N' (to store without validation). The API then creates the GORBLOB row storing the binary file locator using this call: bfilename('THE_DIRECTORY','sample.pdf').

4) Store BLOB by loading from binary file.
The application program passes file location information by either of the methods described above, with the p_loadfromfile parameter set to 'Y'. The API reads the binary file copies it into the database, and stores the BLOB locator for the newly created BLOB.

This API is considered private, meaning that it is in support of other applications that manage indexing of the documents, purging, security, and user interface interactions. This API does not have execute grants (except for e-Print) or public synonyms. To use this functionality, there must be a public application that provides an interface between the Large Object API and the end user. An example of this interface is the Accounts Receivable eBill processing.

Program units

f_api_version	Returns the API version number.
f_is_blob_unspecified	Checks to see if a large object (BLOB) contains an unspecified value.
f_exists	Checks to see if a record exists.
f_get_bfile_location	Returns the BFILE information, optionally in operating system format, for a BFILE locator.
f_get_bfile_location	Returns the BFILE information, optionally in operating system format, for a media ID.
f_isequal	Compares two records for equality.
f_query_all	Selects all records for the entity.
f_query_one	Selects one record using key.
f_query_by_rowid	Selects one record using ROWID.
f_query_one_lock	Selects one record and locks it.
p_create	Creates a record.
p_delete	Deletes a record.
p_display_blob	Downloads the file to a browser.
p_lock	Locks a record.
p_purge	Deletes a record containing a large object (BLOB) reference.
p_update	Updates a record.

Types

large_object_rec	Business Entity record type
large_object_ref	Entity cursor variable type
large_object_tab	Entity table type

Constants

M_ENTITY_NAME	Business Entity Name
M_BASE_TABLE_NAME	Base table name

M_ENTITY_NAME

M_ENTITY_NAME    CONSTANT VARCHAR2(12) := 'LARGE_OBJECT';

Business Entity Name

M_BASE_TABLE_NAME

M_BASE_TABLE_NAME    CONSTANT VARCHAR2(7) := 'GORBLOB';

Base table name

large_object_rec

TYPE large_object_rec IS RECORD (
  r_media_id                gorblob.gorblob_media_id%TYPE,
  r_blot_code               gorblob.gorblob_blot_code%TYPE,
  r_user_id                 gorblob.gorblob_user_id%TYPE,
  r_blob                    gorblob.gorblob_blob%TYPE,
  r_bfile                   gorblob.gorblob_bfile%TYPE,
  r_deleted                 gorblob.gorblob_deleted%TYPE,
  r_data_origin             gorblob.gorblob_data_origin%TYPE,
  r_internal_record_id      gb_common.internal_record_id_type);

Business Entity record type

large_object_ref

TYPE large_object_ref IS REF CURSOR RETURN large_object_rec;

Entity cursor variable type

large_object_tab

TYPE large_object_tab IS TABLE OF large_object_rec INDEX BY BINARY_INTEGER;

Entity table type

f_api_version

FUNCTION f_api_version RETURN PLS_INTEGER

Returns the API version number.

Returns

Version of the API signature. Changes only when the signature changes.

f_is_blob_unspecified

FUNCTION f_is_blob_unspecified(p_blob BLOB) RETURN BOOLEAN

Checks to see if a large object (BLOB) contains an unspecified value.

Returns

BOOLEAN TRUE or FALSE.

f_exists

FUNCTION f_exists(p_media_id gorblob.gorblob_media_id%TYPE,
                  p_rowid    gb_common.internal_record_id_type DEFAULT NULL)
  RETURN VARCHAR2

Checks to see if a record exists.

Parameters

	p_media_id	Unique generated ID number. NUMBER(38) Required Key
	p_rowid	Database ROWID of the record to be selected. VARCHAR2(18)

Returns

Y if found, otherwise N.

f_get_bfile_location

FUNCTION f_get_bfile_location(p_bfile     BFILE,
                              p_info_type VARCHAR2 DEFAULT 'B')
  RETURN VARCHAR2

Returns the BFILE information, optionally in operating system format, for a BFILE locator.

Parameters

p_bfile

Binary File Locator. BFILE Required

p_info_type

Type of information being requested. VARCHAR2(01)

	B	Return directory path and file name in operating system format. (Default)
	D	Return directory path only.
	F	Return filename only.

Returns

Directory path and/or file name based upon p_info_type.

f_get_bfile_location

FUNCTION f_get_bfile_location(p_media_id  gorblob.gorblob_media_id%TYPE,
                              p_info_type VARCHAR2 DEFAULT 'B')
  RETURN VARCHAR2

Returns the BFILE information, optionally in operating system format, for a media ID.

Parameters

p_media_id

Unique generated ID number. NUMBER(38) Required

p_info_type

Type of information being requested. VARCHAR2(01)

	B	Return directory path and file name in OS format. (Default)
	D	Return OS directory path only.
	F	Return filename only.

Returns

Directory path and/or file name based upon p_info_type. NULL will be returned if p_media_id does not exist.

f_isequal

FUNCTION f_isequal(rec_one large_object_rec,
                   rec_two large_object_rec) RETURN VARCHAR2

Compares two records for equality.

Tests each field of rec_one against the corresponding field of rec_two. Two null values are considered equal.

Parameters

	rec_one	The first record to compare. Type large_object_rec Required
	rec_two	The second record to compare. Type large_object_rec Required

Returns

Y if all values in records are equal, otherwise N. Nulls match Nulls.

f_query_all

FUNCTION f_query_all(p_media_id gorblob.gorblob_media_id%TYPE DEFAULT NULL,
                     p_deleted  gorblob.gorblob_deleted%TYPE DEFAULT NULL)
  RETURN large_object_ref

Selects all records for the entity.

Parameters

	p_media_id	Unique generated ID number to return one record or NULL to return multiple records. NUMBER(38)
	p_deleted	If set to 'Y' then only records with gorblob_deleted = Y will be returned. VARCHAR2(01)

Returns

A cursor variable that will fetch the set of records.

f_query_one

FUNCTION f_query_one(p_media_id gorblob.gorblob_media_id%TYPE)
  RETURN large_object_ref

Selects one record using key.

Parameters

p_media_id

Unique generated ID number. NUMBER(38) Required Key

Returns

A cursor variable that will fetch exactly one record.

f_query_by_rowid

FUNCTION f_query_by_rowid(p_rowid gb_common.internal_record_id_type)
  RETURN large_object_ref

Selects one record using ROWID.

Parameters

p_rowid

Database ROWID of the record to be selected. VARCHAR2(18) Required

Returns

A cursor variable that will fetch exactly one record.

f_query_one_lock

FUNCTION f_query_one_lock(p_media_id gorblob.gorblob_media_id%TYPE,
                          p_rowid    gb_common.internal_record_id_type DEFAULT NULL)
  RETURN large_object_ref

Selects one record and locks it.

Parameters

	p_media_id	Unique generated ID number. NUMBER(38) Required Key
	p_rowid	Database ROWID of the record to be selected. VARCHAR2(18)

Returns

A cursor variable for one record, locking the record.

p_create

PROCEDURE p_create(p_media_id_out OUT gorblob.gorblob_media_id%TYPE,
                   p_blot_code    gorblob.gorblob_blot_code%TYPE,
                   p_user_id      gorblob.gorblob_user_id%TYPE DEFAULT gb_common.f_sct_user,
                   p_blob         gorblob.gorblob_blob%TYPE DEFAULT NULL,
                   p_bfile        gorblob.gorblob_bfile%TYPE DEFAULT NULL,
                   p_directory    VARCHAR2 DEFAULT NULL,
                   p_filename     VARCHAR2 DEFAULT NULL,
                   p_loadfromfile VARCHAR2 DEFAULT 'Y',
                   p_deleted      gorblob.gorblob_deleted%TYPE DEFAULT NULL,
                   p_data_origin  gorblob.gorblob_data_origin%TYPE DEFAULT NULL,
                   p_rowid_out    OUT gb_common.internal_record_id_type)

Creates a record.

It is the caller's responsibility to ensure that the p_blot_code is not only a valid code, but is the correct type for the related data. For example, if the BLOB is a PDF and the p_blot_code equals JPG, the API will not raise an error because the code is valid, but a subsequent attempt to display the file may fail because the mime type will tell the browser that it is a JPG when in fact the content is PDF.

When creating a record, one and only one of the following must be passed: p_blob or p_bfile or the combination of p_directory and p_filename.

Parameters

p_media_id_out

Unique generated ID number. NUMBER(38) Required Key

p_blot_code

Code that describes the contents of the binary large object (BLOB) or binary file locator (BFILE). The p_blot_code is defined in the gtvblot table. VARCHAR2(30) Required

p_user_id

User ID of the User who created or last updated the record. VARCHAR2(30) Required

p_blob

The binary large object stored in the database. BLOB

p_bfile

The binary file locator pointing to the file system file. BFILE

p_directory

Field to define the logical directory of external file. The logical is created with the CREATE DIRECTORY command. VARCHAR2(30)

p_filename

Field to define the file name of an external file. VARCHAR2(2000)

p_loadfromfile

Field used with external files only. VARCHAR2(01)

	N	do not load or validate an external file.
	V	validates that the external file exists.
	Y	indicates the external file should be loaded into a BLOB (validation is implied) (default).

p_deleted

The record is flagged for deletion from the associated file system. It may be deleted if this column has a value of Y. Once the file is deleted this row may be deleted from table gorblob. VARCHAR2(01)

p_data_origin

Source system that created or updated the row. VARCHAR2(30)

p_rowid_out

Database ROWID of the record to be created. VARCHAR2(18) Required

p_delete

PROCEDURE p_delete(p_media_id gorblob.gorblob_media_id%TYPE,
                   p_rowid    gb_common.internal_record_id_type DEFAULT NULL)

Deletes a record.

Parameters

	p_media_id	Unique generated ID number. NUMBER(38) Required Key
	p_rowid	Database ROWID of the record to be deleted. VARCHAR2(18)

p_display_blob

PROCEDURE p_display_blob(p_media_id gorblob.gorblob_media_id%TYPE)

Downloads the file to a browser.

There is no security check in this procedure. The assumption is that a higher level package (such as the student self service "Statement and Payment History" page which displays files stored via the AR statement API) is checking security before calling this procedure.

Parameters

p_media_id

Unique generated ID number. NUMBER(38) Required Key

p_lock

PROCEDURE p_lock(p_media_id    gorblob.gorblob_media_id%TYPE,
                 p_rowid_inout IN OUT gb_common.internal_record_id_type)

Locks a record.

If ROWID is not passed in, the record is located using the key values and the ROWID of the locked row is passed in p_rowid_inout.

Parameters

	p_media_id	Unique generated ID number. NUMBER(38) Required Key
	p_rowid_inout	Database ROWID of the record to be locked. VARCHAR2(18) Required

p_purge

PROCEDURE p_purge(p_media_id gorblob.gorblob_media_id%TYPE,
                  p_rowid    gb_common.internal_record_id_type DEFAULT NULL)

Deletes a record containing a large object (BLOB) reference. For records containing binary file (BFILE) references, updates GORBLOB_DELETED to Y. This is done because Oracle cannot manage external files. If a file associated with a BFILE entry needs to be deleted, an external script must be written to select all rows that have GORBLOB_DELETED = Y and then remove the external file followed by a call to p_delete.

Parameters

	p_media_id	Unique generated ID number. NUMBER(38) Required Key
	p_rowid	Database ROWID of the record to be deleted. VARCHAR2(18)

p_update

PROCEDURE p_update(p_media_id     gorblob.gorblob_media_id%TYPE,
                   p_blot_code    gorblob.gorblob_blot_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_user_id      gorblob.gorblob_user_id%TYPE DEFAULT gb_common.f_sct_user,
                   p_blob         gorblob.gorblob_blob%TYPE DEFAULT dml_gorblob.unspecified_blob,
                   p_bfile        gorblob.gorblob_bfile%TYPE DEFAULT dml_gorblob.unspecified_bfile,
                   p_directory    VARCHAR2 DEFAULT dml_common.f_unspecified_string,
                   p_filename     VARCHAR2 DEFAULT dml_common.f_unspecified_string,
                   p_loadfromfile VARCHAR2 DEFAULT dml_common.f_unspecified_string,
                   p_deleted      gorblob.gorblob_deleted%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_data_origin  gorblob.gorblob_data_origin%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_rowid        gb_common.internal_record_id_type DEFAULT NULL)

Updates a record.

During an update you do not need to pass p_blob, p_bfile, or p_directory and p_filename unless you are making a change to the file previously stored. If you are changing any of them, then one and only one of the following must be passed: p_blob or p_bfile or the combination of p_directory and p_filename, and the others must be set to NULL.

It is the caller's responsibility to ensure that the p_blot_code is not only a valid code, but is the correct type for the related data. For example, if the BLOB is a PDF and the p_blot_code equals JPG, the API will not raise an error because the code is valid, but a subsequent attempt to display the file may fail because the mime type will tell the browser that it is a JPG when in fact the content is PDF.

Parameters

p_media_id

Unique generated ID number. NUMBER(38) Required Key

p_blot_code

Code that describes the contents of the binary large object (BLOB) or binary file locator (BFILE). The p_blot_code is defined in the gtvblot table. VARCHAR2(30)

p_user_id

User ID of the User who created or last updated the record. VARCHAR2(30) Required

p_blob

The binary large object stored in the database. BLOB

p_bfile

The binary file locator pointing to the file system file. BFILE

p_directory

Field to define the logical directory of external file. The logical is created with the CREATE DIRECTORY command. VARCHAR2(30)

p_filename

Field to define the file name of an external file. VARCHAR2(2000)

p_loadfromfile

Field used with external files only. VARCHAR2(01)

	N	do not load or validate an external file.
	V	validates that the external file exists.
	Y	indicates the external file should be loaded into a BLOB (validation is implied) (default).

p_deleted

The record is flagged for deletion from the associated file system file. It may be deleted if this column has a value of Y. Once the file is deleted this row may be deleted from table gorblob. VARCHAR2(01)

p_data_origin

Source system that created or updated the row. VARCHAR2(30)

p_rowid

Database ROWID of the record to be updated. VARCHAR2(18)