7/27/2019 Adv Plsql2
1/29
LOB DatatypesOracle 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)
7/27/2019 Adv Plsql2
2/29
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
7/27/2019 Adv Plsql2
3/29
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.
7/27/2019 Adv Plsql2
4/29
Comparing null with empty locatorDECLAREv_clob1 clob;v_clob2 clob:=empty_clob();
beginif v_clob1 is null thendbms_output.put_line('v_clob1=null');
end if;if v_clob2 is null thendbms_output.put_line('v_clob2=null');
elsedbms_output.put_line('v_clob2=not null');dbms_output.put_line('length='||length(v_clob2));
end if;end;
/
v_clob1=nullv_clob2=not nulllength=0
7/27/2019 Adv Plsql2
5/29
7/27/2019 Adv Plsql2
6/29
DBMS_LOB package contains
7/27/2019 Adv Plsql2
7/29
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;
7/27/2019 Adv Plsql2
8/29
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);
7/27/2019 Adv Plsql2
9/29
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');
7/27/2019 Adv Plsql2
10/29
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 existingdeclare
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 ;/
7/27/2019 Adv Plsql2
11/29
Temporary LOBs
Temporary LOBs provide an interface to support
the creation and deletion of LOBs that act like localvariables
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 LOBsbecause they do not generate any redo or rollback
information
7/27/2019 Adv Plsql2
12/29
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
7/27/2019 Adv Plsql2
13/29
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.
7/27/2019 Adv Plsql2
14/29
Dynamic SQL
Dynamic SQL refers to SQL statements that are constructed andexecuted at runtime.
Static SQL refers to SQL statements that are fully specified, or fixed,at the time the code containing that statement is compiled. DynamicPL/SQL refers to entire PL/SQL blocks of code that are constructeddynamically, then compiled and executed.
Advantages
By constructing and executing dynamically, you gain a tremendousamount of flexibility. You can also build extremely generic and widelyuseful reusable code.
For an example a reporting application in a data warehouse
environment does not know a table name until runtime. These tablesare 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.
7/27/2019 Adv Plsql2
15/29
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 varythe 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
7/27/2019 Adv Plsql2
16/29
NDS EXECUTE IMMEDIATE statement
Executing DDL commands in procedures. Make sure you have createany table privilege
CREATE OR REPLACE PROCEDURE ddl_proc(p_tabname IN varchar2,p_colnames IN varchar2)
ISv_objname varchar2(30);
BEGINSELECT object_name INTO v_objname FROM USER_OBJECTSWHERE object_name=upper(p_tabname);dbms_output.put_line(p_tabname||' object already exists');
EXCEPTION
WHEN no_data_found THENEXECUTE IMMEDIATE 'CREATE TABLE
'||p_tabname||'('||p_colnames||')';END;/
EXECUTE ddl_proc('mytab','id number(4),name varchar2(12)');
bl
7/27/2019 Adv Plsql2
17/29
For Dropping tableCREATE or REPLACE PROCEDURE drop_table (table_name IN VARCHAR2)
ASBEGINEXECUTE IMMEDIATE 'DROP TABLE ' || table_name;
END;/
EXEC DROP_TABLE('t');
D l ti f t bl
7/27/2019 Adv Plsql2
18/29
Deleting row from a table
CREATE or REPLACE PROCEDURE delete_row(table_name IN VARCHAR2,condition IN VARCHAR2)
ASwhere_clause VARCHAR2(100) := ' WHERE ' || condition;
BEGINEXECUTE IMMEDIATE 'DELETE ' || table_name || where_clause;
EXCEPTION
WHEN others THENdbms_output.put_line('Exception:'||SQLERRM);
END;/
execute delete_row('emp','empno=7566');
execute delete_row('dept','deptno=40');
U i DBMS SQL
7/27/2019 Adv Plsql2
19/29
Using DBMS_SQLOracle lets you to write stored procedures andanonymous PL/SQL blocks that use dynamic SQL.
DBMS_SQL enables you to parse any DML or DDLstatement (syntax/formation of statement).
Therefore, you can parse DDL statements directly using
PL/SQL.
CREATE l PROCEDURE d l t (
7/27/2019 Adv Plsql2
20/29
CREATE or replace PROCEDURE delete_row(table_name VARCHAR2,condition varchar2)
IScur_hdl INTEGER;
stmt_str VARCHAR2(200);rows_processed BINARY_INTEGER;
BEGINstmt_str := 'DELETE FROM ' || table_name || ' where ' ||condition;-- open cursor
cur_hdl := dbms_sql.open_cursor;-- parse cursordbms_sql.parse(cur_hdl, stmt_str,dbms_sql.native);-- supply bindsrows_processed := dbms_sql.execute(cur_hdl);
-- close cursordbms_sql.close_cursor(cur_hdl);
END;/
exec delete_row('dept','dname=''SPORTS''');
Th f ll i l d i d SQL hi h i
7/27/2019 Adv Plsql2
21/29
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'')');
E ti Fl
7/27/2019 Adv Plsql2
22/29
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 structurerepresenting 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 BIND ARRAY
7/27/2019 Adv Plsql2
23/29
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 useplaceholders in the SQL statement to mark where data must be
supplied.
For each placeholder in the SQL statement, you must call oneof 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
7/27/2019 Adv Plsql2
24/29
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 theSELECT 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(
7/27/2019 Adv Plsql2
25/29
CREATE or replace PROCEDURE insert_into_table(
table_name VARCHAR2,
v_deptno integer,
v_dname VARCHAR2,
v_loc VARCHAR2)
IScur_hdl INTEGER;
stmt_str VARCHAR2(200);
rows_processed BINARY_INTEGER;
BEGIN
stmt_str := 'INSERT INTO ' || table_name || ' VALUES (:deptno,:dname,:loc)';
-- open cursorcur_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;
F f t hi d t f t bl
7/27/2019 Adv Plsql2
26/29
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 cursorstmt_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
7/27/2019 Adv Plsql2
27/29
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;
U d ti d t t bl
7/27/2019 Adv Plsql2
28/29
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 = :deptnoRETURNING dname INTO :dname';
cur_hdl := dbms_sql.open_cursor;
dbms_sql.parse (cur_hdl, stmt_str, dbms_sql.native);
7/27/2019 Adv Plsql2
29/29
-- 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;
Top Related