My Sql Trial Commands_current

create table COMFORT2 ( city VARCHAR2(13) NOT NULL, Sampledate DATE NOT NULL, Noon NUMBER(3,1), MIDNIGHT NUMBER(3,1), Precipitation NUMBER );

create table COMFORT_TEST ( city VARCHAR2(13) NOT NULL, Sampledate DATE NOT NULL, Measure VARCHAR2(13), Value NUMBER(3,1) ); insert ALL into COMFORT_TEST(city, sampledate,MEasure,Val) values(city, sampledate,'NOON',Noon) into COMFORT_TEST(city, sampledate,MEasure,Val) values(city, sampledate,'MIDNIGHT',Midnight) into COMFORT_TEST(city, sampledate,MEasure,Val) values(city, sampledate,'PRECIP',Prepicipation) select city, sampledate,noon, midnight, precipatation from comfort where city='Khene'; insert into comfort values('WALPOLE',TO_DATE('06/22/2003','MM/DD/YYYY'), 86.3,72.1,NULL);

insert into comfort (sampledate,Precipatation,city,NOON,Midnight) select '22-dec-2003',Precipatation, 'WALPOLE',noon,midnight from comfort where city = 'KEENE' and sampledate='22-dec-2003';

declare pi constant Number(9,7) := 3.1415927; radius integer (5); area Number (14,2); begin radius:=3; area:=pi*power(radius,2); insert into areas values (radius,area); end;

create or replace view prac as select city, sampledate from trouble where city = 'Pleasant';

create type address_ty as object (street varchar2(50), city varchar2(25), state char(2), zip number); create table customer (cust_id number, address address_ty); THIS IS ONLY FOR 1 ROW declare pi constant Number(9,7) := 3.1415927; area Number(14,2); cursor rad_cursor is select * from radius_vals; rad_val rad_cursor%ROWTYPE; begin open rad_cursor; fetch rad_cursor into rad_val; area:=pi*power(rad_val.radius,2); insert into areas values (rad_val.radius,area); close rad_cursor; end; FOR ALL ROWS declare pi constant Number(9,7) := 3.1415927; area Number(14,2); cursor rad_cursor is select * from radius_vals; rad_val rad_cursor%ROWTYPE; begin open rad_cursor; LOOP fetch rad_cursor into rad_val; EXIT when rad_cursor%NOTFOUND; area:=pi*power(rad_val.radius,2); insert into areas values (rad_val.radius,area); END LOOP; close rad_cursor; end;

exec procedurename(param1, param2,:param3) provided param3 is an out put

create procedure calc (radius number in, area number(14,2) out ) is pi constant Number(9,7) := 3.1415927; begin area:=pi*power(rad_val.radius,2); insert into areas values (rad_val.radius,area); end; create or replace procedure calc (in radius number ) is pi constant Number(9,7) := 3.1415927; area number(14,2); begin area:=pi*power(rad_val.radius,2); insert into areas values (rad_val.radius,area); end;

create or replace procedure p12( par1 in number) begin as declare var1 number; set var1= par1+1; if var1=0 then insert into radius_vals values(143); end if; if par1=0 then update radius_vals set radius=radius+1; else update radius_vals set radius=radius+2 end if; end; exec procedurename(param1, param2,:param3) provided param3 is an out put create or replace procedure prac(pra in number) is begin select * from trouble; end;

create or replace procedure plsql18 (i_paint_id IN number, i_qty IN number) is begin declare cursor c_paint(i_paint_id IN paints.pt_id%type) is select pt_stk_on_hand from paints where pt_id = i_paint_id; l_qty

paints.pt_stk_on_hand%type;

begin if c_paint%isopen then close c_paint; end if; open c_paint(i_paint_id); fetch c_paint into l_qty; if l_qty < i_qty then null; end if; update paints set pt_stk_on_hand = pt_stk_on_hand - i_qty where pt_id = i_paint_id; end; end;

create or replace procedure calc is pi constant Number(9,7) := 3.1415927; area Number(14,2); cursor rad_cursor is select * from radius_vals; rad_val rad_cursor%ROWTYPE; begin open rad_cursor; LOOP fetch rad_cursor into rad_val; EXIT when rad_cursor%NOTFOUND; area:=pi*power(rad_val.radius,2); insert into areas values (rad_val.radius,area); END LOOP; close rad_cursor; end calc; / create or replace procedure p12( par1 in number) is var1 number:= par1+1; begin if var1=0 then insert into radius_vals values(143); end if; if par1=0 then update radius_vals set radius=radius+1; else update radius_vals set radius=radius+2; end if; end p12; / execute p12(-1);

