Ram appszone: 2016

Saturday, 12 November 2016

Oracle Collections

COLLECTIONS:

Oracle server supports following types
1) PL/SQL Record (or) Record Type
2) Index By Table (or) PL/SQL Table (or) Associative Arrays
3) Nested Tables
4) V arrays
5) Ref Cursors

1) PL/SQL Record (or) Record Type:

Is is one of the user defined temporary data type which is used to store more than one table data (or) to assign more than one column datatypes.
They must at least contain one element.
Pinpoint of data is not possible.

Syntax : Type Typename is Record ( Val-1 Datatype, Val-2 Datatype,…..);
Var Typename
Ex :
Declare
Type Rec is record ( vname emp.ename%type,
Vsal emp.sal%type,
VLoc dept.loc%type);
Vrec Rec;
Vno emp.empno%type:=&n;
Begin
Select ename,sal,loc into vrec from emp,dept where emp.deptno=dept.deptno and
emp.empno=vno;
Dbms_output.put_line(vrec.vname||’,’||vrec.vsal||’,’||vrec.vloc);
End;

2) Index By Table (or) PL/SQL Table (or) Associative Arrays:

This is an user defined type which is used to store multiple data items in to a single unit.Basically this is an unconstrained table
Generally these tables are used to improve performance of applications because these tables are stored in memory area that's why these tables are also called as memory tables.
Basically these table contains key value pairs i.e value field is stored in actual data and key field stored in indexes.
Key field values are either integer or character and also these values are either -ve or +ve.
These indexes key behaves like a primary key i.e does not accept duplicate and null values.basically this key datatype is binary_integer.
Index by table having following collection methods.

Exists
first
last
prior
next
count
delete(range of indexes)
Example1:
declare
type t1 is table of number(10)
index by binary_integer;
v_t t1;
begin
v_t(1):=10;
v_t(2):=20;
v_t(3):=30;
v_t(4):=40;
v_t(5):=50;
dbms_output.put_line(v_t(3));
dbms_output.put_line(v_t.first);
dbms_output.put_line(v_t.last);
dbms_output.put_line(v_t.prior(3));
dbms_output.put_line(v_t.next(4));
dbms_output.put_line(v_t.count);
dbms_output.put_line(v_t(5));
end;

Example2:
declare
type t1 is table of number(10)
index by binary_integer;
v_t t1;
begin
v_t(1):=10;
v_t(2):=20;
v_t(3):=30;
v_t(4):=40;
v_t(5):=50;
dbms_output.put_line(v_t.count);
v_t.delete(2,3);
dbms_output.put_line(v_t.count);
v_t.delete;
dbms_output.put_line(v_t.count);
end;

Question: Write a PLSQL program to get all employee names from emp table and store it into index by table and display data from index by table?

Program:
declare
type t1 is table of varchar2(10)
index by binary_integer;
v_t t1;
cursor c1 is select ename from emp;
n number(5):=1;
begin
open c1;
loop
fetch c1 into v_t(n);
exit when c1%notfound;
n:=n+1;
end loop;
close c1;
for i in v_t.first..v_t.last
loop
dbms_output.put_line(v_t(i));
end loop;
end;

Program:
declare
type t1 is table of varchar2(10)
index by binary_integer;
v_t t1;
begin
select ename bulk collect into v_t from emp;
for i in v_t.first..v_t.last
loop
dbms_output.put_line(v_t(i));
end loop;
end;

Program:
declare
type t1 is table of date
index by binary_integer;
v_t t1;
begin
for i in 1..10
loop
v_t(i):=sysdate+i;
end loop;
for i in v_t.first..v_t.last
loop
dbms_output.put_line(v_t(i));
end loop;
end;

Question: Write a PLSQL Program to retrieve all joining dates from emp table and store it into index by table and display content from index by table?

program:
declare
type t1 is table of date
index by binary_integer;
v_t t1;
begin
select hiredate bulk collect into v_t from emp;
for i in v_t.first..v_t.last
loop
dbms_output.put_line(v_t(i));
end loop;
end;

Example3:
declare
type t1 is table of varchar2(10)
index by varchar2(10);
v_t t1;
x varchar2(10);
begin
v_t('a'):= 'ARUN';
v_t('b'):= 'AJAY';
v_t('c'):= 'ABHI';
x :='a';
loop
dbms_output.put_line(v_t(x));
x := v_t.next(x);
exit when x is null;
end loop;
end;

Example4:
declare
type t1 is table of emp%rowtype
index by binary_integer;
v_t t1;
x number(5);
begin
select * bulk collect into v_t from emp;
x:=1;
loop
dbms_output.put_line(v_t(x).empno||','||v_t(x).ename);
x:=v_t.next(x);
exit when x is null;
end loop;
end;

( OR )

Example5:
declare
type t1 is table of emp%rowtype
index by binary_integer;
v_t t1;
begin
select * bulk collect into v_t from emp;
for i in v_t.first..v_t.last
loop
dbms_output.put_line(v_t(i).empno||','||v_t(i).ename);
endloop;
end;

3) Nested Tables:

This is also user defined type which is used to store multiple data items in a single unit but before we are storing actual data we must initialize the data while using constructor. Here constructor name is same as type name.
Generally we are not allow to store index by tables permanently into database, to overcome this problem they are introduce Nested Tables to extension of the index by tables.
These user defined types stored permanently into database using sql.
In Index by tables we can not add or remove the indexes. where as in Nested tables we can add or remove the indexes using Extend, Trim collection methods.
In Nested tables we can allocate the memory explicitly while using Extend method.

Syntax : Type type_name is Table of datatype( size );

variable_name type_name( ); => Constructor_Name

Example1:

Declare

type t1 is table of number(10);

v t1:=t1();

begin

v.extend(100);

v(100):=10;

dbms_output.put_line(v(100));

end;

Example2:

Declare

type t1 is table of number(10);

v1 t1:=t1(10,20,30,40,50);

begin

dbms_output.put_line(v1.first);

dbms_output.put_line(v1.last);

dbms_output.put_line(v1.prior(3));

dbms_output.put_line(v1.next(3));

dbms_output.put_line(v1.count);

dbms_output.put_line(v1(3));

for i in v1.first..v1.last

loop

dbms_output.put_line(v1(i));

end loop;

end;

Example3:

Declare

type t1 is table of number(10);

v1 t1;

v2 t1:=t1();

begin

if v1 is null then

dbms_output.put_line('v1 is null');

else

dbms_output.put_line('v1 is not null');

end if;

if v2 is null then

dbms_output.put_line('v2 is null');

else

dbms_output.put_line('v2 is not null');

end if;

end;

Example4:

declare

type t1 is table of number(10);

v t1:=t1();

begin

v.extend;

v(1):=5;

dbms_output.put_line(v(1));

end;

Question:Write a PLSQL program to get all employee names from emp table and store it into Nested Table and display data from Nested Table?

Program:

declare

type t1 is table of varchar2(10);

v t1:=t1();

cursor c1 is select ename from emp;

n number(10):=1;

begin

for i in c1

loop

v.extend();

v(n):=i.ename;

n:=n+1;

end loop;

for i in v.first..v.last

loop

dbms_output.put_line(v(i));

end loop;

end;

( OR )

declare

type t1 is table of varchar2(10);

v t1:=t1();

begin

select ename bulk collect into v from emp;

for i in v.first..v.last

loop

dbms_output.put_line(v(i));

end loop;

end;

( OR )

declare

type t1 is table of emp%rowtype;

v t1:=t1();

begin

select * bulk collect into v from emp;

for i in v.first..v.last

loop

dbms_output.put_line(v(i).empno||','||v(i).ename||','||v(i).job);

end loop;

end;

4) V arrays:

