d2k_11am

5)Report Trigger:-
Report triggers execute PL/SQL functions at specific times during
the execution and formatting of your report.
Note:-
We can not create new global report triggers.
Report Builder has five global report triggers.

11)Before parameter Form trigger

2)After parameter Form trigger

3)Before Report

4)Between Pages

5)After Report
Note:-
Any processing that will effect the data retrieved by the report should be
performed in the before form or after form trigger.
1)Before parameter Form trigger:-
Fires before the report is executed but after queries
are parsed and data is fetched.
2)After parameter Form trigger:-
Fires after you exit the previewer,or after a report output is sent to a
specified destination.

d2k_nov15

function F_SALFormatTrigger return boolean
is
begin
if :sal >:p_sal then
srw.message(100,’the sal is below’||:p_sal);
–it is used dispaly message
srw.set_foreground_border_color(‘red’);
–it used fill colour round border
srw.set_border_pattern(‘solid’);
–draw brower round the value cell.
srw.set_foreground_fill_color(‘magenta’);
srw.set_fill_pattern(‘solid’);
–it is used set colour for cells.
end if;
return (true);
end;

Validation Trigger:-
————————–
Validation Trigger are PL/SQL functions
that are executed when parameter values are specified on the command
line when you accept the runtime parameter form.
Validation triggers are also used to validate the initial value of the
parameter in the parameter property pallet.
1)OB
2)PL/SQL program unit
3)parameter Property pallet.
Generate report
accepts ‘mon’ name from user if any employee
joined in that month display output if not
Give error message
‘There is no employees in this month’
The cursor has to go same parameter filed only
Steps:-
1)Create one report
2)create one parameter p_mon
3)Go to property of parameter
uncheck the check box
(I.c Restrict List to predetermined values)
4)click on validation trigger property to define a
validation trigger.
5)write the code here.

select empno,ename,job,hiredate,sal,deptno,comm
from emp
where upper(to_char(hiredate,’mon’))=upper(:p_mon)

function P_monValidTrigger
return boolean is
v_mon varchar2(3);
begin
select unique to_char(hiredate,’mon’) into v_mon
from emp
where to_char(hiredate,’mon’)=:P_mon;
return (TRUE);
exception
when no_data_found then
srw.message(101,:p_mon||’month no emps’);
end;

ora11am_nov14

SQL> desc student
Name Null? Type
—————————————– ——– —————————-
SID NUMBER(4)
FNAME VARCHAR2(10)
LNAME CHAR(10)
DOB DATE

SQL> alter table student add
2
SQL> ed
Wrote file afiedt.buf

1* alter table student add
2
SQL> ed
Wrote file afiedt.buf

1* alter table student add doj date
2 /

Table altered.

SQL> ed
Wrote file afiedt.buf

1* alter table student add(fee number(7,2),gender char)
2 /

Table altered.

SQL> desc student
Name Null? Type
—————————————– ——– —————————-
SID NUMBER(4)
FNAME VARCHAR2(10)
LNAME CHAR(10)
DOB DATE
DOJ DATE
FEE NUMBER(7,2)
GENDER CHAR(1)

SQL> ed
Wrote file afiedt.buf

1* alter table student add(fee number(7,2),gender char)
SQL> ed
Wrote file afiedt.buf

1* alter table student add(fee number(7,2),gender char)
SQL>
SQL> ed
Wrote file afiedt.buf

1* alter table student add(fee number(7,2),gender char)
SQL>
SQL> cl scr

SQL> desc student
Name Null? Type
—————————————– ——– —————————-
SID NUMBER(4)
FNAME VARCHAR2(10)
LNAME CHAR(10)
DOB DATE
DOJ DATE
FEE NUMBER(7,2)
GENDER CHAR(1)

SQL> CREATE TABLE EMP
2 (EMPNO NUMBER(4) NOT NULL,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2));

Table created.