create or replace procedure merge_table is (par in number(6)) begin create or replace procedure anystring( string in varchar2) as begin execute immediate (string); end; execute anystring(‘create table cd( artist varchar2(25),title varchar2(25))’); create or replace function atable (aname in number) return number is ttable number(10); begin for i in 1 .. 10 loop ttable := aname*i; insert into abt values (i,ttable); return(ttable); end loop; end; variable ta number; execute : ta := atable(9); DECLARE ta number; BEGIN ta := atable(7); DBMS_OUTPUT.PUT_LINE('TA END;

' ||ta);

create or replace function atable (aname in number) return number is ttable number(10); begin for i in 1 .. 10 loop ttable := aname*i; insert into abt values (i,ttable); end loop; return (ttable); end; set serveroutput on; CREATE TRIGGER upper_soundex BEFORE INSERT OR UPDATE OF ename, uppername on emp FOR EACH ROW

/

BEGIN :NEW.uppername := UPPER END;

(:NEW.ename);

declare v_char varchar2(90); v_num number(11,2); begin v_char:='42 is'; v_num:=to_number(substr(v_char,1,2)); :g_char:=v_char; :g_num:=v_num; end; / variable g_char varchar2 (90); variable g_num number;

declare ghar abt.i%type :=11; begin merge into ab a using abt b on (b.i=ghar) when matched then update set a.ind =b.i, a.num=b.ttable when not matched then insert values(b.i,b.ttable); end; / variable rows_d varchar2(30); declare ia number:=2; begin delete from abt where i=ia; :rows_d:=(sql%rowcount || ' row deleted.'); end;

declare cn number; i number; begin i:=0; loop insert into abt values(122,323); i:=i+1; exit when i>10; end loop; cn:= sql%rowcount; dbms_output.put_line(cn); end; declare vg char(1) := Upper('&pg'); va varchar2(20); begin va:= case vg when 'A' then 'Excellent' when 'B' then 'Very Good' when 'C' then 'Good' else 'No such grade' end; dbms_output.put_line('GRADE:' || vg || 'appraisal: ' ||va); end; / declare fl number :=1; begin loop if (fl!=6 and fl !=8) then insert into messages values(fl); end if; fl:=fl+1; exit when fl>10; end loop; select * into ab from messages where fl=1; commit; end; create table messages(rs number); select * from messages;

alter table employees add stars varchar2(100); define emp=100; declare va varchar2(50) := Null; cn number; ar number; sa number; cursor e_cursor is select empi from emp1; abh e_cursor%ROWTYPE; begin open e_cursor; <> loop fetch e_cursor into abh; ar:= abh.empi; select sal into sa from emp1 where empi=ar; select NVL(round(sa/1000),0) into cn from dual; va:=NULL; /*is assigning is important becoz if this is not done then for next employess *’s of previous employees are also added*/ <> for i in 1 .. cn loop va:= va||’*’; end loop inner; update emp1 set stars= va where empi=ar; exit when e_cursor%NOTFOUND; end loop outer; close e_cursor; end;

declare TYPE emp_table_type is TABLE OF Emp1%ROWTYPE INDEX BY BINARY_INTEGER; My_emp_table emp_table_type; V_count NUMBER(3) :=103; BEGIN FOR I IN 100 .. v_count LOOP

SELECT * INTO my_emp_table(i) FROM emp1 where empi=i; end LOOP; For I in my_emp_table.FIRST .. my_emp_table.LAST LOOP DBMS_OUTPUT.PUT_LINE(my_emp_table(i).sal); End loop; End; update emp1 set empi = 103 where empi = 143; insert into countries values(‘UK’, ‘UNITED KINGDOM’,12); insert into countries values (‘USA’,’UNITED STATES’,15); insert into countries values (‘IND’,’INDIA’,11); insert into countries values (‘AUS’, ‘AUSTRALIA’,17); DEFINE CI= ind; Declare Count_record countries%ROWTYPE; Cis Varchar2(30) := ‘&CI’; Begin Select * into count_record from countries where country_id=UPPER(CIS); DBMS_OUTPUT.PUT_LINE(‘COUNTRY ID IS ’ || Count_record.country_id || ‘ ,COUNTRY NAME IS ’ || count_record.country_name || ‘ AND REGION ID IS ’ || count_record.region_id); Commit; End; Declare type ddept_table is TABLE of Emp1.stars%TYPE INDEX BY BINARY_INTEGER; Dept_table ddept_table; Begin For I in 100.. 103 LOOP Select stars into dept_table(i) from emp1 where empi=i; End loop; For I in dept_table.first .. dept_table.last Loop DBMS_OUTPUT.PUT_LINE(dept_table(i)); End loop; End;

insert into departments values(70, ‘public relations’ , ‘UK’, 131, 2300); insert into departments values(50, ‘ART’ , ‘ABN’); update departments set name = 'executive' where num =90; Declare Type dept_table is table of departments%ROWTYPE index by binary_integer; my_dept_table dept_table; v_count number :=7; v_deptno departments.num%TYPE; begin FOR i in 1..v_count LOOP If i=1 then V_deptno:=10; Elsif i=2 then V_deptno:=20; Elsif i=3 then V_deptno:=50; Elsif i=4 then V_deptno:=60; Elsif i=5 then V_deptno:=80; Elsif i=6 then V_deptno:=90; Elsif i=7 then V_deptno:=110; End if; Select * into my_dept_table(i) from departments where num=v_deptno; End loop; DBMS_OUTPUT.PUT_LINE (‘my_dept_table.COUNT is ’ || my_dept_table.COUNT); --FOR i in 1..my_dept_table.COUNT --any command the upper or lower wil do For I in my_dept_table.first .. my_dept_table.last LOOP DBMS_OUTPUT.PUT_LINE(‘ DEPT NO IS ‘ || my_dept_table(i).num|| ‘ DEPARTMENT NAME IS ’ || my_dept_table(i).name || ‘ AND LOC IS ‘ || my_dept_table(i).loc);

End loop; Commit; End;

Define x = 5; Declare Cursor sal_cursor is Select distinct sal from emp order by sal desc; /*distinct keyword is used so that if two users have same sal they wont be repeated*/ Inter number; --My_sal sal_cursor; Begin Delete from top_dogs; Open sal_cursor; fetch sal_cursor into inter; While sal_cursor%ROWCOUNT <= &x and sal_cursor%FOUND loop fetch sal_cursor into inter; insert into top_dogs values(inter); end loop; close sal_cursor; end;

select * from top_dogs; define x =10; declare cursor my_dept is select name,sal,man from departments where num=&x; sala number; nama varchar2(50); mana number; begin open my_dept; fetch my_dept into nama, sala, mana; while my_dept%FOUND LOOP fetch my_dept into nama, sala, mana ;

if (sala<5000 AND (mana=101 OR mana =124)) THEN DBMS_OUTPUT.PUT_LINE(‘ ‘ ||nama || ‘ DUE for a raise’); Else DBMS_OUTPUT.PUT_LINE(nama || ‘ NOT DUE for a raise’); End if; fetch my_dept into nama, sala, mana ; End loop; End;

Insert into employees(name,job,hiredate,salary,dept_id,emp_id) Values(&a,&b,&c,&d,&e,&f); ALL ARE WORKING(1st is my logic) Declare Cursor my_dept is Select * from departments where num<100; Abhi my_dept%ROWTYPE; Cursor my_emp(tdep number) is Select * from employees where dept_id= tdep and emp_id<120; ghar my_emp%ROWTYPE; separator varchar2(80); Begin Separator:=’ ‘; Open my_dept; LOOP Fetch my_dept into abhi; EXIT WHEN my_dept%NOTFOUND; DBMS_OUTPUT.PUT_LINE(‘DEPARTMENT NAME IS ‘ || abhi.name || ‘ DEPARTMENT ID IS ’ || abhi.num); DBMS_OUTPUT.PUT_LINE(separator); Open my_emp(abhi.num); LOOP Fetch my_emp into ghar; EXIT WHEN my_emp%NOTFOUND; DBMS_OUTPUT.PUT_LINE(‘NAME IS ‘ || ghar.name ||’ JOB IS ‘ ||ghar.job ||’ HIREDATE IS ‘ || ghar.hiredate ||’ SALARY IS ‘ || ghar.salary); END LOOP; Close my_emp; END LOOP; Close my_dept; END;

Declare Cursor my_dept is Select num from departments where num<100; Abh number; Ab number; Cursor my_emp(tdep number) is Select name from employees where dept_id= tdep and emp_id<120; Emp_rec my_emp%ROWTYPE; Begin Open my_dept; LOOP Fetch my_dept into abh; EXIT WHEN my_dept%NOTFOUND; Ab:=abh; Open my_emp(ab); LOOP Fetch my_emp into emp_rec; EXIT when my_emp%NOTFOUND; DBMS_OUTPUT.PUT_LINE(‘NAME IS ‘ || emp_rec.name); End loop; Close my_emp; End loop; Close my_dept; End; Declare Cursor dept_cur is Select num from departments where num<100; Cursor emp_cursor(p_deptno number) is Select * from employees where dept_id=p_deptno and emp_id<120; Begin For dept_rec in dept_cur loop Dbms_output.put_line(‘DEPARTMENT NUMBER: ‘ || dept_rec.num); For emp_rec in emp_cursor (dept_rec.num) loop Dbms_output.put_line(emp_rec.name|| ‘ |’||emp_rec.job); End loop; End loop; End; /

WORKING IN !ST ATTEMPT declare va varchar2(100) := Null; cn number; cursor my_emp is select * from employees FOR UPDATE OF stars NOWAIT; emp_rec my_emp%ROWTYPE; begin open my_emp; LOOP Fetch my_emp into emp_rec; EXIT when my_emp%NOTFOUND; cn:= ROUND(NVL(emp_rec.salary,0)/1000); --where current of cursor; va:=NULL; for I in 1..cn LOOP va:=va||’*’; end loop; update employees set stars = va where current of my_emp; end loop; close my_emp; end; declare e_invalid_dept exception; erm varchar2(255); erc number; begin update departments set name = ‘abhi’ where num = 230; if SQL%NOTFOUND then raise e_invalid_dept; end if; commit; exception when e_invalid_dept then erc:=sqlcode; erm:=sqlerrm; dbms_output.put_line(erc ||’ ‘ ||erm); DBMS_OUTPUT.PUT_LINE('No such dept id'); end;

Declare e exception; pragma exception_init(e,-2292); begin delete from try where i = 20; if SQL%NOTFOUND then raise e; end if; commit; exception when e then dbms_output.put_line('good work'); end; Declare erm varchar2(255); erc number; e exception; begin select * into erc from try; if SQL%NOTFOUND then raise e; end if; commit; exception when e then dbms_output.put_line('good work'); when no_data_found then dbms_output.put_line(‘bad work’); when others then erc:=sqlcode; erm:=sqlerrm; dbms_output.put_line(erc ||’ ‘ ||erm); end; define p=17000; declare erm varchar2(255); more_rec exception; no_rec exception; sal number;

nam varchar2(50); begin select name,salary into nam, sal from employees where salary = &p --select name,salary into nam, sal from employees where salary = &p/0; /*the above state statement will create divide by zero error which wil lead to storage of some other error in messages table*/ INSERT INTO MESSAGES VALUES (to_char(NAM) || ‘ ‘ || to_char(SAL)); exception WHEN NO_DATA_FOUND THEN INSERT INTO MESSAGES VALUES (‘no employee exist’); WHEN TOO_MANY_ROWS THEN INSERT INTO MESSAGES VALUES (‘MORE THAN 1 EMPLOYEE HAVING SALARY ’ || to_char(SAL)); WHEN OTHERS THEN INSERT INTO MESSAGES VALUES(‘SOME OTHER ERROR’); END;

CREATE OR REPLACE PROCEDURE query_emp (p_id IN employees.emp_id%TYPE, p_name OUT employees.name%TYPE, p_salary OUT employees.salary%TYPE) is begin select name, salary into p_name, p_salary from employees where emp_id = p_id; END query_emp; Variable g_name varchar2(25); Variable g_sal number; Execute query_emp(45, :g_name, :g_sal) Print g_name; Print g_sal CREATE OR REPLACE PROCEDURE format_phone (p_phone_no IN OUT VARCHAR2) is begin p_phone_no := '(' || SUBSTR(p_phone_no,1,3) || ')' || SUBSTR(p_phone_no,4,3) || '-' || SUBSTR(p_phone_no,7); END format_phone;

VARIABLE g_phone_no VARCHAR2(15) ; begin :g_phone_no := '8006330575'; end; / execute format_phone(:g_phone_no); print g_phone_no; CREATE OR REPLACE PROCEDURE add_dept (p_name IN departments.name%TYPE DEFAULT 'University', P_loc IN departments.loc %TYPE DEFAULT ‘unknown’) as Begin INSERT INTO departments(num, name,loc,man,sal) VALUES (190, p_name, p_loc,151,23765); END add_dept; BEGIN add_dept; add_dept ('TRAINING', ‘SA’); add_dept ( p_loc => ‘NZ’, p_name =>'EDUCATION'); add_dept ( p_loc =>’ENG’) ; end; / SELECT department_id, department_name, location_id FROM departments; Create or replace procedure raise_sal(id IN number) is Begin Update employees set salary = salary*1.1 where emp_id=id; DBMS_output.put_line(‘hi I m in process_emps’); End; / CREATE OR REPLACE PROCEDURE process_emps is CURSOR emp_cursor IS SELECT emp_id from employees; Begin FOR emp_rec IN emp_cursor LOOP raise_sal(emp_rec.emp_id); DBMS_output.put_line(‘hi I m in process_emps’);

END LOOP; COMMIT; END process_emps; Sub prog cannot handle the exception but main prog can To see results delete data from table t4 Main prog CREATE OR REPLACE PROCEDURE handle_check is CURSOR emp_cursor IS SELECT emp_id from employees; Begin FOR emp_rec IN emp_cursor LOOP raise_check; DBMS_output.put_line(‘hi I m in process_emps’); End loop; Exception When no_data_found then DBMS_output.put_line(‘hi exception of process raise check handled successfully’); End; Sub prog Create or replace procedure raise_check is X number; Y number; Begin select a,b into x,y from t4; DBMS_output.put_line(‘hi I m in raise check to display no data found exception’); End; Sub prog cannot handle the exception but main prog can with another sub program being called Main prog To see results delete data from table t4 CREATE OR REPLACE PROCEDURE handle_check2 is Begin raise_check; DBMS_output.put_line(‘hi I m in process_emps’); raise_check1; Exception When no_data_found then DBMS_output.put_line(‘hi exception of process raise check handled successfully’); End;

Another subprogram Create or replace procedure raise_check1 is X number:=43; Y number:=32; Begin Insert into t4 values( x,y ); DBMS_output.put_line(‘hi I m in raise check1 for insertion of data’); End; Sub-prog with exception handler with main prog also having exception handler(actually not reqd in main prog) To see results delete data from table t4 in this case after subprog handles its exception main prog runs normally; Sub prog: Create or replace procedure raise_check_with_handler is X number; Y number; Begin select a,b into x,y from t4; DBMS_output.put_line(‘hi I m in raise check to display no data found exception’); Exception When no_data_found then DBMS_output.put_line(‘hi exception of process raise check handled by itself successfully’); End; Main program CREATE OR REPLACE PROCEDURE handle_check_5 is Begin raise_check_with_handler ; DBMS_output.put_line(‘hi I m in handle check 5’); raise_check1; Exception When no_data_found then DBMS_output.put_line(‘ exception of process raise check handled successfully’); End;

Sub-prog and main both without exception handler) Sub prog: Create or replace procedure raise_check_without is X number; Y number; Begin select a,b into x,y from t4; DBMS_output.put_line(‘hi I m in raise check to display no data found exception’); End; Main program CREATE OR REPLACE PROCEDURE nohandle_check_ is Begin raise_check_without ; DBMS_output.put_line(‘hi I m in no handle check ’); raise_check1; End; These procedures will be crated but wont be executed if table t4 have no data Create or replace procedure del_t4(X in number) is Begin delete from t4 where a = x; if sql%NOTFOUND then raise_application_error(-20203, ‘No jobs deleted’); end if; End;