This is also user defined type which is used to store multiple data items in a single unit but before we are storing actual data we must initialize the data while using constructor.

These user defined types stored permanently into database using sql.

Basically we are using the Varrays for retrieving the huge data.

Syntax : Type type_name is varray( maxsize ) of datatype( size );

Variable_name Type_name := Type_name( );

example1:

Declare

type t1 is varray(50) of emp%rowtype;

v t1:=t1();

begin

select * bulk collect into v from emp;

for i in v.first..v.last

loop

dbms_output.put_line(v(i).empno||','||v(i).ename||','||v(i).job);

end loop;

end;

5) Ref Cursors:

Ref Cursors are user define types which is used to process multiple records and also this is record by record process
Generally through the static cursors we are using only one select statement at a time for single active set area where as in ref cursors we are executing no of select statements dynamically for single active set area.
That's why these type of cursors are also called as dynamic cursors.
By using ref cursors we return large amount of data from oracle database into client applications.
There are 2 Types > Strong Ref Cursor > Weak Ref Cursor

Strong Ref Cursor:It is one of the ref cursor which is having return type.
Weak Ref Cursor:It is one of the ref cursor which does not have a return type.

Note : In ref cursor we are executing select statements using open .... for statement.

example1:

Declare

Type t1 is ref cursor;

v_t t1;

i emp%rowtype;

begin

open v_t for select * from emp where sal>2000;

loop

fetch v_t into i;

exit when v_t%notfound;

dbms_output.put_line(i.ename||' '||i.sal);

end loop;

close v_t;

end;

example2:

declare

type t1 is ref cursor;

v_t t1;

i emp%rowtype;

j dept%rowtype;

v_no number(5):=&no;

begin

if v_no=1 then

open v_t for select * from emp;

loop

fetch v_t into i;

exit when v_t%notfound;

dbms_output.put_line(i.ename||' '||i.deptno);

end loop;

close v_t;

elsif v_no=2 then

open v_t for select * from dept;

loop

fetch v_t into j;

exit when v_t%notfound;

dbms_output.put_line(j.deptno||' '||j.dname);

end loop;

close v_t;

end if;

end;

example3:

Package Specification:

create or replace package pg1

type t1 is ref cursor return emp%rowtype;

type t2 is ref cursor return dept%rowtype;

procedure p1 (p_t1 out t1);

procedure p2 (p_t2 out t2);

end;

Package Body:

create or replace package body pg1 is

procedure p1 (p_t1 out t1)

begin

open p_t1 for select * from emp;

end p1;

procedure p2 (p_t2 out t2)

begin

open p_t2 for select * from dept;

end p2;

end;

Execution of Package:

variable a refcursor

variable b refcursor

exec pg1.p1(:a);

exec pg1.p2(:b);

print a b;

BULK MECHANISM:

Bulk is one of the method which is used to improve the performance of the applications.
Oracle introduce bulk bind process using collection i.e in this process we are putting all sql statement related values into collection and in this collection we are performing insert, update, delete at a time using for all statement.
In this bulk we have two actions

1) Bulk Collect

2) Bulk Bind

1 ) Bulk Collect:

In this clause we are used to fetch the data from resource into collection
This clauses used in

1) Bulk Collect used in Select ...........into............clause

2) Bulk Collect used in Cursor...........Fetch...........Statement

3) Bulk Collect used in Dml............Returning.........Clauses

1) Bulk Collect used in select .....into .....clause:

Syntax : select * bulk collect into collection_name from table_name.

Example1:

Declare

type t1 is table of emp%rowtype

index by binary_integer;

v t1;

begin

select * bulk collect into v from emp;

for i in v.first..v.last loop

dbms_output.put_line(v(i).empno||','||v(i).ename||','||v(i).job);

end loop;

end;

2) Bulk Collect used in cursor......fetch.......statement

Syntax : fetch cursor_name bulk collect into collection_variable.

Example1:

Declare

type t1 is table of varchar2(10)

index by binary_integer;

v1 t1;

v2 t1;

cursor c1 is select ename,job from emp;

begin

open c1;

fetch c1 bulk collect into v1,v2;

close c1;

for i in v1.first..v1.last

loop

dbms_output.put_line(v1(i)||','||v2(i));

end loop;

end;

Time Program with out BULK:

Declare

vrow varchar2(50);

cursor c1 is select object_name from all_objects;

z1 number(10);

z2 number(10);

begin

z1:=dbms_utility.get_time;

open c1;

loop

fetch c1 into vrow;

exit when c1%notfound;

end loop;

close c1;

z2:=dbms_utility.get_time;

dbms_output.put_line(z1);

dbms_output.put_line(z2);

dbms_output.put_line(z2-z1);

end;

Time Program with BULK:

Declare

type t1 is table of varchar2(50) index by binary_integer;

v1 t1;

cursor c1 is select object_name from all_objects;

z1 number(10);

z2 number(10);

begin

z1:=dbms_utility.get_time;

open c1;

loop

fetch c1 bulk collect into v1;

exit when c1%notfound;

end loop;

close c1;

z2:=dbms_utility.get_time;

dbms_output.put_line(z1);

dbms_output.put_line(z2);

dbms_output.put_line(z2-z1);

end;

3) Bulk Collect used in DML..........Returning clauses.

Syntax : dml statement returning column_name into variable_name;

Example1:

Variable a varchar2(10);

Update emp set sal=sal+100 where ename ='KING' returning job into :a;

Print a;

Question:Write a PLSQL Stored Procedure modify salaries of the clerk from emp table and also these modified value immediately stored into index by table by using dml ...returning clause and also display content from index by table?

Program:

Create or replace procedure p1 is

type t1 is table of emp%rowtype

index by binary_integer;

v1 t1;

begin

update emp set sal=sal+100 where job='CLERK'

returning empno,ename,job,mgr,hiredate,sal,comm,deptno

bulk collect into v1;

dbms_output.put_line('updated no:of clerks are:'||sql%rowcount);

for i in v1.first..v1.last

loop

dbms_output.put_line(v1(i).ename||','||v1(i).job||','||v1(i).sal);

end loop;

end;

2 ) Bulk Bind:

In bulk bind process we are performing bulk of operations using collection i.e in this process we are using bulk update, bulk delete, bulk insert using forall statement.
Before we are using bulk bind process we are fetching data from database into collections using bulk collect clause.

Syntax : forall indexvar in collectionvar.frist..collectionvar.last

Example1:

Declare

type t1 is varray(10) of number(10);

v1 t1:=t1(10,20);

begin

forall i in v1.first..v1.last

update emp set sal=sal+100 where deptno=v1(i);

end;

Bulk Update:

Example1:

Declare

type t1 is table of number(5) index by binary_integer;

v1 t1;

begin

select empno bulk collect into v1 from emp;

forall i in v1.first..v1.last

update emp set sal=sal+111 where empno=v1(i);

end;

Bulk Delete:

Example1:

Declare

type t1 is varray(10) of number(10);

v1 t1:=t1(20,30,40);

begin

forall i in v1.first..v1.last

delete from emp where empno=v1(i);

end;

Bulk Insert:

Example1:

Declare

type t1 is table of number(10) index by binary_integer;

v1 t1;

begin

for i in 1..100

loop

v1(i):=i;

end loop;

forall i in v1.first..v1.last

insert into bt values (v1(i));

end;

Sunday, 26 June 2016

Loading the Data (.Csv) from File to Oracle Database

Follow Below steps:

Step 1: create a directory for placing the .csv file using SQL developer or TOAD or SQL*Plus or any tool that connects with the database

create or replace directory OGL_REPORT_ACCESS as 'G:\ram_file_load_task';

step 2: place the .csv file in the above path mentioned in the step 1

