INTERVIEW QUESTIONS FOR RICE
INTERVIEW QUESTIONS AND ANSWERS
1. What is NOCOPY?
By default the IN parameter is passed by reference and the OUT and IN OUT parameters are passed by value.
NOCOPY is a compiler hint that can be used with OUT and IN OUT parameter to request to pass by reference. This improves the performance with OUT and INOUT parameters.
2. REPORT TYPES:
A) Tabular Report
B) Group Left Report
C) Group Above Report
D) Form like report
E) Matrix Report
F) Matrix with group
G) Mailing Label Report
H) Form Letter
3. Anchors: are used to determine the vertical and horizontal positioning of a child object relative to its parent object. The end of the anchor should be attached to the parent object. An Anchor can relate two child objects to one parent object.
Anchor Properties:
A) Child Edge percent on child edge type
B) Child Object Name
C) Collapse Horizontally ---- If the parent object does not print, the child object moves horizontally into the space vacated by the parent.
D) Collapse vertically
E) Comments
F) Name
4. User Exits:
Program that can be written and linked into the report builder executable or user exit DLL files.
User exits are build when ever the control need to be passed from the report builder to a program, which performs some function and then control returns to the Report Builder.
Example:
Function BeforeReport return boolean is
Begin
SRW.USER_EXIT('FND SRWINIT');
:CP_1:=FND_GLOBAL.USER_ID;
:CP_2:=FND_GLOBAL.USER_NAME;
Return (TRUE);
End;
Function AfterReport return boolean is
Begin
SRW.USER_EXIT('FND SRWEXIT');
return (TRUE);
End;
Function AfterReport return Boolean is
Begin
IF :SAL >= 0 THEN
SRW.REFERENCE(:SAL);
SRW.USER_EXIT('STORE SAL');
ELSE
SRW.MESSAGE(100, 'FOUND A NEGATIVE SALARY. CHECK THE
EMP TABLE.');
END IF;
EXCEPTION
WHEN SRW.UNKNOWN_USER_EXIT THEN
SRW.MESSAGE(200, 'STORE USER EXIT WAS UNKNOWN.
CHECK IF IT''S LINKED.');
WHEN SRW.USER_EXIT_FAILURE THEN
SRW.MESSAGE(200, 'STORE USER EXIT FAILED.
CHECK ITS CODE.');
Return (TRUE);
End;
Types of user exits :-
A) Oracle Pre-Complier user exits.
B) OCI (Oracle Call Interface user exits)
C) Non – Oracle user exits.
User exits can perform the following tasks.
Perform complex data manipulation.
Pass data to report builder from OS text files.
Support PL/SQL blocks.
Control real time devices be printer or robot.
5.Report Triggers: - Before Parameter form Trigger
- Fires before the runtime parameter form is displayed.
- The parameter values can be accessed and changed.
After Parameter form Trigger.
- Fires after the runtime parameter form is displayed.
- The parameters can be accessed and their values can be checked.
Before Report Trigger
- Fires before the reports is executed but after the queries are passed and data is fetched.
Between Pages Trigger.
- Fires between each page of the report are formatted, except the very first pages.
- This is used for customized page formatting.
After Report Trigger
- Fires after exiting from the run time premier or after report output is sent to a specified destination. (File, Printer, Mail id etc….)
- This is used to clean up any initial processing that was done such as deleting the tables.
- This Trigger always fires irrespective of success or failure of the report.
DATA TRIGGERS:
REF CURSORS QUERY:
This uses PL/SQL to fetch data for the report.
- In this a PL/SQL function need to be specified to return a cursor value from a cursor variable.
Weak REF CURSORS vs. Strong REF CURSORS
---------------------------------------
When a cursor subquery appears in the select list of some SQL statement, the
Corresponding column must be fetched into a PL/SQL REF CURSOR variable. Only
Weak REF CURSOR variables are currently supported. For example,
-- A cursor subquery column CAN be fetched into myweakcur
TYPE weak_ref_cursor is REF CURSOR;
myweakcur weak_ref_cursor;
-- A cursor subquery column CANNOT be fetched into mystrongcur
TYPE strong_ref_cursor is REF CURSOR RETURN emp%ROWTYPE;
mystrongcur strong_ref_cursor;
The primary benefit of strong REF CURSORS is to permit certain checks to be
Performed during compilation, preventing runtime errors that would occur if the rows returned don't match the variables into which they are fetched. So It is possible for a program using cursor subqueries to compile cleanly and fail at runtime if care is not taken to ensure that the cursor subquery and any fetches against the resulting REF CURSOR match.
Example:
DECLARE
TYPE REFCUR IS REF CURSOR;
A REFCUR;
B REFCUR;
VEMP EMP%ROWTYPE;
VDEPT DEPT%ROWTYPE;
BEGIN
OPEN A FOR SELECT * FROM DEPT;
LOOP
FETCH A INTO VDEPT;
EXIT WHEN A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(VDEPT.DEPTNO||' '||VDEPT.DNAME);
OPEN B FOR SELECT * FROM EMP WHERE DEPTNO=VDEPT.DEPTNO;
LOOP
FETCH B INTO VEM
P;
EXIT WHEN B%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(VEMP.EMPNO||' '||VEMP.ENAME);
END LOOP;
CLOSE B;
END LOOP;
CLOSE A;
END;
Group Filter:
This is PL/SQL function that determines which records to be included in a group in the property are PL/SQL.
- The function must return a BOOLEAN value.
True ……. Includes the current record in the report.
False ……. Excludes the current record from the report.
A group filter determines which records to include in a group. You can use the packaged filters, first and last, to display the first n or last n records for the group, or you can create your own filters using PL/SQL.
Example:
function filter_comm return boolean is
begin
if :comm IS NOT NULL then
if :comm < 100 then
return (FALSE);
else
return (TRUE);
end if;
else
return (FALSE); -- for rows with NULL commissions
end if;
end;
Formula
- These are PL/SQL functions that populate formula or place holder columns.
Validation Trigger
- These are also PL/SQL functions that are executed when parameter values are specified on the command line and when the runtime parameter form is accepted.
- Are also used to validate the initial value property of the parameter.
Layout Triggers
Format Trigger.
- These are PL/SQL functions executed before the object is formatted.
- Used to dynamically change the formatting attributes of the object.
Action Trigger
- These are PL/SQL procedures executed when a button is selected in the run time
Pre-viewer.
- This can be used to dynamically call another report or execute any other PL/SQL.
Example:
procedure U_1ButtonAction is
begin
SRW.RUN_REPORT('report=C:\TEMP\EMP.RDF');
-- srw.run_report('batch=yes report=C:\TEMP\EMP.RDF');
EXCEPTION
when srw.run_report_failure then
srw.message(30, 'Error mailing reports.');
raise srw.program_abort;
end;
6 Formula Column
It performs a user-defined computation on another columns data, including Place-
holder columns.
Formulas are PL/SQL functions that populate formula or place holder columns.
Cannot be used to populate parameter values.
7 Summary Column
Performs a computation on another columns data like sum, average, count, minimum, maximum, %, total.
For group reports, the report wizard and data wizard create ‘n’ summary fields in the data model for each summary column that is defined.
One at each group level above the column being summarized.
One at the report level.
8 Place Holder Column
- A Place holder column is a column for which, the data type and value can be set dynamically (Programmatically)
The value can be set for a placeholder column in the following places.
- Before report trigger if the placeholder is a report level column.
- Report level formula column, if the placeholder is a report level column.
- A formula in the place holders group below it (The value is set once for each record of the group)
9 Repeating Frame
- Repeating frame surrounds all of the fields that are created for group’s columns.
- Repeating frame prints once for each record of the group.
- For frames and repeating frames, the property elasticity defines whether the size of the frame or repeating frame should with the objects inside of it at runtime.
10 Frame
- Surrounds the objects and protect them from being over written or pushed by other objects.
11.System Parameters:
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., ",").
12 Data Link
- Data links relate the results of multiple queries.
- A data link (Parent – Child Relation Ship) causes the child query to be executed once for each instance of its parent group.
Three types of data links are
- Query to query
- Column to column
- Group to group
13 In which tables FF are stored?
A) FND – ID – FLEXS
B) FND-ID-FLEX-STRUCTURES
A flexfield is a field made up of sub–fields, or segments. A flexfield
appears on your form as a pop–up window that contains a prompt for
each segment. Each segment has a name and a set of valid values.
There are two types of flexfields: key flexfields and descriptive
flexfields.
14 Advantages of stored functions and procedures
* Applications can be modularized.
* Easy maintenance.
Routines can be modified online without interfering other users.
One routine can be modified to effect multiple applications.
* Improved data security and integrity.
- Indirect access to database objects can be controlled from non-privileged users with security privileges.
* Improved performance.
- Reparsing for multiple users can be avoided by exploiting the shared SQL area.
- PL/SQL parsing at run-time can be avoided by pursing at compile time.
- Number of calls to the database can be reduced and network traffic decreased by bundling commands.
* Improved code clarity.
- The clarity of code increases by using appropriate identifier names to describe the
action of the routines that reduces the need for comments.
15 Difference between a function and a procedure
Functions Procedures
* Invoke as a part of an expression. Execute as a PL/SQL statement.
* Must contain a RETURN clause Do not contain a RETURN
in the header. Clause in the header.
* Must return a single value. Can return none, one or many
values.
* Must contain at fast one RETURN Can contain a RETURN
Statement. Statement.
* Do not contain OUT and INOUT Can contain IN, Out and
IN OUT parameters. IN OUT Parameters.
16 About Cursors
- Oracle server uses some private work areas to execute SQL statements and to store processing information.
- By using PL/SQL cursors these private SQL areas can be named and the stored Information can be accessed.
Two Types:
* Implicit Cursors.
- Implicit cursors are declared by PL/SQL implicitly for all DML and PL/SQL select statements, including queries that return only one row.
- Oracle Server implicitly opens a cursor to process each SQL statement not associated with on explicitly declared cursor.
- The most recent implicit cursor can be returned as the SQL cursor.
* Explicit Cursors
- For queries that return more than one row, explicit cursors are declared and named by the programs and manipulated through specific statements in the block’s executable actions.
- Explicit cursors are used to individually process each row returned by a multiple-row SELECT statement.
- The set of rows returned by a multiple – row query is called as active set.
Cursor Attributes: -
Attribute Type Description
% is open -- Boolean Evaluates to TRUE if the cursor is open.
% not found -- Boolean Evaluates to TRUE if the most recent fetch
doesn’t return a row.
% found -- Boolean Evaluate to TRUE if the most recent fetch
returns a row. Complement of % not found.
% Row Count -- Number Evaluates the total number of rows returned
so far.
Parameterized Cursors: -
- Parameters can be passed to the cursor in a cursor for loop.
- It allow to open and close an explicit cursor several times in a block, returning a different active set on each occasion for each execution, the previous cursor is closed and reopened with a new set of parameters.
- Sizes should not be mentioned for the data types of parameters the parameters names are for references in the query expression of the cursor.
Example:
DECLARE
CURSOR c1(test NUMBER,test1 VARCHAR2) IS SELECT * FROM emp WHERE empno=test AND ename=test1;
R1 C1%ROWTYPE;
BEGIN
OPEN c1(7369,'SASHI');
FETCH C1 INTO R1;
DBMS_OUTPUT.PUT_LINE(R1.JOB);
END;
17 Confined Modes: -
- If it is on, child objects cannot be moved outside their enclosing parent objects.
- If it is off child objects can be moved out sides their enclosing parent objects.
Flex Mode: -
- If it is on, parent borders stretch when child objects are moved against them.
- If it is off, parent borders remain fixed when child objects are moved against them.
18 Parameters
- A parameter is a variable whose value can be set at runtime (from the run time parameter of the command line).
- User parameters are created by the user and system parameters are created by Report Builder.
- System parameters cannot be renamed or deleted.
Bind Parameters (Variables)
- Bind references (or Variables) are used to replace a single value in SQL or PL/SQL, such as a character string, number or date.
- Bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY and START WITH clauses of queries.
- Bind references cannot be referenced in FROM clauses.
- Bind variables can be referenced by entering a colon (:) followed immediately by the column or parameter name.
- If the parameter / column is not created before making a bind reference, report builder will create a parameter.
Lexical Parameters (Variables)
- Lexical references are placeholders for text that is embedded in a SELECT statement.
- Lexical Variables can replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH.
What is % Row type?
- % Row types is used to declare a record based on a collection of columns in a database table or view.
- The fields in the record take their names and data types from the columns of the table or view.
- The record can also store an entire row of data fetched from a cursor or cursor variable.
- % Row type should be prefixed with the database table.
Ex: Declare
Emp_record employee 5% row type.
Then emp_record will have a structure consisting of all the fields each representing a column in the employee’s table.
What is a Ref Cursor?
- Oracle server uses unnamed memory spaces to store data used in implicit cursors.
- Ref cursors are used to define a cursor variable, which will point to that memory space and can be used like pointers in SQL ‘S’.
About Exceptions
- An exception is an identifier in PL/SQL that is raised during the execution of a black that terminates its main body of actions.
- A block always terminates when PL/SQL raises an exception so that an exception handler should be specified to perform final actions.
* Exception can be raised in two ways exception is raised automatically.
1. Ex: - when no rows are retrieved from the database in a SELECT statement, then error
ORA-01403 occurs and the exception NO-DATA-FOUND is raised by PL/SQL.
2. Ex: - Exception can be raised explicitly by issuing the RAISE statement with in the
Block.
- The exception being raised may be either.
User-Defined or Pre Defined
Trapping an exception: -
- If the exception is raised in executable section of the block, processing branches to the corresponding exception handler in the exception section of the block.
- If PL/SQL successfully handles the exception, then the exception doesn’t propagate to the enclosing block or calling environment.
- The PL/SQL block terminates successfully.
Propagating an exception: -
- If the exception is raised in the executable section of the block and there is no corresponding exception handler, the PL/SQL block terminates with failure and the exception will be propagated to the calling environment.
Types of exceptions: -
A) Pre-Defined Oracle Server Exceptions. -Implicitly Raised.
B) Non-Pre defined Oracle server exceptions. -Implicitly Raised.
C) User-defined exceptions -Explicitly Raised.
Pre-Defined Oracle Server Exceptions: -
- These are the error (20) that occurs most often in PL/SQL code.
- These exceptions need not be declared and raised implicitly by Oracle Server, NO-DATA-FOUND, LOGIN_DENIED, and ZERO_DIVIDE.
BEGIN
. . .
EXCEPTION
WHEN NO_DATA_FOUND THEN
statement1;
statement2;
WHEN TOO_MANY_ROWS THEN
statement1;
WHEN OTHERS THEN
statement1;
statement2;
statement3;
END;
Non-Pre-Defined Oracle Server Exceptions: -
- These are the other standard Oracle Server errors.
- These exceptions need to be declared in the declarative section and raised by Oracle server implicitly.
DEFINE p_deptno = 10
DECLARE
e_emps_remaining EXCEPTION;
PRAGMA EXCEPTION_INIT
(e_emps_remaining, -2292);
BEGIN
DELETE FROM departments
WHERE department_id = &p_deptno;
COMMIT;
EXCEPTION
WHEN e_emps_remaining THEN
DBMS_OUTPUT.PUT_LINE (’Cannot remove dept ’ ||
TO_CHAR(&p_deptno) || ’. Employees exist. ’);
END;
User Defined Exceptions: -
- These are the conditions that the developer determines as abnormal.
- These need to be declared and raised explicitly.
DECLARE
e_invalid_department EXCEPTION;
BEGIN
UPDATE departments
SET department_name = ’&p_department_desc’
WHERE department_id = &p_department_number;
IF SQL%NOTFOUND THEN
RAISE e_invalid_department;
END IF;
COMMIT;
EXCEPTION
WHEN THEN e_invalid_department THEN
DBMS_OUTPUT.PUT_LINE(’No such department id.’);
END;
PRAGMA EXCEPTION_INIT
Statement is used to associate a declared exception with the standard Oracle Server error number.
Syntax: - PRAGMA EXCEPTION_INIT (exception, error number)
* SQLCODE, SQL ERRM are two functions that can be used to identify the associated error code or error message when exception occurs.
- SQLCODE function returns the numeric value for the error code.
- SQLERRM function returns the character data containing the message associated with the error number.
- SQLCODE f SQLERRM cannot be used directly in SQL statements.
RAISE_APPLICATION_ERROR
Executable section:
BEGIN
...
DELETE FROM employees
WHERE manager_id = v_mgr;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20202,
’This is not a valid manager’);
END IF;
...
Exception section:
...
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (-20201,
’Manager is not a valid employee.’);
END;
What is Dynamic SQL?
- Dynamic SQL is a SQL statement that contains variables that can change during runtime.
- It is a SQL statement with place holders and is stored as a character string.
- Dynamic SQL enables DDL, DCL or session control statements to be written and executed (by) from PL/SQL.
* Dynamic SQL can be written in two ways.
A) DBMS_SQL. -��8i
B) Native Dynamic SQL. -��8i
- Basically Dynamic SQL means creating the SQL statements dynamically at runtime by using variables.
Ex: - Dynamic SQL can be used to create a procedure that operates on a table whose name is not known until runtime or to execute DDL/DCL/SCS statements.
In PL/SQL such statements cannot be executed statically.
EXECUTE IMMEDIATE Statement can perform dynamic single row queries.
CREATE OR REPLACE PROCEDURE delete_all_rows
(p_tab_name IN VARCHAR2, p_rows_del OUT NUMBER)
IS
cursor_name INTEGER;
BEGIN
cursor_name := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_name, ’DELETE FROM ’||p_tab_name,
DBMS_SQL.NATIVE );
p_rows_del := DBMS_SQL.EXECUTE (cursor_name);
DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;
/
Using the EXECUTE IMMEDIATE statement
EXECUTE IMMEDIATE dynamic_string
[INTO {define_variable
[, define_variable] ... | record}]
[USING [IN|OUT|IN OUT] bind_argument
[, [IN|OUT|IN OUT] bind_argument] ... ];
INTO is used for single-row queries and specifies
the variables or records into which column values
are retrieved.
USING is used to hold all bind arguments. The
default parameter mode is IN.
CREATE PROCEDURE del_rows
(p_table_name IN VARCHAR2,
p_rows_deld OUT NUMBER)
IS
BEGIN
EXECUTE IMMEDIATE ’delete from ’||p_table_name;
p_rows_deld := SQL%ROWCOUNT;
END;
What are Autonomous Transactions?
- Autonomous transactions are the processes run independently of its parent.
- By means of Autonomous Transaction, the current transaction can be temporarily suspended and another operation can be begun.
- The basic idea behind this is to have some operation take place independently of the current transaction.
Ex: - to allow error messages written to table to be committed but to rollback everything
else that has taken place prior to the error.
- The autonomous or child transaction can commit or rollback as applicable with the execution of the parent transaction being resumed upon its completion.
- The parent may then perform further operations of any operations performed with in the child transaction.
- By using Autonomous Transactions, modular and reusable components can be developed more easily.
- In fact Oracle already uses similar functionality internally, known as recu transactions to handle the updating of system resources.
Ex: - When one application selects ‘nextval’ from a non eached sequence, the value is in
the database.
- Thus a second application will always get the incremented application has committed or rolled back.
- Autonomous Transaction should be defined in PL/SQL in the following manner.
PRAGMA AUTONOMOUS_TRANSACTION;
- Autonomous transaction also can be nested.
- The parent transaction remains active while any statements specified in the declare section of the autonomous unit are executed.
- As the code unit exits and control returns to the parent the main (parent) transaction is resumed and the transaction context is switched back to the parent.
Example:
Create or replace trigger emp_trig
After insert on emp
Declare
Pragma autonomous_transaction;
Begin
Insert into log values(‘The user has inserted’);
Commit;
End;
What is Bulk binding of Bulk collect?
Bulk bind: -
- The assignment of values to PL/SQL variables in SQL statements is called binding.
- The binding of an entire collection at once is refilled to as bulk binding.
- Bulk bind improves performance by minimizing the number of context switches between PL/SQL and SQL engines while they pass an entire collection of elements (varray, nested tables, index-by table or host array) as bind variables back and forth.
- Prior to Oracle 8i, the execution of every SQL statements required a switch between the PL/SQL and SQL engines, where as bulk binds use only one context switch.
Bulk binding includes the following
A) Input collections; use the FORALL statement.
B) Output collections, use the BULK COLLECT clause.
Input Collections: -
- Input collections are data passed from PL/SQL engine to the SQL engine to execute INSERT, UPDATE and DELETE statements.
Syntax: - FORALL index in lower bound.. Upper bound sql_statement;
Output Collections: -
- Output collections are the data passed from the SQL engine to the PL/SQL engine as a result of SELECT or FETCH statements.
- The keyword BULK COLLECT can be used with SELECT INTO, FETCH INTO and RETURNING INTO clauses.
Syntax:- BULK COLLECT into collection name, ……
Example: select * bulk collect into variable_record from emp;
DECLARE
CURSOR c_orders IS
SELECT order_id, currency_code, amount_local /* bulk bind */
FROM open_orders;
TYPE t_num_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
TYPE t_char_array IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
v_order_ids t_num_array;
v_currency_codes t_char_array;
v_amounts_local t_num_array;
v_amounts_usd t_num_array;
v_row_count NUMBER := 0;
BEGIN
OPEN c_orders;
LOOP
FETCH c_orders
BULK COLLECT INTO v_order_ids, v_currency_codes, v_amounts_local
LIMIT 100;
EXIT WHEN v_row_count = c_orders%ROWCOUNT;
v_row_count := c_orders%ROWCOUNT;
FOR i IN 1..v_order_ids.count LOOP
v_amounts_usd(i) := currency_convert (v_amounts_local(i),
v_currency_codes(i));
END LOOP;
FORALL i IN 1..v_order_ids.count
UPDATE open_orders /* bulk bind */
SET amount_usd = v_amounts_usd(i)
WHERE order_id = v_order_ids(i);
END LOOP;
CLOSE c_orders;
COMMIT;
END;
DECLARE
TYPE test IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
test1 test;
indx INTEGER;
BEGIN
FOR i IN 1..10 LOOP
test1(i):=10;
NULL;
END LOOP;
FORALL INDX IN VALUES OF test1
INSERT INTO emp(empno) VALUES test1(INDX);
END;
What are Materialized Views and Snapshots?
Materialized View: -
- A Materialized view is a replica of a target master from a single point in time.
- In Oracle 7, it is termed as SNAPSHOT
- Oracle 7.1.6 --Uptable Snapshots
- Oracle 7.3 -Primary Key Snapshots
- Oracle 8 -Materialized view
- Oracle 9 -Multifier Materialized View.
- Materialized views can be used both for creating summaries to be utilized in data warehouse environments.
* Replicating data in distributed environments.
Target Master -The table(s) which is (are) referenced by the MVIEW query.
Base Table -The tables are that is (are) created by MVIEW create statement and that stores data that satisfy the MVIEW query.
Syntax: - Create materialized view <name>
Refresh fast
No comments:
Post a Comment