CREATE OR REPLACE FUNCTION get_sal (p_id IN employees.emp_id%TYPE) RETURN NUMBER as v_salary employees.salary%TYPE :=0; begin SELECT salary INTO v_salary FROM employees WHERE emp_id = p_id; RETURN v_salary; END get_sal;

variable p number; execute :p :=get_sal(23); print p; create or replace function tax(sal in number) return number is begin return(sal*0.124); end; select name, emp_id, tax(salary) from employees where dept_id=30; the function calls wont be completed CREATE OR REPLACE FUNCTION dml_call_sql (p_sal NUMBER) RETURN NUMBER IS begin INSERT INTO employees(emp_id, name, hiredate, job, salary) VALUES(1, 'employee 1', SYSDATE, 'SA_MAN', 1000); RETURN (p_sal + 100); End;

UPDATE employees SET salary = dml_call_sql(2000) WHERE emp_id = 23; CREATE OR REPLACE FUNCTION query_call_sql(a NUMBER) RETURN NUMBER IS s NUMBER; BEGIN SELECT salary INTO s FROM employees WHERE emp_id = 23; RETURN (s + a); END; / UPDATE employees SET salary = query_call_sql(100) WHERE emp_id = 23;

Create or replace function annual_comp (psal in number, pcomm in number) return number is begin return(NVL(psal,0)*12+ NVL(pcomm,0)*psal*12); end; select emp_id,name,annual_comp(salary,.2)”Annual Compensation” from employees where dept_id =30; create or replace procedure new_emp (aNAME VARCHAR2, aJOB VARCHAR2, aHIREDATE DATE, aSALARY NUMBER, aDEPT_ID NUMBER, aEMP_ID NUMBER, aSTARS VARCHAR2 ) is Begin If valid_dept(adept_id) then Insert into employees values(aNAME, aJOB, aHIREDATE, aSALARY, aDEPT_ID, aEMP_ID, aSTARS); else DBMS_OUTPUT.PUT_LINE(‘NO SUCH DEPT SO NO INSERTION’); End if; End; Create or replace function valid_dept(depno number) Return Boolean Is Dummy varchar2(2); begin Select ‘a’ into dummy from departments where num=depno; Return TRUE; Exception When no_data_found then Return FALSE; End; VALID DEPT No execute new_emp('Abhish',’ASE-T’,SYSDATE,15096,30,1432,’***************’);

