Oracle Sql Pl/Sql Faqs

1.   What is the diff between Exist and In Operator?

·         Exists return Boolean and in return values.

2.   What is the Diff between Delete and Truncate and Drop?

·         delete –dml explicit commit required,

·         truncate- ddl implicit commit,

·         drop –ddl drop db objects permanently from db.

·         Delete-we can use in where clause

3.   Can we drop the table if table is having the data?

·         Yes

4.   I have executed the Delete command after that I have created table wether deletions will be commit or not?if table is success fully created?

·         Not commit

5.   I have executed the Delete command after the I have created table weather deletions will be committed or not? if table is not created?

·         Error table or view does not exists

6.   What is RowID ? When it will be created? What is the format? What is the diff between RowID and Rownum?

·         It is pseudocol associated to each row of table,18 charcters long hexadecimal,rowid is associated when each row is created while rownum is attached to each row when it is retrieved from db in a query.

7.   Can we use RowID in the where clause ? if Yes can we use like follows

·         Yes

8.   Where rowid = 12;

·         No. We have to use hexadecimal format.

9.   What is Decode operator and what is the syntax when u have used in Oracle Apps?

·         it is a fn used in sql for if-else-then as in procedural language ,compares expression value by value and if matches return corresponding result else return default and if default is omitted return null

·         syntax: decode(gender,’m’,1,’f’,1,0);

10.What is NVL operator and what is the syntax when u have used in Oracle Apps?

·         it is function used to deal with null values. it return value if found col value null else return col value

·         syntax: nvl(commission,0);

11.What is Join and types of Joins and what is Outer Join?

·         It is query that combines rows from 2 or more tables.

o   Types: equi, nonequi, self, outer join

·         Outer join is a join which extract all the rows from one table and matching rows from other table based on the outer join operator(+)

12.What is Set operator and what are the types what is the diff between Set operators and Joins?

·         These operator are used to join the o/p of 2 queries.

o   types: union, union all, intersect, minus.

·         Difference - Set operator require same structure of both queries while join need not require this

13.What is View? What is the advantage? What are the types of views we have?

·         It is also a db objects .it is virtual table that is not having data of its own.

·         Easy retrieval of the data from database

·         Types: updatable, not updateable (or) simple ,complex

14.When we can not update the view?

·         When it contains joins, set operators, rownum, group by ,having

15.What is Materialized view and what is Snap shot?

·         It is replicas of data from remote database, local copy of remote table, used for replication, distributing, computing. Used in large data like db warehousing.

·         It is a view which is associated with a query having any no of joins and any no of group functions

·         We can’t perform DML operations in Materialized views

·         Snap shot is used for increase the performance by reducing network traffic by reducing i/o

·         In older version of oracle is called as snapshot

·         It is used for replication of data between distributed databases.

16.What is the diff between Materialized view and Snap shot?

·         No difference

17. What are the Inline views?

·         Sub query defined in from clause of select statement.

·         In place of table name in from clause of select statement, we write sub query that is inline view.

·         It is also a virtual table.

18.What is synonym and what are the types and what is the advantage?

·         Synonym is an alternate name for a table, view.

·         Two types – public, private      

19.What is Index? Advantage of Index? Types of Indexes?

• Create indexes if u frequently want to retrieve less than 15% of the rows in a large table
• index columns used for joins to improve the performance on joins.
• Don’t use indexes on tables having less no rows.
• If the column has non-unique values u can use indexes on these columns.
• Don’t use indexes if the data type is lob, CLOB & BLOB.
• If the table is read only we can create more indexes on the table.
• We can't create indexes on views.
• Indexes are logically & physically independent of data
• If the index is developed all applications continue to function          

·         Index is an ordered list of contents of a column or group of columns in a table. Index created on a single table Simple Index and which is created on multiple tables is called Composite Index.

20.What is the Bit mapped Index advantage?

·         It is used for low coordination number

21.What is sub-query and what is Co-Related Sub Query and diff between those two?

·         Sub Query  is select statement which is embedded with the other select statement and which executes once before the main query execution.

·         Co Related sub query is for row by row processing, in this first outer query will be executed once based on that iner query will be executed for each row.

22.What is the syntax for To Date function?

·         To_Date (DD-MON-RR)

23.What is diff between Replace and Translate?

·         Replace is to replace character by character in a string

·         Where as Translate is used for to replace string

24.How to execute DOS Commands from SQL Prompt?

·         HOST

25.How can we find out no of Indexes we have create against a Table?

·         USER_INDEXES

