PL/SQL Examples 1)/*DISPLAYING THE FIRST FIFTY EVEN NUMBERS BY USING PL/SQL BLOCK*/ declare i number; begin for i in 1..50 loop if mod(i,2)=0 then dbms_output.put_line('THE EVEN NUMBERS ARE'||i); end if; end loop; end; 2)/*DISPLAYING THE FIRST FIFTY ODD NUMBERS BY USING PL/SQL BLOCK*/ declare i number; begin for i in 1..50 loop if mod(i,2)<>0 then dbms_output.put_line('THE ODD NUMBERS ARE'||i); end if; end loop; 3)/*SCOPE AND VISIBILITY OF VARIABLES IN A PL/SQL PROGRAMME*/ declare one number:=10; two number:=20; three number:=30; begin declare four number:=40; five number:=50; begin dbms_output.put_line('THE LOCAL NUMBERS ARE'||' '||four||' '||five); end; dbms_output.put_line('THE PRIVATE NUMBERS ARE' ||one||' '||two||' '||three); end; end;
4)declare e emp_dup%rowtype; begin select * into e from emp_dup where empno=&eno; if e.job='MANAGER' then e.sal:=e.sal+1000; update emp_dup set sal=e.sal where empno=e.empno; dbms_output.put_line('the manager and his salary was now'||e.sal); elsif e.job='CLERK' then e.sal:=e.sal+300; update emp_dup set sal=e.sal where empno=e.empno; dbms_output.put_line('the clerk and his salary was now'||e.sal); else e.sal:=e.sal+500; update emp_dup set sal=e.sal where empno=e.empno; dbms_output.put_line('the others salary was now'||e.sal); end if; end; 4)/*COMPUTE A 10%BONUS FOR THE EMPLOYEE WITH THE EMPLOYEE_ID 7369 AND ASSIGN THE COMPUTED VALUE TO THE V_BONUS VARIABLE BY USING THE PL/SQL BLOCK*/ declare v_bonus number; begin select sal*0.10 into v_bonus from emp_dup where empno=7369; dbms_output.put_line('THE BONUS SALARY WAS'||v_bonus); end; 5)/*AN EXAMPLE OF USING A HOST VARIABLE IN A PL/SQL BLOCK*/ SQL> variable annual_sal number;
begin select (sal*12)+nvl(comm,0) into :annual_sal from emp_dup where empno=7369; end; SQL> print annual_sal; 6)/*PRINTING THE VALUE OF THE SALARY OF THE EMPLOYEE NUMBER 7369 BY USING THE BIND VARIABLE*/
SQL> variable salary number begin select sal into :salary from emp_dup where empno=7369; end;
4)declare e emp_dup%rowtype; begin select * into e from emp_dup where empno=&eno; if e.job='MANAGER' then e.sal:=e.sal+1000; update emp_dup set sal=e.sal where empno=e.empno; dbms_output.put_line('the manager and his salary was now'||e.sal); elsif e.job='CLERK' then e.sal:=e.sal+300; update emp_dup set sal=e.sal where empno=e.empno; dbms_output.put_line('the clerk and his salary was now'||e.sal); else e.sal:=e.sal+500; update emp_dup set sal=e.sal where empno=e.empno; dbms_output.put_line('the others salary was now'||e.sal); end if; end; 4)/*COMPUTE A 10%BONUS FOR THE EMPLOYEE WITH THE EMPLOYEE_ID 7369 AND ASSIGN THE COMPUTED VALUE TO THE V_BONUS VARIABLE BY USING THE PL/SQL BLOCK*/ declare v_bonus number; begin select sal*0.10 into v_bonus from emp_dup where empno=7369; dbms_output.put_line('THE BONUS SALARY WAS'||v_bonus); end; 5)/*AN EXAMPLE OF USING A HOST VARIABLE IN A PL/SQL BLOCK*/ SQL> variable annual_sal number;
begin select (sal*12)+nvl(comm,0) into :annual_sal from emp_dup where empno=7369; end; SQL> print annual_sal; 6)/*PRINTING THE VALUE OF THE SALARY OF THE EMPLOYEE NUMBER 7369 BY USING THE BIND VARIABLE*/
SQL> variable salary number begin select sal into :salary from emp_dup where empno=7369; end;
SQL> print salary; 7)/*COMPUTE THE MONTHLY SALARY,BASED UPON THE ANNUAL SALARY SUPPLIED BY THE USER*/
SQL> variable g_monthly_salary number; SQL> define p_annual_salary=50000; begin :g_monthly_salary:=&p_annual_salary/12; end; SQL> print :g_monthly_salary; (OR) SQL> variable g_monthly_salary number; SQL> define p_annual_salary=50000; declare v_sal number:=&p_annual_salary; begin :g_monthly_salary:=v_sal/12; end; SQL> print g_monthly_salary; 8)/*COMPUTE AND PRINT THE MONTHLY SALARY ON TO THE SCREEN USING THE ORACLE SUPPLIED PACKAGE PROCEDURE BASED UPON THE ANNUAL SALARY SUPPLIED BY THE USER*/
SQL> define p_annual_salary=50000; declare v_sal number:=&p_annual_salary; g_monthly_salary number; begin g_monthly_salary:=v_sal/12; dbms_output.put_line('THE MONTHLY SALARY WAS'||g_monthly_salary WAS'||g_monthly_salary); ); end;
9)/*CREATE AN ANONYMOUS BLOCK TO OUTPUT THE PHRASE "MY PL/SQL BLOCK WORKS" TO THE SCREEN*/
SQL> variable g_message varchar2(30) begin :g_message:='MY PL/SQL BLOCK WORKS'; end; SQL> print g_message; 10)/*CREATE A BLOCK THAT DECLARES TWO VARIABLES.ASSIGN THE VALUE OF THESE iSQL*PLUS HOST VARIABLES AND PRINT THE RESULTS OF THE PL/SQL VARIABLES TO THE SCREEN*/
SQL> variable g_char varchar2(30) SQL> variable g_num number begin :g_char:='42 IS THE ANSWER'; :g_num:=42; end; SQL> print g_char; SQL> print g_num; 11)/*CREATE AND EXECUTE A PL/SQL BLOCK THAT ACCEPTS TWO NUMBERS THROUGH iSQL*PLUS SUBSTITUTION VARIABLE a)USE THE DEFINE COMMAND TO PROVIDE THE TWO VALUES. DEFINE p_num1=2 DEFINE p_num2=4 b)THE FIRST NUMBER SHOULD BE DIVIDED BY THE SECOND NUMBER AND HAVE THE THE SECOND NUMBER ADDED TO THE RESULT*/
SQL> define p_num1 =2; SQL> define p_num2=4; SQL> variable tot_result number; SQL> variable result number; begin :result:=&p_num1/&p_num2;
:tot_result:=:result+&p_num2; end; SQL> print result; SQL> print tot_result; 12)/*CREATE AND EXECUTE A PL/SQL BLOCK THAT ACCEPTS TWO NUMBERS THROUGH iSQL*PLUS SUBSTITUTION VARIABLE a)USE THE DEFINE COMMAND TO PROVIDE THE TWO VALUES. DEFINE p_num1=2 DEFINE p_num2=4 b)THE FIRST NUMBER SHOULD BE DIVIDED BY THE SECOND NUMBER AND HAVE THE THE SECOND NUMBER ADDED TO THE RESULT.THE RESULT SHOULD BE STORED IN A PL/SQL VARIABLE AND PRINTED ON THE SCREEN*/
SQL> define p_num1 =2; SQL> define p_num2=4; declare result number; tot_result number; begin result:=&p_num1/&p_num2; tot_result:=result+&p_num2; dbms_output.put_line('THE TOTAL RESULT WAS'||' '||tot_result); end; 13)/*BUILD A PL/SQL BLOCK THAT COMPUTES THE TOTAL COMPENSATION FOR ONE YEAR a)THE ANNUAL SALARY AND THE ANNUAL BONUS PERCENTAGE VALUES ARE DEFINED USING THE DEFINE COMMAND b)PASS THE VALUES DEFINED IN THE ABOVE STEP TO THE PL/SQL BLOCK THROUGH iSQL*PLUS SUBSTITUTION VARIABLES.THE BONUS MUST BE CONVERTED FROM A WHOLE NUMBER TO A DECIMAL(FOR EXAMPLE,15 TO 0.15).IF THE SALARY IS NULL,SET IT TO ZERO BEFORE COMPUTING THE TOTAL COMPENSATION. EXECUTE THE PL/SQL BLOCK*/
SQL> variable g_total number SQL> define p_salary =50000 SQL> define p_bonus =10
begin :g_total:=nvl(&p_salary,0)+nvl(&p_salary,0)*(&p_bonus/100); end; SQL> variable g_total number 14)/*DISPLAYING THE DEPARTMENT NAME SALES THROUGH PL/SQL BLOCK*/ declare v_deptno number; v_dname varchar2(30); v_loc varchar(30); begin select deptno,dname,loc into v_deptno,v_dname,v_loc from dept where dname='SALES'; dbms_output.put_line('THE DEPARTMENT NAME SALES DETAILS WAS'||' '|| v_deptno||' '||v_dname||' '||v_loc); end; 15)/*DISPLAYING THE EMPLOYEE DETAILS OF 7369 THROUGH PL/SQL BLOCK*/ declare p emp_dup%rowtype; begin select * into p from emp_dup where empno=7369; dbms_output.put_line('THE EMPLOYEE DETAILS WAS'||' '||p.empno||' ' ||p.ename||' '||p.sal||' '||p.job||' '||p.deptno); end;
16)/*RETURN THE SUM OF THE SALARIES FOR ALL EMPLOYEES IN THE SPECIFIED DEPARTMENT*/ declare v_deptno number:=30; v_sum_sal number; begin select deptno,sum(sal) into v_deptno,v_sum_sal from emp_dup where deptno=v_deptno group by deptno; dbms_output.put_line('THE SUM OF SALARY OF DEPTNO30 WAS'||v_sum_sal); end;
17)/*ADDING THE NEW EMPLOYEE TO THE EMP_DUP TABLE THROUGH PL/SQL BLOCK*/
begin insert into emp_dup values(7778,'GVR','manager',66,sysdate, 6000,300,50); end; SQL> select * from emp_dup; 18)/*UPDATING THE EMPLOYEE SALARY WITH 800 THAN EARLIER AMOUNT AND WITH JOB AS manager*/
declare p emp_dup%rowtype; begin select * into p from emp_dup where job='manager'; update emp_dup set sal=p.sal+800 where job='manager'; end; 19)/*DELETE ROWS FOR THE DEPARTMENT 10 AND PRINT THE NUMBER OF ROWS DELETED USING SQL CURSOR ATTRIBUTES*/
SQL> variable rows_deleted varchar2(30) begin delete from emp_dup where deptno=10; :rows_deleted:=(sql%rowcount||' '||'rows_deleted'); end; 20)/*CREATE A PL/SQL BLOCK THAT SELECTS THE MAXIMUM DEPARTMENT NUMBER IN THE DEPARTMENTS TABLE IT IN AN iSQL*PLUS VARIABLE.PRINT THE RESULTS TO THE SCREEN*/
SQL> variable max_deptno number declare v_max_deptno number; begin select max(deptno) into v_max_deptno from dept; :max_deptno:=v_max_deptno; end; SQL> print max_deptno;
21)/*INSERT INTO A NEW DEPARTMENT INTO THE TABLE a)USE THE DEFINE COMMAND TO PROVIDE THE DEPARTMENT NAME.NAME THE DEPARTMENT EDUCATION b)PASS THE VALUE DEFINED FOR THE DEPARTMENT NAME TO THE PL/SQL BLOCK THROUGH A iSQL*PLUS SUBSTITUTION VARIABLE.ADD 10 TO THE MAX DEPARTMENT FOR THE NEW DEPARTMENT c)LEAVE THE LOCATION AS NULL*/
SQL> define department_name='EDUCATION'; declare v_deptno number:=50; v_dname varchar2(30):='&department_name'; begin insert into dept(deptno,dname) values(v_deptno,v_dname); end; SQL> select * from dept; 22)/*CREATE A PL/SQL BLOCK THAT UPDATES THE LOCATION FOR THE DEPARTMENT NUMBER 50 a)USE THE DEFINE COMMAND TO PROVIDE THE LOCATION.NAME THE LOCATION AS 'DELHI' b)PASS THE LOCATION THROUGH THE iSQL*PLUS SUBSTITUTION VARIABLE.
SQL> define location='NRT'; declare v_loc varchar2(30):='&location'; begin update dept set loc=v_loc where deptno=50; end; SQL> select * from dept; 23)/*CREATE A PL/SQL BLOCK THAT DELETES THE DEPARTMENT NUMBER 50 a)USE THE DEFINE COMMAND TO PROVIDE THE DEPARTMENT NUMBER b)PASS THE VALUE TO THE PL/SQL BLOCK THROUGH A iSQL*PLUS SUBSTITUTION VARIABLE.PRINT TO THE SCREEN THE NUMBER OF ROWS AFFECTED*/
SQL> define p_deptno=50; SQL> variable g_result varchar2(30)
declare v_deptno number:=&p_deptno; result varchar2(30); begin delete from dept where deptno=v_deptno; result:=(sql%rowcount||(row(s) deleted'); :g_result:=result; end; SQL> print g_result; SQL> select * from dept; 24)/*CREATE A PL/SQL BLOCK SUCH THAT IF THE EMPLOYEE NAME WAS 'SMITH' THEN SET THE MGR_ID TO 100*/
declare p emp_dup%rowtype; begin select * into p from emp_dup where ename='SMITH'; if p.ename ='SMITH' then p.mgr:=100; update emp_dup set mgr=p.mgr where ename='SMITH'; end if; end; SQL> select * from emp_dup where ename='SMITH'; 25)/*CREATE A PL/SQL BLOCK SUCH THAT IF THE EMPLOYEE NAME WAS 'SMITH' THEN SET THE DEPT_ID TO 80 AND JOB AS 'SA_REP'*/
declare r emp_dup%rowtype; begin select * into r from emp_dup where ename='SMITH'; if r.ename='SMITH' then r.job:='SA_REP'; r.deptno:=80; update emp_dup set job=r.job where ename='SMITH'; update emp_dup set deptno=r.deptno where ename='SMITH'; end if; end; SQL> select * from emp_dup where ename='SMITH';
26)/*CREATE A PL/SQL BLOCK SUCH THAT IF THE EMPLOYEE NAME IS 'SMITH' AND ALSO THE EMPLOYEE SALARY IS LESS THAN 4000 THEN SET THE DEPTNO TO 60*/
declare p emp_dup%rowtype; begin select * into p from emp_dup where ename='SMITH'; if p.ename ='SMITH' and p.sal>4000 then p.deptno:=60; update emp_dup set deptno=p.deptno where ename='SMITH'; end if; end; SQL> select * from emp_dup where ename='SMITH'; 27)/*IF THE DEPTNO WAS 60 OR THE HIREDATE IS GRATER THAN THE '01 DEC-1999' FOR THE EMPLOYEE NAME WAS SMITH THEN SET THE MGR_ID TO 101*/ declare p emp_dup%rowtype; begin select * into p from emp_dup where ename='SMITH'; if p.deptno=60 or p.hiredate>'01-dec-1999' then p.mgr:=101; update emp_dup set mgr=p.mgr where ename='SMITH'; end if; end;
SQL> select * from emp_dup where ename='SMITH'; 28)/*THE VALUE IS ACCEPTED FROM THE USER USING A SUBSTITUTION VARIABLE. BASED ON THE VALUE ENTERED BY THE USER,THE PL/SQL BLOCK EVALUTES THE VALUE OF THE V_APPRAISAL ON THE VALUE OF THE V_GRADE VALUE*/
declare v_grade varchar2(5):=('&p_grade'); v_apprisal varchar2(15); begin if v_grade='A' then v_apprisal:='EXCELLENT'; elsif v_grade='B' then v_apprisal:='VERY GOOD'; elsif v_grade='C' then v_apprisal:='GOOD';
elsif v_grade='D' then v_apprisal:='BAD'; end if; dbms_output.put_line('THE APPRISAL CATEGORY WAS'||v_apprisal); end;
29)/*INSERTING THE VALUES OF THE DEPARTMENT BY USING THE BASIC LOOP*/ declare v_deptid number; v_loc varchar2(15):='NRT'; v_dname varchar2(15):='CREDIT'; v_counter number:=1; begin select max(deptno) into v_deptid from dept; loop insert into dept(deptno,dname,loc) values((v_deptid+v_counter),v_dname,v_loc); v_counter:=v_counter+1; exit when v_counter>3; end loop; end;
SQL>SELECT * FROM DEPT; 30)/*INSERTING THE VALUES OF THE EMPLOYEES BY USING THE WHILE LOOP*/ declare v_counter number:=1; v_ename varchar2(15):='GVREDDY'; v_job varchar2(15):='SRINU'; v_empno number; begin select max(empno) into v_empno from emp_dup; while v_counter<=3 loop insert into emp_dup(empno,ename,job) values((v_empno+v_counter), v_ename,v_job); v_counter:=v_counter+1; end loop; end;
SQL>SELECT * FROM EMP_DUP;
31)/*INSERTING THE VALUES OF THE DEPT TABLE USING THE FOR LOOP*/ declare v_deptno number; v_dname varchar2(15):='DEBIT'; v_loc varchar2(15):='GUNTUR'; begin select max(deptno) into v_deptno from dept; for i in 1..3 loop insert into dept(deptno,dname,loc) values((v_deptno+i),v_dname,v_loc); end loop; end;
SQL>SELECT * FROM DEPT; 32)/*a)CREATE THE MESSAGE TABLE WITH RESULTS AS ONE COLUMN.WRITE A PL/SQL BLOCK TO INSERT NUMBERS INTO THE MESSAGES TABLE b)INSERT THE NUMBERS 1 TO 10,EXCLUDING 6 AND 8 c)COMMIT BEFORE THE END OF THE BLOCK d)SELECT THE MESSAGES TABLE TO VERIFY THAT YOUR PL/SQL BLOCK WORKED*/
begin for i in 1..5 loop insert into messages values(i); end loop; insert into messages values(7); for i in 9..10 loop insert into messages values(i); end loop; commit; end; SQL> select * from messages;
CURSORS 1)/*RETRIEVING THE FIRST 8 RECORDS OF THE EMP_DUP TABLE BY USING THE CURSORS*/
declare cursor emp_cursor is select empno,ename from emp_dup; r emp_cursor%rowtype; begin open emp_cursor; loop fetch emp_cursor into r; dbms_output.put_line('THE EMPLOYEE DETAILS WAS'||r.empno||r.ename); exit when emp_cursor%rowcount>=8; end loop; close emp_cursor; end; 2)/*RETRIEVING THE DETAILS OF THE EMPLOYEES WHOSE DEPARTMENT WAS 20 USING THE CURSORS*/
declare cursor dept_cursor is select * from emp_dup where deptno=20; r emp_dup%rowtype; begin open dept_cursor; loop fetch dept_cursor into r; dbms_output.put_line('THE EMPLOYEE DETAILS FOR DEPT20 WAS'||r.empno||' ' ||r.ename||' '||r.job||' '||r.sal||r.deptno); exit when dept_cursor%rowcount>=5; end loop; close dept_cursor; end; 3)/*RETRIEVING THE FIRST 10 RECORD OF THE EMPLOYEE TABLE USING CURSORS*/
declare cursor emp_10 is select empno,ename from emp_dup; r emp_10%rowtype; begin open emp_10; loop fetch emp_10 into r;
dbms_output.put_line('THE EMPLOYEE DETAILS WAS'||r.empno||' '||r.ename); exit when emp_10%rowcount>=10; end loop; dbms_output.put_line('THE NO OF RECORDS DISPLAYED ARE'|| emp_10%rowcount); close emp_10; end; 4)/*RETRIEVING THE EMPLOYEE NUMBER AND NAME OF THE EMPLOYEE TABLE AND INSERTING INTO TEMP_LIST TABLE BY USING CURORS AND RECORDS*/
declare cursor temp_insert is select empno,ename from emp_dup; emp_record temp_insert%rowtype; begin open temp_insert; loop fetch temp_insert into emp_record; exit when temp_insert%notfound; insert into temp_list(empid,tname) values(emp_record.empno,emp_record.ename); end loop; close temp_insert; end; 5)/*RETRIEVING THE EMPLOYEE RECORDS FOR DEPARTMENT 20 USING CURSOR FOR LOOPS*/
declare cursor emp_cur is select deptno,ename from emp_dup where deptno=20; begin for emp_record in emp_cur loop dbms_output.put_line('THE EMPLOYEE DETAILS WAS'||emp_record.deptno|| ' '||emp_record.ename); exit when emp_cur%notfound; end loop; end; 6)/*RETRIEVING THE EMPLOYEE RECORDS FOR DEPARTMENT 20 USING CURSOR FOR LOOP SUBQUERY*/
begin for emp_rec in (select empno,ename,job,deptno from emp_dup where deptno=20) loop
dbms_output.put_line('THE EMPLOYEE DETAILS WAS'||emp_rec.empno||' ' ||emp_rec.ename||' '||emp_rec.job||' '||emp_rec.deptno); exit when sql%notfound; end loop; end; 7)/*RETRIEVING THE FIRST FIVE EMPLOYEES WITH A JOB HISTORY*/
declare cursor emp_job is select * from emp_dup; r emp_job%rowtype; begin open emp_job; loop fetch emp_job into r; dbms_output.put_line('EMPLOYEE #:'||r.empno||'held the job of' ||r.job||'from'||r.hiredate); exit when emp_job%rowcount>5; end loop; close emp_job; end; 8)/* a) Firstly create a top_dogs table with salary as column b) Use the define command to provide the value of n for displaying thetop earners(n) of the company c) In a loop use the isql*plus substitution parameter created and gatherthe salaries of the top n people from the employees table.there should be no duplication in the salaries.if the two employees earn the samesalary,the salary should be picked up only once d) Store the salaries in the top_dogs table*/
declare cursor emp_sal is select distinct sal from emp_dup order by sal desc; r emp_dup.sal%type; begin open emp_sal; loop fetch emp_sal into r; dbms_output.put_line('THE TOP SALARY WISE'||r); insert into top_dogs(salary) values(r); exit when emp_sal%rowcount>&order_sal; end loop; close emp_sal; end;
9)/*UPDATING THE SALARY FOR 10% THOSE SALARIES ARE LESS THAN 3000 BY USING "UPDATE OF" CLAUSE AND WHERE "CURRENT OF" IN THE CURSORS*/
declare cursor upd_curr is select e.empno,e.ename,e.job,e.sal, d.deptno,d.loc,d.dname from emp e,dept d where e.deptno=d.deptno and d.deptno=30 for update of sal NOWAIT; begin for emp_rec in upd_curr loop if emp_rec.sal<3000 then update emp set sal=emp_rec.sal*1.10 where current of upd_curr; end if; end loop; end; 10)/*FOR GETTING INTO THE DISPLAY OF THE DEPT AND EMP TABLES TOGETHERLY*/ declare cursor emp_dept is select d.deptno,d.dname,e.ename,e.job, e.hiredate,e.sal from emp e,dept_id d where e.deptno=d.deptno; begin for emp_record in emp_dept loop if emp_record.deptno <>30 then dbms_output.put_line('departmentnumber:'||emp_record.deptno|| 'department name'||emp_record.dname); end if; end loop; for emp_record in emp_dept loop if emp_record.deptno<>30 then dbms_output.put_line(emp_record.ename||emp_record.job||emp_record.hiredate ||emp_record.sal); end if; end loop; for emp_record in emp_dept loop if emp_record.deptno=30 then dbms_output.put_line('departmentnumber:'||emp_record.deptno|| 'department name'||emp_record.dname); end if; end loop;
for emp_record in emp_dept loop if emp_record.deptno=30 then dbms_output.put_line(emp_record.ename||emp_record.job||emp_record.hiredate ||emp_record.sal); end if; end loop; end; 11)/*ANOTHER TYPE OF THE SAME ABOVE PROBLEM*/ declare cursor emp_dept is select distinct d.deptno,d.dname,e.ename,e.job, e.hiredate,e.sal from emp e,dept_id d where e.deptno=d.deptno; a dept_id.deptno%type; b dept_id.dname%type; begin select d.deptno,d.dname into a,b from dept_id d where deptno =10; dbms_output.put_line('departmentnumber:'||a|| 'department name'||b); select d.deptno,d.dname into a,b from dept_id d where deptno =20; dbms_output.put_line('departmentnumber:'||a|| 'department name'||b); for emp_record in emp_dept loop if emp_record.deptno<>30 then dbms_output.put_line(emp_record.ename||emp_record.job||emp_record.hiredate ||emp_record.sal); end if; end loop; select d.deptno,d.dname into a,b from dept_id d where deptno =30; dbms_output.put_line('departmentnumber:'||a|| 'department name'||b); for emp_record in emp_dept loop if emp_record.deptno=30 then dbms_output.put_line(emp_record.ename||emp_record.job||emp_record.hiredate ||emp_record.sal); end if; end loop; end;
PROCEDURE 1)---------- PROCEDURE-------------------------------------------------------------------------create or replace procedure get_order_lines(order_number in number,output1 out varchar2) as cursor c2(header_id in number) is select line_number, substr(ordered_item,1,20) item,ordered_quantity,unit_selling_price from oe_order_lines_all oel where HEADER_ID = header_id; line_number number(10); ordered_item varchar2(25); ordered_qty number(10); unit_selling_price number(10); HEADER_ID NUMBER(10); begin SELECT HEADER_ID INTO HEADER_ID FROM OE_ORDEr_HEADERS_ALL WHERE ORDER_NUMBER = ORDER_NUMBER; DBMS_OUTPUT.PUT_LINE(HEADER_ID); open c2(HEADER_ID); loop fetch c2 into line_number,ordered_item,ordered_qty,unit_selling_price; dbms_output.put_line(line_number||' '||ordered_item||' '||ordered_qty||' '|| unit_selling_price); output1:= line_number||' '||ordered_item||' '||ordered_qty||' '||unit_selling_price ; end loop; close c2; exception when no_data_found then null; end; 2)-------------PROCEDURE-----------------------------------------------------------------------create or replace procedure get_header_id(order_number number, output2 out number) is header_id number(10); ord_number number(10); begin select order_number into ord_number from dual; select header_id into header_id from oe_order_headers_all where order_number= ord_number; dbms_output.put_line(header_id); output2 := header_id; end; 3)-------------PROCEDURE-----------------------------------------------------------------------create or replace procedure get_order_lines(header_id in number,output1 out varchar2) as head_id number(10);
line_number number(10); ordered_item varchar2(25); ordered_qty number(10); unit_selling_price number(10); order_amnt number(15); BEGIN select header_id into head_id from dual; DBMS_OUTPUT.PUT_LINE(HEAD_ID); select line_number,substr(ordered_item,1,20) item,ordered_quantity,unit_selling_price,nvl((ordered_quantity*unit_selling_price),0) Amount into line_number,ordered_item,ordered_qty,unit_selling_price,order_amnt from oe_order_lines_all where header_id = head_id ; dbms_output.put_line(line_number||' '||ordered_item||' '||ordered_qty||' '|| unit_selling_price||' '||order_amnt); output1:= line_number||' '||ordered_item||' '||ordered_qty||' '||unit_selling_price||' '|| order_amnt ; exception when NO_DATA_FOUND then null; end;
1)--------------------------------------------------------------------------------declare a number; begin select sal into a FROM emp where empno = 7839;
dbms_output.put_line(a); EXCEPTION when others then dbms_output.put_line(sqlerrm(sqlcode)); end; 2)------------------------------------------------------------------------------------declare a emp%rowtype; begin select * into a from emp where empno =7788; dbms_output.put_line('empno' || a.empno); dbms_output.put_line('enmae' || a.ename); dbms_output.put_line('sal' || a.sal); exception when others then dbms_output.put_line(sqlerrm(sqlcode)); end; 3)---------------------------------------------------------------------------------------declare my_grade salgrade.grade%type; my_losal salgrade.losal%type; my_hisal salgrade.hisal%type; begin select grade, losal, hisal into my_grade,my_losal,my_hisal FROM salgrade where grade = 3; dbms_output.put_line(my_grade||' '||my_losal||' '||my_hisal); EXCEPTION when others then dbms_output.put_line(sqlerrm(sqlcode));
end; 4)----------------------------------------------------------------------------declare x number:=20; y number:=10; begin declare x number:=5; z number:=99; begin dbms_output.put_line('x='||x||' '||'y='||y||' '||'z='||z); end; dbms_output.put_line('x='||x||' '||'y='||y); end; 5)--------------------------------------------------------------------------------<
> declare x number:=20; begin <> declare x number:=5; begin <> declare x number:=7; begin dbms_output.put_line('x='||x||' '||'Outer x='||outer.x||' '||'Inner x='|| Inner.x); end; end; end; 6)----------------------------------------------------------------------------------------declare a number; begin select sal into a FROM emp where empno = 7777; dbms_output.put_line(a); EXCEPTION when NO_DATA_FOUND THEN dbms_output.put_line('Caught NDF exception');
when others then dbms_output.put_line(sqlerrm(sqlcode)); end; 7)---------------------------------------------------------------------------------------declare name varchar2(30):='&Name'; ct number := 1; begin loop exit when ct = 10; dbms_output.put_line(ct||' '||name); ct := ct + 1; end loop; dbms_output.put_line('Alias out of the loop!!'); end; 8)--------------------------------------------------------------------------------------declare name varchar2(30):='GVREDDY'; ct number := 1; begin while ct <> 10 loop dbms_output.put_line(ct||' '||name); ct := ct + 1; end loop; dbms_output.put_line('Alias out of the loop!!'); end; 9)-----------------------------------------------------------------------------------------declare name varchar2(30):='GVREDDY'; begin for ct in 1..10 loop dbms_output.put_line(ct||' '||name); end loop; dbms_output.put_line('Alias out of the loop!!'); end; 10)-------------------------------------------------------------------------------------------declare name varchar2(30):='GVREDDY'; begin for ct in REVERSE 1..9
loop dbms_output.put_line(ct||' '||name); end loop; dbms_output.put_line('Alias out of the loop!!'); end; 11)-------------------------------------------------------------------------------------------------declare name varchar2(30):='&Name'; begin for ct in reverse 1..9 loop dbms_output.put_line(ct||' '||name); return; --Is equivalent of "exit" in C. end loop; dbms_output.put_line('Alias out of the loop!!'); end; 12)--------------------------------------------------------------------------------------------------- begin for i in 1..5 loop for j in 1..5 loop for k in 1..5 loop exit when k = 3; dbms_output.put_line(i||' '||j||' '||k); end loop; end loop; end loop; end; 13)------------------------------------------------------------------------------------------------------ begin <> for i in 1..5 loop for j in 1..5 loop for k in 1..5 loop exit outer when k = 3; dbms_output.put_line(i||' '||j||' '||k); end loop; end loop; end loop;
end; 14)----------------------------------------------------------------------------------------------- begin for i in 1..5 loop for j in 1..5 loop dbms_output.put(j*i||' '); end loop; dbms_output.put_line(' '); end loop; end; 15)------------------------------------------------------------------------------------------------- begin for i in (select * from emp) loop dbms_output.put_line(i.empno||' '||i.ename||' '||i.job); end loop; end; 16)--------------------------------------------------------------------------------------------------declare gender varchar2(1) := '&Gender'; begin if gender = 'F' then dbms_output.put_line('Hello Mam.'); elsif gender = 'M' then dbms_output.put_line('Hello Sir.'); else dbms_output.put_line('Invalid Option.'); end if; end; 17)-----------------------------------------------------------------------------------------------------declare a number := 10; b number := 20; c number:=30; begin if a > b then if a > c then dbms_output.put_line(a||' is greatest.'); else dbms_output.put_line(c||' is greatest.'); end if;
else if b > c then dbms_output.put_line(b||' is greatest.'); else dbms_output.put_line(c||' is greatest.'); end if; end if; end; 18)-----------------------------------------------------------------------------------------------declare v_dummy varchar2(1); begin dbms_output.put_line('start111111'); select 'y' into v_dummy from gl_je_categories where rtrim(user_je_category_name) = rtrim('allocation ') or rtrim(user_je_category_name) = rtrim('budget');
exception when no_data_found then -- v_error_flag := 'y'; --v_error_message:= substr(v_error_message||',invalid category',1,240); --fnd_file.put_line(fnd_file.log,'category is invalid for the line: '|| p_journal_id); dbms_output.put_line('there is an exception!!!!'); when others then dbms_output.put_line('****'||substr(sqlerrm,1,100)); --fnd_file.put_line(fnd_file.log,v_sqlerrm); --fnd_file.put_line(fnd_file.log,'exception in validating category'); --app_exception.raise_exception; end; 19)------------------------------------------------------------------------------------------------declare below_cr_lt EXCEPTION; my_sal number; begin select sal into my_sal from emp where empno = 7839;
if my_sal < 6000 then raise below_cr_lt; end if; exception
when below_cr_lt then dbms_output.put_line('Sorry!!!'); when others then dbms_output.put_line(sqlerrm(sqlcode)); end; 20)-----------------TABLE TYPE (A)--------------------------------------------------------------declare type ty_tab is table of number; v_tab ty_tab := ty_tab(); begin for i in 1..50 loop v_tab.extend; v_tab(i) := (i); end loop; v_tab.delete(5); v_tab.delete(10); v_tab.delete(15); for i in v_tab.first..v_tab.last loop if v_tab.exists(i) then dbms_output.put_line(i||' '||v_tab(i)); else dbms_output.put_line(i||' has been deleted.'); end if; end loop; end; 20)-----------------TABLE TYPE (B)--------------------------------------------------------------create or replace type vtype1 as object (pno varchar2(20),pdesc varchar2(20),udm number, price number); create or replace type vendor1 as table of vtype1; create table vendor_master (ven_code varchar2(20), ven_name varchar2(20) ,prod_details vendor1) nested table prod_details store as vtemp; insert into vendor_master values ( 'v001','HCL', vendor1 ( vtype1('p001','XYZ',5,100), vtype1('p002','ABC',6,150), vtype1('p003','PQR',7,200) ) );
insert into vendor_master values ( 'v002','WIPRO', vendor1 ( vtype1('p004','XYZ',5,100), vtype1('p005','ABC',6,150), vtype1('p006','PQR',7,200) ) );
22)---------------------------------------------------------------------------------------------------select length(ename) -length(translate(ename,'1aeiouAEIOU','1')), ename from emp 23)----------------------------------------------------------------------------------------------------declare gn number(3) := &Number_Please; lt number(2) := gn / 2; ct number(2) := 3; begin if gn = 2 or gn = 3 then goto prime; elsif mod(gn,2) = 0 then goto noprime; else while (ct <= lt) loop if mod(gn,ct) = 0 then goto noprime; end if; ct := ct + 2; end loop; goto prime; end if; <> dbms_output.put_line(gn||' is not prime.'); goto pend; <> dbms_output.put_line(gn||' is prime.'); <> null; end;
24)-------------------------------------------------------------------------------------------------declare cursor c1 is select * from dept; drec dept%rowtype; begin open c1; loop fetch c1 into drec; exit when c1%NOTFOUND; dbms_output.put_line(drec.dname||' '||drec.loc||' '||drec.deptno); end loop; close c1; end; 25)-----------------------------------------------------------------------------------------------------declare cursor c1 is select * from dept; drec dept%rowtype; begin open c1; loop fetch c1 into drec; exit when c1%NOTFOUND; dbms_output.put_line(drec.dname||' '||drec.loc||' '||drec.deptno); end loop; close c1; dbms_output.put_line('==============================='); FOR I IN C1 LOOP dbms_output.put_line(I.dname||' '||I.loc||' '||I.deptno); END LOOP; end; 26)----------------------------------------------------------------------------------------------declare cursor c1(d number) is select * from emp wh ere deptno = d; cursor c2 is select * from dept; begin for i in c2 loop dbms_output.put_line('============================================= ='); dbms_output.put_line(i.deptno||' '||i.dname||' '||i.loc); dbms_output.put_line('============================================= =');
for j in c1(i.deptno) loop dbms_output.put_line(j.ename||' end loop; end loop; end;
'||j.job||''||j.sal);
27)----------------------------------------------------------------------------------------------------declare tn varchar2(30) := '&Table_Name'; cn varchar2(30) := '&Column_Name'; type refCursorType is ref cursor; rcv refCursorType; str varchar2(30); val varchar2(30); begin dbms_output.put_line(cn); str := 'Select '||cn||' from '||tn; open rcv for str; loop fetch rcv into val; exit when rcv%notfound; dbms_output.put_line(val); end loop; close rcv; end; 28)-----------------------------------------------------------------------------------------------------declare n ff.a%type; begin insert into ff(a) values (98); commit; select a into n from ff where a = 98; dbms_output.put_line(n); exception when DUP_VAL_ON_INDEX then dbms_output.put_line('Insertion failed as such a value already exists.'); when NO_DATA_FOUND then dbms_output.put_line('No such number.'); when others then dbms_output.put_line(sqlerrm(sqlcode));
end; 29)----------------------------------------------------------------------------------------------------declare cursor c1 is select * from dept; drec dept%rowtype; begin open c1; open c1; loop fetch c1 into drec; exit when c1%notfound; dbms_output.put_line(drec.dname||' '||drec.loc||' '||drec.deptno); end loop; close c1; exception when CURSOR_ALREADY_OPEN then dbms_output.put_line('Forgot!! You have an open c ursor.'); when INVALID_CURSOR then dbms_output.put_line('Hey!!! You have not opened the cursor.'); when others then dbms_output.put_line(sqlerrm(sqlcode)); end; 30)----------------------------------------------------------------------------------------------------declare cursor c1 is select * from dept; drec dept%rowtype; begin open c1; if c1%ISOPEN then dbms_output.put_line('Cursor is open.'); else dbms_output.put_line('Cursor has to be opened.'); end if;
loop fetch c1 into drec; exit when c1%rowcount = 3; dbms_output.put_line(drec.dname||' '||drec.deptno); end loop; close c1; end;
31)------------------------------------------------------------------------------------------------create table acct (bal number, no number); --insert into acct(bal, no) values(345, 2); declare min_bal constant number := 500; over_drawn exception; withdrawl_amt number := 1000; mybal number; begin select bal into mybal from acct where no = 2;
if mybal - withdrawl_amt < 500 then raise over_drawn; else update acct set bal = bal - withdrawl_amt where no = 2; commit; end if; exception when over_drawn then dbms_output.put_line('Sorry!! sir/madam minimum balance is Rs.500/-.'); when others then dbms_output.put_line(sqlerrm(sqlcode)); end; 31) A---------DOING THE DML OPERATIONS BY USING EXCEPTIONS----------declare r emp_dup%rowtype; begin select * into r from emp_dup where empno=7463; if r.sal>2000 then r.sal:=r.sal/(r.sal-r.comm); update emp set sal=r.sal where empno=7463; dbms_output.put_line('THE EMPLOYEE DETAILS WAS'||r.empno||r.ename|| r.job||r.sal||r.comm||r.deptno); end if; exception when no_data_found then insert into emp_dup(empno,ename,job,sal,comm,deptno) values(7463,'chsr','manager',8000,8000,40); when too_many_rows then delete from emp_dup where empno=7463;
when zero_divide then update emp_dup set sal=r.sal+1000 where empno=7463; end; 31) B------------------ANOTHER USER-DEFINED EXCEPTION-------------------------declare r emp_dup%rowtype; moresal exception; begin select * into r from emp_dup where empno=7463; if r.sal>2000 then raise moresal; end if; exception when no_data_found then insert into emp_dup(empno,ename,job,sal,comm,deptno) values(7463,'chsr','manager',8000,8000,40); when too_many_rows then delete from emp_dup where empno=7463; when zero_divide then update emp_dup set sal=r.sal+1000 where empno=7463; when moresal then dbms_output.put_line('THE SALARY WAS MORE THAN EXPECTED'); end; 32)------------------------------------------------------------------------------------------------ begin lock table acct in exclusive mode nowait; exception when others then dbms_output.put_line(sqlerrm(sqlcode)); end; 33)-----------------------------------------------------------------------------------------------------declare table_locked exception; pragma exception_init(table_locked, -00054); begin lock table acct in exclusive mode nowait; exception when table_locked then dbms_output.put_line('The table is locked. Please try after some time.'); when others then dbms_output.put_line(sqlerrm(sqlcode)); end;
34)--------------------------------------------------------------------------------------------------------create table ratio(a number, b number, r number) --insert into ratio(a, b, r) values (4,5,6); --commit; declare cursor c1 is select * from ratio for update; rrec ratio%rowtype; begin open c1; loop fetch c1 into rrec; exit when c1%notfound; begin update ratio set r = rrec.a / rrec.b where current of c1; commit; exception when ZERO_DIVIDE then update ratio set r = 0 where current of c1; commit; end; end loop; close c1; end; /
35)------------------------------------------------------------------------------------------------declare no_data_found exception; n number; begin select sal into n from emp where empno = 7777; dbms_output.put_line('Employee no 7777 is drawing'||n); exception when standard.no_data_found or no_data_found then dbms_output.put_line('Caught no data found'); when others then dbms_output.put_line('Caught others exception'); dbms_output.put_line(sqlerrm(sqlcode)); end;
36)-------------------- Procedures--------------------------------------------------------------------create or replace procedure Playwin(no in number, prize out number) as begin dbms_output.put_line('Your ticket Number is '||no); dbms_output.put_line('You have got a prize!! Keep Playing...'); prize := ((no / 3) * 24 )+ 4 ; end; / =================36 X========================================== declare tno number:= 123456; prize number; begin playwin(tno, prize); dbms_output.put_line('You have become '||prize||' pati.'); end; 37)-----------------------------------------------------------------------------------------------------create or replace function CompInt(pri in number ,noy in number ,roi in number) return number is ci number; begin ci := pri * power((1 + roi/100),noy); dbms_output.put_line('ci '||ci); return ci; end; =============37 X========================================== declare c number; begin c := CompInt(100,1,10); dbms_output.put_line('The compound interest = '||c); end; 38)--------------------------------------------------------------------------------------------------create or replace procedure CalTot(up number, qty number, runtot in out number) as begin runtot := runtot + (up * qty); end;
=============37 X=========================================== declare total number := 0; HamamQ number := 7; HamamP number := 10; BaboolQ number := 3; BaboolP number := 16; HuggyQ number := 1; HuggyP number := 80; begin CalTot(HamamP, HamamQ, Total); dbms_output.put_line('Total so far (Hamam) '||Total); CalTot(BaboolP, BaboolQ, Total); dbms_output.put_line('Total so far (Hamam,Babool) '||Total); CalTot(HuggyP, HuggyQ, Total); dbms_output.put_line('Total so far (Grand) '||Total); end; 39)----------------------------------------------------------------------------------------------------create or replace procedure Greet(n number default 5) is begin for i in 1..n loop dbms_output.put_line('Greetings!!!'); end loop; end; ===============40 X=========================================== begin Greet(7); end; 40)------------------------------- Packages-(spec )------------------------------------------------CREATE OR REPLACE PACKAGE emp_actions AS -- spec TYPE EmpRecTyp IS RECORD (emp_id INTEGER, salary REAL); CURSOR desc_salary RETURN EmpRecTyp; PROCEDURE hire_employee ( ename VARCHAR2, job VARCHAR2, mgr NUMBER, sal NUMBER, comm NUMBER, deptno NUMBER);
PROCEDURE fire_employee (emp_id NUMBER); END emp_actions; ============Body============================================= CREATE OR REPLACE PACKAGE BODY emp_actions AS -- body CURSOR desc_salary RETURN EmpRecTyp IS SELECT empno, sal FROM emp ORDER BY sal DESC; PROCEDURE hire_employee ( ename VARCHAR2, job VARCHAR2, mgr NUMBER, sal NUMBER, comm NUMBER, deptno NUMBER) IS BEGIN INSERT INTO emp VALUES (empno_seq.NEXTVAL, ename, job, mgr, SYSDATE, sal, comm, deptno); END hire_employee; PROCEDURE fire_employee (emp_id NUMBER) IS BEGIN DELETE FROM emp WHERE empno = emp_id; END fire_employee; END emp_actions; =============40 X =========================================== declare begin --emp_actions.hire_employee('Akash','Analyst',7839,4999,null,10); emp_actions.fire_employee(5); end; / 41)---------------------- Spec--------------------------------------------------------------------------create or replace package ox as function add(a number, b number) return number; function add(a varchar2, b varchar2) return varchar2; function add(a number, b varchar2) return varchar2; function add(a varchar2, b number) return varchar2; end; ===============Body============================================ create or replace package body ox as function add( a number, b number ) return number is begin return (a + b); end;
function add(a varchar2, b varchar2) return varchar2 is begin return a||b; end; function add(a varchar2, b number) return varchar2 is begin return a||b; end; function add(a number, b varchar2) return varchar2 is begin return a||b; end; end ox; ================41 X============================================ declare A NUMBER := 12345; B NUMBER := 56789; C VARCHAR2(30) := 'RAM '; D VARCHAR2(30) := ' LAKSHMAN'; RES VARCHAR2(20); RESN NUMBER; BEGIN RESN := OX.ADD(A,B); DBMS_OUTPUT.PUT_LINE(RESN); RES := OX.ADD(A,C); DBMS_OUTPUT.PUT_LINE(RES); RES := OX.ADD(D,A); DBMS_OUTPUT.PUT_LINE(RES); RES := OX.ADD(C, D); DBMS_OUTPUT.PUT_LINE(RES); END; / 42)--------------------------X-------------------------------------------------------------------------declare begin bt.create_acct('ahmed',5000039); for i in (select * from acct) loop dbms_output.put_line(i.acctno||' '||i.name||' '||i.bal); end loop; bt.deposit(1, 50000); for i in (select * from acct) loop
dbms_output.put_line(i.acctno||' '||i.name||' '||i.bal); end loop; bt.withdrawl(1, 25000); for i in (select * from acct) loop dbms_output.put_line(i.acctno||' '||i.name||' '||i.bal); end loop; end; / 43)------Triggers-------------------------------------------------------------------------------------SQL> CREATE TABLE ABC(A NUMBER , B DATE);
Table created. SQL> CREATE OR REPLACE TRIGGER ABC_TR 2 BEFORE INSERT ON ABC 3 FOR EACH ROW 4 BEGIN 5 DBMS_OUTPUT.PUT_LINE('A REC IS BEING INSERTED.'); 6 END; 7 / Trigger created. SQL> SET SERVEROUT ON SQL> INSERT INTO ABC(A,B) VALUES (1, SYSDATE); A REC IS BEING INSERTED. 1 row created. SQL> CREATE OR REPLACE TRIGGER ABC_DEL_TR 2 AFTER DELETE ON ABC 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE('TABLE IS BEING DELETED.'); 5 END; 6 / Trigger created. SQL> DELETE ABC; TABLE IS BEING DELETED. 1 row deleted.
SQL> INSERT INTO ABC 2 SELECT EMPNO, HIREDATE 3 FROM EMP; A REC IS BEING INSERTED. A REC IS BEING INSERTED. A REC IS BEING INSERTED. A REC IS BEING INSERTED. A REC IS BEING INSERTED. A REC IS BEING INSERTED. A REC IS BEING INSERTED. A REC IS BEING INSERTED. A REC IS BEING INSERTED. A REC IS BEING INSERTED. A REC IS BEING INSERTED. A REC IS BEING INSERTED. A REC IS BEING INSERTED. A REC IS BEING INSERTED. A REC IS BEING INSERTED. A REC IS BEING INSERTED. 16 rows created. SQL> commit; Commit complete. SQL> DELETE ABC; TABLE IS BEING DELETED. 16 rows deleted. SQL> roll Rollback complete. SQL> SELECT * FROM ABC; AB --------- --------7935 7936 28-NOV-05 7369 17-DEC-80 7499 20-FEB-81 7521 22-FEB-81 7566 02-APR-81 7654 28-SEP-81 7698 01-MAY-81 7782 09-JUN-81
7788 09-DEC-82 7839 17-NOV-81 7844 08-SEP-81 7876 12-JAN-83 7900 03-DEC-81 7902 03-DEC-81 7934 23-JAN-82 16 rows selected. SQL> CREATE TABLE ABC_BAK 2 AS 3 SELECT * FROM ABC WHERE 0=9; Table created.
Trigger Sequence pre-block pre-record pre-text-item when-new-record-instance when-new-item-instance when-validate-item post-text-item when-validate-record post-record post-block
NESTED TABLES step1:creation of a type
create type btype1 as object (bno number, bname varchar2(20), author varchar2(20)); step2:creation of table of the above type
create type btype1 as object (bno number, bname varchar2(20), author varchar2(20)); step3:creation of parent table
create type btype1 as object (bno number, bname varchar2(20), author varchar2(20)); step4:inserting the data
create type btype1 as object (bno number, bname varchar2(20), author varchar2(20)); create type btype1 as object (bno number, bname varchar2(20), author varchar2(20)); step5: selecting
select s.bdetails from empbook s where empno=1002
SQL LOADER What is SQL*Loader and what is it used for? SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. Its syntax is similar to that of the DB2 Load utility, but comes with more options. SQL*Loader supports various load formats, selective loading, and multitable loads. How does one use the SQL*Loader utility? One can load data into an Oracle database by using the sqlldr (sqlload on some platforms) utility. Invoke the utility without arguments to get a list of available parameters. Look at the following example: sqlldr scott/tiger control=loader.ctl
This sample control file (loader.ctl) will load an external data file containing delimited data: load data infile 'c:\data\mydata.csv' into table emp ( empno, empname, sal, deptno ) fields terminated by "," optionally enclosed by '"' The mydata.csv file may look like this: 10001,"Scott Tiger", 1000, 40 10002,"Frank Naude", 500, 20 Another Sample control file with in-line data formatted as fix length records. The trick is to specify "*" as the name of the data file, and use BEGINDATA to start the data section in the control file. load data infile * replace into table departments ( dept position (02:05) char(4), deptname position (08:27) char(20) ) begindata COSC COMPUTER SCIENCE ENGL ENGLISH LITERATURE MATH MATHEMATICS POLY POLITICAL SCIENCE
Is there a SQL*Unloader to download data to a flat file? Oracle does not supply any data unload utilities. However, you can use SQL*Plus to select and format your data and then spool it to a file: set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on spool oradata.txt select col1 || ',' || col2 || ',' || col3 from tab1 where col2 = 'XYZ'; spool off
Alternatively use the UTL_FILE PL/SQL package: rem Remember to update initSID.ora, utl_file_dir='c:\oradata' parameter declare fp utl_file.file_type; begin fp := utl_file.fopen('c:\oradata','tab1.txt','w'); utl_file.putf(fp, '%s, %s\n', 'TextField', 55); utl_file.fclose(fp); end; / You might also want to investigate third party tools like TOAD or ManageIT Fast Unloader from CA to help you unload data from Oracle.
Can one load variable and fix length data records? Yes, look at the following control file examples. In the first we will load delimited data (variable length): LOAD DATA INFILE * INTO TABLE load_delimited_data FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS ( data1, data2 ) BEGINDATA 11111,AAAAAAAAAA 22222,"A,B,C,D,"
If you need to load positional data (fixed length), look at the following control file example: LOAD DATA INFILE * INTO TABLE load_positional_data
( data1 POSITION(1:5), data2 POSITION(6:15) ) BEGINDATA 11111AAAAAAAAAA 22222BBBBBBBBBB
Can one skip header records load while loading? Use the "SKIP n" keyword, where n = number of logical rows to skip. Look at this example: LOAD DATA INFILE * INTO TABLE load_positional_data SKIP 5 ( data1 POSITION(1:5), data2 POSITION(6:15) ) BEGINDATA 11111AAAAAAAAAA 22222BBBBBBBBBB Can one modify data as it loads into the database? Data can be modified as it loads into the Oracle Database. Note that this only applies for the conventional load path and not for direct path loads. LOAD DATA INFILE * INTO TABLE modified_data ( rec_no "my_db_sequence.nextval", region CONSTANT '31', time_loaded "to_char(SYSDATE, 'HH24:MI')", data1 POSITION(1:5) ":data1/100", data2 POSITION(6:15) "upper(:data2)", data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')" ) BEGINDATA 11111AAAAAAAAAA991201 22222BBBBBBBBBB990112
LOAD DATA INFILE 'mail_orders.txt' BADFILE 'bad_orders.txt' APPEND INTO TABLE mailing_list FIELDS TERMINATED BY ","
( addr, city, state, zipcode, mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)", mailing_city "decode(:mailing_city, null, :city, :mailing_city)", mailing_state ) Can one load data into multiple tables at once? Look at the following control file: LOAD DATA INFILE * REPLACE INTO TABLE emp WHEN empno != ' ' ( empno POSITION(1:4) INTEGER EXTERNAL, ename POSITION(6:15) CHAR, deptno POSITION(17:18) CHAR, mgr POSITION(20:23) INTEGER EXTERNAL ) INTO TABLE proj WHEN projno != ' ' ( projno POSITION(25:27) INTEGER EXTERNAL, empno POSITION(1:4) INTEGER EXTERNAL ) Can one selectively load only the records that one need? Look at this example, (01) is the first character, (30:37) are characters 30 to 37: LOAD DATA INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis' APPEND INTO TABLE my_selective_table WHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '19991217' ( region CONSTANT '31', service_key POSITION(01:11) INTEGER EXTERNAL, call_b_no POSITION(12:29) CHAR ) Can one skip certain columns while loading data? One cannot use POSTION(x:y) with delimited data. Luckily, from Oracle 8i on e can specify FILLER columns. FILLER columns are used to skip columns/fields in the load file, ignoring fields that one does not want. Look at this example: LOAD DATA TRUNCATE INTO TABLE T1
FIELDS TERMINATED BY ',' ( field1, field2 FILLER, field3 ) How does one load multi-line records? One can create one logical record from multiple physical records using one o f the following two clauses: CONCATENATE: - use when SQL*Loader should combine the same number of physical records together to form one logical record. CONTINUEIF - use if a condition indicates that multiple records should be treated as one. Eg. by having a '#' character in column 1. How can get SQL*Loader to COMMIT only at the end of the load file? One cannot, but by setting the ROWS= parameter to a large value, committing can be reduced. Make sure you have big rollback segments ready when you use a high value for ROWS=. Can one improve the performance of SQL*Loader? A very simple but easily overlooked hint is no t to have any indexes and/or constraints (primary key) on your load tables during the load process. This will significantly slow down load times even with ROWS= set to a high value.
Add the following option in the command line: DIRECT=TRUE. This will effectively bypass most of the RDBMS processing. However, there are cases when you can't use direct load. Refer to chapter 8 on Oracle server Utilities manual. Turn off database logging by specifying the UNRECOVERABLE option. This option can only be used with direct data loads. Run multiple load jobs concurrently. What is the difference between the conventional and direct path loader? The conventional path loader essentially loads the data by using standard INSERT statements. The direct path loader (DIRECT=TRUE) bypasses much of the logic involved with that, and loads directly into the Oracle data files. More information about the restrictions of direct path loading can be obtain