INVALID DEPT No execute new_emp('Abhi',’ASE-T’,SYSDATE,15096,3000,15432,’***************’); using user_objects select object_name, object_type from user_objects where object_type in (‘PROCEDURE’, 'FUNCTION ') order by object_name; using user_source; select text from user_source where name = 'NOHANDLE_CHECK_' order by line; SET ECHO OFF HEADING OFF FEEDBACK OFF VERIFY OFF COLUMN LINE NOPRINT SET PAGESIZE 0 SELECT ‘CREATE OR REPLACE’,0 line From DUAL UNION select text, line From USER_SOURCE Where name in(‘NEW_EMP’, ‘VALID_DEPTNO’) ORDER BY line; SELECT ‘/’ FROM DUAL; SET PAGESIZE 24 COLUMN LINE CLEAR SET ECHO ON HEADING ON FEEDBACK ON VERIFY ON PACKAGES Create or replace PACKAGE sal_package is g_sal number := 10000; Procedure reset_sal(p_sal in number); End sal_package; Create or replace PACKAGE BODY sal_package is Function validate_sal(p_sal in number) Return BOOLEAN Is V_max_sal number; Begin Select MAX(salary) into v_max_sal from Employees; If p_sal> v_max_sal then

Return(FALSE); Else Return (TRUE); End if; End validate_sal; Procedure reset_sal (p_sal in number) is Begin DBMS_OUTPUT.PUT_LINE(‘old global variable ‘||g_sal); If validate_sal(p_sal) and p_sal Execute sal_package.reset_sal(9040); old global variable 9070