26.How to delete duplicate rows from the table?

                  DELETE FROM EMP A

                  WHERE ROWID>(SELECT MIN(ROWID)

                  FROM EMP B

                  WHERE A.EMPNO=B.EMPNO)

27.What is where clause?

·         Conditional Clause

28.What is having clause and what is the diff between these two clauses?

·         Having Clause is used with Group Functions

·         Where Clause is used for simple conditions

29.How to handle the table level locks and column level locks?

·         Shared/exclusive -When 2 transaction wants to read/write from db at the same time.

·         Table level, row level

·         Row Exclusive locks are obtained when updating, inserting or deleting rows

·        Duration of locks

·         All locks acquired during a transaction are released when the transaction is committed

·         All locks are acquired during a transaction are released when the transaction is rolled back

·         All locks are acquired after a savepoint are released when the transaction is rolled back to the savepoint

·         Dead lock-It is will come in multi-user environment

·         When the two users issues update statements then dead lock will come

·         Dead- 1trans updates EMP and dep

·         2 trans update dep and EMP

·         Dead locks are released when commit/rollback statements are issued or logging off

30.What are the constraints we have and how to declare?

·         Which enforces the rules at table level

·         Primary Key

·         Foreign Key

31.What is the diff between primary key and Unique?

·         Primary is a combination of unique

·         Table can have only one Primary Key

·         Table can have number of Unique Keys

32.Can we compare two Null values? Can we insert more than one null value in the unique column?

·         No, Yes

33.What are CBO and RBO? What is the diff between these two?

·         Cost Based optimization

·         Role Based optimization

34.What are the performing tuning levels?

35.What is Explain plan and what is TKPROFF?

36.How can u tune the query?

-       EXPLAN_PLAN

37. How to display odd rows

SELECT ROWNUM,ENAME FROM EMP

GROUP BY ROWNUM,ENAME

HAVING MOD(ROWNUM,2)=1

PL/SQL

1.   What is Exception? Types of Exceptions? Exception Propagations?

·         Exception is a warning or error condition in oracle

o   User defined

o   Pre-defined

o   Non pre-defined

2.   What is RAISE_APPLICATION_ERROR? And what is PRAGMA EXCEPTION_ INIT?

·         It is user defined exception,

·         it is used to write the our own error message with oracle error number

3.   What are the Predefined exceptions we have?

Predefined exception is nothing but already defined by Oracle,

                 

4.   When we SELECT statement in the Execution block what are the Predefined exceptions we have to use in the Exception Block?

NO_DATA_FOUND

5.   What is CURSOR? What are the Cursor types? What are cursor declaration steps?

·         Cursor is nothing but a private SQL work area which is used to store process information.

·         Types – Implicit – Explicit

6.   What is the diff between Implicit and Explicit and Ref Cursor?

·         Implicit – It is defined by the Oracle Server for queries that return only one row.

·         Explicit – Which is defined by the Users, for queries that return more than one row

·         Ref – With this we can change the select statement dynamically.

Without closing we can again open the cursor.

7.   In which program u has used the Cursor? One example for implicit cursor?

8.   What are the Cursor attributes we have?

·         %OPEN              

·         %NOT FOUND

·         %FOUND

·         %ROWCOUNT

9.   Can we issue the commit inside of the Cursor For Loop?

·         Yes

10.What is For Update OF Clause in the Cursors?

·         Is used in cursor to lock the rows first before u performs DML operations.

11.What is Procedure and what is Function?

·         Procedure: Is used perform an action

·         Function: Is used to compute a value

12.What is the RND and WND?

RND – Read No Database

WND – Write No Database

13.What is the diff between Procedure and Function?

·         Function must return at least one value, where procedure may or may not return value.

14.If we drop the table which we have used in the procedure do we need to recompile the procedure then how?

Yes, we need to recompile the procedure by using below command

 - ALTER PROCEDURE COMPILE

15.How to get the Procedure Source code from database?

      SELECT * FROM USER_SOURCE   -  Text is the column name

16.What is the Advantage of Package?

·         We can declare the global variables

·         Other users can’t see the package body or logic what it contains.

17.What are the other objects we can group inside of Package?

                  Procedures              

                  Functions

                 

18.When we will go for creation of Package? Can we create Package body without creation of Package Specification?

·         NO

19.Can we declare Procedure directly in the package body without declaring in the package specification?

·         Yes

20.What is Trigger?

·         Trigger is a PL/SQL block when ever an event occurs it will fire.

21.Can we commit inside of trigger? How to delete the Trigger? How many triggers we can use maximum?

·         No  - You can’t Commit Inside a Trigger

·         Drop Trigger

·         12

