MANIPULATING LARGE OBJECT

MANIPULATING BFILE

/*Grant the directory creation prioviledge to scott */
GRANT CREATE ANY DIRECTORY TO SCOTT;
GRANT DROP ANY DIRECTORY TO SCOTT;

/*create a file into scott which hold the bfile*/
CREATE TABLE HOLD(NAME BFILE);

/*directory information are stored in the table */
select * from DBA_DIRECTORIES;
select * from ALL_DBA_DIRECTORIES;

/*CREATE A PHYSICAL DIRECTORY IN E:/ARKA/ABC */

/*CREATE DIRECTORY*/
CREATE DIRECTORY ABC AS 'E:/ARKA/ABC';

/*GRANTING FOR PRIVILEDGE TO READ DIRECTORY OBJECT TO THE USER*/
GRANT READ ON DIRECTORY ABC TO PUBLIC;

/*ADDING BFILE*/
insert into hold values(bfilename('abc','cat.wmv'));
insert into hold values(bfilename('abc','kyo2.gif'));
insert into hold values(bfilename('abc','moustache.jpg'));
insert into hold values(bfilename('abc','10roses.pps'));

/**/
SELECT * FROM HOLD;
/*it returns error because sql* plus cannot open any gif or....*/

MANIPULATING EXTERNAL LOB

-----------------------------------------------------
/*LOB */
/*create the directory in the physical path*/
create or replace directory LOB_SOURCE as 'e:/arka/abc';
/*create a physical file in that named "lobfile.txt"*/
/*create a table which hold the clob file*/
create table lob_tab ( the_lob clob );

/*procedure to fecth the file length*/
declare
v_lob clob;
l_bfile bfile;
buf raw(32767);
vc varchar2(32767);
amt number;
v_offset number := 1;
begin
l_bfile := bfilename( 'LOB_SOURCE', 'lobfile.txt' );
insert into lob_tab (the_lob) values ( empty_clob() )
returning the_lob into v_lob;
amt := dbms_lob.getlength( l_bfile );
dbms_lob.fileopen( l_bfile ,dbms_lob.file_readonly);
dbms_lob.read(l_bfile,amt,v_offset,buf);
vc := utl_raw.cast_to_varchar2(buf);
dbms_lob.writeappend(v_lob,amt,vc);
dbms_output.put_line('File length is: '||dbms_lob.getlength( l_bfile ));
dbms_output.put_line('Loaded length is: '||dbms_lob.getlength(v_lob));
dbms_lob.fileclose( l_bfile );
end;
/
-------------------------------------------------------------------------------------------------------------------------------

MANIPULATING INTERNAL LOB

/*CREATING A TABLE CARRYING A LOB VALUE*/
create table lob_exa(name varchar2(10),resume clob);

/*INSERTING DATA INTOLOB */
insert into lob_exa values('arka','B.tech engineer passout from west bengal university of technology. in 2007 . hometown Chandernagore');

/*DECLARING A CLOB WITHOUT ASSIGNING A VALUE INTO IT*/
insert into lob_exa values('Dibyendu',empty_clob());

/*ASSIGNING DATA TO EMPTY_CLOB()*/
UPDATE LOB_EXA
SET RESUME='B.COM FROM RISHI BANKIM COLLEGE. HOMETOWN CHUCHURA'
WHERE NAME= 'Dibyendu';

/*APPNEDING TO LOB(FOR INSERTING DBMS_LOB.WRITE(LOBLOC,AMOUNT,OFFSET,TEXT)*/
DECLARE
LOBLOC CLOB;
TEXT VARCHAR2(32767):='NATIONALITY INDIAN';
OFFSET INTEGER;
AMOUNT NUMBER;
BEGIN
SELECT RESUME INTO LOBLOC
FROM LOB_EXA WHERE NAME='Dibyendu' FOR UPDATE;
OFFSET:=DBMS_LOB.GETLENGTH(LOBLOC)+2;
AMOUNT:=LENGTH(TEXT);
DBMS_LOB.WRITEAPPEND(LOBLOC,AMOUNT,TEXT);
COMMIT;
END;

TRIGGER

TRIGGER

*****used table for creating trigger**********
create table trig_test(order1 char);

------------------------------------------------------
a simple "DML STATEMENT TRIGGER"trigger

create or replace trigger trig1 before insert or delete or update on stud
begin
if inserting then insert into trig_test values('I');
elsif deleting then insert into trig_test values('d');
elsif updating then insert into trig_test values('U');
end if;
end;
/
-------------------------------------------------------
checking the trigger
insert into stud(roll) values(20);
insert into stud(roll) values(30);
select * from trig_test;
update stud set roll=10 where roll=20;
delete from stud where roll=10;
select * from trig_test;

