LOB Datatypes

Oracle and PL/SQL support several variations of LOB (large object) datatypes. LOBs can store large amountsfrom 8 to 128 terabytes of binary data and character text data. Eg: Used for storing documents, media files, audio files, movie files etc., There are two types of LOBs Internal LOBs Data is stored within the database (clob, blob, nclob) External LOBs Data is stored in OS files.(bfile)

Understanding LOB Locators Fundamental to working with LOBs is the concept of a LOB locator. A LOB locator is a pointer to large object data in a database. Let's see what happens when you select a BLOB column into a BLOB PL/SQL variable:

DECLARE photo BLOB; BEGIN SELECT photo_col INTO photo . ..

Is photo_col itself retrieved into photo ? NO ! Only a pointer to the photo is retrieved

Database LOB columns store LOB locators , and those locators point to the real data stored elsewhere in the database. Likewise, PL/SQL LOB variables hold those same LOB locators, which point to LOB data within the database.

To work with LOB data, you first retrieve a LOB locator, and you then use a built-in package named DBMS_LOB to retrieve and/or modify the actual LOB data.

Comparing null with empty locator DECLARE v_clob1 clob; v_clob2 clob:=empty_clob(); begin if v_clob1 is null then dbms_output.put_line('v_clob1=null'); end if; if v_clob2 is null then dbms_output.put_line('v_clob2=null'); else dbms_output.put_line('v_clob2=not null'); dbms_output.put_line('length='||length(v_clob2)); end if; end; / v_clob1=null v_clob2=not null length=0

Differentiating LONG and LOB Data Types

LONG and LONG RAW

Single LONG column per table Up to 2 GB SELECT returns data Data stored in-line Sequential access to data

LOB
Multiple LOB columns per table Up to 4 GB SELECT returns locator Data stored in-line or out-ofline Random access to data

DBMS_LOB package contains

CLOB example: CREATE TABLE employees( empno NUMBER(4), ename VARCHAR2(15), resume CLOB); INSERT INTO EMPLOYEES VALUES(7369,'SMITH','Date of Birth: 12-FEB-1945'); INSERT INTO EMPLOYEES VALUES(7566,'JONES','Date of Birth: 15-DEC-1943');

SELECT EMPNO,ENAME,RESUME FROM EMPLOYEES;

SELECT ENAME,DBMS_LOB.SUBSTR(RESUME,12,15) FROM EMPLOYEES;

BLOB Example Initially create a folder in 'C:\PICTURES' & place some images with extension ".jpg".

Let files have the names SMITH.JPG,JONES.JPG,KING.JPG etc.,

Create a logical directory "IMAGES" in oracle using DBA account.

CREATE DIRECTORY IMAGES AS 'C:\PICTURES';

GRANT READ ON DIRECTORY IMAGES TO PUBLIC;

CREATE TABLE EMP_PHOTOS(empid NUMBER,photo BLOB);

Copy the pictures using following procedure into EMP_PHOTOS table

CREATE OR REPLACE PROCEDURE copy_photo(id NUMBER,img VARCHAR2) IS v_photo blob; imgfile bfile; begin insert into EMP_PHOTOS values(id,empty_blob()) returning PHOTO into v_photo; imgfile:=bfilename('IMAGES',img); dbms_lob.fileopen(imgfile); dbms_lob.loadfromfile(v_photo,imgfile,dbms_lob.getlength(imgfile)); dbms_lob.fileclose(imgfile); commit; end; exec copy_photo(1,'smith.jpg'); exec copy_photo(2,'jones.jpg'); exec copy_phots(3,'king.jpg');

BFILE Example

CREATE TABLE EMP_PICTURES(ID NUMBER,PICTURE BFILE);