Note: Your .csv filename is "EmpData.csv"

step 3: create table for the inserting the data from the file we placed in step 2

create table ram_test(str varchar2(100));

step 4: here is the code :I am using UTL_File package in order to load from the file

set serveroutput on;
declare
fp utl_file.file_type;
str varchar2(500);
begin
fp := utl_file.fopen('OGL_REPORT_ACCESS','EmpData.csv','r');
--reading next lines and in put into ram_test table
loop
utl_file.get_line(fp,str);
insert into ram_test values(str);
end loop;
exception
when no_data_found then
commit;
end;
/
show errors;

step5: query the table whether data inserted or not.

select *from ram_test;

Thursday, 16 June 2016

Short Note on Financial Statement Generator (FSG Reports)

What Are Oracle FSGs?

FSG is short form of Financial Statement Generator.

In brief , is a powerful reporting engine that supports interchangeable report objects,
server-based processing for high performance, and report scheduling for efficient use of system resources.

A user defined report that allows for the rollup of GL Account Balances (i.e. combinations of accounting segments "EUR"GL Code Combinations) into logical groupings for reporting
This is End user tool , which enables end business user for financial reporting without any programming

Most of these reports are oriented toward financial statements
These are very very easy to use :-)
Transfer accounting information to a popular spreadsheet if you prefer to use your personal computer for analysis and planning.
Report on both translated and entered foreign currency amounts.
Access a wide variety of standard management and accounting reports for the detail information you need,including chart of account listings, journals, general ledgers and trial balances.
Define the complex financial statements you need to analyze your business,including responsibility reports for business units, profit centres and cost centers. The good things is that all these reports run through concurrent manager.
Most important there is no additional setup required We can design our reports to meet your business needs
Design to print directly to MS Excel Schedule reports to run directly from the application You can do what with FSG

What are the reports which is based out of FSG Reports?

Financials-P&L, Bal Sht, Cash Flow, Rev/Exp detail, cost center detail
Auditor Schedules - trial balance mapped to financial stmts, Income tax workpapers ,Partial trial balances,Income Statement

What are the FSG Component or Structure?

Row Sets
Column Sets
Content Sets
Row Orders
Display Sets
Reports
Report Sets

what are the Row Set?

Description to appear on left hand side
Define accounting flexfield ranges or summary accounts here
Define row calculation - total, etc.

What are the Column Set?

Column Sets typically define the format and content of the columns in an FSG report
Oracle provides seeded column sets, or you can build your own
Column Set Attributes
-The commonly assumed attributes for a column set are:
-Time period (PTD, QTD, YTD, etc)
-Account type (Actual, Budget or Encumbrance)
-These two elements are defined by an Amount Type

What is Column Set: Proforma

Proforma is an accounting term that refers to an annual fiscal amount that is comprised of actual amounts and budget amounts together.

What is a Content Set?

Content Sets are used to override row/column account assignments and display types to create multiple variations of existing reports You can generate many similar reports in a single run by using content sets Use to a range to give multiple values on one report OR separate reports for each value in the range Override applied separately to each segment in flexfield

What is a Row Order?

Row Orders are used to control how detail rows are displayed and/or sorted on a report Used with Row Set expand and Content Sets Allow user to expand detail of a row Can display Segment value Segment description Both List alphabetically or numerically Report Display Group

Grouping of rows or columns i.e. Specify a sequence number range from your row set or column set i.e. from 20 to 20
Used in your display set Report Display Set

Assign Display Group(s) to your Set i.e. ?EURoedisplay column 1, 3, 4Can assign multiple row and/or column groups to one set i.e.column For each assignment, can specify whether to display the values on the report (Display = Yes) or (Display = No)
The Rule of Thumb for FSG

One FSG = One row set + one column set + one content set

Underline technology of FSG?? any guess , C, SQL, Java...

Important to know FSG reports are created from a program written in 'C' code, not from Oracle Reports, as is the case with standard reports. Many modules and tables are used to create reports based on the definition and component of a particular FSG.

What is FSG (Financial Statement Generator) Controller Program?

The FSG (Financial Statement Generator) Controller runs when you generate an FSG with a content set 'Type' set to Parallel. The FSG Controller kicks off the sub requests for the FSG's that are created with the content set.

What is in Background "The FSG Query

As these report are developed in C, it's impossible to break the code, but if you do some trace on you will find , the code is based out of GL balances table, addition to the some other tables which keeps the structure on the front end.

The data is loaded into memory, and then sorted according to report format

How do you define a FSG Report?

Reports are created by specifying a combination of report components (ie Row Set, Column Set, etc). Typically, values specified when defining a report override values specified at the individual component level
The value entered in the Title field will appear at the top of the report
Where to run FSG Reports:

In Oracle EBS Running FSG Reports can be achieved from 3 different ways:

Run Financial Reports form
Concurrent Manager
The name of the request is "Program -Run Financial Statement Generator"
ADI Request Center

Monday, 13 June 2016

PLSQL PROGRAMS TO PRACTICE 12

150. Create a package to store the following procedure for multiplication table,even-odd, function for factorial and function for palindrome?

CREATE OR REPLACE PACKAGE DATA
IS
PROCEDURE MULT(A NUMBER);
PROCEDURE EVEN_ODD(N NUMBER);
FUNCTION FACT(N NUMBER) RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES(FACT,WNDS);
FUNCTION PALEN(SRT VARCHAR2) RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES(PALEN,WNDS);
END;
/
CREATE OR REPLACE PACKAGE BODY DATA
IS
PROCEDURE MULT(A NUMBER)
IS
M NUMBER;
BEGIN
FOR I IN 1..10
LOOP
M:=A*I;
DBMS_OUTPUT.PUT_LINE(A||'*'||I||'='||M);
END LOOP;
END;
PROCEDURE EVEN_ODD(N NUMBER)
IS
BEGIN
IF MOD(N,2)=0 THEN
DBMS_OUTPUT.PUT_LINE(N||' IS EVEN NUMBER');
ELSE
DBMS_OUTPUT.PUT_LINE(N||' IS NOT EVEN NUMBER');
END IF;
END;
FUNCTION FACT(N NUMBER) RETURN NUMBER
IS
F NUMBER:=1;
BEGIN
FOR I IN 1..N
LOOP
F:=F*I;
END LOOP;
RETURN F;
END;
FUNCTION PALEN(SRT VARCHAR2) RETURN VARCHAR2
IS
S CHAR;
V VARCHAR2(50);
BEGIN
FOR I IN REVERSE 1..LENGTH(SRT)
LOOP
S:=SUBSTR(SRT,I,1);
V:=V||S;
END LOOP;
IF V=SRT THEN
RETURN 'PALINDROME';
ELSE
RETURN 'NOT PALINDROME';
END IF;
END;
END;
/

151.Write a database trigger halt the transaction on Sunday on EMP table

CREATE OR REPLACE TRIGGER SUN_TRI
AFTER INSERT OR UPDATE OR DELETE ON EMP
DECLARE
DY VARCHAR2(200);
BEGIN
DY:=TO_CHAR(SYSDATE,'DY');
IF DY='SUN' THEN
RAISE_APPLICATION_ERROR(-20005,'TODAY IS SUNDAY TRANSACTION NOT ALLOWED TODAY');
END IF;
END;
/

152.Write a database trigger halt the transaction of USER SCOTT on table EMP

CREATE OR REPLACE TRIGGER SCOTT_TRI
BEFORE INSERT OR UPDATE OR DELETE ON EMP
BEGIN
IF USER = 'SCOTT' THEN
RAISE_APPLICATION_ERROR(-20006,'TRANSACTION NOT ALLOWED FOR SCOTT');
END IF;
END;
/