----------------------------------------------------------
used table and values
insert into stud values('arka',1,'16-may-1985');
insert into stud values('dibyendu',2,'29-dec-1982');
insert into stud values('gora',3,'21-may-1989');
create table oldstud(name varchar2(20),roll number(5),dob date);
---------------------------------------------------------
A SIMPLE "DML ROW LEVEL TRIGGER"
create or replace trigger trig2 after delete or insert or update on stud for each row
begin
if inserting then
dbms_output.put_line('one row inserted');
elsif deleting then
insert into oldstud values(:old.name,:old.roll,:old.dob);
dbms_output.put_line('one row deleted');
elsif updating then
insert into oldstud values(:old.name,:old.roll,:old.dob);
dbms_output.put_line('one row updated');
end if;
end;
/

insert into stud values('india',6,'15-aug-1947');
update stud set roll=10 where name='arka';
---------------------------------------------------------------

Managing trigger

-----------disable or enable trigger-----
alter trigger trig1 disable;
alter trigger trig1 enable;

-----------disable or enable all trigger on a table ---------
alter table stud disable all triggers;
alter table stud enable all triggers;

----recompile trigger-----------------------
alter trigger trig1 compile;

-------drop trigger-----------
drop trigger trig1;
------------------------------------------------------

LOGON LOGOF TRIGGER

used table
create table LOGIN_CHECKER(a varchar2(20));

CREATE OR REPLACE TRIGGER logged
AFTER LOGON ON SCHEMA
begin
INSERT INTO LOGIN_CHECKER VALUES('T');
end;

CREATE OR REPLACE TRIGGER log_out
AFTER LOGON ON SCHEMA
begin
INSERT INTO LOGIN_CHECKER VALUES('F');
end;

--------------------------------------------------

CALL statement

used procedure

CREATE OR REPLACE PROCEDURE PROC_TRIG IS
BEGIN
INSERT INTO LOGIN_CHECKER VALUES('INSERTED');
END;

TRIGGER BODY

CREATE OR REPLACE TRIGGER CALL_TRIG
AFTER INSERT ON STUD FOR EACH ROW
CALL PROC_TRIG
/

INSERTING VALUES
INSERT INTO STUD(ROLL) VALUES(78);

CHECKING
SELECT * FROM LOGIN_CHECKER;

--------------------------------------------------
controlling sesurity through trigger

CREATE OR REPLACE trigger SECU_STUD
BEFORE INSERT ON STUD FOR EACH ROW
BEGIN IF(TO_CHAR(SYSDATE,'DY') IN ('SAT','FRI')) THEN
RAISE_APPLICATION_ERROR(-20503,'Please Do not change the value it is problemetic for Arka');
end if;
end;

now check
insert into stud(roll) values(145);
--------------------------------------------------

Function

Function

create or replace FUNCTION f1 (a1 number)
RETURN
varchar IS
a number;
v varchar2(25);
BEGIN
select ename into v from emp where empno=a1;
RETURN v;
END ;

**** To exec the fun ****

select f1(7369) from dual;

**** Func with select and boolean and exception****

create or replace FUNCTION f1 (a1 number)
RETURN
boolean IS
v varchar2(25);
t1 boolean;
BEGIN
select ename into v from emp where empno=a1;
if v='SMITH' then
return TRUE;
end if;

exception
when no_data_found then
return FALSE;
END ;

**** To exec the fun ****

declare
t2 boolean;
begin
t2:=f1(7368);
if t2 then
dbms_output.put_line('found');
else
dbms_output.put_line('not found');
end if;
end;

Procedure

Procedure

/*A SIMPLE PROCEDURE*/
CREATE OR REPLACE PROCEDURE showname
IS
BEGIN
DELETE FROM STUD
WHERE ROLL=6;
END;
-------------------------------------------
CALLING PROCEDURE FROM SQL* PLUS
EXECUTE SHOWNAME;

CALLING PROCEDURE FROM PL/SQL BLOCK

BEGIN
SHOWNAME;
END;

---------------------------------------------
/*NORMAL PARAMETERIZE PROCEDURE(USING IN PARAMETER)*/
CREATE OR REPLACE PROCEDURE INS
(A IN STUD.ROLL%TYPE)
IS
BEGIN
INSERT INTO STUD(ROLL) VALUES(A);
END INS;
---------------------------------------------
/*PARAMETERIZE PROCEDURE 2 (USING OUT PARAMETER)*/
CREATE OR REPLACE PROCEDURE SHOWTAB
(A IN EMP.ENAME%TYPE,B OUT EMP.JOB%TYPE,C OUT EMP.SAL%TYPE)
IS
BEGIN
SELECT JOB,SAL INTO B,C FROM EMP WHERE ENAME=A;
DBMS_OUTPUT.PUT_LINE(B||' '||C);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('sORRY THE PERSON YOU HAVE ENTERED IS NOT AN EMPLOYEE');
END SHOWTAB;

/*show output*/
declare
x varchar2(30):='&Name';
y varchar2(10);
z number(5);
begin
showtab(x,y,z);
end;

---------------------------------------------
/*PARAMETERIZE PROCEDURE 2 (USING IN OUT PARAMETER)*/
CREATE OR REPLACE PROCEDURE PHONE
(PHNO IN OUT VARCHAR2)
IS
BEGIN
PHNO:='('||SUBSTR(PHNO,1,3)||')'||SUBSTR(PHNO,4,4)||'-'||SUBSTR(PHNO,8);
END PHONE;

/*SHOW OUTPUT*/
DECLARE
PH_NO varchar2(25):='&PHONE_NUMBER';
BEGIN
PHONE(PH_NO);
DBMS_OUTPUT.PUT_LINE(PH_NO);
END;
/

---------------------------------------------
/*PROCEDURE USING DEFAULT PARAMETER*/
CREATE OR REPLACE PROCEDURE ADD_STUD
(SNAME IN STUD.NAME%TYPE DEFAULT 'NOONE',SROLL IN STUD.ROLL%TYPE DEFAULT 99,SDOB IN STUD.DOB%TYPE)
IS
BEGIN
INSERT INTO STUD(NAME,ROLL,DOB) VALUES(SNAME,SROLL,SDOB);
END ADD_STUD;
/

/*SHOW OUTPUT*/
DECLARE
A STUD.NAME%TYPE:='&NAME';
B STUD.ROLL%TYPE:=&ROLL;
C STUD.DOB%TYPE:='&DATE';
BEGIN
ADD_STUD(A,B,C);
END;

---------------------------------------------
/*CALLING A PROCEDURE FROM ANOTHER PROCEDURE*/
CREATE OR REPLACE PROCEDURE PROC1
IS
CURSOR EMP_CUR IS
SELECT EMPNO
FROM EMP;
BEGIN
FOR EMP_REC IN EMP_CUR
LOOP
INS(EMP_REC.EMPNO);
END LOOP;
COMMIT;
END PROC1;
/
---------------------------------------------

/*REMOVING PROCEDURE*/
DROP PROCEDURE PROC1;

Exception Handling

Exception Handling

declare
A NUMBER(2):=0;
B NUMBER(2):=0;
C NUMBER(2);
begin
C:=A/B;
DBMS_OUTPUT.PUT_LINE('THE DIVISOR YOU GAVE IS ZERO');
DBMS_OUTPUT.PUT_LINE(C);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('THERE IS AN ERROR');
commit;
end;
/
-----------------------------------------------
CREATE TABLE STUD(NAME VARCHAR2(30),ROLL NUMBER(5),DOB DATE,PRIMARY KEY(ROLL));
INSERT INTO STUD VALUES('ARKA',12,'16-MAY-1985');
INSERT INTO STUD VALUES('INDIA',1,'15-AUG-1947');
INSERT INTO STUD VALUES('DIBYENDU',2,'1-DEC-2007');

--------------------------------------------------------------------

/*NO_DATA_FOUND EXCEPTION*/

DECLARE
A VARCHAR2(10);
BEGIN
SELECT NAME INTO A FROM STUD WHERE NAME='STG';
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('SORY NO DATA IS FOUND');
END;
/
--------------------------------------------------

/*USER DEFINE EXCEPTION(USING "RAISE" STATEMENT)*/
DECLARE
INVALID EXCEPTION;
a number:=&x;
BEGIN
if a>20 THEN
insert into stud(roll) values(a);
else
RAISE INVALID;
END IF;
COMMIT;
EXCEPTION
WHEN INVALID THEN
DBMS_OUTPUT.PUT_LINE('SORY INVALID NUMBER');
END;
/

--------------------------------------------------
integrity constant violation (using PRAGMA)

define a=10
DECLARE
EX EXCEPTION;
PRAGMA EXCEPTION_INIT(EX,-2292);
BEGIN
DELETE FROM STUD
WHERE ROLL=&A;
COMMIT;
EXCEPTION
WHEN EX THEN
DBMS_OUTPUT.PUT_LINE('CANNOT REMOVE NAME STUDENT EXST');
END;
/
--------------------------------------------------

/*EXCEPTION DECLARATION USING
RAISE_APPLICATION_ERROR----RANGE(-20000 TO -20999)*/

