Lob types in pl/sql, PL-SQL Programming

Assignment Help:

LOB Types

The large object (LOB) datatypes like BFILE, BLOB, CLOB, and NCLOB store the blocks of unstructured data (like graphic images, text, video clips, and sound waveforms) of up to four gigabytes in size. And, they allow random, efficient, piece-wise access to the data.

The LOB types are differing from the LONG and LONG RAW types in many ways. For example, the LOBs (except NCLOB) can be the attributes of an object type, but LONGs cannot. The maximum size of a LOB is 4 gigabytes, but the maximum size of a LONG is 2 gigabytes. The LOBs also support random access to data, but LONGs support only the sequential access.

BFILE

You use the BFILE datatype to store the large binary objects in an operating system files outside the database. Every BFILE variable stores a file locator that points to a large binary file on the server. The locator involves a directory alias that specifies a full path name.

The BFILEs are read-only. You cannot modify or change them. The size of a BFILE is system dependent but cannot exceed 4 gigabytes (2**32 - 1 bytes). Your DBA makes sure that a given BFILE exists and that Oracle has read permissions on it. The underlying operating system maintains the file integrity.

The BFILEs do not participate in transactions, are not recoverable, and cannot be simulated. The highest number of open BFILEs is set by the Oracle initialization parameter SESSION_MAX_OPEN_FILES, that is system dependent.

BLOB

You use the BLOB datatype to store the large binary objects in the database in-line or out-of-line. Each BLOB variable stores a locator, thatpoints to a large binary object. The size of a BLOB cannot exceed 4 gigabytes. The BLOBs participate fully in transactions, and are recoverable, and can replicate. The Changes made by package DBMS_LOB or the OCI can be committed or rolled back. Though, the BLOB locators cannot span transactions or sessions.

CLOB

You use the CLOB datatype to store the large blocks of single-byte character data in the database, in-line or out-of-line. Both the fixed-width and variable-width character sets are supported. Each CLOB variable stores a locator that points to a large block of character data. The size of a CLOB cannot exceed 4 gigabytes. The CLOBs participate fully in transactions, and are recoverable, and can be replicated. The Changes made by package DBMS_LOB or the OCI can be committed or rolled back. Though, the CLOB locators cannot span transactions or sessions.

Other Types

The following types permit you to store and manipulate the logical values and date/time values.

BOOLEAN

You use the BOOLEAN datatype to store the logical values like TRUE, FALSE, & NULL. The BOOLEAN datatype takes no parameters. Only the values TRUE, FALSE, & NULL can be assigned to a BOOLEAN variable. You cannot insert the values like TRUE & FALSE into a database column. You cannot also select or fetch the column values into a BOOLEAN variable.

DATE

You use the DATE datatype to store the fixed-length date/time values. The DATE values involve the time of day in seconds since the midnight. The date portion defaults to the first day of the present month; the time portion defaults to midnight. The date function SYSDATE returns the present date and time.

For illustration, the following statement returns the number of days since an employee was hired:

SELECT SYSDATE - hiredate INTO days_worked FROM emp WHERE empno = 7499;


Related Discussions:- Lob types in pl/sql

Solve the business problems using sql, Use the MASCOT tables CREDITRS, PORD...

Use the MASCOT tables CREDITRS, PORDS and PAYMENTS to write SQL queries to solve the following business problems. These tables / data are available to you via the USQ Oracle server

Cosmozoic theory - origin of life, COSMOZOI C THEORY - Richter (1865...

COSMOZOI C THEORY - Richter (1865) proposed the cosmozoic theory that says that life came by spores (cosmozoa) or other particles from other planets on the earth.

Triggers, At times, customers make mistakes in submitting their orders and ...

At times, customers make mistakes in submitting their orders and call to cancel the order. Brewbean’s wants to create a trigger that automatically updates the stock level of all pr

Advantages of pl/sql, Advantages of PL/SQL The PL/SQL is a high-perform...

Advantages of PL/SQL The PL/SQL is a high-performance transaction processing, completely portable language that offers the following advantages as shown: 1) Support for SQL

Oracle, Literature review

Literature review

What is a collection, What Is a Collection The collection is an ordered...

What Is a Collection The collection is an ordered group of elements, all of similar type (for e.g. the grades for a class of students). Each element has a unique subscript whic

Keyword and parameter description - delete statement, Keyword and Parameter...

Keyword and Parameter Description: table_reference: This specifies a table or view that should be accessible when you execute the DELETE statement, and for that you must

Authorisations - privileges, Authorisations - Privileges As relational...

Authorisations - Privileges As relational theory is silent on the issue of authorisation, it offers nothing with which SQL's vast edifice in support of what it calls privilege

Naming conventions-pl/sql, Naming Conventions The similar naming conventi...

Naming Conventions The similar naming conventions apply to all PL/SQL program items and units including the variables, cursors, constants, cursor variables, procedures, exception

Design a script and integrate procedures, Initial thought process: Design...

Initial thought process: Design a script which was simple and user friendly. Integrate procedures/functions to extract data under the hood. I focused on giving the user the opt

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd