Skip to main content

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.

Popular posts from this blog

Cognizant Company Profile and it's information for Interview

Website: www.cognizant.com HQ Teaneck, NJ Industry Information Technology Services Size 130K+ Employees, $6B+ Revenue NASDAQ CTSH Competitors Infosys, Wipro, Tata Consultancy Services   About cognizant Cognizant Corporate view: Cognizant is an American multinational IT services and consulting corporation headquartered in Teaneck, New Jersey, United States. Cognizant has been named to the 2010 Fortune 100 Fastest-Growing Companies List for the eighth consecutive year. Cognizant has also been named to the Fortune 1000 and Forbes Global 2000 lists. It has consistently ranked among the fastest growing companies including the 2010 Business Week 50 list of the top-performing U.S. companies, the Business Week Hottest Tech Companies 2010, and the Forbes Fast Tech 2010 list of 25 Fastest Growing Technology Companies In America. Founded: 1994 Headquarters: Teaneck, New Jersey, U.S. Key people:  Francisco D'Souza (President & CEO) Lakshmi Naray...

CIVIL SERVICES' (I.A.S.) EXAMINATION

The Union Public Service Commission (U.P.S.C.)  conducts Civil Services' Examination once a year in two stages. The Preliminary Examination (Objective Type) for selection of candidates for the Main Examination is held in the month of May. The Civil Services Main Examination  is held in the months of October/November. Blank application forms and other particulars are published in the Employment News, generally in the month of December. The last date for the submission of applications to the Secretary, Union Public Service Commission, Dholpur House, Shahjahan Road, NewDelhi-11001 1 is usually the last week of January of the year of examination. The Combined Civil Services Examination is conducted for Recruitment to the following Services/Posts: 1. Indian Administrative Service. 2. Indian Foreign Service. 3. Indian Police Service. 4. Indian P & T Accounts & Finance Service, Group 'A'. 5. Indian Audit and Accounts Service, Group 'A'. 6. Indian Customs and Centr...

Two sticks and matchbox, measure exactly 45 minutes by burning these sticks

Question: You have two sticks and matchbox. Each stick takes exactly an hour to burn from one end to the other. The sticks are not identical and do not burn at a constant rate. As a result, two equal lengths of the stick would not necessarily burn in the same amount of time.  How would you measure exactly 45 minutes by burning these sticks? Answer: This puzzle used to be asked in Wall Street interviews long time ago. It is very rare for this question to be asked now but it is a very good question to help you think a little outside the normal thought process. The answer is really simple. Since the sticks do not burn at a constant rate, we can not use the length of the stick as any sort of measurement of time. If we light a stick, it takes 60 minutes to burn completely. What if we light the stick from both sides? It will take exactly half the original time, i.e. 30 minutes to burn completely. 0 minutes – Light stick 1 on both sides and stick 2 on one s...