DECLARE
a number:=&x;
BEGIN
if a>20 THEN
insert into stud(roll) values(a);
else
RAISE_APPLICATION_ERROR(-20005,'Number greater then 20');
END IF;
COMMIT;
END;
/
--------------------------------------------------

Explicit cursor declaration

NORMAL CURSOR DECLARATION

/*USE OF OPEN,FETCH,%TYPE*/
DECLARE
CURSOR c1 IS SELECT ENAME FROM emp;
name1 emp.ENAME%TYPE;
name2 emp.ENAME%TYPE;
name3 emp.ENAME%TYPE;
BEGIN
OPEN c1;
FETCH c1 INTO name1; -- this fetches first row
DBMS_OUTPUT.PUT_LINE('ENAME:>'||NAME1);
FETCH c1 INTO name2; -- this fetches SECOND row
DBMS_OUTPUT.PUT_LINE('ENAME:>'||NAME2);
CLOSE c1;
END;
------------------------------------------------------------

/*FETCHING DATA FROM CURSOR USING for loop*/
DECLARE
name1 emp.ENAME%TYPE;
name2 emp.SAL%TYPE;
name3 emp.HIREDATE%TYPE;
CURSOR c1 IS SELECT ENAME,SAL,HIREDATE FROM emp;
BEGIN
OPEN c1;
FOR I IN 1..10
LOOP
FETCH c1 INTO NAME1,NAME2,NAME3;
DBMS_OUTPUT.PUT_LINE(' '||NAME1||' '||NAME2||' '||NAME3);
END LOOP;
CLOSE c1;
END;

------------------------------------------------------------

/*Example of %ISOPEN, %NOTFOUND,%ROWCOUNT */
DECLARE
name1 emp.ENAME%TYPE;
name2 emp.SAL%TYPE;
name3 emp.HIREDATE%TYPE;
CURSOR c1 IS SELECT ENAME,SAL,HIREDATE FROM emp;
BEGIN
IF NOT C1%ISOPEN THEN OPEN C1;
END IF;
LOOP
FETCH c1 INTO NAME1,NAME2,NAME3;
EXIT WHEN C1%ROWCOUNT>10 OR C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(' '||NAME1||' '||NAME2||' '||NAME3);
END LOOP;
close c1;
END;
------------------------------------------------------------

/*CURSOR USING %ROWTYPE*/

DECLARE
CURSOR c1 IS SELECT * FROM emp;
name1 emp%ROWTYPE;
name2 emp%ROWTYPE;
name3 emp%ROWTYPE;
BEGIN
OPEN c1;
FETCH c1 INTO name1; -- this fetches first row
DBMS_OUTPUT.PUT_LINE('ENAME:>'||NAME1.ENAME||'SAL:>'||NAME1.SAL);
FETCH c1 INTO name2; -- this fetches second row
DBMS_OUTPUT.PUT_LINE('ENAME:>'||NAME2.ENAME||'SAL:>'||NAME2.SAL);
FETCH c1 INTO name3; -- this fetches third row
CLOSE c1;

END;

------------------------------------------------------------
/*CURSOR AND RECORD*/

DECLARE
CURSOR c1 IS SELECT ENAME,SAL,HIREDATE FROM emp WHERE COMM IS NULL;
C1_REC C1%ROWTYPE;
BEGIN
OPEN C1;
LOOP
FETCH c1 INTO C1_REC;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(' '||C1_REC.ENAME||''||C1_REC.SAL||' '||C1_REC.HIREDATE);
END LOOP;
END;
------------------------------------------------------------

/*CURSOR FOR LOOP*/

DECLARE
CURSOR C1 IS
SELECT Ename,EMPNO, HIREDATE
FROM EMP
WHERE DEPTNO = 20;
BEGIN
FOR C1_DAT IN C1 LOOP
INSERT INTO STUD (NAME,ROLL,DOB)
VALUES (C1_DAT.ENAME, C1_DAT.EMPNO,C1_DAT.HIREDATE);
END LOOP;
COMMIT;
END;
/
------------------------------------------------------------

/*CURSOR FOR LOOP(A FAULTY CONDITION)*/

DECLARE
CURSOR c1 IS SELECT ENAME,SAL,HIREDATE FROM emp WHERE COMM IS NULL;
name1 emp.ENAME%TYPE;
name2 emp.SAL%TYPE;
name3 emp.HIREDATE%TYPE;
BEGIN
for C1_REC IN C1 LOOP
FETCH c1 INTO NAME1,NAME2,NAME3;
DBMS_OUTPUT.PUT_LINE(' '||NAME1||' '||NAME2||' '||NAME3);
END LOOP;
END;