URL: http://freekaamaal.com/
Loading...
Freekaamaal deal website
URL: http://freekaamaal.com/
Triggers and its examples....
Trigger:- Trigger is a pl/sql block or procedure that implicitly execute when some event occur.
Application Trigger:- Fire whenever an event occurs with a particular application.
Database Trigger:- Fire whenever a data event(such as DML) or system event(such as logon or
shutdown) occur on a schema or database
Trigger Timing
For table:- Before, After
For View:- Instead of
Trigger Event:-
Insert, Update or Delete
Trigger Name:-
On Table, View
Trigger Type:-
Row Level
Statement Level
Trigger Body:-
What Action perform
CREATE OR REPLACE TRIGGER XXC05_TRIGGER001
BEFORE INSERT ON XXC05_TRIGGER_TEST
BEGIN
IF TO_CHAR(SYSDATE,'DY') = 'SAT'
THEN
RAISE_APPLICATION_ERROR(-20500,'YOU NOT INSERT DATA INTO XXC05_TRIGGER_TEST TABLE');
END IF;
END XXC05_TRIGGER_TEST;
CREATE OR REPLACE TRIGGER XXC05_TRIGGER001
BEFORE INSERT OR UPDATE OR DELETE ON XXC05_TRIGGER_TEST
BEGIN
IF TO_CHAR(SYSDATE,'DY') = 'SAT'
THEN
IF INSERTING
THEN
RAISE_APPLICATION_ERROR(-20500,'YOU NOT INSERT DATA INTO XXC05_TRIGGER_TEST TABLE');
ELSIF UPDATING
THEN
RAISE_APPLICATION_ERROR(-20501,'YOU NOT UPDATE DATA INTO XXC05_TRIGGER_TEST TABLE');
ELSIF DELETING
THEN
RAISE_APPLICATION_ERROR(-20502,'YOU NOT DELETE DATA INTO XXC05_TRIGGER_TEST TABLE');
END IF;
END IF;
END XXC05_TRIGGER_TEST;
CREATE OR REPLACE TRIGGER XXC05_TRIGGER001
BEFORE INSERT OR UPDATE OR DELETE ON XXC05_TRIGGER_TEST
FOR EACH ROW
BEGIN
IF TO_CHAR(SYSDATE,'DY') = 'SAT'
THEN
IF INSERTING
THEN
RAISE_APPLICATION_ERROR(-20500,'YOU NOT INSERT DATA INTO XXC05_TRIGGER_TEST TABLE');
ELSIF UPDATING
THEN
RAISE_APPLICATION_ERROR(-20501,'YOU NOT UPDATE DATA INTO XXC05_TRIGGER_TEST TABLE');
ELSIF DELETING
THEN
RAISE_APPLICATION_ERROR(-20502,'YOU NOT DELETE DATA INTO XXC05_TRIGGER_TEST TABLE');
END IF;
END IF;
END XXC05_TRIGGER_TEST;
Application Trigger:- Fire whenever an event occurs with a particular application.
Database Trigger:- Fire whenever a data event(such as DML) or system event(such as logon or
shutdown) occur on a schema or database
Trigger Timing
For table:- Before, After
For View:- Instead of
Trigger Event:-
Insert, Update or Delete
Trigger Name:-
On Table, View
Trigger Type:-
Row Level
Statement Level
Trigger Body:-
What Action perform
CREATE OR REPLACE TRIGGER XXC05_TRIGGER001
BEFORE INSERT ON XXC05_TRIGGER_TEST
BEGIN
IF TO_CHAR(SYSDATE,'DY') = 'SAT'
THEN
RAISE_APPLICATION_ERROR(-20500,'YOU NOT INSERT DATA INTO XXC05_TRIGGER_TEST TABLE');
END IF;
END XXC05_TRIGGER_TEST;
CREATE OR REPLACE TRIGGER XXC05_TRIGGER001
BEFORE INSERT OR UPDATE OR DELETE ON XXC05_TRIGGER_TEST
BEGIN
IF TO_CHAR(SYSDATE,'DY') = 'SAT'
THEN
IF INSERTING
THEN
RAISE_APPLICATION_ERROR(-20500,'YOU NOT INSERT DATA INTO XXC05_TRIGGER_TEST TABLE');
ELSIF UPDATING
THEN
RAISE_APPLICATION_ERROR(-20501,'YOU NOT UPDATE DATA INTO XXC05_TRIGGER_TEST TABLE');
ELSIF DELETING
THEN
RAISE_APPLICATION_ERROR(-20502,'YOU NOT DELETE DATA INTO XXC05_TRIGGER_TEST TABLE');
END IF;
END IF;
END XXC05_TRIGGER_TEST;
CREATE OR REPLACE TRIGGER XXC05_TRIGGER001
BEFORE INSERT OR UPDATE OR DELETE ON XXC05_TRIGGER_TEST
FOR EACH ROW
BEGIN
IF TO_CHAR(SYSDATE,'DY') = 'SAT'
THEN
IF INSERTING
THEN
RAISE_APPLICATION_ERROR(-20500,'YOU NOT INSERT DATA INTO XXC05_TRIGGER_TEST TABLE');
ELSIF UPDATING
THEN
RAISE_APPLICATION_ERROR(-20501,'YOU NOT UPDATE DATA INTO XXC05_TRIGGER_TEST TABLE');
ELSIF DELETING
THEN
RAISE_APPLICATION_ERROR(-20502,'YOU NOT DELETE DATA INTO XXC05_TRIGGER_TEST TABLE');
END IF;
END IF;
END XXC05_TRIGGER_TEST;
What is Procedure and its Examples
Procedure:-
Procedure is a named PL/SQL block. It may or may not return value.
Syntax:-
CREATE OR REPLACE PROCEDURE procedure_name
IS/AS
BEGIN
STATEMENT;
EXCEPTION; --(optional)
END procedure_name;
After creation of procedure, we execute it for see the output.
There are many different ways for execute the procedure
1.
EXECUTE/EXEC procedure_name;
2.
DECLARE
BEGIN
procedure_name;
END;
We can use different parameter mode in procedure
1. IN
2. OUT
3. IN OUT
4. OUT NOCOPY
5. IN OUT NOCOPY
Note:- a) By default parameter mode is IN.
b) NOCPOY pass the reference of variable not value. NOCOPY use only with OUT and
IN OUT mode.
Examples:-
1.
CREATE OR REPLACE PROCEDURE XXC05_PROC
IS
ID NUMBER;
BEGIN
ID:=10;
DBMS_OUTPUT.PUT_LINE('ID:- '||ID);
END XXC05_PROC;
2.
CREATE OR REPLACE PROCEDURE XXC05_PROC
IS
CURSOR C1 IS
SELECT EMPLOYEE_ID,LAST_NAME
FROM EMPLOYEES;
L_ID NUMBER;
L_NAME VARCHAR2(20);
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO L_ID,L_NAME;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee ID:- '||CHR(9)||L_ID||CHR(9)||'Last Name:- '||CHR(9)||L_NAME);
END LOOP;
END XXC05_PROC;
3. We can use cursor within Procedure....
CREATE OR REPLACE PROCEDURE XXC05_PROC(P_EMP_ID IN NUMBER)
IS
CURSOR C1 IS
SELECT EMPLOYEE_ID,LAST_NAME
FROM EMPLOYEES
WHERE EMPLOYEE_ID = P_EMP_ID;
L_ID NUMBER;
L_NAME VARCHAR2(20);
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO L_ID,L_NAME;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee ID:- '||CHR(9)||L_ID||CHR(9)||'Last Name:- '||CHR(9)||L_NAME);
END LOOP;
END XXC05_PROC;
exec XXC05_PROC(100);
4.
CREATE OR REPLACE PROCEDURE XXC05_PROC(P_EMP_ID IN NUMBER,P_SAL OUT NUMBER)
IS
CURSOR C1 IS
SELECT EMPLOYEE_ID,LAST_NAME ,SALARY
FROM EMPLOYEES
WHERE EMPLOYEE_ID = P_EMP_ID;
L_ID NUMBER;
L_NAME VARCHAR2(20);
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO L_ID,L_NAME,P_SAL;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee ID:- '||CHR(9)||L_ID||CHR(9)||'Last Name:- '||CHR(9)||L_NAME||'Salary:- '||CHR(9)||P_SAL);
END LOOP;
END XXC05_PROC;
Note:- If we use OUT or IN OUT mode parameter in procedure then it call only within BEGIN block.
Example given below.
DECLARE
L_SAL NUMBER;
BEGIN
XXC05_PROC(100,L_SAL);
DBMS_OUTPUT.PUT_LINE('Salary:- '||CHR(9)||L_SAL);
END;
5.
CREATE OR REPLACE PROCEDURE XXC05_PROC(P_EMP_ID IN NUMBER,P_SAL OUT NOCOPY NUMBER)
IS
CURSOR C1 IS
SELECT EMPLOYEE_ID,LAST_NAME ,SALARY
FROM EMPLOYEES
WHERE EMPLOYEE_ID = P_EMP_ID;
L_ID NUMBER;
L_NAME VARCHAR2(20);
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO L_ID,L_NAME,P_SAL;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee ID:- '||CHR(9)||L_ID||CHR(9)||'Last Name:- '||CHR(9)||L_NAME||'Salary:- '||CHR(9)||P_SAL);
END LOOP;
END XXC05_PROC;
6.
CREATE OR REPLACE PROCEDURE XXC05_PROC(P_EMP_ID IN OUT NUMBER)
IS
CURSOR C1 IS
SELECT EMPLOYEE_ID,LAST_NAME
FROM EMPLOYEES
WHERE EMPLOYEE_ID = P_EMP_ID;
L_NAME VARCHAR2(20);
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO P_EMP_ID,L_NAME;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee ID:- '||CHR(9)||P_EMP_ID||CHR(9)||'Last Name:- '||CHR(9)||L_NAME);
END LOOP;
END XXC05_PROC;
7.
CREATE OR REPLACE PROCEDURE XXC05_PROC(P_EMP_ID IN NUMBER)
IS
CURSOR C1 IS
SELECT *
FROM EMPLOYEES
WHERE EMPLOYEE_ID = P_EMP_ID;
L_EMP_REC EMPLOYEES%ROWTYPE;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO L_EMP_REC ;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee ID:- '||CHR(9)||L_EMP_REC.EMPLOYEE_ID||CHR(9)||'Last Name:- '||CHR(9)||L_EMP_REC.LAST_NAME);
END LOOP;
END XXC05_PROC;
Some Important Point for Procedure..........
Ques:- Can we create procedure within procedure?
Ans:- YES, Refer below example.
Example:-
CREATE OR REPLACE PROCEDURE XXC05_OUTER_PROC
IS
PROCEDURE XXC05_INNER_PROC
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('INNER PROEDURE');
END XXC05_INNER_PROC;
BEGIN
DBMS_OUTPUT.PUT_LINE('OUTER PROEDURE');
XXC05_INNER_PROC;
END XXC05_OUTER_PROC;
Quese:- Can we use RETURN clause within procedure?
Ans:- YES, But no statement are executed which written after the RETURN clause.
In below example only first message print.
Example:-
CREATE OR REPLACE PROCEDURE XXC05_OUTER_PROC
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('OUTER PROEDURE 1');
RETURN;
DBMS_OUTPUT.PUT_LINE('OUTER PROEDURE 2');
END XXC05_OUTER_PROC;
Output:-
OUTER PROEDURE 1
Procedure is a named PL/SQL block. It may or may not return value.
Syntax:-
CREATE OR REPLACE PROCEDURE procedure_name
IS/AS
BEGIN
STATEMENT;
EXCEPTION; --(optional)
END procedure_name;
After creation of procedure, we execute it for see the output.
There are many different ways for execute the procedure
1.
EXECUTE/EXEC procedure_name;
2.
DECLARE
BEGIN
procedure_name;
END;
We can use different parameter mode in procedure
1. IN
2. OUT
3. IN OUT
4. OUT NOCOPY
5. IN OUT NOCOPY
Note:- a) By default parameter mode is IN.
b) NOCPOY pass the reference of variable not value. NOCOPY use only with OUT and
IN OUT mode.
Examples:-
1.
CREATE OR REPLACE PROCEDURE XXC05_PROC
IS
ID NUMBER;
BEGIN
ID:=10;
DBMS_OUTPUT.PUT_LINE('ID:- '||ID);
END XXC05_PROC;
2.
CREATE OR REPLACE PROCEDURE XXC05_PROC
IS
CURSOR C1 IS
SELECT EMPLOYEE_ID,LAST_NAME
FROM EMPLOYEES;
L_ID NUMBER;
L_NAME VARCHAR2(20);
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO L_ID,L_NAME;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee ID:- '||CHR(9)||L_ID||CHR(9)||'Last Name:- '||CHR(9)||L_NAME);
END LOOP;
END XXC05_PROC;
3. We can use cursor within Procedure....
CREATE OR REPLACE PROCEDURE XXC05_PROC(P_EMP_ID IN NUMBER)
IS
CURSOR C1 IS
SELECT EMPLOYEE_ID,LAST_NAME
FROM EMPLOYEES
WHERE EMPLOYEE_ID = P_EMP_ID;
L_ID NUMBER;
L_NAME VARCHAR2(20);
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO L_ID,L_NAME;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee ID:- '||CHR(9)||L_ID||CHR(9)||'Last Name:- '||CHR(9)||L_NAME);
END LOOP;
END XXC05_PROC;
exec XXC05_PROC(100);
4.
CREATE OR REPLACE PROCEDURE XXC05_PROC(P_EMP_ID IN NUMBER,P_SAL OUT NUMBER)
IS
CURSOR C1 IS
SELECT EMPLOYEE_ID,LAST_NAME ,SALARY
FROM EMPLOYEES
WHERE EMPLOYEE_ID = P_EMP_ID;
L_ID NUMBER;
L_NAME VARCHAR2(20);
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO L_ID,L_NAME,P_SAL;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee ID:- '||CHR(9)||L_ID||CHR(9)||'Last Name:- '||CHR(9)||L_NAME||'Salary:- '||CHR(9)||P_SAL);
END LOOP;
END XXC05_PROC;
Note:- If we use OUT or IN OUT mode parameter in procedure then it call only within BEGIN block.
Example given below.
DECLARE
L_SAL NUMBER;
BEGIN
XXC05_PROC(100,L_SAL);
DBMS_OUTPUT.PUT_LINE('Salary:- '||CHR(9)||L_SAL);
END;
5.
CREATE OR REPLACE PROCEDURE XXC05_PROC(P_EMP_ID IN NUMBER,P_SAL OUT NOCOPY NUMBER)
IS
CURSOR C1 IS
SELECT EMPLOYEE_ID,LAST_NAME ,SALARY
FROM EMPLOYEES
WHERE EMPLOYEE_ID = P_EMP_ID;
L_ID NUMBER;
L_NAME VARCHAR2(20);
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO L_ID,L_NAME,P_SAL;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee ID:- '||CHR(9)||L_ID||CHR(9)||'Last Name:- '||CHR(9)||L_NAME||'Salary:- '||CHR(9)||P_SAL);
END LOOP;
END XXC05_PROC;
6.
CREATE OR REPLACE PROCEDURE XXC05_PROC(P_EMP_ID IN OUT NUMBER)
IS
CURSOR C1 IS
SELECT EMPLOYEE_ID,LAST_NAME
FROM EMPLOYEES
WHERE EMPLOYEE_ID = P_EMP_ID;
L_NAME VARCHAR2(20);
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO P_EMP_ID,L_NAME;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee ID:- '||CHR(9)||P_EMP_ID||CHR(9)||'Last Name:- '||CHR(9)||L_NAME);
END LOOP;
END XXC05_PROC;
7.
CREATE OR REPLACE PROCEDURE XXC05_PROC(P_EMP_ID IN NUMBER)
IS
CURSOR C1 IS
SELECT *
FROM EMPLOYEES
WHERE EMPLOYEE_ID = P_EMP_ID;
L_EMP_REC EMPLOYEES%ROWTYPE;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO L_EMP_REC ;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee ID:- '||CHR(9)||L_EMP_REC.EMPLOYEE_ID||CHR(9)||'Last Name:- '||CHR(9)||L_EMP_REC.LAST_NAME);
END LOOP;
END XXC05_PROC;
Some Important Point for Procedure..........
Ques:- Can we create procedure within procedure?
Ans:- YES, Refer below example.
Example:-
CREATE OR REPLACE PROCEDURE XXC05_OUTER_PROC
IS
PROCEDURE XXC05_INNER_PROC
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('INNER PROEDURE');
END XXC05_INNER_PROC;
BEGIN
DBMS_OUTPUT.PUT_LINE('OUTER PROEDURE');
XXC05_INNER_PROC;
END XXC05_OUTER_PROC;
Quese:- Can we use RETURN clause within procedure?
Ans:- YES, But no statement are executed which written after the RETURN clause.
In below example only first message print.
Example:-
CREATE OR REPLACE PROCEDURE XXC05_OUTER_PROC
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('OUTER PROEDURE 1');
RETURN;
DBMS_OUTPUT.PUT_LINE('OUTER PROEDURE 2');
END XXC05_OUTER_PROC;
Output:-
OUTER PROEDURE 1
What is Function and its examples
Function:-
Function is named PL/SQL block. It must return a single value. We can call function in SELECT Statement.
Syntax:-
CREATE OR REPLACE FUNCTION function_name
RETURN data_type
IS
BEGIN
STATEMENT;
RETURN (value/variables);
END function_name;
After creation of FUNCTION, we call it for the output.
There are many different ways for execute the procedure
1.
SELECT function_name FROM table_name;
2.
DECLARE
variable_name data_type;
BEGIN
variable:= function_name;
END;
We can use different parameter mode in function
1. IN
2. OUT
3. IN OUT
4. OUT NOCOPY
5. IN OUT NOCOPY
Note:- *a) If we use OUT, IN OUT mode in function's parameter, we can not call that function in
SELECT statement.
b) By default parameter mode is IN.
c) NOCPOY pass the reference of variable not value. NOCOPY use only with OUT and
IN OUT mode.
Examples:-
1.
CREATE OR REPLACE FUNCTION XXC05_FUNC
RETURN NUMBER
IS
ID NUMBER;
BEGIN
ID:=100;
RETURN 100;
END XXC05_FUNC;
SELECT XXC05_FUNC FROM DUAL;
2.
CREATE OR REPLACE FUNCTION XXC05_FUNC
RETURN NUMBER
IS
L_SAL NUMBER;
BEGIN
SELECT SALARY
INTO L_SAL
FROM EMPLOYEES
WHERE EMPLOYEE_ID=199;
RETURN L_SAL;
END XXC05_FUNC;
SELECT XXC05_FUNC FROM DUAL;
3.
CREATE OR REPLACE FUNCTION XXC05_FUNC(P_ID NUMBER)
RETURN NUMBER
IS
L_SAL NUMBER;
BEGIN
SELECT SALARY
INTO L_SAL
FROM EMPLOYEES
WHERE EMPLOYEE_ID=P_ID;
RETURN L_SAL;
END XXC05_FUNC;
SELECT XXC05_FUNC(198) FROM DUAL;
4.
CREATE OR REPLACE FUNCTION XXC05_FUNC(P_ID NUMBER,P_DEPT_ID OUT NUMBER)
RETURN NUMBER
IS
L_SAL NUMBER;
BEGIN
SELECT SALARY,DEPARTMENT_ID
INTO L_SAL,P_DEPT_ID
FROM EMPLOYEES
WHERE EMPLOYEE_ID=P_ID;
RETURN L_SAL;
END XXC05_FUNC;
Note:- This type of function can not call in SELECT Statement. This type of function call within BEGIN block;
For Example:-
DECLARE
L_DEPT_ID NUMBER;
L_SAL NUMBER;
BEGIN
L_SAL:= XXC05_FUNC(199,L_DEPT_ID);
DBMS_OUTPUT.PUT_LINE('Department id:- '||L_DEPT_ID||CHR(9)||'Salary:- '||L_SAL);
END;
Function is named PL/SQL block. It must return a single value. We can call function in SELECT Statement.
Syntax:-
CREATE OR REPLACE FUNCTION function_name
RETURN data_type
IS
BEGIN
STATEMENT;
RETURN (value/variables);
END function_name;
After creation of FUNCTION, we call it for the output.
There are many different ways for execute the procedure
1.
SELECT function_name FROM table_name;
2.
DECLARE
variable_name data_type;
BEGIN
variable:= function_name;
END;
We can use different parameter mode in function
1. IN
2. OUT
3. IN OUT
4. OUT NOCOPY
5. IN OUT NOCOPY
Note:- *a) If we use OUT, IN OUT mode in function's parameter, we can not call that function in
SELECT statement.
b) By default parameter mode is IN.
c) NOCPOY pass the reference of variable not value. NOCOPY use only with OUT and
IN OUT mode.
Examples:-
1.
CREATE OR REPLACE FUNCTION XXC05_FUNC
RETURN NUMBER
IS
ID NUMBER;
BEGIN
ID:=100;
RETURN 100;
END XXC05_FUNC;
SELECT XXC05_FUNC FROM DUAL;
2.
CREATE OR REPLACE FUNCTION XXC05_FUNC
RETURN NUMBER
IS
L_SAL NUMBER;
BEGIN
SELECT SALARY
INTO L_SAL
FROM EMPLOYEES
WHERE EMPLOYEE_ID=199;
RETURN L_SAL;
END XXC05_FUNC;
SELECT XXC05_FUNC FROM DUAL;
3.
CREATE OR REPLACE FUNCTION XXC05_FUNC(P_ID NUMBER)
RETURN NUMBER
IS
L_SAL NUMBER;
BEGIN
SELECT SALARY
INTO L_SAL
FROM EMPLOYEES
WHERE EMPLOYEE_ID=P_ID;
RETURN L_SAL;
END XXC05_FUNC;
SELECT XXC05_FUNC(198) FROM DUAL;
4.
CREATE OR REPLACE FUNCTION XXC05_FUNC(P_ID NUMBER,P_DEPT_ID OUT NUMBER)
RETURN NUMBER
IS
L_SAL NUMBER;
BEGIN
SELECT SALARY,DEPARTMENT_ID
INTO L_SAL,P_DEPT_ID
FROM EMPLOYEES
WHERE EMPLOYEE_ID=P_ID;
RETURN L_SAL;
END XXC05_FUNC;
Note:- This type of function can not call in SELECT Statement. This type of function call within BEGIN block;
For Example:-
DECLARE
L_DEPT_ID NUMBER;
L_SAL NUMBER;
BEGIN
L_SAL:= XXC05_FUNC(199,L_DEPT_ID);
DBMS_OUTPUT.PUT_LINE('Department id:- '||L_DEPT_ID||CHR(9)||'Salary:- '||L_SAL);
END;
ORACLE APPS REPORTS INTERVIEW QUESTIONS
1. What is SRW Package?
Ans:
The Report builder Built in package known as SRW Package (Sql Report Writer)
This package extends reports, Control report execution, output message at
runtime, Initialize layout fields, Perform DDL
statements used to create or Drop temporary
table, Call User Exit, to format width of the columns, to page break
the column, to set the colors
Ex: SRW.DO_SQL, It’s like DDL
command, we can create table, views , etc.,
SRW.SET_FIELD_NUM
SRW.
SET_FIELD_CHAR
SRW.
SET FIELD _DATE
2. What are Lexical Parameters and bind parameters?
Lexical Parameter is a Simple
text string that to replace any part of a SELECT statement.
Column names, the from clause, where clause or the order by clause. To create a
lexical reference in a query we prefix the parameter name with an ampersand
(ex. &.dname,)
3. What is User Parameters?
A parameter, which is created
by user. For to restrict values with where clause in select statement.
Data type, width, input mask,
initial value, validation trigger, list of values
We can use Lovs in use in
user parameter with static and Dynamic Select Statement.
4. What is System Parameters: These are built-in parameters
by corporation.
BACKGROUND: Is whether the
report should run in the foreground or the background.
COPIES Is
the number of report copies that should be made when the report is printed.
CURRENCY Is
the symbol for the currency indicator (e.g., “$”).
DECIMAL Is
the symbol for the decimal indicator (e.g., “.”).
DESFORMAT Is the definition
of the output device’s format (e.g., landscape mode for a
printer). This parameter is used when running a report in a
character-mode environment, and when sending a bitmap report to a
file (e.g. to create PDF or HTML output).
DESNAME Is
the name of the output device (e.g., the file name, printer’s name, mail
userid).
DESTYPE Is
the type of device to which to send the report output (screen, file, mail,
printer, or screen using PostScript format).
MODE Is
whether the report should run in character mode or bitmap.
ORIENTATION Is the print
direction for the report (landscape, portrait, default).
PRINTJOB Is
whether the Print Job dialog box should appear before the report is run.
THOUSANDS Is the symbol for
the thousand’s indicator (e.g., “,”).
5. How many Types of Reports available in
Reports
Tabular
Form - like
Form –
letter
Group left
Group above
Matrix
Matrix with
group
Mailing label
Matrix
Report: Simple, Group above,
Nested
Simple Matrix Report required
4 groups
1.
Cross Product Group
2.
Row and Column Group
3.
Cell Group
4.
Cell column is the source of a cross product summary that becomes the cell
content.
Frames: 1.Repeating
frame for rows (down direction)
2.
Repeating frame for columns (Across)
3.
Matrix object the intersection of the two repeating frames
6. What Types of Triggers are Available in
Reports.
·
Report level Triggers
·
Data Model Triggers
·
Layout Model Triggers
·
Report Level Triggers
Before parameter form:
If u want take
parameters passed to the report and manipulate them so that they appear
differently in the parameter form., this is where modification can be done for
ex: when u want pass a deptno but show the dname selected , use a before
parameter form trigger.
After parameter form &
Before Report:
These two triggers are
fired one after the other. No event occurs in between them. However the way the
way that the reports product behaves when the triggers fail is quite different.
If the After Parameter trigger fails the report will be put back into the
parameter form. It’s useful to place code here to check whether values in your
parameter form are valid. Even though the Before Report trigger is executed
before the query runs, if this trigger fails it won’t fail until reports tries
to display the first page of the report. This means that even if something goes
wrong in the before report trigger (meaning that you may not want to run the
query at all) It will run anyway
Between pages:
This Trigger fires before
all pages except first page one. It will not fire after the
last page of a report. If a report only has one page it will not fire at all.
You can use this trigger to send specific control to the change the paper
orientation or to do double sided printing
After report:
This trigger fires the report
has printed or in the case of a screen report, after the report is closed
following viewing. This trigger can be used to update a global variable if u r
returning the number of pages in a report. It is also used to delete temporary
table used to print the report
Data Model
Triggers
Formula Column,
Group Filter,
Parameter values
Layout Model
Triggers
7. What are Format triggers.
Format triggers enable
you to modify the display of objects dynamically at run time or to suppress
display altogether
For Headings, for repeating
frames, for field, for boilerplate object
To format a column based
on certain criteria for example
i) To format
the max (Sal) for particular department.
ii) To
format the Sal column with a Dollar ($) prefix.
iii) To
format Date formats….etc
8. What
is Data
Model?
Data Model is logically group
of the Report Objects through query and Data model tools. Once query is
compiled report automatically generates group. The queries build the groups ant
then Groups are used to populate the report. The only function of queries
in report is to create the groups. The Report Editor’s Data Model
view enables you to define and modify the data model objects for a
report. In this view, objects and their property settings are
represented symbolically to highlight their types and
relationships. To create the query objects for your data model, you
can use the Report Wizard, Data Wizard, or the Query tools in the tool palette.
Layout Model is to physically
arrange Data model group objects on the Report. The Report Editor’s Layout
Model view enables you to define and modify the layout model objects for a
report. In this view, objects and their property settings are
represented symbolically to highlight their types and relationships.
Ans: The Live Previewer is a
work area in which you can preview your report and manipulate the actual, or
live data at the same time. In the Live Previewer you can customize
reports interactively, meaning that you can see the results immediately as you
make each change.
To activate buttons in the
Live Previewer, you must display the report output in the Runtime
Previewer. In order to edit your report, such as changing column
size ,move columns, align columns insert page numbers, edit text, change
colors, change fonts set format masks, insert field the Live
Previewer must be in Flex Mode.
Access
Title
Viewing region
Rulers
Grid
Toolbar
Style bar
Tool palette
Status bar
11. What is Parameter Form ?
Ans: Parameters are variables
for report that users can change at runtime immediately prior to the execution
of the report. You can use system parameters to specify aspects of report
execution, such as the output format, printer name, mailed or number of copies.
We can also create own parameters through sql or Pl/sql at runtime.
The Parameter Form view is
the work area in which you define the format of the report’s Runtime Parameter
Form. To do this, you define and modify parameter form objects
(fields and boilerplate).
When you run a report, Report
Builder uses the Parameter Form view as a template for the Runtime Parameter
Form. Fields and boilerplate appear in the Runtime Parameter Form
exactly as they appear in the Parameter Form view. If you do
not define a Runtime Parameter Form in the Parameter Form view, Report Builder
displays a default Parameter Form for you at runtime.
The first thing in data model
is the query. Through query we access database objects with sql
query. Compiled query creates groups. We can create query through query
builder, sql query and import query from o/s file or database.
13. What
is Group?
Ans: Groups are created to
organize the columns in your report. When you create a query, Report
Builder automatically creates a group that contains the columns selected by the
query. You create additional groups to produce break levels in the
report, either manually or by using the Report Wizard to create a group above
or group left report.
14 What is Repeating
Frame?
Ans: Repeating frames
surround all of the fields that are created for a group’s columns.
Repeating frames correspond
to groups in the data model. Each repeating frame must to be associated with a
group of data model; The repeating frame prints (is fired) once for each
record of the group.
15. What is Reference
Cursor?
A ref cursor query uses
PL/SQL to fetch data. Each ref cursor query is associated with a
PL/SQL function that returns a strongly typed ref cursor. The
function must ensure that the ref cursor is opened and associated with a SELECT
statement that has a SELECT list that matches the type of the ref cursor.
You base a query on a ref
cursor when you want to:
n more
easily administer SQL
n avoid
the use of lexical parameters in your reports
n share
datasources with other applications, such as Form Builder
n increase
control and security
n encapsulate
logic within a subprogram
Furthermore, if you use a
stored program unit to implement ref cursors, you receive the added benefits
that go along with storing your program units in the Oracle database.
16. What is Template?
Ans: Templates
define common characteristics and objects that you want to apply to multiple
reports. For example, you can define a template that includes the
company logo and sets fonts and colors for selected areas of a report. And
properties of the objects also
Creation of Template: In
Report editor , open a existing Template or Create a new Template and save it
concerned directory. Then Edit CAGPREFS.ORA File , and Specify which
type of Template are u going to develop.
Ex. Tabular,
form, matrix Then give your developed
template *.tdf file name.
Develop Report
with Newly developed Template.
17 what is Flex mode and Confine mode?
Confine mode
On: child
objects cannot be moved outside their enclosing parent objects.
Off: child
objects can be moved outside their enclosing parent objects.
Flex mode:
On: parent
borders “stretch” when child objects are moved against them.
Off: parent
borders remain fixed when child objects are moved against them.
Ans: To limit the records per
page.
Ans: The Page Protect
property indicates whether to try to keep the entire object and its contents on
the same logical page. Setting Page Protect to yes means that if the
contents of the object cannot fit on the current logical page, the object and
all of its contents will be moved to the next logical page. Ex: if you set yes,
the object information prints another page.
The
print condition types First, All, All but first; Last, All but last refer to
the frequency with which you want to appear based upon the setting of the print
condition object. A print condition object of Enclosing Object is whichever
object encloses the current object (could be the parent or a frame within the
parent), while Anchoring Object is the parent object (unless you have
explicitly anchored the object in which case it is the object to which it is
anchored). The key here is that this is about the pages on which the Print
Condition Object appears, not the current object. Oracle views First as the
first page on which any part of the Print Condition Object is printed, likewise
Last is the last page on which any part of the Print Condition Object is
printed. For objects inside a repeating frame, this condition is re-evaluated
for each instance of the frame.
Ans: The
Print Direction property is the direction in which successive instances of the
repeating frame appear.
21 What is Vertical and Horizontal
Elacity
Ans: The
Horizontal Elasticity property is how the horizontal size of the object will
change at runtime to accommodate the objects or data within it:
Ans: A
placeholder is a column is an empty container at design time. The placeholder
can hold a value at run time has been calculated and placed in to It by pl/sql
code from anther object. You can set the value of a placeholder column
is in a Before Report trigger , A report level formula
column(if the place holder column is at report level) A formula column
in the place holder group or a group below it
Uses of place holder columns
enable u to populate multiple columns from one piece of code. U can calculate
several values in one block of pl/sql code in a formula column and assign each
value into a different placeholder column. U therefore create and maintain only
program unit instead of many.
Store a Temporary value for
future reference. EX. Store the current max salary as
records are retrieved.
23 What is Formula Column?
Ans: A
formula column performs a user-defined computation on another column(s) data,
including placeholder columns.
Ans: A
summary column performs a computation on another column’s
data. Using the Report Wizard or Data Wizard, you can create the
following summaries: sum, average, count, minimum, maximum, %
total. You can also create a summary column manually in the Data
Model view, and use the Property Palette to create the following additional
summaries: first, last, standard deviation, variance.
Ans: Boilerplate
is any text or graphics that appear in a report every time it is
run. Report Builder will create one boilerplate object for each
label selected in the Report Wizard (it is named B_
Column
name). Also, one boilerplate object is sometimes created for each
report summary. A boilerplate object is owned by the object surrounding
it, unless otherwise noted.
26 What is Data Link
When we join
multiple quires in a report the join condition is stored in the data
link section
Data links relate the results
of multiple queries. A data link (or parent-child relationship)
causes the child query to be executed once for each instance of its parent
group. When you create a data link in the Data Model view of your
report, Report Builder constructs a clause (as specified in the link’s Property
Palette) that will be added to the child query’s SELECT statement at
runtime. You can view the SELECT statements for the individual
parent and child queries in the Builder, but can not view the SELECT statement
that includes the clause created by the data link you define.
28.What is Query Builder
Ans: it’s a gui tool to
build a query in Report Wizard, Data Wizard or Data model.
Ans: We can break a column
through data model , it Display once for a
group
Ans: RUN_PRODUCT and RUN_REPORT_OBJECT
40. HOW CAN U CREATE TWO
FORMATS
USING DISTRIBUTION WE CAN
CREATE DIFFERENT FORMATS
45 HOW TO DISPLY ONE RECORD PER PAGE ( WHICH
PROPERTY WE SHOULD SET)
Set Repeating Frame Properties:
Maximum records per page=1 And it will override group filter property.
In Data model Layout, Group
Property through Filter Type & No of records to display
Properties, Values are first,
last, pl/sql
47. What is Header, Body, Trailer, and Footer in Reports
Header: The
header consists of on e or more pages that are printed before report proper.
The type of Information you might want to print title of the page, company
logo and address or chart the Summarizes the report.
Trailer: The
trailer consists of one or more pages that print after the report itself,
usually used for nothing more than an end of report blank page, but also used
for a report summary or chart.
Body: The
body is where all the main report objects are placed
Margin: the
report layout only governs the part of the pages designated for the main data
portion of the report. The margins are can be used to specify page headers and
page footers.
49. what are Executable file
definitions in Reports
Report Builder (RWBLD60.EXE)
n Reports
Runtime (RWRUN60.EXE)
n Reports
Convert (RWCON60.EXE)
n Reports
Background Engine (RWRBE60.EXE)
n Reports
Server (RWMTS60.EXE)
n Reports
Web Cartridge (RWOWS60.DLL)
n Reports
CGI (RWCGI60.EXE)
n Reports
Queue Manager (RWRQM60.EXE)
n Reports
Launcher (RWSXC60.EXE)
n Reports
ActiveX Control (RWSXA60.OCX)
Aggregated Information,
Calculated information, A string Function
Can I
highlight and change all the format masks and print conditions of a bunch of
fields all at once?
You
can. If you highlight a bunch of objects and then right click and select
“properties..”, Oracle gives you a stacked set of the individual properties
forms for each of the selected objects. While this may be useful for some
things, it requires changing values individually for each object. However,
instead you can select the group of fields and then select “Common properties”
from the “Tools” menu which will allow you to set the format mask , print
conditions etc. for the whole set of objects at once.
How
do I change the printed value of a field at runtime?
Triggers
are intended to simply provide a true or false return value to determine
whether an object should be printed. It is generally not allowed to change any
values held in the cursor, make changes to the database, or change the value of
it’s objects value.
That
being said, there is a highly unpublicized method of doing just that using the
SRW.Set_Field_Char procedure.
The
syntax is SRW.Set_Field_char (0,) and the output of the object that the current
trigger is attached to will be replaced by.
There
are also SRW.set_fileld_num and SRW.set_field_date for numeric or date
fields.
While these options do work, they should only be used if a suitable NVL or DECODE statement in the original query is not possible as they are much, much slower to run. Also, note that this change of value only applies to the formatted output. It does not change the value held in the cursor and so cannot be used for evaluating summary totals
While these options do work, they should only be used if a suitable NVL or DECODE statement in the original query is not possible as they are much, much slower to run. Also, note that this change of value only applies to the formatted output. It does not change the value held in the cursor and so cannot be used for evaluating summary totals
The
capability of producing multiple copies of a given report or portion of it in
different output formats is referred to as report bursting.
Additional
layout created for to different format using same query and groups
without modifying default layout created by report wizard., we can
use both layouts according to user requirement.
Ans:
Current date, Page Number, Panel number, Physical Page Number, Total Pages,
Total
Panels, Total Physical Pages.
Link File : Is a special type of boilerplate, that doesn’t have to remain
constant for each report run
The
type of file contents, can be Text, Image, CGM, Oracle drawing format, or
image URL
Source
filename: the name of the file the u want link to the report through
import Image from