new global variable 9040 PL/SQL procedure successfully completed.

create or replace PACKAGE emp_pack is Procedure new_emp (aNAME VARCHAR2, aJOB VARCHAR2, aHIREDATE DATE, aSALARY NUMBER, aDEPT_ID NUMBER, aEMP_ID NUMBER, aSTARS VARCHAR2 ); end emp_pack; create or replace PACKAGE BODY emp_pack is function valid_dept (depno number) Return Boolean Is Dummy varchar2(2); begin Select 'a' into dummy from departments where num=depno; Return TRUE; Exception When no_data_found then Return FALSE; End; Procedure new_emp (aNAME VARCHAR2, aJOB VARCHAR2, aHIREDATE DATE, aSALARY NUMBER, aDEPT_ID NUMBER, aEMP_ID NUMBER, aSTARS VARCHAR2 ) is Begin If valid_dept(adept_id) then Insert into employees values(aNAME, aJOB, aHIREDATE, aSALARY, aDEPT_ID, aEMP_ID, aSTARS);

else DBMS_OUTPUT.PUT_LINE('NO SUCH DEPT SO NO INSERTION'); End if; End new_emp; End emp_pack; Create or replace Package chk_pack is Procedure chk_hiredate( entdate IN date); Procedure chk_dept_em(de in number, em in number); End chk_pack; / create or replace Package Body chk_pack is Procedure chk_hiredate( entdate IN date) Is Dtmin date := add_months(sysdate,-50*12); Dtmax date := add_months(sysdate,3); Begin If TRUNC(entdate) not BETWEEN dtmin AND dtmax OR TRUNC(entdate) = NULL Then RAISE_APPLICATION_ERROR(-20000,’ INVALID DATE ENTERED’); Else DBMS_OUTPUT.PUT_LINE(‘correct date’); End if; End chk_hiredate; Procedure chk_dept_em(de in number, em in number) is Jo varchar2(50); Begin Select job into jo from employees where emp_id=em and dept_id=de; DBMS_OUTPUT.PUT_LINE(jo); EXCEPTION When no_data_found then RAISE_APPLICATION_ERROR(-20000,’NOT A VALID COMBINATION’); End chk_dept_em; End chk_pack; Valid execute chk_pack.chk_dept_em(30,1232); invalid execute chk_pack.chk_dept_em(1232,30);