SQL>
SQL> INSERT INTO EMP VALUES
2 (7369, ‘SMITH’, ‘CLERK’, 7902,
3 TO_DATE(’17-DEC-1980′, ‘DD-MON-YYYY’), 800, NULL, 20);
INSERT INTO EMP VALUES
*
ERROR at line 1:
ORA-01950: no privileges on tablespace ‘USERS’
SQL> INSERT INTO EMP VALUES
2 (7499, ‘ALLEN’, ‘SALESMAN’, 7698,
3 TO_DATE(’20-FEB-1981′, ‘DD-MON-YYYY’), 1600, 300, 30);
INSERT INTO EMP VALUES
*
ERROR at line 1:
ORA-01950: no privileges on tablespace ‘USERS’
SQL> INSERT INTO EMP VALUES
2 (7521, ‘WARD’, ‘SALESMAN’, 7698,
3 TO_DATE(’22-FEB-1981′, ‘DD-MON-YYYY’), 1250, 500, 30);
INSERT INTO EMP VALUES
*
ERROR at line 1:
ORA-01950: no privileges on tablespace ‘USERS’
SQL> INSERT INTO EMP VALUES
2 (7566, ‘JONES’, ‘MANAGER’, 7839,
3 TO_DATE(‘2-APR-1981’, ‘DD-MON-YYYY’), 2975, NULL, 20);
INSERT INTO EMP VALUES
*
ERROR at line 1:
ORA-01950: no privileges on tablespace ‘USERS’
SQL> INSERT INTO EMP VALUES
2 (7654, ‘MARTIN’, ‘SALESMAN’, 7698,
3 TO_DATE(’28-SEP-1981′, ‘DD-MON-YYYY’), 1250, 1400, 30);
INSERT INTO EMP VALUES
*
ERROR at line 1:
ORA-01950: no privileges on tablespace ‘USERS’
SQL> INSERT INTO EMP VALUES
2 (7698, ‘BLAKE’, ‘MANAGER’, 7839,
3 TO_DATE(‘1-MAY-1981’, ‘DD-MON-YYYY’), 2850, NULL, 30);
INSERT INTO EMP VALUES
*
ERROR at line 1:
ORA-01950: no privileges on tablespace ‘USERS’
SQL> INSERT INTO EMP VALUES
2 (7782, ‘CLARK’, ‘MANAGER’, 7839,
3 TO_DATE(‘9-JUN-1981’, ‘DD-MON-YYYY’), 2450, NULL, 10);
INSERT INTO EMP VALUES
*
ERROR at line 1:
ORA-01950: no privileges on tablespace ‘USERS’
SQL> INSERT INTO EMP VALUES
2 (7788, ‘SCOTT’, ‘ANALYST’, 7566,
3 TO_DATE(’09-DEC-1982′, ‘DD-MON-YYYY’), 3000, NULL, 20);
INSERT INTO EMP VALUES
*
ERROR at line 1:
ORA-01950: no privileges on tablespace ‘USERS’
SQL> INSERT INTO EMP VALUES
2 (7839, ‘KING’, ‘PRESIDENT’, NULL,
3 TO_DATE(’17-NOV-1981′, ‘DD-MON-YYYY’), 5000, NULL, 10);
INSERT INTO EMP VALUES
*
ERROR at line 1:
ORA-01950: no privileges on tablespace ‘USERS’
SQL> INSERT INTO EMP VALUES
2 (7844, ‘TURNER’, ‘SALESMAN’, 7698,
3 TO_DATE(‘8-SEP-1981’, ‘DD-MON-YYYY’), 1500, 0, 30);
INSERT INTO EMP VALUES
*
ERROR at line 1:
ORA-01950: no privileges on tablespace ‘USERS’
SQL> INSERT INTO EMP VALUES
2 (7876, ‘ADAMS’, ‘CLERK’, 7788,
3 TO_DATE(’12-JAN-1983′, ‘DD-MON-YYYY’), 1100, NULL, 20);
INSERT INTO EMP VALUES
*
ERROR at line 1:
ORA-01950: no privileges on tablespace ‘USERS’
SQL> INSERT INTO EMP VALUES
2 (7900, ‘JAMES’, ‘CLERK’, 7698,
3 TO_DATE(‘3-DEC-1981’, ‘DD-MON-YYYY’), 950, NULL, 30);
INSERT INTO EMP VALUES
*
ERROR at line 1:
ORA-01950: no privileges on tablespace ‘USERS’
SQL> INSERT INTO EMP VALUES
2 (7902, ‘FORD’, ‘ANALYST’, 7566,
3 TO_DATE(‘3-DEC-1981’, ‘DD-MON-YYYY’), 3000, NULL, 20);
INSERT INTO EMP VALUES
*
ERROR at line 1:
ORA-01950: no privileges on tablespace ‘USERS’
SQL> INSERT INTO EMP VALUES
2 (7934, ‘MILLER’, ‘CLERK’, 7782,
3 TO_DATE(’23-JAN-1982′, ‘DD-MON-YYYY’), 1300, NULL, 10);
INSERT INTO EMP VALUES
*
ERROR at line 1:
ORA-01950: no privileges on tablespace ‘USERS’
SQL> conn sys/sys as sysdba
Connected.
SQL> grant dba to james;