153.Write a database trigger halt the transaction between the the time 6pm to 10am on table emp

CREATE OR REPLACE TRIGGER OVER_TIME_TRI
BEFORE INSERT OR DELETE OR UPDATE ON EMP
DECLARE
T NUMBER;
BEGIN
T:=TO_CHAR(SYSDATE,'HH24');
IF T NOT BETWEEN 10 AND 18 THEN
RAISE_APPLICATION_ERROR(-20007,'TIME ALREADY OVER.....TRANSACTION NOT ALLOWED NOW');
END IF;
END;
/

154.Write a database trigger to halt the transaction for the employee SALESMAN and
PRESIDENT

CREATE OR REPLACE TRIGGER SALES_PRI
BEFORE INSERT OR UPDATE OR DELETE ON EMP
FOR EACH ROW
WHEN (OLD.JOB IN ('SALESMAN','PRESIDENT') OR
NEW.JOB IN ('SALESMAN','PRESIDENT'))
BEGIN
RAISE_APPLICATION_ERROR(-20008,'TRANSACTION NOT ALLOWED FOR SALESMAN AND PRESIDENT....');
END;
/

155.Write a database trigger stroe the username ,type of transaction ,date of transaction and time of transaction of table emp into the table EMP_LOG

CREATE OR REPLACE TRIGGER TRANS_TYPE
AFTER INSERT OR UPDATE OR DELETE ON EMP
DECLARE
V VARCHAR2(50);
BEGIN
IF INSERTING THEN
V:='I';
ELSIF UPDATING THEN
V:='U';
ELSE
V:='D';
END IF;
INSERT INTO EMP_LOG VALUES (USER,V,SYSDATE,TO_CHAR(SYSDATE,'HH:MI:SS'));
END;
/

156.Write a database trigger store the deleted data of EMP table in EMPDEL table

CREATE OR REPLACE TRIGGER DEL_TRI
BEFORE DELETE ON EMP
FOR EACH ROW
BEGIN
INSERT INTO EMPDEL
VALUES (:OLD.EMPNO,:OLD.ENAME,:OLD.JOB,:OLD.MGR,:OLD.HIREDATE,:OLD.SAL,:OLD.COMM,
:OLD.DEPTNO,SYSDATE,TO_CHAR(SYSDATE,'HH:MI:SS'));
END;
/

157.Write a database trigger display the message when the inserting hiredate is greater than system date

CREATE OR REPLACE TRIGGER HIREDATE_OVER
AFTER INSERT ON EMP
FOR EACH ROW
BEGIN
IF :NEW.HIREDATE > SYSDATE THEN
RAISE_APPLICATION_ERROR(-20009,'INVALID HIREDATE.....');
END IF;
END;
/

158.Write a database trigger halt the transaction of EMP table if the deptno is does not exist in the dept table

CREATE OR REPLACE TRIGGER DEPT_NO
BEFORE INSERT OR UPDATE OR DELETE ON EMP
FOR EACH ROW
DECLARE
DNO NUMBER:=0;
BEGIN
SELECT COUNT(*) INTO DNO FROM DEPT WHERE DEPTNO=:NEW.DEPTNO;
DBMS_OUTPUT.PUT_LINE(DNO);
IF DNO=0 THEN
RAISE_APPLICATION_ERROR(-20009,'DEPTNO NOT EXIST IN DEPT TABLE....');
END IF;
END;
/

159.Write a database trigger add Rs 500 if the inserting salary is less than Rs 1000

CREATE OR REPLACE TRIGGER SAL_ADD
BEFORE INSERT ON EMP
FOR EACH ROW
BEGIN
IF :NEW.SAL <= 1000 THEN
:NEW.SAL:=:NEW.SAL+500;
END IF;
END;
/

160.Write a database trigger give the appropriate message if the record exceed more than 100 on EMP table

CREATE OR REPLACE TRIGGER EMP_OVER_REC
AFTER INSERT ON EMP
DECLARE
R NUMBER;
BEGIN
SELECT COUNT(*) INTO R FROM EMP;
IF R>=100 THEN
RAISE_APPLICATION_ERROR(-20009,'100 RECORD ALLOWED IN EMP TABLE.....');
END IF;
END;
/

161.Write a program to month and year and display the Calendar of that month.

DECLARE
D NUMBER:=1;
M VARCHAR2(10):='&MONTH';
Y NUMBER:=&YEAR;
C CHAR(20);
V VARCHAR2(500);
N NUMBER;
BEGIN
N:=TO_CHAR(LAST_DAY(D||'-'||M||'-'||Y),'DD');
C:= TO_CHAR(TO_DATE(D||'-'||M||'-'||Y),'DY');
dbms_output.put_line('*********************************');
dbms_output.put_line('* '||M||'-'||Y||' *');
dbms_output.put_line('*SUN MON TUE WED THU FRI SAT *');
dbms_output.put_line('**********************************');
IF C='MON' THEN
V:=' ';
ELSIF C='TUE' THEN
V:=' ';
ELSIF C='WED' THEN
V:=' ';
ELSIF C='THU' THEN
V:=' ';
ELSIF C='FRI' THEN
V:=' ';
ELSIF C='SAT' THEN
V:=' ';
END IF;
FOR I IN 1..N
LOOP
V:=V||LPAD(I,4);
IF LENGTH(V)=28 THEN
dbms_output.put_line(LPAD(V,29,'*')||' *');
V:=NULL;
END IF;
END LOOP;
dbms_output.put_line('*'||RPAD(V,29)||'*');
END;
/

PLSQL PROGRAMS TO PRACTICE 11

143. Write a function to accept the empno and return exp with minimum 3 decimal?

CREATE OR REPLACE FUNCTION E_DETAILS(EMPNOV NUMBER) RETURN NUMBER
IS
HIREDATEV EMP.HIREDATE%TYPE;
EXP NUMBER(6,3);
BEGIN
SELECT HIREDATE INTO HIREDATEV FROM EMP WHERE EMPNO=EMPNOV;
EXP:=MONTHS_BETWEEN(SYSDATE,HIREDATEV)/12;
RETURN EXP;
END;
/

144. Write a function to accept a number and print the factorial of that number?

CREATE OR REPLACE FUNCTION FAC(NUM NUMBER) RETURN NUMBER
IS
FACT NUMBER(4):=1;
BEGIN
FOR I IN REVERSE 1..NUM
LOOP
FACT:=FACT*I;
END LOOP;
RETURN FACT;
END;
/

145. Write a function to accept a grade and return the number of emps belongs to that grade?

CREATE OR REPLACE FUNCTION EMPGRADE(GRADEV NUMBER) RETURN VARCHAR2
IS
N NUMBER(4);
BEGIN
SELECT COUNT(*) INTO N FROM EMP,SALGRADE
WHERE SAL BETWEEN LOSAL AND HISAL AND GRADE=GRADEV;
RETURN 'NO OF EMPS ARE'||N;
END;
/

146. Write a program to accept the mgr number and return no of emp working at that mgr?

CREATE OR REPLACE FUNCTION N_EMPS(MGRV NUMBER) RETURN VARCHAR2
IS
N NUMBER(4);
BEGIN
SELECT COUNT(*) INTO N FROM EMP WHERE MGR=MGRV;
RETURN 'THE NO OF EMPS ARE WORKING UNDER THIS MGR IS '||N;
END;
/

147. Write a function to accept a character string and print it in reverse case?