create or replace PACKAGE pack_cur is cursor c1 is select emp_id from employees order by emp_id desc; procedure proc1_3rows; procedure proc4_6rows; END pack_cur; / create or replace PACKAGE BODY pack_cur is abhi number; procedure proc1_3rows is begin open c1; for I in 1..3 loop fetch c1 into abhi; DBMS_OUTPUT.PUT_LINE(‘Emp ID ‘ || I ||’ is ‘ ||abhi); End LOOP; End proc1_3rows; procedure proc4_6rows is begin for I in 4..6 loop fetch c1 into abhi; DBMS_OUTPUT.PUT_LINE(‘Emp ID ‘ || I || ’ is ‘ || abhi); End LOOP; Close c1; End proc4_6rows; End pack_cur; execute pack_cur. Proc1_3rows execute pack_cur. proc4_6rows;

create or replace PACKAGE emp_package is type emp_table is TABLE OF employees%ROWTYPE INDEX BY BINARY_INTEGER; Cursor a is Select * from employees; Procedure read_table(emp_rec out emp_table); End emp_package; create or replace PACKAGE BODY emp_package is Procedure read_table(emp_rec out emp_table) is i BINARY_INTEGER:=0;

Begin For em in a LOOP Emp_rec(i):= em; i:=i+1; end LOOP; end read_table; end emp_package;

declare dummy number; v_emp_table emp_package.emp_table; begin select count(*) into dummy from employees; for i in 1 .. dummy LOOP emp_package.read_table (v_emp_table); DBMS_OUTPUT.PUT_LINE(‘Emp name ‘ || i ||’ is ‘ || v_emp_table (i).name); End LOOP; End;

Declare v_emp_table testpe.emp_package.emp_table; begin testpe.emp_package.read_table (v_emp_table); DBMS_OUTPUT.PUT_LINE(v_emp_table (4).emp_id); End; Create or replace PACKAGE CHECK_PACK is Cursor a is Select * from employees; Procedure chk_dept_job( num in employees.dept_id%type, Jo in employees.job%type); End CHECK_PACK; Create or replace PACKAGE BODY CHECK_PACK is Procedure chk_dept_job ( num in employees.dept_id%type, Jo in employees.job%type) is I number:=0; --initialisation of I is must other wise the cond I !=1 will never be validated Begin For emp_rec in a LOOP If emp_rec.dept_id = num AND emp_rec.job=jo then

DBMS_OUTPUT.PUT_LINE(‘ Valid combination’); I:=1; EXIT when i=1; End if; End loop; If I !=1 then RAISE_APPLICATION_ERROR(-20500, ‘ NOT a VALID COMBINATION’); End if; End chk_dept_job; End CHECK_PACK; In the soln given in pdf as soon as the package body is created one time procedure is run and the values of all dept_id and job for all employees are populated in the table Later on execution we can use table attributes like LAST and FIRST….. DYNAMIC SQL Create or replace procedure delete_all_rows(p_table_name in varchar2,num_del out number) is cursor_name INTEGER; Begin Cursor_name:= DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE( cursor_name,’DELETE FROM ’ || p_table_name, DBMS_SQL.NATIVE ); --the blank space after DELETE FROM is very important Num_del := DBMS_SQL.EXECUTE(cursor_name); End; Variable deleted number; execute delete_all_rows('T5',:deleted); print deleted; Create or replace procedure delete_rows(p_table_name in varchar2,num_del out number) is begin EXECUTE IMMEDIATE(’DELETE FROM ’ || p_table_name); num_del:=SQL%ROWCOUNT; end; Variable deleted number; execute delete_rows('T5',:deleted); print deleted;

execute anystring('insert into t5 values(''asa'',10)'); DBMS_DDL.ANALYZE_OBJECT(‘TABLE’,’TESTPE’,’EMPLOYEES’,’COMPUTE’); DBMS_DDL.ALTER_COMPILE(‘PROCEDURE’,’TESTPE’,’ raise_check_without’);

DBMS_JOB Variable jobno NUMBER; Begin DBMS_JOB.SUBMIT( Job=>:jobno, What =>’ chk_pack.chk_dept_em(30,1232);’, Next_date=> TRUNC(SYSDATE+1/24), Interval => ‘TRUNC(SYSDATE+1)’ ); commit; end; / PRINT jobno SQL> PRINT jobno; JOBNO ---------21 SQL> execute dbms_job.run(21); ASE-T PL/SQL procedure successfully completed. Begin DBMS_JOB.CHANGE(21,NULL,TRUNC(SYSDATE+1)+7/48,’SYSDATE+4/24’); End; select job,log_user,next_date,next_sec,broken,what from DBA_JOBS;