Grant succeeded.

SQL> conn james/inetsolv
Connected.
SQL> drop table emp purge;

Table dropped.

SQL> cl scr

SQL> CREATE TABLE EMP
2 (EMPNO NUMBER(4) NOT NULL,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2));

Table created.

SQL>
SQL> INSERT INTO EMP VALUES
2 (7369, ‘SMITH’, ‘CLERK’, 7902,
3 TO_DATE(’17-DEC-1980′, ‘DD-MON-YYYY’), 800, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7499, ‘ALLEN’, ‘SALESMAN’, 7698,
3 TO_DATE(’20-FEB-1981′, ‘DD-MON-YYYY’), 1600, 300, 30);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7521, ‘WARD’, ‘SALESMAN’, 7698,
3 TO_DATE(’22-FEB-1981′, ‘DD-MON-YYYY’), 1250, 500, 30);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7566, ‘JONES’, ‘MANAGER’, 7839,
3 TO_DATE(‘2-APR-1981’, ‘DD-MON-YYYY’), 2975, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7654, ‘MARTIN’, ‘SALESMAN’, 7698,
3 TO_DATE(’28-SEP-1981′, ‘DD-MON-YYYY’), 1250, 1400, 30);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7698, ‘BLAKE’, ‘MANAGER’, 7839,
3 TO_DATE(‘1-MAY-1981’, ‘DD-MON-YYYY’), 2850, NULL, 30);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7782, ‘CLARK’, ‘MANAGER’, 7839,
3 TO_DATE(‘9-JUN-1981’, ‘DD-MON-YYYY’), 2450, NULL, 10);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7788, ‘SCOTT’, ‘ANALYST’, 7566,
3 TO_DATE(’09-DEC-1982′, ‘DD-MON-YYYY’), 3000, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7839, ‘KING’, ‘PRESIDENT’, NULL,
3 TO_DATE(’17-NOV-1981′, ‘DD-MON-YYYY’), 5000, NULL, 10);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7844, ‘TURNER’, ‘SALESMAN’, 7698,
3 TO_DATE(‘8-SEP-1981’, ‘DD-MON-YYYY’), 1500, 0, 30);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7876, ‘ADAMS’, ‘CLERK’, 7788,
3 TO_DATE(’12-JAN-1983′, ‘DD-MON-YYYY’), 1100, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7900, ‘JAMES’, ‘CLERK’, 7698,
3 TO_DATE(‘3-DEC-1981’, ‘DD-MON-YYYY’), 950, NULL, 30);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7902, ‘FORD’, ‘ANALYST’, 7566,
3 TO_DATE(‘3-DEC-1981’, ‘DD-MON-YYYY’), 3000, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7934, ‘MILLER’, ‘CLERK’, 7782,
3 TO_DATE(’23-JAN-1982′, ‘DD-MON-YYYY’), 1300, NULL, 10);

1 row created.

SQL> commit;

Commit complete.

SQL> select *from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7369 SMITH CLERK 7902 17-DEC-80 800
20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7566 JONES MANAGER 7839 02-APR-81 2975
20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30

7698 BLAKE MANAGER 7839 01-MAY-81 2850
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7782 CLARK MANAGER 7839 09-JUN-81 2450
10