CREATE OR REPLACE FUNCTION REVERSE(STR VARCHAR2) RETURN VARCHAR2
IS
STR1 VARCHAR2(20);
S VARCHAR2(20);
N NUMBER(4);
BEGIN
FOR I IN 1..LENGTH(STR)
LOOP
S:=SUBSTR(STR,I,1);
N:=ASCII(S);
IF N BETWEEN 65 AND 90 THEN
STR1:=STR1||CHR(N+32);
ELSE
STR1:=STR1||CHR(N-32);
END IF;
END LOOP;
RETURN 'THE REVERSE CASE IS '||STR1;
END;
/

148. Write a function to accept a string and check whether it is palindrome or not?

CREATE OR REPLACE FUNCTION STRPAL1(STR VARCHAR2) RETURN VARCHAR2
IS
STR1 VARCHAR2(10);
S VARCHAR2(10);
BEGIN
FOR I IN REVERSE 1..LENGTH(STR)
LOOP
S:=SUBSTR(STR,I,1);
STR1:=STR1||S;
END LOOP;
IF STR1=STR THEN
RETURN 'IT IS PALINDROME '||STR1;
ELSE
RETURN 'IT IS NOT PALINDROME '||STR1;
END IF;
END;
/

149. Write a function to accept the grade and return max, tot, avg salary and number of emps belongs to that grade as script without using any group functions?

CREATE OR REPLACE FUNCTION EMP_DETAILS_SCRIPT (GRADEV SALGRADE.GRADE%TYPE) RETURN VARCHAR2
IS
V VARCHAR2(30000);
CURSOR EMP_CUR IS
SELECT EMP.*,GRADE,DNAME FROM DEPT,EMP,SALGRADE
WHERE GRADE=GRADEV AND EMP.DEPTNO=DEPT.DEPTNO AND
SAL BETWEEN LOSAL AND HISAL;
EMP_CUR_V EMP_CUR%ROWTYPE;
MAXSAL EMP.SAL%TYPE:=0;
MINSAL EMP.SAL%TYPE;
AVGSAL NUMBER(6,2);
SUMSAL NUMBER(10,2):=0;
CNT NUMBER:=0;
FLAG CHAR:=0;
EX EXCEPTION;
BEGIN
OPEN EMP_CUR;
LOOP
FETCH EMP_CUR INTO EMP_CUR_V;
EXIT WHEN EMP_CUR%NOTFOUND;
IF MAXSAL < EMP_CUR_V.SAL THEN
MAXSAL:=EMP_CUR_V.SAL;
END IF;
IF FLAG=0 THEN
MINSAL:=EMP_CUR_V.SAL;
FLAG:=1;
ELSIF FLAG=1 AND MINSAL > EMP_CUR_V.SAL THEN
MINSAL:=EMP_CUR_V.SAL;
END IF;
SUMSAL:=SUMSAL+EMP_CUR_V.SAL;
CNT:=CNT+1;
ENDLOOP;
IF CNT=0 THEN
RAISE EX;
END IF;
AVGSAL:=SUMSAL/CNT;
V:='THE MAXIMUM SALARY OF GRADE' ||GRADEV||' IS'||MAXSAL||' MINIMUM SALARY IS'||MINSAL||
'AVERAGE SALARY IS'||AVGSAL||' TOTAL EMPS WORKING FOR THIS GRADE ARE'||CNT;
CLOSE EMP_CUR;
RETURN V;
EXCEPTION
WHEN EX THEN
RETURN 'THERE IS NO EMPLOYEE WORKING FOR THIS GRADE, CHECK AND RE-ENTER THE GRADE....';
END;
/

PLSQL PROGRAMS TO PRACTICE 10

131. Write a procedure to create your own print statement?