22.What are The DML Triggers? What is diff between Row and Statement level trigger?

·         Insert, Update, Delete – DML Trigger

·         Row level  - For every row, when row doesn’t exists it will not fire

·         Statement level – only once

23.How to Debug PL/SQL Code?

·         DBMS_OUTPUT Package

24.What are the Trigger Predicates we have?

·         :OLD  :NEW

25.What is diff between Trigger and Procedure?

·         It is action based

·         When ever we need we can call

26.What is Package Variable what is advantage?

·         The variable which are declared in the Package Specifications

o   We can use these variables any where

27.What is PL/SQL Table what is the advantage of that when we will use PL/SQL Table?

·         PL/SQL Table is nothing but a INDEX by table

28.What is Global temporary table what diff between PL/SQL table and Temporary table?

·          

29.What is %ROWTYPE?

·         It is a record type to hold the different type of data types of the particular table.

30.What is mutating trigger? How can you handle this?

·         If Procedure contains any DML operations on the table again we will using the same table for same operations, it will lock the table

·         By using Pragma Autonomous Transaction

31.What are PL/SQL table attributes?

·         %ROWTYPE

·         %TYPE

Reports6i:

1.   What is Frame, Repeating frame and Anchor Objects?

·         Frames are used to surround other objects and protect them from being overwritten or pushed by other objects.

·         Repeating frames surround all of the fields that are created for a group’s columns. 

·         Anchors are used to determine the vertical and horizontal positioning of a child object relative to its parent. 

2.   What are SYSTEM Parameters?

·         Background, copies, currency, decimal, desformat, desname, destype, mode, orientation, printjob, thousands.

3.   What are Bind Parameter and Lexical Parameter and what is the diff?

·         Bind references (or bind variables) are used to replace a single value in SQL or PL/SQL, such as a character string, number, or date. 

·         Lexical references are placeholders for text that you embed in a SELECT statement. 

4.   When we will use Lexical Parameters?

·         You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH.

5.   Where we will handle the Lexical parameters?

·         Data Model – SQL Window

6.   Tell me where u has used Lexical parameters in u r exp?

7.   What are the report triggers?

·         Before parameter form 

·         After parameter form

·         Before report

·         Between pages

·         After report          .

8.   What is the sequence of triggers firing?

·         Before parameter form 

·         After parameter form

9.   What is the diff between After Parameter Form and Before Report Trigger?

10.        What are Format and validation and action triggers?

• A format trigger is a PL/SQL function executed before an object is formatted.  A trigger can be used to dynamically change the formatting attributes (font and color) of the object. 

• Validation triggers are PL/SQL functions that are executed when parameter values are specified on the command line and when you accept the Runtime Parameter Form. 

• Action triggers are PL/SQL procedures executed when a button is selected in the Runtime Preview.  The trigger can be used to dynamically call another report (drill down) or execute any other PL/SQL.

 

11.        How to call another report from report?

·         By using SRW.RUN_REPORT (command_line CHAR) built in

12.        What if confine mode and Flex Mode?

·         When confine mode is on u cannot move the child object from its parent,

·         When flex mode is on u can resize the child object according to its parent.

13.        What are SRW Packages we have?

SRW.DO_SQL (DDL &DML STMT),

SRW.RUN_REPORT,

14.        What is User Exit?

·         User exit is used to transfer the control from report builder to 3gl program and vice versa.

15.        What are Formula, Summary, and Placeholder column? Difference?

·         A formula column performs a user-defined computation on another column(s) data

·         A summary column performs group functions like avg(),sum(),max() on the fields and returns a single value.

·         A Place holder column is used to declare Global variables in a report

16.        What is the diff between Data link and Group by?

·         Data links relate the results of multiple queries. 

17.        What is the purpose of Between Pages trigger?

·         The Between Pages trigger fires before each page of the report is formatted, except the very first page.

18.        If out put is 10 pages how many times between pages trigger will be

Fired? If cursors go from last page to first page weather the trigger

will be created?

9 times   -   0 time

Oracle Reports Faqs

1. What is Frame, Repeating frame and Anchor Objects?

A. Frame: It is a layout object, which will be used to place the object and objects will be

displayed only one time in the output

Repeating Frame: It is a layout object, which will be used to display the database

columns and other objects which will be displayed multiple times on the output.

Anchor Objects: It is a layout object which will be used to set the child object

horizontal and vertical position based on parent object.

2. What are System Parameters?

A. MODE: To set the report mode weather char(99% apps report)/Bit map(Xml pub).

ORIENTATION: Landscape or portrait.

DESTYPE: File, Screen,Printer,E-Mail , Fax.