7788 SCOTT ANALYST 7566 09-DEC-82 3000
20

7839 KING PRESIDENT 17-NOV-81 5000
10

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30

7876 ADAMS CLERK 7788 12-JAN-83 1100
20

7900 JAMES CLERK 7698 03-DEC-81 950
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7902 FORD ANALYST 7566 03-DEC-81 3000
20

7934 MILLER CLERK 7782 23-JAN-82 1300
10

14 rows selected.

SQL> drop table emp purge;

Table dropped.

SQL> cl scr

SQL> CREATE TABLE EMP
2 (EMPNO NUMBER(4) NOT NULL,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2));

Table created.

SQL>
SQL> INSERT INTO EMP VALUES
2 (7369, ‘SMITH’, ‘CLERK’, 7902,
3 TO_DATE(’17-DEC-1980′, ‘DD-MON-YYYY’), 800, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7499, ‘ALLEN’, ‘SALESMAN’, 7698,
3 TO_DATE(’20-FEB-1981′, ‘DD-MON-YYYY’), 1600, 300, 30);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7521, ‘WARD’, ‘SALESMAN’, 7698,
3 TO_DATE(’22-FEB-1981′, ‘DD-MON-YYYY’), 1250, 500, 30);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7566, ‘JONES’, ‘MANAGER’, 7839,
3 TO_DATE(‘2-APR-1981’, ‘DD-MON-YYYY’), 2975, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7654, ‘MARTIN’, ‘SALESMAN’, 7698,
3 TO_DATE(’28-SEP-1981′, ‘DD-MON-YYYY’), 1250, 1400, 30);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7698, ‘BLAKE’, ‘MANAGER’, 7839,
3 TO_DATE(‘1-MAY-1981’, ‘DD-MON-YYYY’), 2850, NULL, 30);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7782, ‘CLARK’, ‘MANAGER’, 7839,
3 TO_DATE(‘9-JUN-1981’, ‘DD-MON-YYYY’), 2450, NULL, 10);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7788, ‘SCOTT’, ‘ANALYST’, 7566,
3 TO_DATE(’09-DEC-1982′, ‘DD-MON-YYYY’), 3000, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7839, ‘KING’, ‘PRESIDENT’, NULL,
3 TO_DATE(’17-NOV-1981′, ‘DD-MON-YYYY’), 5000, NULL, 10);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7844, ‘TURNER’, ‘SALESMAN’, 7698,
3 TO_DATE(‘8-SEP-1981’, ‘DD-MON-YYYY’), 1500, 0, 30);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7876, ‘ADAMS’, ‘CLERK’, 7788,
3 TO_DATE(’12-JAN-1983′, ‘DD-MON-YYYY’), 1100, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7900, ‘JAMES’, ‘CLERK’, 7698,
3 TO_DATE(‘3-DEC-1981’, ‘DD-MON-YYYY’), 950, NULL, 30);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7902, ‘FORD’, ‘ANALYST’, 7566,
3 TO_DATE(‘3-DEC-1981’, ‘DD-MON-YYYY’), 3000, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7934, ‘MILLER’, ‘CLERK’, 7782,
3 TO_DATE(’23-JAN-1982′, ‘DD-MON-YYYY’), 1300, NULL, 10);

1 row created.

SQL> commit;

Commit complete.

SQL> select *from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7369 SMITH CLERK 7902 17-DEC-80 800
20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7566 JONES MANAGER 7839 02-APR-81 2975
20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30

7698 BLAKE MANAGER 7839 01-MAY-81 2850
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7782 CLARK MANAGER 7839 09-JUN-81 2450
10

7788 SCOTT ANALYST 7566 09-DEC-82 3000
20

7839 KING PRESIDENT 17-NOV-81 5000
10

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30

7876 ADAMS CLERK 7788 12-JAN-83 1100
20

7900 JAMES CLERK 7698 03-DEC-81 950
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7902 FORD ANALYST 7566 03-DEC-81 3000
20

7934 MILLER CLERK 7782 23-JAN-82 1300
10

14 rows selected.

SQL> ed
Wrote file afiedt.buf

1* select *from emp
2
SQL> cl scr