UTL_FILE Create or replace procedure sal_status (p_filedir in varchar2,p_filename in varchar2) is v_filehandle UTL_FILE.FILE_TYPE; cursor emp_info is select name,salary, dept_id from employees order by dept_id; v_newdeptno number; v_olddeptno number:=0; BEGIN V_filehandle:=UTL_FILE.FOPEN(p_filedir, p_filename,’w’); UTL_FILE.PUTF(v_filehandle,’SALARY REPORT, GENERATED ON %s\n’,SYSDATE); UTL_FILE.NEW_LINE(v_filehandle); For v_emp_rec in emp_info LOOP v_newdeptno := v_emp_rec.dept_id; If V_newdeptno <> v_olddeptno THEN UTL_FILE.PUTF(v_filehandle,’ DEPARTMENT %s \n’, v_emp_rec.dept_id); End if; UTL_FILE.PUTF(v_filehandle,’ EMPLOYEE %s earns, %s\n’, v_emp_rec.name, v_emp_rec.salary); V_newdeptno := v_olddeptno; END LOOP; UTL_FILE.PUT_LINE(v_filehandle,’***END OF REPORT’); UTL_FILE.FCLOSE(v_filehandle); EXCEPTION When UTL_FILE.INVALID_FILEHANDLE then RAISE_APPLICATION_ERROR(-20001,’Invalid FILE’); When UTL_FILE.WRITE_ERROR then RAISE_APPLICATION_ERROR(-20002,’UNABLE TO WRITE TO FILE’); END sal_status; UTL_HTTP --WORKS SELECT UTL_HTTP.REQUEST(‘http://www.google.co.in’) from dual; DECLARE X UTL_HTTP.HTML_PIECES; Begin X:= UTL_HTTP.REQUEST_PIECES(‘http://www.google.co.in’,100); DBMS_OUTPUT.PUT_LINE(x.COUNT || ‘ pieces were retrieved ‘); DBMS_OUTPUT.PUT_LINE(‘ with total length ‘);

If x.COUNT <1 then DBMS_OUTPUT.PUT_LINE(‘0’); Else DBMS_OUTPUT.PUT_LINE((2000*(x.COUNT-1))+ LENGTH(x(x.COUNT))); End if; End; Create or replace procedure DROP_TABLE (tab_name in varchar2) is Cur varchar2(100); Dyn_err varchar2(255); Begin Cur:=DBMS_SQL.open_cursor; DBMS_SQL.PARSE(cur,‘DROP TABLE ‘|| tab_name,DBMS_SQL.NATIVE); DBMS_SQL.CLOSE_CURSOR(cur); Exception When others then Dyn_err:=sqlerrm; DBMS_SQL.CLOSE_CURSOR(cur); DBMS_OUTPUT.PUT_LINE(‘WELCOME’); --RAISE_APPLICATION_ERROR(-20600,dyn_err); End; Create or replace procedure DROP_TABLE2 (tab_name in varchar2) is Begin EXECUTE IMMEDIATE ‘DROP TABLE ‘|| tab_name; Exception When others then DBMS_OUTPUT.PUT_LINE(‘WELCOME’); End;

Create or replace procedure analyse_object (p_obj_type in varchar2, p_obj_name in varchar2) is BEGIN --NOTE THE SPELLING OF ANALYZE BELOW DBMS_DDL.ANALYZE_OBJECT(p_obj_type,USER, UPPER(p_obj_name),’COMPUTE’); END;

execute analyse_object('TABLE','EMPLOYEES');

select LAST_ANALYZED FROM USER_TABLES WHERE TABLE_NAME=’EMPLOYEES’; LAST_ANAL --------23-JAN-07 variable jobno number; Create or replace procedure schedule_analyse is ab number; BEGIN DBMS_JOB.SUBMIT( Job =>ab, WHAT=> ‘DBMS_DDL.ANALYZE_OBJECT(‘’TABLE’’,USER, ‘’DEPARTMENTS’’,’’COMPUTE’’);’, NEXT_DATE=>SYSDATE+1/288); DBMS_OUTPUT.PUT_LINE(ab); Commit; END;

Execute DBMS_JOB.SUBMIT(:jobno,’ANALYSE_OBJECT(‘’TABLE’’,’’DEPARTMENTS’’);’, SYSDATE+1/1440) TRIGGERS Create or replace trigger secure_emp after insert or update on employees FOR EACH ROW Begin If (to_char(sysdate,’DY’) in (‘SAT’,’SUN’)) OR (to_char(sysdate, ‘HH24:MI’) not between ’08:00’ and ’18:00’) THEN RAISE_APPLICATION_ERROR(-20500,’You may insert employee only during business hours’ || :NEW.SALARY|| SQL%ROWCOUNT); End if; End; Create or replace trigger secure_emp after insert or update on employees Begin If (to_char(sysdate,’DY’) in (‘SAT’,’SUN’)) OR

(to_char(sysdate, ‘HH24:MI’) not between ’08:00’ and ’18:00’) THEN RAISE_APPLICATION_ERROR(-20500,’You may insert employee only during business hours’); End if; End;

Create or replace trigger secure_emp Before insert or update or delete on employees Begin If(to_char(sysdate,’DY’) in (‘SAT’,’SUN’)) or (to_char(sysdate, ‘HH24:MI’) not between ’08:00’ and ’09:00’) THEN If DELETING then RAISE_APPLICATION_ERROR(-20502,’You may delete employee only during business hours’); ELSIf INSERTING then RAISE_APPLICATION_ERROR(-20500,’You may insert employee only during business hours’); ELSIf UPDATING(‘SALARY’) then RAISE_APPLICATION_ERROR(-20503,’You may update salary of employee only during business hours’); ELSE RAISE_APPLICATION_ERROR(-20504,’You may update employee table only during business hours’); End if; END if; End; --only employees in AD_VP AND AD_PRES can earn more than 15000 Create or replace trigger restrict_salary Before insert or update of salary on employees For each row Begin If not (:NEW.job in (‘AD_PRES’,’AD_VP’)) AND :NEW.salary>15000 Then Raise_APPLICATION_ERROR(-20202,’EMPLOYEE CANT HEARN THIS AMOUNT’); End if; End;

CREATE OR REPLACE PACKAGE var_pack is --these functions are used to return the --values of package variables FUNCTION g_del RETURN NUMBER; FUNCTION g_ins RETURN NUMBER; FUNCTION g_upd RETURN NUMBER; FUNCTION g_up_sal RETURN NUMBER; --these procedures are used to modify the --values of the package variables PROCEDURE set_g_del (p_val IN NUMBER); PROCEDURE set_g_ins (p_val IN NUMBER); PROCEDURE set_g_upd (p_val IN NUMBER); PROCEDURE set_g_up_sal (p_val IN NUMBER); END var_pack;

CREATE OR REPLACE PACKAGE BODY var_pack IS gv_del NUMBER := 0; gv_ins NUMBER := 0; gv_upd NUMBER := 0; gv_up_sal NUMBER := 0; FUNCTION g_del RETURN NUMBER IS BEGIN RETURN gv_del; END; FUNCTION g_ins RETURN NUMBER IS BEGIN RETURN gv_ins; END; FUNCTION g_upd RETURN NUMBER IS BEGIN RETURN gv_upd; END; FUNCTION g_up_sal RETURN NUMBER IS BEGIN RETURN gv_up_sal; END; PROCEDURE set_g_del (p_val IN NUMBER) IS BEGIN IF p_val = 0 THEN gv_del := p_val; ELSE gv_del := gv_del +1; END IF; END set_g_del;

PROCEDURE set_g_ins (p_val IN NUMBER) IS BEGIN IF p_val = 0 THEN gv_ins := p_val; ELSE gv_ins := gv_ins +1; END IF; END set_g_ins; PROCEDURE set_g_upd (p_val IN NUMBER) IS BEGIN IF p_val = 0 THEN gv_upd := p_val; ELSE gv_upd := gv_upd +1; END IF; END set_g_upd; PROCEDURE set_g_up_sal (p_val IN NUMBER) IS BEGIN IF p_val = 0 THEN gv_up_sal := p_val; ELSE gv_up_sal := gv_up_sal +1; END IF; END set_g_up_sal; END var_pack;

CREATE OR REPLACE PROCEDURE audit_emp IS V_del NUMBER:= var_pack.g_del; V_ins NUMBER:= var_pack.g_ins; V_upd NUMBER:= var_pack.g_upd; V_up_sal NUMBER:= var_pack.g_up_sal; BEGIN If v_del + v_ins + v_upd != 0 THEN UPDATE audit_table SET del = del + v_del, ins = ins + v_ins, upd = upd + v_upd WHERE user_name=USER AND tablename='EMPLOYEES' AND columnname IS NULL; END IF; IF v_up_sal != 0 THEN UPDATE audit_table SET upd = upd + v_up_sal WHERE user_name=USER AND tablename='EMPLOYEES' AND columnname = 'SALARY'; END IF; --resetting global variables in package VAR_PACK var_pack.set_g_del (0); var_pack.set_g_ins (0); var_pack.set_g_upd (0); var_pack.set_g_up_sal (0);

END audit_emp; CREATE OR REPLACE TRIGGER audit_emp_trig AFTER UPDATE or INSERT or DELETE on EMPLOYEES FOR EACH ROW BEGIN IF DELETING THEN var_pack.set_g_del(1); ELSIF INSERTING THEN var_pack.set_g_ins(1); ELSIF UPDATING ('SALARY') THEN var_pack.set_g_up_sal(1); ELSE var_pack.set_g_upd(1); END IF; END audit_emp_trig; CREATE OR REPLACE TRIGGER audit_emp_tab AFTER UPDATE or INSERT or DELETE on employees BEGIN audit_emp; END audit_emp_tab; TESTRUN SQL> delete from employees where emp_id=8657; 1 row deleted. SQL> select * from audit_table; DEL INS UPD USER_NAME TABLENAME ---------- ---------- ---------- -------------------------------------------------- ---------------0 0 0 TESTPE EMPLOYEES 1 0 0 TESTPE EMPLOYEES SQL> commit; Commit complete. SQL> update employees set salary=45670 where emp_id=545; 1 row updated. SQL> select * from audit_table; DEL

INS

UPD USER_NAME

TABLENAME

---------- ---------- ---------- -------------------------------------------------- ---------------0 0 1 TESTPE EMPLOYEES 1 0 0 TESTPE EMPLOYEES SQL> update employees set dept_id=25 where emp_id=545; 1 row updated. SQL> select * from audit_table; DEL INS UPD USER_NAME TABLENAME ---------- ---------- ---------- -------------------------------------------------- ---------------0 0 1 TESTPE EMPLOYEES 1 0 1 TESTPE EMPLOYEES SQL> commit; Commit complete. SQL>

CREATE OR REPLACE TRIGGER derive_com BEFORE INSERT OR UPDATE OF salary ON employees FOR EACH ROW WHEN (NEW.job = 'AD_VP') BEGIN IF INSERTING THEN :NEW.com := 0; ELSIF :OLD.com IS NULL THEN :NEW.com := 0; ELSE :NEW.com := :OLD.com * (:NEW.salary/:OLD.salary); END IF; END;