CREATE OR REPLACE PROCEDURE PRINT(V VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(V);
END;
/

132. Write a procedure to accept the deptno as parameter and display the details of that dept also display the total salary, no of employees, max sal and avg sal?

CREATE OR REPLACE PROCEDURE EMPPRO(DEPTNOV NUMBER)
IS
CURSOR A IS
SELECT * FROM EMP WHERE DEPTNO=DEPTNOV;
B A%ROWTYPE;
NOE NUMBER:=0;
TOT NUMBER:=0;
AVGS NUMBER(7,2):=0;
MAXS NUMBER(7,2):=0;
BEGIN
OPEN A;
LOOP
FETCH A INTO B;
EXIT WHEN A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMPNO :'||B.EMPNO);
DBMS_OUTPUT.PUT_LINE('ENAME :'||B.ENAME);
DBMS_OUTPUT.PUT_LINE('JOB :'||B.JOB);
DBMS_OUTPUT.PUT_LINE('SAL :'||B.SAL);
DBMS_OUTPUT.PUT_LINE('HIREDATE :'||B.HIREDATE);
DBMS_OUTPUT.PUT_LINE('COMM :'||B.COMM);
DBMS_OUTPUT.PUT_LINE('**********************');
TOT:=TOT+B.SAL;
NOE:=NOE+1;
IF B.SAL>MAXS THEN
MAXS:=B.SAL;
END IF;
END LOOP;
AVGS:=TOT/NOE;
DBMS_OUTPUT.PUT_LINE('NO OF EMPLOYEE :'||NOE);
DBMS_OUTPUT.PUT_LINE('TOTAL SALARY :'||TOT);
DBMS_OUTPUT.PUT_LINE('AVG SALARY :'||AVGS);
DBMS_OUTPUT.PUT_LINE('MAX SALARY :'||MAXS);
CLOSE A;
END;
/

133. Write a procedure to accept two different numbers and print all odd numbers between the two given numbers?

CREATE OR REPLACE PROCEDURE ODDNO(A NUMBER,B NUMBER)
IS
N NUMBER(4);
BEGIN
N:=A;
WHILE N<B
LOOP
IF MOD(N,2)!=0 THEN
DBMS_OUTPUT.PUT_LINE(N);
END IF;
N:=N+1;
END LOOP;
END;
/

134. Write a procedure to accept two different numbers and print even numbers between the two given numbers?

CREATE OR REPLACE PROCEDURE EVENNO(A NUMBER,B NUMBER)
IS
N NUMBER(4);
BEGIN
N:=A;
WHILE N<B
LOOP
IF MOD(N,2)=0 THEN
DBMS_OUTPUT.PUT_LINE(N);
END IF;
N:=N+1;
END LOOP;
END;
/

135. Write a procedure to accept deptno as input and print the details of emps along with grade?

CREATE OR REPLACE PROCEDURE EMP_DETAIL(DEPTNOV NUMBER)
IS
CURSOR A IS
SELECT EMP.*,GRADE FROM EMP,SALGRADE
WHERE SAL BETWEEN LOSAL AND HISAL
AND DEPTNO=DEPTNOV;
B A%ROWTYPE;
BEGIN
OPEN A;
LOOP
FETCH A INTO B;
EXIT WHEN A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMPNO IS '||B.EMPNO);
DBMS_OUTPUT.PUT_LINE('ENAME IS '||B.ENAME);
DBMS_OUTPUT.PUT_LINE('JOB IS '||B.JOB);
DBMS_OUTPUT.PUT_LINE('SAL IS '||B.SAL);
DBMS_OUTPUT.PUT_LINE('DEPTNO IS '||B.DEPTNO);
DBMS_OUTPUT.PUT_LINE('GRADE IS '||B.GRADE);
END LOOP;
CLOSE A;
END;
/

136. Write a procedure to accept a number as parameter and print its multiplication table?

CREATE OR REPLACE PROCEDURE MULT(A NUMBER)
IS
B NUMBER(2) DEFAULT 1;
C NUMBER(3);
BEGIN
WHILE B<=10
LOOP
C:=A*B;
DBMS_OUTPUT.PUT_LINE(A||'*'||B||'='||C);
B:=B+1;
END LOOP;
END;
/

137. Write a procedure to accept two different numbers as input and print all even numbers and odd numbers in between them in two different horizontal lines?

CREATE OR REPLACE PROCEDURE EVENODD(A NUMBER,B NUMBER)
IS
N NUMBER;
EV VARCHAR2(1000);
OD VARCHAR2(1000);
BEGIN
N:=A;
WHILE N<B
LOOP
IF MOD(N,2)!=0 THEN
OD:=OD||' '||N;
ELSE
EV:=EV||' '||N;
END IF;
N:=N+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('THE ODD NOS ARE '||OD);
DBMS_OUTPUT.PUT_LINE('THE EVEN NOS ARE '||EV);
END;
/

138. Write a procedure to accept a string and check whether it is palindrome or not?

CREATE OR REPLACE PROCEDURE STRPAL(STR VARCHAR2)
IS
STR1 VARCHAR2(10);
S VARCHAR2(10);
BEGIN
FOR I IN REVERSE 1..LENGTH(STR)
LOOP
S:=SUBSTR(STR,I,1);
STR1:=STR1||S;
END LOOP;
IF STR1=STR THEN
DBMS_OUTPUT.PUT_LINE('IT IS PALINDROME '||STR1);
ELSE
DBMS_OUTPUT.PUT_LINE('IT IS NOT PALINDROME '||STR1);
END IF;
END;
/

139. Write a procedure to accept a string and print it in reverse order?

CREATE OR REPLACE PROCEDURE STRREV(STR VARCHAR2)
IS
STR1 VARCHAR2(10);
S VARCHAR2(10);
BEGIN
FOR I IN REVERSE 1..LENGTH(STR)
LOOP
S:=SUBSTR(STR,I,1);
STR1:=STR1||S;
END LOOP;
DBMS_OUTPUT.PUT_LINE('ORIGINAL '||STR);
DBMS_OUTPUT.PUT_LINE('REVERSE '||STR1);
END;
/

140. Write a procedure to accept the empno and print all the details of emp along with exp, grade and loc?

CREATE OR REPLACE PROCEDURE EMP_DET(EMPNOV NUMBER)
IS
EXP NUMBER(6,2);
E EMP%ROWTYPE;
GRADEV SALGRADE.GRADE%TYPE;
LOCV DEPT.LOC%TYPE;
BEGIN
SELECT EMP.* INTO E FROM EMP WHERE EMPNO=EMPNOV;
SELECT LOC INTO LOCV FROM DEPT WHERE DEPT.DEPTNO=E.DEPTNO;
SELECT GRADE INTO GRADEV FROM SALGRADE WHERE E.SAL BETWEEN LOSAL AND HISAL;
EXP:=MONTHS_BETWEEN(SYSDATE,E.HIREDATE)/12;
DBMS_OUTPUT.PUT_LINE('EMPNO IS '||E.EMPNO);
DBMS_OUTPUT.PUT_LINE('ENAME IS '||E.ENAME);
DBMS_OUTPUT.PUT_LINE('SAL IS '||E.SAL);
DBMS_OUTPUT.PUT_LINE('JOB IS '||E.JOB);
DBMS_OUTPUT.PUT_LINE('LOC IS '||LOCV);
DBMS_OUTPUT.PUT_LINE('GRADE IS '||GRADEV);
DBMS_OUTPUT.PUT_LINE('EXP IS '||EXP);
END;
/

141. Write a procedure to accept dname irrespective of case and print all the details of emps?

CREATE OR REPLACE PROCEDURE DETAILS(DNAMEV VARCHAR2)
IS
CURSOR A IS
SELECT EMP.*,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND DNAME=DNAMEV;
B A%ROWTYPE;
BEGIN
OPEN A;
LOOP
FETCH A INTO B;
EXIT WHEN A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMPNO IS '||B.EMPNO);
DBMS_OUTPUT.PUT_LINE('ENAME IS '||B.ENAME);
DBMS_OUTPUT.PUT_LINE('SAL IS '||B.SAL);
DBMS_OUTPUT.PUT_LINE('JOB IS '||B.JOB);
DBMS_OUTPUT.PUT_LINE('DNAME IS '||B.DNAME);
DBMS_OUTPUT.PUT_LINE('HIREDATE IS '||B.HIREDATE);
END LOOP;
END;
/

142. Write a procedure to accept a string and print it in reverse case?

CREATE OR REPLACE PROCEDURE S_R_CASE(STR VARCHAR2)
IS
S VARCHAR2(10);
V VARCHAR2(10);
N NUMBER(3);
BEGIN
FOR I IN 1..LENGTH(STR)
LOOP
S:=SUBSTR(STR,I,1);
N:=ASCII(S);
IF N BETWEEN 65 AND 90 THEN
V:=V||CHR(N+32);
ELSE
V:=V||CHR(N-32);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('STRING IN REVERSE CASE IS '||V);
END;
/

PLSQL PROGRAMS TO PRACTICE 9

121. Write a program to accept a year and display the emps belongs to that year?

DECLARE

Y NUMBER(4):=&YEAR;

CURSOR YEAR IS

SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'YYYY')=Y;

B YEAR%ROWTYPE;

BEGIN

OPEN YEAR;

LOOP

FETCH YEAR INTO B;

EXIT WHEN YEAR%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);

DBMS_OUTPUT.PUT_LINE('EMP NAME IS ' || B.ENAME);

DBMS_OUTPUT.PUT_LINE('EMP SAL IS ' || B.SAL);

DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || B.HIREDATE);

DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);

DBMS_OUTPUT.PUT_LINE('*************************');

END LOOP;

CLOSE YEAR;

END;

122.Write a program to accept a mgr and display who are working under that mgr?

DECLARE

MGRV NUMBER(4):=&MGR;

CURSOR AMGR IS

SELECT * FROM EMP WHERE MGR=MGRV;

B AMGR%ROWTYPE;

BEGIN

OPEN AMGR;

LOOP

FETCH AMGR INTO B;

EXIT WHEN AMGR%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);

DBMS_OUTPUT.PUT_LINE('EMP NAME IS ' || B.ENAME);

DBMS_OUTPUT.PUT_LINE('EMP SAL IS ' || B.SAL);

DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || B.HIREDATE);

DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);

DBMS_OUTPUT.PUT_LINE('*************************');

END LOOP;

CLOSE AMGR;

END;

123. Write a program to accept the grade and display emps belongs to that grade?

DECLARE

GRADEV SALGRADE.GRADE%TYPE:=&GRADE;

CURSOR A IS

SELECT EMP.*,GRADE FROM EMP,SALGRADE WHERE SAL BETWEEN LOSAL AND HISAL AND GRADE=GRADEV;

B A%ROWTYPE;

BEGIN

OPEN A;

LOOP

FETCH A INTO B;

EXIT WHEN A%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);

DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);

DBMS_OUTPUT.PUT_LINE('SAL IS ' || B.SAL);

DBMS_OUTPUT.PUT_LINE('MGR NO IS ' || B.MGR);

DBMS_OUTPUT.PUT_LINE('COMM IS ' || B.COMM);

DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || B.HIREDATE);

DBMS_OUTPUT.PUT_LINE('GRADE IS ' || B.GRADE);

DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);

DBMS_OUTPUT.PUT_LINE('*************************');

END LOOP;

CLOSE A;

END;

124. Write a program to accept a deptno and display who are working in that dept?

DECLARE

DEPTV EMP.DEPTNO%TYPE:=&DEPTNO;

CURSOR A IS

SELECT * FROM EMP WHERE DEPTNO=DEPTV;

B A%ROWTYPE;

BEGIN

OPEN A;

LOOP

FETCH A INTO B;

EXIT WHEN A%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);

DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);

DBMS_OUTPUT.PUT_LINE('SAL IS ' || B.SAL);

DBMS_OUTPUT.PUT_LINE('MGR NO IS ' || B.MGR);