SQL> ed
Wrote file afiedt.buf

1* select *from emp
2 /

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7369 SMITH CLERK 7902 17-DEC-80 800
20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7566 JONES MANAGER 7839 02-APR-81 2975
20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30

7698 BLAKE MANAGER 7839 01-MAY-81 2850
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7782 CLARK MANAGER 7839 09-JUN-81 2450
10

7788 SCOTT ANALYST 7566 09-DEC-82 3000
20

7839 KING PRESIDENT 17-NOV-81 5000
10

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30

7876 ADAMS CLERK 7788 12-JAN-83 1100
20

7900 JAMES CLERK 7698 03-DEC-81 950
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7902 FORD ANALYST 7566 03-DEC-81 3000
20

7934 MILLER CLERK 7782 23-JAN-82 1300
10

14 rows selected.

SQL> set line 100
SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

SQL> set pagesize 50
SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

SQL> cl scr

SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

SQL> alter table emp add gender char;

Table altered.

SQL> ed
Wrote file afiedt.buf

1* alter table emp add gender char
SQL>
SQL> select *from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO G
———- ———- ——— ———- ——— ———- ———- ———- –
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

SQL>
SQL> cl scr

SQL> desc emp
Name Null? Type
—————————————————– ——– ————————————
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
GENDER CHAR(1)

SQL> alter table emp modify empno number(6);

Table altered.

SQL> ed
Wrote file afiedt.buf

1* alter table emp modify empno number(5)
SQL> /
alter table emp modify empno number(5)
*
ERROR at line 1:
ORA-01440: column to be modified must be empty to decrease precision or scale
SQL> ed
Wrote file afiedt.buf

1* alter table emp modify empno char(10)
SQL> /
alter table emp modify empno char(10)
*
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype
SQL> spool off

d2k_nov13

2)Format Trigger:-
This trigger are PL/SQL functions
executed before an object is formatted.
The trigger can be used to dynamically
change the formatting attributes of
objects.
Ex:-
1)Font,Font weight etc

Access:-
1)OB
2)PL/SQL program unit
3)Layout Object Property.
(Main SectionBodyselect Field
PL/SQL Editor(F11))

Example:-
A conditionally print or not print a report column value.
Format trigger returns Boolean values
TRUE or FALES.
If the return value for the format trigger is FALES,the value is not displayed
in the report.
Requirement:-
Display sal above 3000
Steps:-
create report
go filed in
Main selectBodyselect filed (I.c sal)
Go to PL/SQL Editor
SRW
(SQL Report Writer)
It is a Report Package which keeps the Report
Buit_Ins.
SRW is used as prefix with Buit_ins

SRW.message(<NO>,’Msg’);
Nouser number.
function F_salFormatTrigger
return boolean
is
begin
if :sal>2500 then
srw.message(100,’The report will display above 2500′);
return (TRUE);
else
return(FALSE);
end if;
end;

Parameter Report
—————————
A parameter is a variable whose value
can be set at runtime
(e.g, from the Runtime Parameter Form
or the command line).
There are two types parameters
1)User parameters are created by you
for the report.
2)System parameters are created by
Report Builder.
Note:-
we can delete or rename a user
parameter
we cannot delete or rename
a system parameter.
expand the Data Model node in the object
navigator.
Go to User Parameter node.
select user parameter node and click on the
Create Button Button to create a user
parameter for passing deptno.
Note:-
Default name of parameter is P_1 will be
created
Go to property of Queries at object
Navigator Navigator
(I.c Q-emp)
Select the
Node—Query
Sub Node—SQL Query Statement
Click on SQL Query Statement.
In select statement
Give a parameter as
select empno,ename,sal from emp
where deptno=:a
Here ‘a’ is parameter.
If we want two parameter the give
the two parameter
select empno,ename,sal from emp
where deptno=:a and sal>:b
Note:-
Here we can fined two parameter(I.c A,B)
in user parameter object.
Expand the user parameter object for
find parameters.
function F_enameFormatTrigger return boolean is
begin
if :ename not in(:p_ename1,:p_ename2,:p_ename3) then
return (TRUE);
else
return(false);
end if;

end;