DESFORMAT: .pdf,.html,.txt.

DESNAME: Printer name, Fax number, E-Mail Id.

CURRENCY

COPY

THOUSAND etc.

3. What is Bind Parameter and lexical parameter ? What is the difference?

A. Bind Parameter : Will be used to pass the value into the sql query ‘WHERE’ Clause.

Lexical Parameter: Will be used to replace the string inside of the query in any clause.

Lexical parameter will be character(min 600). When we are using Lexical parameters

in ‘FROM’ clause then we have to specify the default value.

Bind parameter noted with : .

Lexical Parameter noted with & .

4. When will we use the Lexical parameters in our application?

A. When we want to change the query(SELECT/ WHERE FROM/ORDER BY) query

Dynamically then we will use the Lexical Parameters.

5. Where we handle the Lexical Parameters in your application?

A. After Parameter form trigger we will assign the values.

6. Tell me Where u have used lexical parameters in Oracle Applications?

A. In Oracle Applications KFF Desc ‘Select ‘ Clause.

To Change the sort option dynamically.

7. What are report triggers?

A. Before Parameter Form Trigger

After Parameter Form Trigger

Before Report Trigger

Between Pages Trigger

After Report Trigger.

8. What is the sequence of Trigger Firing ?

A. Same Sequence Listed Above.

9. What is the difference between After Parameter Form Trigger and Before Report

Trigger?

A. After Parameter Form Trigger: We Can’t access data mode column values?

Before Report Trigger : We can access all pl/sql global variables and report level

Columns.

10. What is Format trigger, Validation Trigger, and Action Trigger?

A. Format Triggers: will be used to hide/display the layout object dynamically and it returns Boolean value(True-display, False-Hide).

Validation Triggers : Will be used to validate the report parameter values.

Action Triggers: When user select the button in the report layout if we want to execute the same pl/sql code then we will use the Action Triggers.

11. What is Drill Down report and Matrix Report?

A. Drill Down : Calling one report from another.

Matrix : A matrix report contains one row of labels, one column of labels and information in a grid format that is related to the row and column labels.

For matrix report at least we have to define 4 groups.

12. How to run a report from another report?

A. srw.run_report(d2k projects)

Fnd.request.submit_request (For Oracle Apps).

13. What is confine Mode and Flex Mode?

A. Confine Mode: It is alock mode. Which will be used to lock the child objects.

If we enable the confine mode we can’t move the child objects out side of parent objects.

OFF, then we can move the child objects out side of parent objects.

Flex Mode: ON : IF we increase/decrease the child objects then its parent object also increase/decrease.

OFF: The parent object will not get effect.

14. How to define Anchor?

A. By selecting the Parent and Child object, we will define the Anchor.

15. What are SRW Packages?

A. srw.message

srw.progrma_abort

srw.user_exit

srw..reference.

srw.do_sql

srw.get_page_number.

16. What is user_exit?

A. User_exit is a program which will be used to pass the control from report builder to 3^rd generation language and get some information.

17. What is Summary Column,Formula Column and Place Holder Column?

A. Summary Column: It is a built in Function, Which we can apply at group level or report level.

Formula Column: It is a pl/sql function where we can write our logic and return some value.

Place Holder Column: It is also a function but it will be used to store the value , we can access the this across the report builder.

It will work like global variable.

18. What is data link and group by?

A. In Group By we can get the records which are matching with the child records.

In datalink we can give any condions like =,>,<.

19. What is Between Pages Trigger ?

A. If we want to change the output format dynamically then we will use this trigger.

It will run n-1 times. Ex. If no.of pages are 10 then it will run 10-1= 9times.

If we come reverse (From last page to first page) trigger will not be fired.

20. If we don’t have the parameter form wether parameter trigger will fire or not?

A. Yes.

21. Which trigger will not fire?

A. Between Pages Trigger will not fired, we have only one/zero page output.

22. What is program units ? difference between pl/sql object program units?

A. Program unit contains Procedures,functions,package specification and package body.

Difference:

Program units: will be saved in the file. We can access from current report only.

Pl/Sql: Will be saved in database. We can access from any report.

23. What is Pl/Sql Library?

A. It is a group of Program Units, We can attach the library to other report so thatwe can use the program units which are there in libraries.

24. What is .rdf an .rep?

A. .rdf = report definition file.

.rep= report executable program.

25. What are different Versions?

A. 2.5/6i/9i/10g.

26. How to create table from report?

A. srw.do_sql for any ddl command.

27. How to Develop Multiple Layouts in reports?

A. By using Format Triggers , We can change the report layout dynamically.