DBMS_OUTPUT.PUT_LINE('COMM IS ' || B.COMM);

DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || B.HIREDATE);

DBMS_OUTPUT.PUT_LINE('DEPTNO IS ' || B.DEPTNO);

DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);

DBMS_OUTPUT.PUT_LINE('*************************');

END LOOP;

CLOSE A;

END;

125. Write a program to display all the information of emp table?

DECLARE

CURSOR A IS

SELECT * FROM EMP;

B A%ROWTYPE;

BEGIN

OPEN A;

LOOP

FETCH A INTO B;

EXIT WHEN A%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);

DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);

DBMS_OUTPUT.PUT_LINE('SAL IS ' || B.SAL);

DBMS_OUTPUT.PUT_LINE('MGR NO IS ' || B.MGR);

DBMS_OUTPUT.PUT_LINE('COMM IS ' || B.COMM);

DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || B.HIREDATE);

DBMS_OUTPUT.PUT_LINE('DEPTNO IS ' || B.DEPTNO);

DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);

DBMS_OUTPUT.PUT_LINE('*************************');

END LOOP;

CLOSE A;

END;

126. Write a program to accept the location and display empno, name, sal , date of join and also display the total salary, avg salary and no of emps?

DECLARE

LOCV DEPT.LOC%TYPE:='&LOC';

TOT NUMBER(10,2):=0;

ASAL NUMBER(10,2):=0;

NOEMPS NUMBER(5):=0;

CURSOR A IS

SELECT EMP.*,LOC FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND LOC=LOCV;

B A%ROWTYPE;

BEGIN

OPEN A;

LOOP

FETCH A INTO B;

NOEMPS:=NOEMPS+1;

TOT:=TOT+B.SAL;

ASAL:=TOT/NOEMPS;

EXIT WHEN A%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);

DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);

DBMS_OUTPUT.PUT_LINE('SAL IS ' || B.SAL);

DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || B.HIREDATE);

DBMS_OUTPUT.PUT_LINE('DEPTNO IS ' || B.DEPTNO);

DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);

DBMS_OUTPUT.PUT_LINE('LOC IS ' || B.LOC);

DBMS_OUTPUT.PUT_LINE('TOT IS ' || TOT);

DBMS_OUTPUT.PUT_LINE('NOEMPS IS ' || NOEMPS);

DBMS_OUTPUT.PUT_LINE('ASAL IS ' || ASAL);

DBMS_OUTPUT.PUT_LINE('*************************');

END LOOP;

CLOSE A;

END;

127. Write a program to accept a range of salary (that is lower boundary and higher boundary) and print the details of emps along with loc,grade and exp?

DECLARE

LOSALV SALGRADE.LOSAL%TYPE:=&LOSAL;

HISALV SALGRADE.HISAL%TYPE:=&HISAL;

EXP NUMBER(5,2);

CURSOR A IS

SELECT EMP.*,LOC,GRADE FROM EMP,DEPT,SALGRADE WHERE EMP.DEPTNO=DEPT.DEPTNO

AND SAL BETWEEN LOSALV AND HISALV

AND SAL BETWEEN LOSAL AND HISAL;

B A%ROWTYPE;

BEGIN

OPEN A;

LOOP

FETCH A INTO B;

EXIT WHEN A%NOTFOUND;

EXP:=MONTHS_BETWEEN(SYSDATE,B.HIREDATE)/12;

DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);

DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);

DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);

DBMS_OUTPUT.PUT_LINE('LOC IS ' || B.LOC);

DBMS_OUTPUT.PUT_LINE('EXP IS ' || EXP);

DBMS_OUTPUT.PUT_LINE('GRADE IS ' || B.GRADE);

DBMS_OUTPUT.PUT_LINE('*************************');

END LOOP;

CLOSE A;

END;

128. Write a program to print all the details of emps accepting the job?

DECLARE

JOBV EMP.JOB%TYPE:='&JOB';

CURSOR A IS

SELECT * FROM EMP WHERE JOB=JOBV;

B A%ROWTYPE;

BEGIN

OPEN A;

LOOP

FETCH A INTO B;

EXIT WHEN A%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);

DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);

DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);

DBMS_OUTPUT.PUT_LINE('*************************');

END LOOP;

CLOSE A;

END;

129. Write a program to display the details of emps year wise?

DECLARE

CURSOR YEARS IS

SELECT DISTINCT TO_CHAR(HIREDATE,'YYYY') YEARS1 FROM EMP ORDER BY 1;

YEAR YEARS%ROWTYPE;

CURSOR A IS

SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'YYYY')=YEAR.YEARS1;

B A%ROWTYPE;

BEGIN

DBMS_OUTPUT.ENABLE(10000);

OPEN YEARS;

DBMS_OUTPUT.PUT_LINE('********************');

LOOP

FETCH YEARS INTO YEAR;

EXIT WHEN YEARS%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('YEAR :' || YEAR.YEARS1);

DBMS_OUTPUT.PUT_LINE('**********************');

OPEN A;

LOOP

FETCH A INTO B;

EXIT WHEN A%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('EMPNO IS ' || B.EMPNO);

DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);

DBMS_OUTPUT.PUT_LINE('SALARY IS ' || B.SAL);

DBMS_OUTPUT.PUT_LINE('JOB IS ' || B.JOB);

DBMS_OUTPUT.PUT_LINE('DEPTNO IS ' || B.DEPTNO);

DBMS_OUTPUT.PUT_LINE('*************************');

END LOOP;

CLOSE A;

END LOOP;

CLOSE YEARS;

END;

130. Write a program to accept empno and print all the details along with loc and grade?

DECLARE

EMPNOV EMP.EMPNO%TYPE:=&EMPNO;

CURSOR A IS

SELECT EMP.*,GRADE,LOC FROM EMP,DEPT,SALGRADE

WHERE EMP.DEPTNO=DEPT.DEPTNO

AND SAL BETWEEN LOSAL AND HISAL AND EMPNO=EMPNOV;

B A%ROWTYPE;

BEGIN

OPEN A;

LOOP

FETCH A INTO B;

EXIT WHEN A%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('EMPNO IS ' || B.EMPNO);

DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);

DBMS_OUTPUT.PUT_LINE('SALARY IS ' || B.SAL);

DBMS_OUTPUT.PUT_LINE('JOB IS ' || B.JOB);

DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || B.HIREDATE);

DBMS_OUTPUT.PUT_LINE('LOC IS ' || B.LOC);

DBMS_OUTPUT.PUT_LINE('GRADE IS ' || B.GRADE);

DBMS_OUTPUT.PUT_LINE('*************************');

END LOOP;

CLOSE A;

END;

PLSQL PROGRAMS TO PRACTICE 8

102.WAP to accept 2 nos and find the sum and product of the nos and print the output

DECLARE
A NUMBER:=&A;
B NUMBER:=&B;
S NUMBER;
M NUMBER;
BEGIN
S:=A+B;
M:=A*B;
DBMS_OUTPUT.PUT_LINE('SUM OF '||'A'||' AND '||'B'||' IS '||S);
DBMS_OUTPUT.PUT_LINE('PRODUCT OF '||'A'||' AND '||'B'||' IS '||M);
END;
/

103.WAP to accept 2 nos and find the remainder when the first number is divided by sencond(dont use mod function)

DECLARE
A NUMBER:=&A;
B NUMBER:=&B;
C NUMBER;
M NUMBER;
BEGIN
C:=TRUNC(A/B);
M:=A-C*B;
DBMS_OUTPUT.PUT_LINE('REMAINDER IS '||M);
END;
/

104.WAP to display all the ASCII characters 0-9--48-57,A-Z--65-90,a-z--97-122

