-->Pseudo columns:
Automatically filled by oracle.
Ex: sysdate, nextval, currval, rowid, rownum,level, sqlcode, sqlerrm, new, old .
--> What is External TABLE
External tables can be used to load flat files into the database. Steps: First create a directory say ext_dir and place the flat file (file.csv) in it and grant read/write access to it. Then create the table as below: create table erp_ext_table ( i Number, n Varchar2(20), m Varchar2(20) ) organization external ( type oracle_loader default directory ext_dir access parameters ( records delimited by newline fields terminated by , missing field values are null ) location (file.csv) ) reject limit unlimited;
--> Explain the purpose of %TYPE and %ROWTYPE data types with the example?
Answer: PL/SQL uses %TYPE declaration attribute for anchoring. This attribute provides the datatype of a variable, constant or column. %TYPE attribute is useful while declaring a variable that has the same datatype as a table column.
For example, the variable m_empno has the same data type and size as the column empno in table emp.
m_empno emp.empno%TYPE;
%ROWTYPE attribute is used to declare a variable to be a record having the same structure as a row in a table. The row is defined as a record and its fields have the same names and data types as the columns in the table or view.
For example: dept_rec dept%ROWTYPE;
This declares a record that can store an entire row for DEPT table.
--> What are the Parameters for raise_application_errror()?
The parameters are: Error Code and an Error Message.
The Syntax is: raise_application_error(Error Code, Error Message);
-- Show the cursor attributes of PL/SQL.
%ISOPEN : Checks if the cursor is open or not
%ROWCOUNT : The number of rows that are updated, deleted or fetched.
%FOUND : Checks if the cursor has fetched any row. It is true if rows are fetched
%NOT FOUND : Checks if the cursor has fetched any row. It is True if rows are not fetched.
--> What is the difference between View and Materialized view?
Materialized view will not be refreshed every time you query the view so to have good performance when data is not changed so rapidly we use Materialized views rather than normal views which always fetches data from tables every time you run a query on it.
-- Can a view be updated/inserted/deleted? If Yes – under what conditions?
A View can be updated/deleted/inserted if it has only one base table if the view is based on columns from one or more tables then insert, update and delete is not possible.
-- Can triggers be used on views? If so How?
Yes only INSTEAD OF trigger can be used to modify a view. CREATE OR REPLACE TRIGGER trigger_name INSTEAD OF INSERT ON view name begin ... end;
--> Sequence of Exceptions
--> What is global temporary table?
Global temporary tables are session specific, meaning the users in other sessions cannot see or manipulate the data in the temporary table you have created. Only you can access or insert or delete or perform anything with the data in the temporary table in your session and the other users cannot use or access this. Once you end your session, the data in the temporary table will be purged.
-- How do you retrieve the last N records from a table?
A: The RANK() and DENSE_RANK() functions can be used to
determine the LAST N or BOTTOM N rows.
-- 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
-- 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,)
-- What are triggers in reports?
A. They are 5 types of triggers in reports.
1. Before Parameter form
2. After Parameter form
3. before Report
4. between Pages
5. after Report
-- What are the User PARAMETERS in the Reports?
P_CONC_REQUEST_ID
P_FLEX_VALUE
-- What is the order of execution of report trigger?
4) Order of execution of report trigger:
1. Before Parameter Form trigger is fired.
2. Runtime Parameters Form appears (if not suppressed).
3. After Parameters Form trigger is fired(unbless the user cancels from the Runtime Parameter Form).
4. Report is "compiled".
5. Queries are parsed.
6. Before Report trigger is fired.
7. Set TRANSACTION READONLY is executed(if specified via the READONLY argument or setting).
8. The report is executed and the Between Pages trigger fires for each page except the last one. (Note that data can be fetched at any time while the report is being formatted).
-- What is user exit?
A) User exit is a program which will transfer the control from report execution to another third generation language it will the required data & it will complete the remaining report execution process.
--> What Are The Various Sections In The Data Template?
Parameter section
Trigger Section
Sql statement section
Data Structure section
Lexical Section
-- What Are Sub-templates?
Sub-templates are secondary RTF or XSL templates that are imported by primary RTF or XSL report templates. The primary template accesses the sub-template through the XSL import style sheet feature.
The sub-template files are independently stored and are not registered in association with a data source or primary template.
-- When is Custom.pll used?
Custom.pll is used while making new or customizing standard oraclke forms in apps. It contains all the forms libraries for apps.
-- What are the two parameters that are mandatory for pl/sql type concurrent prog?
Procedure/Function (ERRBUF OUT, RETCODE OUT…)
ERRBUF: Used to write the error message to log or request file.
RETCODE: Populate log request file with program submission details info.
-- What is Bulk binding of Bilk collect?
Bulkbind:-
- 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 (varay, nested tables, index-by table or host array) as bind variables back and forth.
- Prior to Oracle 81, 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 SLECT INTO, FETCH INTO and RETURNING INTO clauses.
Syntax:- BULK COLLECT into collection_name, ……
No comments:
Post a Comment