PL/SQL to Create or Replace Triggers, Varray and nested tables

Question: 1)
Create orreplace trigger trig_msg after update ordelete or insert on customer for each row
Code:
declare
oper varchar2(10);
begin
if updating then
DBMS_OUTPUT.PUT_LINE('UPDATING');
end if;
if deleting then
DBMS_OUTPUT.PUT_LINE('DELETING');
end if;
if inserting then
DBMS_OUTPUT.PUT_LINE('INSERTING');
end if;
end;
SQL> set serveroutput on;
SQL> insert into customer values(1,'vish','delhi',500,'21-jan-2001');
INSERTING
1 row created.
SQL> delete from customer where loanno = 1;
DELETING
1 row deleted.
Question 2)
create or replace trigger trig_nodml after update orinsert or delete on loan for each row
Code:
declare
m varchar2(15);
begin
if updating then
RAISE_APPLICATION_ERROR(-20000,'NO UPDATE ALLOWED');
end if;
if deleting then
RAISE_APPLICATION_ERROR(-20000,'NO DELETING ALLOWED');
end if;
if inserting then
m:=TO_CHAR(sysdate,'DAY');
if (upper(m)<> upper('sunday')) then
RAISE_APPLICATION_ERROR(-20000,'NOT ALLOWED ON WEEKDAY');
end if;
end if;
end;
SQL> insert into loan values(1,'vishal','delhi',2000);
insert into loan values(1,'vishal','delhi',2000)
*
ERROR at line 1:
ORA-20000: NOT ALLOWED ON WEEKDAY
ORA-06512: at "SCOTT.TRIG_NODML", line 13
ORA-04088: error during execution of trigger 'SCOTT.TRIG_NODML'
5)Varray and nested tables
i)
declare
typevmarktype is varray(10) of integer ;
c int;
xvmarktype;
begin
x:=vmarktype(12,45,78,45,77);
c:=x.count;
for iin 1..c loop
dbms_output.put_line(x(i));
endloop;
end;
SQL> @c:\varray.sql
12 /
12
45
78
45
77
PL/SQL procedure successfully completed.

Categories

10th 3i InfoTech Academic Books Accenture ACIO ActiveX ADT Agricultural AIEEE Air Force Algebraic Amdocs Android Answers Application Development Aptitude Aptitude Questions Architectures ASP ATOS B.Sc B.Tech.B.E. Bank Exam BCA BE Board Exam Books break-continue Business Plan C C Programming C# C++ Campus campus interview Candidate profile Capgemini Career CDS Certification CET Challenge Circle Cisco class code Cognizant communication Company Company Profile Competitive Exams computer Computer Networks concentric circles constructor Course Credit Suisse CSS CTS Data Structure DBMS DC Deloitte difficult interview questions dimensions Distributed Computing do while dotNet Download ds Dynamic Web Development e-Admit card Educational engineering entertainment Even Odd Events exam schedule exception for loop fresher GATE general Discussion general knowledge Get Placed Government Job Hall Ticket HCL how to answer How to Prepare HR HR Interview HSC hypertext preprocessor IB IBM IBPS IIT Indian Army Information infosys Intelligence Bureau Internship interview Experience interview questions Interview Tips IntroC IntroC# IntroJava IntroPHP IT J2EE J2ME Java JavaScript jobs Language Books Language Tutorial Languages limit number of objectsJava Limit the number of objects being created in JAVA Linux Linux Administrator Linux Developer Logical Questions loops M Tech M.E. M.Tech M.Tech AND B.Tech Management Management Skills Matrices MBA mca ME microsoft mistakes Mixture Mobile Computing mock questions mock test MySql naukri NDA OOP opening Operating System Oracle paper Persistent PHP php programming php string variables PL/SQL Placement placement guide Placement Paper Placement Process preparing for placement presentation probability Problems Professional program Programming Project Engineer project idea Projects Puzzle qualities Question of the day Questions Quiz Question Recruitment Recruitment Pattern Requirement Result Resume Reviews Screen Sizes Scripting Session Skills Software Software Engineering solved papers Source Code Speed time and distance SQL SSC story Stress Interview Study Material study tips submit resume Synonym TCS Tech Mahindra tech news Technical Books Technical Interview Testing thank you letter Thought Time Table TutC++ Unix questions asked in aptitude and inteviews for MCA UPSC verbal Web Designing Web Developer Website Development What to read while loop Wipro Writing Resume

Receive Quality Tutorials Straight in your Inbox by submitting your Email ID below.

Categories

10th 3i InfoTech Academic Books Accenture ACIO ActiveX ADT Agricultural AIEEE Air Force Algebraic Amdocs Android Answers Application Development Aptitude Aptitude Questions Architectures ASP ATOS B.Sc B.Tech.B.E. Bank Exam BCA BE Board Exam Books break-continue Business Plan C C Programming C# C++ Campus campus interview Candidate profile Capgemini Career CDS Certification CET Challenge Circle Cisco class code Cognizant communication Company Company Profile Competitive Exams computer Computer Networks concentric circles constructor Course Credit Suisse CSS CTS Data Structure DBMS DC Deloitte difficult interview questions dimensions Distributed Computing do while dotNet Download ds Dynamic Web Development e-Admit card Educational engineering entertainment Even Odd Events exam schedule exception for loop fresher GATE general Discussion general knowledge Get Placed Government Job Hall Ticket HCL how to answer How to Prepare HR HR Interview HSC hypertext preprocessor IB IBM IBPS IIT Indian Army Information infosys Intelligence Bureau Internship interview Experience interview questions Interview Tips IntroC IntroC# IntroJava IntroPHP IT J2EE J2ME Java JavaScript jobs Language Books Language Tutorial Languages limit number of objectsJava Limit the number of objects being created in JAVA Linux Linux Administrator Linux Developer Logical Questions loops M Tech M.E. M.Tech M.Tech AND B.Tech Management Management Skills Matrices MBA mca ME microsoft mistakes Mixture Mobile Computing mock questions mock test MySql naukri NDA OOP opening Operating System Oracle paper Persistent PHP php programming php string variables PL/SQL Placement placement guide Placement Paper Placement Process preparing for placement presentation probability Problems Professional program Programming Project Engineer project idea Projects Puzzle qualities Question of the day Questions Quiz Question Recruitment Recruitment Pattern Requirement Result Resume Reviews Screen Sizes Scripting Session Skills Software Software Engineering solved papers Source Code Speed time and distance SQL SSC story Stress Interview Study Material study tips submit resume Synonym TCS Tech Mahindra tech news Technical Books Technical Interview Testing thank you letter Thought Time Table TutC++ Unix questions asked in aptitude and inteviews for MCA UPSC verbal Web Designing Web Developer Website Development What to read while loop Wipro Writing Resume