BEGIN
FOR I IN 1..255
LOOP
DBMS_OUTPUT.PUT_LINE(I||'-'||CHR(I));
END LOOP;
END;
/

105.Print the following format
ORACLE
ORACL
ORAC
ORA
OR
O

DECLARE
STR VARCHAR2(10):='&STR';
L VARCHAR2(10);
N NUMBER(15);
BEGIN
N:=LENGTH(STR);
WHILE N>=1
LOOP
L:=SUBSTR(STR,1,N);
N:=N-1;
DBMS_OUTPUT.PUT_LINE(L);
END LOOP;
END;
/

106.WAP to display "GOOD MORNING" or "GOOD AFTERNOON" or "GOOD NIGHT" depending upon the current time

DECLARE
HH NUMBER;
BEGIN
HH:=TO_CHAR(SYSDATE,'HH24');
IF HH>6 AND HH<12 THEN
DBMS_OUTPUT.PUT_LINE('GOOD MORNING');
ELSIF HH>=12 AND HH<18 THEN
DBMS_OUTPUT.PUT_LINE('GOOD AFTERNOON');
ELSIF HH>=18 AND HH<25 THEN
DBMS_OUTPUT.PUT_LINE('GOOD NIGHT');
END IF;
END;
/

107.WAP to accept two strings and concat the two strings

DECLARE
STR VARCHAR2(20):='&STR';
STR1 VARCHAR2(20):='&STR1';
V VARCHAR2(40);
BEGIN
V:=STR||''||STR1;
DBMS_OUTPUT.PUT_LINE(V);
END;
/

108.WAP to accept a string and count the no of chars,words in that string

DECLARE
STR VARCHAR2(20):='&STR';
NOC NUMBER(4):=0;
NOW NUMBER(4):=1;
S CHAR;
BEGIN
FOR I IN 1..LENGTH(STR)
LOOP
S:=SUBSTR(STR,I,1);
NOC:=NOC+1;
IF S=' ' THEN
NOW:=NOW+1;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('THE NO. OF CHARS '||NOC);
DBMS_OUTPUT.PUT_LINE('THE NO. OF WORDS '||NOW);
END;
/

109.WAP to accept the octal number and print it in decimal format

DECLARE
N VARCHAR2(20):='&N';
A NUMBER;
P NUMBER:=0;
C CHAR;
BEGIN
A:=LENGTH(N);
FOR I IN 1..A
LOOP
C:=SUBSTR(N,I,1);
P:=P+C*POWER(8,A-I);
END LOOP;
DBMS_OUTPUT.PUT_LINE('THE INTEGER OF A GIVEN OCTAL IS '||P);
END;
/

110.WAP to accept the mgr and find how many emps are working under that mgr

DECLARE
MGRV EMP.MGR%TYPE:=&MGRNO;
N NUMBER:=0;
BEGIN
SELECT COUNT(*) INTO N FROM EMP WHERE MGR=MGRV;
DBMS_OUTPUT.PUT_LINE('NUMBER OF EMPLOYEE UNDER THAT MANAGER ARE '||N);
END;
/

111.WAP to accept the empno and update the employee row on the following
If sal < 2600 then sal=sal+10% of sal make the changes in the emp table

DECLARE
EMPNOV EMP.EMPNO%TYPE:=&EMPNO;
SALV NUMBER(7,2):=0;
BEGIN
SELECT SAL INTO SALV FROM EMP WHERE EMPNO=EMPNOV;
IF SALV < 2600 THEN
SALV:=SALV+SALV*(10/100);
END IF;
UPDATE EMP SET SAL=SALV WHERE EMPNO=EMPNOV;
DBMS_OUTPUT.PUT_LINE('EMPNO IS '||EMPNOV);
DBMS_OUTPUT.PUT_LINE('SAL IS '||SALV);
END;
/

112.Write the floyds triangle
1
2 3
4 5 6
7 8 9 10
11 12 13 14 15
16 17 18 19 20 21
...............
79..............91

DECLARE
N NUMBER:=1;
V VARCHAR2(100);
BEGIN
FOR I IN 1..92
LOOP
FOR J IN 1..I
LOOP
V:=V||' '||N;
N:=N+1;
EXIT WHEN N=92;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
EXIT WHEN N=92;
V:=NULL;
END LOOP;
END;
/

113.WAP to accept the real value and print integer value only

DECLARE
N NUMBER(7,3):=&N;
A NUMBER(5);
BEGIN
A:=TRUNC(N);
DBMS_OUTPUT.PUT_LINE('REAL VALUE IS '||A);
END;
/

114.WAP to calculate the sum of n odd factorials

DECLARE
N NUMBER:=&N;
S NUMBER:=0;
F NUMBER:=1;
BEGIN
FOR I IN 1..N
LOOP
IF MOD(I,2)!=0 THEN
FOR J IN 1..I
LOOP
F:=F*J;
END LOOP;
S:=S+F;
F:=1;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('SUM '||S);
END;
/

115.WAP to calculate the sum of n even factorials

DECLARE
N NUMBER:=&N;
S NUMBER:=0;
F NUMBER:=1;
BEGIN
FOR I IN 1..N
LOOP
IF MOD(I,2)=0 THEN
FOR J IN 1..I
LOOP
F:=F*J;
END LOOP;
S:=S+F;
F:=1;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('SUM '||S);
END;
/

116.WAP to generate the nos which are prime and odd between 1 and 100

DECLARE
N NUMBER;
CNT NUMBER:=0;
BEGIN
FOR I IN 1..100
LOOP
FOR J IN 1..I
LOOP
IF MOD(I,J)=0 THEN
CNT:=CNT+1;
END IF;
END LOOP;
IF CNT <= 2 THEN
IF MOD(I,2)!=0 THEN
DBMS_OUTPUT.PUT_LINE(I);
END IF;
END IF;
CNT:=0;
END LOOP;
END;
/

117.Write a program to generate following series
12
12 22
12 22 32
12 22 32 42
12 22 32 42 52

DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN 1..5
LOOP
FOR J IN 1..I
LOOP
V:=V||' '||J||CHR(178);
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
/

118.Find the roots of a quadratic equation

DECLARE
A NUMBER(4):=&A;
B NUMBER(4):=&B;
C NUMBER(4):=&C;
D NUMBER(8,2);
R1 NUMBER(8,2);
R2 NUMBER(8,2);
BEGIN
D:=POWER(B,2)-4*A*C;
IF D = 0 THEN
DBMS_OUTPUT.PUT_LINE('ROOTS ARE EQUAL');
ELSIF D > 0 THEN
R1:=(-B+SQRT(D))/2*A;
R2:=(-B-SQRT(D))/2*A;
DBMS_OUTPUT.PUT_LINE('FIRST ROOT IS '||R1);
DBMS_OUTPUT.PUT_LINE('SECOND ROOT IS '||R2);
ELSE
DBMS_OUTPUT.PUT_LINE('ROOTS ARE IMAGINARY');
END IF;
END;
/

119.WAP to accept the 2 diff nos, assume that first one is smaller and second one is highest value then print the all even nos in between them horizontally

DECLARE
A NUMBER:=&A;
B NUMBER:=&B;
V VARCHAR2(100);
BEGIN
FOR I IN A..B
LOOP
IF MOD(I,2)=0 THEN
V:=V||' '||I;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
END;
/

120.WAP to accept two diff nos assume that first one is smaller and second one is highest value then print the all odd nos in between them horizontally

DECLARE
A NUMBER:=&A;
B NUMBER:=&B;
V VARCHAR2(100);
BEGIN
FOR I IN A..B
LOOP
IF MOD(I,2)!=0 THEN
V:=V||' '||I;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
END;
/