INSERT INTO EMP_PICTURES VALUES(1,BFILENAME('IMAGES',smith.jpg')); INSERT INTO EMP_PICTURES VALUES(2,BFILENAME('IMAGES','jones.jpg')); INSERT INTO EMP_PICTURES VALUES(3,BFILENAME('IMAGES','king.jpg')); To test whether file is existing declare v_file BFILE; v_file_exists BOOLEAN; BEGIN SELECT picture INTO v_file FROM EMP_PICTURES WHERE id=3; v_file_exists := (DBMS_LOB.FILEEXISTS(v_file) = 1); IF v_file_exists THEN DBMS_OUTPUT.PUT_LINE('File is existing.'); END IF; END ; /

Temporary LOBs

Temporary LOBs provide an interface to support the creation and deletion of LOBs that act like local variables Temporary LOBs can be BLOBs , CLOBs or NCLOBs These are not associated with a specific table Data is stored in your temporary tablespace, not in tables or memory Temporary LOBs are faster than persistent LOBs because they do not generate any redo or rollback information

 A temporary LOB can be created using DBMS_LOB.CREATE TEMPORARY procedure The lifetime of a temporary LOB is a session Temporary LOBs are useful for transforming data in permanent internal LOBs for example, changing an image type from GIF to JPEG A temporary LOB is empty when created and does not support the EMPTY_B/CLOB() functions

Creating and freeing temporary LOB

DECLARE temp_clob CLOB; temp_blob BLOB; BEGIN --Assigning a value to a null CLOB or BLOB variable causes --PL/SQL to implicitly create a session-duration temporary --LOB for you. temp_clob :=' http://www.exploringindia.com/south/south.html'; temp_blob := HEXTORAW('7A');
DBMS_LOB.FREETEMPORARY(temp_clob); DBMS_LOB.FREETEMPORARY(temp_blob); END; PL/SQL will implicitly free temporary LOBs when they go out of scope at the end of a block.

Dynamic SQL
Dynamic SQL refers to SQL statements that are constructed and executed at runtime. Static SQL refers to SQL statements that are fully specified, or fixed, at the time the code containing that statement is compiled. Dynamic PL/SQL refers to entire PL/SQL blocks of code that are constructed dynamically, then compiled and executed. Advantages By constructing and executing dynamically, you gain a tremendous amount of flexibility. You can also build extremely generic and widely useful reusable code. For an example a reporting application in a data warehouse environment does not know a table name until runtime. These tables are named according to the starting month and year of the quarter, for example, inv_01_2003, inv_04_2003, inv_07_2003, inv_10_2003, inv_01_2004, and so on. You can use dynamic SQL in your reporting application to specify the table name at runtime.

What can you do with dynamic SQL Execute DDL statements - You can only execute queries and DML statements with static SQL inside PL/SQL. What if you want to create a table or drop an index? Support ad-hoc query and update requirements of web-based applications - A common requirement of Internet applications is that users may be able to specify which columns they want to see and vary the order in which they see the data Dynamic SQL can be executed using two methods:

- dbms_sql package - native dynamic sql (NDS) : NDS is a native part of the PL/SQL language. It is much easier to use than dbms_sql

NDS EXECUTE IMMEDIATE statement

Executing DDL commands in procedures. Make sure you have create any table privilege
CREATE OR REPLACE PROCEDURE ddl_proc( p_tabname IN varchar2, p_colnames IN varchar2) IS v_objname varchar2(30); BEGIN SELECT object_name INTO v_objname FROM USER_OBJECTS WHERE object_name=upper(p_tabname); dbms_output.put_line(p_tabname||' object already exists'); EXCEPTION WHEN no_data_found THEN EXECUTE IMMEDIATE 'CREATE TABLE '||p_tabname||'('||p_colnames||')'; END; /

EXECUTE ddl_proc('mytab','id number(4),name varchar2(12)');

For Dropping table

CREATE or REPLACE PROCEDURE drop_table ( table_name IN VARCHAR2) AS BEGIN EXECUTE IMMEDIATE 'DROP TABLE ' || table_name; END; / EXEC DROP_TABLE('t');

Deleting row from a table

CREATE or REPLACE PROCEDURE delete_row( table_name IN VARCHAR2, condition IN VARCHAR2) AS where_clause VARCHAR2(100) := ' WHERE ' || condition; BEGIN EXECUTE IMMEDIATE 'DELETE ' || table_name || where_clause; EXCEPTION WHEN others THEN dbms_output.put_line('Exception:'||SQLERRM); END; /
execute delete_row('emp','empno=7566'); execute delete_row('dept','deptno=40');

Using DBMS_SQL Oracle lets you to write stored procedures and anonymous PL/SQL blocks that use dynamic SQL. DBMS_SQL enables you to parse any DML or DDL statement (syntax/formation of statement). Therefore, you can parse DDL statements directly using PL/SQL.

CREATE or replace PROCEDURE delete_row( table_name VARCHAR2, condition varchar2) IS cur_hdl INTEGER; stmt_str VARCHAR2(200); rows_processed BINARY_INTEGER; BEGIN stmt_str := 'DELETE FROM ' || table_name || ' where ' ||condition; -- open cursor cur_hdl := dbms_sql.open_cursor; -- parse cursor dbms_sql.parse(cur_hdl, stmt_str,dbms_sql.native); -- supply binds rows_processed := dbms_sql.execute(cur_hdl); -- close cursor dbms_sql.close_cursor(cur_hdl); END; / exec delete_row('dept','dname=''SPORTS''');

The following sample procedure is passed a SQL statement, which it then parses and runs: CREATE OR REPLACE PROCEDURE exec(STRING IN varchar2) AS cursor_name INTEGER; ret INTEGER; BEGIN cursor_name := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(cursor_name, string, DBMS_SQL.native); ret := DBMS_SQL.EXECUTE(cursor_name); DBMS_SQL.CLOSE_CURSOR(cursor_name); EXCEPTION WHEN others THEN dbms_output.put_line('Exception:'||SQLERRM); END; /

exec exec('insert into dept values(50,''SPORTS'',''BLORE'')');

Execution Flow
OPEN_CURSOR To process a SQL statement, you must have an open cursor. When you call the OPEN_CURSOR function, you receive a cursor ID number for the data structure representing a valid cursor maintained by Oracle. These cursors are distinct from cursors defined at the PL/SQL level, and are used only by the DBMS_SQL package. PARSE Every SQL statement must be parsed by calling the PARSE procedure. Parsing the statement checks the statements syntax and associates it with the cursor in your program. EXECUTE Call the EXECUTE function to run your SQL statement. CLOSE_CURSOR When you no longer need a cursor for a session, close the cursor by calling CLOSE_CURSOR.

BIND_VARIABLE or BIND_ARRAY Many DML statements require that data in your program be input to Oracle. When you define a SQL statement that contains input data to be supplied at runtime, you must use placeholders in the SQL statement to mark where data must be supplied. For each placeholder in the SQL statement, you must call one of the bind procedures, BIND_VARIABLE or BIND_ARRAY, to supply the value of a variable in your program (or the values of an array) to the placeholder. When the SQL statement is subsequently run, Oracle uses the data that your program has placed in the output and input, or bind variables.

DEFINE_COLUMN The columns of the row being selected in a SELECT statement are identified by their relative positions as they appear in the select list, from left to right. For a query, you must call one of the define procedure DEFINE_COLUMN to specify the variables that are to receive the SELECT values, much the way an INTO clause does for a static query. FETCH_ROWS The FETCH_ROWS function retrieves the rows that satisfy the query. Each successive fetch retrieves another set of rows, until the fetch is unable to retrieve anymore rows. VARIABLE_VALUE, COLUMN_VALUE For queries, call COLUMN_VALUE to determine the value of a column retrieved by the FETCH_ROWS call. For anonymous blocks containing calls to PL/SQL procedures or DML statements with returning clause, call VARIABLE_VALUE to retrieve the values assigned to the output variables when statements were run.

CREATE or replace PROCEDURE insert_into_table( table_name VARCHAR2, v_deptno integer, v_dname VARCHAR2, v_loc VARCHAR2) IS cur_hdl INTEGER; stmt_str VARCHAR2(200); rows_processed BINARY_INTEGER; BEGIN stmt_str := 'INSERT INTO ' || table_name || ' VALUES (:deptno,:dname,:loc)'; -- open cursor cur_hdl := dbms_sql.open_cursor; -- parse cursor dbms_sql.parse(cur_hdl, stmt_str,dbms_sql.native); -- supply binds dbms_sql.bind_variable(cur_hdl, ':deptno',v_deptno); dbms_sql.bind_variable(cur_hdl, ':dname', v_dname); dbms_sql.bind_variable(cur_hdl, ':loc', v_loc); -- execute cursor rows_processed := dbms_sql.execute(cur_hdl); -- close cursor dbms_sql.close_cursor(cur_hdl); END;

For fetching data from emp table

DECLARE stmt_str varchar2(200); cur_hdl int; rows_processed int; name varchar2(10); salary int; BEGIN cur_hdl := dbms_sql.open_cursor; -- open cursor stmt_str := 'SELECT ename, sal FROM emp WHERE job = :jobname'; dbms_sql.parse(cur_hdl, stmt_str,dbms_sql.native);
-- supply binds (bind by name)

dbms_sql.bind_variable(cur_hdl, 'jobname', 'SALESMAN'); -- describe defines dbms_sql.define_column(cur_hdl, 1, name, 200); dbms_sql.define_column(cur_hdl, 2, salary); rows_processed := dbms_sql.execute(cur_hdl); --execute

LOOP
-- fetch a row

IF dbms_sql.fetch_rows(cur_hdl) > 0 then

-- fetch columns from the row

dbms_sql.column_value(cur_hdl, 1, name); dbms_sql.column_value(cur_hdl, 2, salary); dbms_output.put_line(name||','||salary); ELSE EXIT; END IF; END LOOP; dbms_sql.close_cursor(cur_hdl); -- close cursor END;

Updating dept_new table

DECLARE deptname_array dbms_sql.varchar2_table; cur_hdl INT; stmt_str VARCHAR2(200); location VARCHAR2(20); deptnumber NUMBER := 10; rows_processed NUMBER; BEGIN stmt_str := 'UPDATE dept_new SET loc = :newloc WHERE deptno = :deptno RETURNING dname INTO :dname'; cur_hdl := dbms_sql.open_cursor; dbms_sql.parse (cur_hdl, stmt_str, dbms_sql.native);

-- supply binds dbms_sql.bind_variable(cur_hdl, ':newloc', location); dbms_sql.bind_variable(cur_hdl, ':deptno', deptnumber); dbms_sql.bind_array(cur_hdl, ':dname', deptname_array); -- execute cursor rows_processed := dbms_sql.execute(cur_hdl); -- get RETURNING column into OUT bind array dbms_sql.variable_value(cur_hdl, ':dname', deptname_array); dbms_sql.close_cursor(cur_hdl); END;