PL/SQL
1.
What’s a
PL/SQL table? Its purpose and Advantages?
A) PL/SQL table is one dimensional, indexed, unbounded
sparsed collection of homogeneous Data.
PLSQL tables are used to move data into and out
of the database and between client side applications and stored sub-programs.
They have attributes such as exits, prior, first, last, delete ,next . These
attributes make PLSQL tables easier to use and applications easier to maintain.
Advantages:
- PL\SQL tables give you the ability to hold multiple values in a structure in memory so that a PL\SQL block does not have to go to the database every time it needs to retrieve one of these values - it can retrieve it directly from the PL\SQL table in memory.
- Global temporary tables act as performance enhancers when compared to standard tables as they greatly reduce the disk IO.
- They also offer the ease-of-use of standard tables, since standard SQL can be used with them; no special array-processing syntax is required.
2. What is
a Cursor? How many types of Cursor are there?
A) Cursor
is an identifier/name to a work area that we can interact with to access its
information. A cursor points to the current row in the result set fetched. There are three types of cursors. They are
·
Implicit cursors – created automatically by PL/SQL
for all SQL-DML statements such as Insert Update, delete and Select
·
Explicit cursors – Created explicitly. They create a
storage area where the set of rows returned by a query are placed.
·
Dynamic Cursors – Ref Cursors( used for the runtime
modification of the select querry).
Declaring the cursor, Opening the cursor,
Fetching data , Closing the cursor(Releasing the work area) are the steps
involved when using explicit cursors.
3. What is the difference between Function and
Procedure?
·
Procedure is a sub program written to perform a set
of actions and returns multiple values
Using out parameters or return no value at all.
·
Function is a subprogram written to perform certain
computations and return a single value.
4.
What are
the modes for passing parameters to Oracle?
A) There are
three modes for passing parameters to subprograms
·
IN - An In-parameter lets you pass values to the
subprogram being called. In the subprogram it acts like a constant and cannot
be assigned a value.
·
OUT – An out-parameter lets you return values to the
caller of the subprogram. It acts like an initialized variable its value cannot
be assigned to another variable or to itself.
·
INOUT – An in-out parameter lets you pass initial
values to the subprogram being called and returns updated values to the caller.
5.
What is the
difference between Truncate and Delete Statement?
·
Truncate – Data truncated by using truncate
statement is lost permanently and cannot be retrieved even by rollback.
Truncate command does not use rollback segment during its execution, hence it
is fast.
·
Delete – Data deleted by using the delete statement
can be retrieved back by Rollback. Delete statement does not free up the table
object allocated space.
6.
What are
Exceptions? How many types of Exceptions are there?
A) Exceptions
are conditions that cause the termination of a block. There are two types of
exceptions
·
Pre-Defined – Predefined by PL/SQL and are
associated with specific error codes.
·
User-Defined – Declared by the users and are rose on
deliberate request. (Breaking a condition etc.)
Exception handlers are used to handle the
exceptions that are raised. They prevent exceptions from propagating out of the
block and define actions to be performed when exception is raised.
7.
What is a
Pragma Exception_Init? Explain its usage?
A) Pragma Exception_Init is used to handle
undefined exceptions. It issues a directive to the compiler asking it to
associate an exception to the oracle error. There by displaying a specific error
message pertaining to the error occurred.
Pragma
Exception_Init (exception_name, oracle_error_name).
8. What
is a Raise and Raise Application Error?
A) Raise statement
is used to raise a user defined exception.
B) A raise
application error is a procedure belonging to dbms_standard package. It allows
to display a user defined error message from a stored subprogram.
8.
What is the
difference between Package, Procedure and Functions?
·
A package is a database objects that logically
groups related PL/SQL types, objects, and Subprograms.
·
Procedure is a sub program written to perform a set
of actions and can return multiple values.
·
Function is a subprogram written to perform certain
computations and return a single value.
Unlike subprograms packages cannot be called,
passed parameters or nested.
9.
How do you
make a Function and Procedure as a Private?
A) Functions
and Procedures can be made private to a package by not mentioning their
declaration in the package specification and by just mentioning them in the
package body.
10.
What is an
Anonymous block?
A) Anonymous
Block is a block of instructions in PL/SQL and SQL which is not saved under a
name as an object in database schema. It is also not compiled and saved in
server storage, so it needs to be parsed and executed each time it is run.
However, this simple form of program can use variables, can have flow of
control logic, can return query results into variables and can prompt the user
for input using the SQL*Plus '&' feature as any stored procedure.
12. What
are the two basic parameters that we have to pass while registering PL/SQL procedure?
A) Error code and Error Buffer.
11.
How do you
kick a Concurrent program from PL/SQL?
A) Using FND_SUBMIT.SUBMIT_REQUEST.
12.
How to display
messages in Log file and Output file?
A) Using FND_FILE.PUT_LINE
13.
What is a
Trigger ? How many types of Triggers are there?
A) Trigger is a procedure that gets implicitly
executed when an insert/update/delete statement is issued against an associated
table. Triggers can only be defined on tables not on views, how ever triggers
on the base table of a view are fired if an insert/update/delete statement is
issued against a view.
There are two types of triggers, Statement level
trigger and Row level trigger. Insert After/For each row Trigger is
fired/Update /Before/For Each
statement Delete
14.
Can we use
Commit in a Database Trigger, if ‘No’ then why?
A) No. Committing in a trigger will violate the
integrity of the transaction.
15.
What is
Commit, Rollback and Save point?
Commit – Makes
changes to the current transaction permanent. It Erases the savepoints and
releases the transaction locks.
Savepoint –Savepoints
allow to arbitrarily hold work at any point of time with option of later
committing. They are used to divide transactions into smaller portions.
Rollback – This
statement is used to undo work.
16.
What is the
difference between DDL, DML and DCL structures?
A)
DDL statements are used for defining data. Ex: Create, Alter, Drop.
DML
statements are used for manipulating data. Ex: Insert, update, truncate,
delete, select.
DCL statements are used for to control the
access of data. Ex; Grant, Revoke.
17.
How can u
create a table in PL/SQL procedure?
A) By using execute immediate statement we can
create a table in PLSQL.
Begin
Execute immediate ‘create table amit as select
* from emp’;
End;
All DDL,DML,DCL commands can be performed by
using this command.
18.
How do we
Tune the Queries?
A) Queries can be tuned by Checking the logic
(table joins), by creating Indexes on objects in the where clause, by avoiding
full table scans. Finally use the trace utility to generate the trace file, use
the TK-Prof utility to generate a statistical analysis about the query using
which appropriate actions can be taken.
21. What is
Explain Plan? How do u use Explain Plan in TOAD?
A) It is
a utility provided by toad that gives the statistics about the performance of the
query. It gives information such as number of full table scans occurred, cost, and
usage of indexes
19.
What is a
TK-PROF and its usage?
A) Tk-Prof
is a utility that reads the trace files and generates more readable data that
gives the statistics about the performance of the query on a line to line basis.
20.
What is
Optimization? How many types of Optimization are there?
A) Rule
based Optimization and Cost Based Optimization.
21.
What is the
default optimization chosen by Oracle?
A) Cost
based Optimization.
22.
What is the
difference between When no data Found and cursor attribute % DATA FOUND?
A) When no Data Found is a predefined internal
exception in PLSQL. Where as % Data found is a cursor attribute that returns
YES when zero rows are retrieved and returns NO when at least one row is
retrieved.
23.
What is the
difference between the snapshot and synonym?
·
A snapshot refers to read-only copies of a master
table or tables located on a remote node. A snapshot can be queried, but not
updated; only the master table can be updated. A snapshot is periodically
refreshed to reflect changes made to the master table. In this sense, a
snapshot is really a view with periodicity.
·
A synonym is an alias for table, view, sequence or
program unit. They are of two types private and public.
25. What is
the difference between data types char and varchar?
A) Char reserves the number of memory locations
mentioned in the variable declarations, even though not used (it can store a
maximum of 255 bytes). Where as Varchar does not reserve any memory locations
when the variable is declared, it stores the values only after they are
assigned (it can store a maximum of 32767 bytes).
26. How can
we place index to a second column in the table i.e. there is already one index
and I want to place another index for the column on the table?
27. Items are
imported from the legacy system using the item import interface using the SRS. How are items imported using the UNIX /PLSQL commands
with out using SRS?
A) From the
operating system, use CONCSUB to submit a concurrent program. It's an
easiest way to test a concurrent program.
Normally, CONCSUB submits a concurrent request and
returns control to the OS prompt/shell
script without waiting for the request to complete. The CONCSUB WAIT parameter can be used to
make CONCSUB wait until the request has completed before returning control to
the OS prompt/shell script
By using the WAIT token, the utility checks the
request status every 60 seconds and returns to the operating system prompt upon
completion of the request. concurrent manager does not abort, shut down, or
start up until the concurrent request completes. If your concurrent program is
compatible with itself, we can check it for data integrity and deadlocks by
submitting it many times so that it runs concurrently with itself.
Syntax: CONCSUB <ORACLE ID> <Responsibility
Application Short Name> <Responsibility Name> <User Name>
[WAIT=<Wait Flag] CONCURRENT <Concurrent Program Application Short
Name> <Concurrent Program Name> [START=<Requested Start Date>]
[REPEAT_DAYS=<Repeat Interval>] [REPEAT_END=<Request Resubmission End
Date>] <Concurrent Program Arguments ...>
To pass null parameters to CONCSUB, use
'""' without spaces for each null parameter.
In words:
single quote double quote double quote
single quote
Following is an example of CONCSUB syntax with null
parameters:
CONCSUB oe/oe OE 'Order Entry Super User'
JWALSH CONCURRENT XOE XOEPACK 4 3
'""' 3
B) To
Invoke a Concurrent Program using PL/SQL:
i) Just insert a row in FND_CONCURRENT_REQUESTS with
the apropriate parameters and commit.
ii) Invoke
the SUBMIT_REQUEST procedure in FND_REQUEST package.
FND_REQUEST.SUBMIT_REQUEST( 'AR', 'RAXMTR', '', '',
FALSE, 'Autoinvoice Master Program', sc_time, FALSE, 1, 1020, 'VRP',
'01-JAN-00', chr(0), '', '', '', '', '', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '');
28. What is
pipelining?
29) How can
the duplicate records be from the table?
SQL>
create table table_name2 as select distinct * from table_name1;
SQL> drop table_name1;
SQL> rename table_name2 to table_name1;
30) What is the significance of _all tables?
SQL> drop table_name1;
SQL> rename table_name2 to table_name1;
30) What is the significance of _all tables?
A) _all
tables are multi-org tables which are associated with the company as a whole.
Multiple Organizations is enabled in Oracle
Applications by partitioning some database
tables by the Operating Unit. Other tables are shared across Operating Units
(and therefore across set of books). Examples of Applications with partitioned
tables are Oracle Payables, Oracle Purchasing, Oracle Receivables, Oracle
Projects, Oracle Sales & Marketing etc. The name of each corresponding
partitioned table is the view name appended by '_ALL'
31)What are
mutating tables? And what is mutating error?
A) A
mutating table is a table that is currently being modified by an UPDATE,
DELETE, or INSERT statement, or it is a table that might need to be updated by
the effects of a declarative DELETE CASCADE referential integrity constraint.
A mutating error occurs when a trigger which
fires when updation/deletion/insertion is done on a table A
performs insertion/updation/deletion on the same table A. This error results
in an infinite loop which is termed as a mutating error.
32) What is
difference between oracle 7 and oracle 8?
A) Oracle 7 is a simple
RDBMS, where as Oracle 8 is ORDBMS i.e., RDBMS with Object Support.
The main add-ons in version 8 are…
·
Abstract Data types
·
Varrays
·
PL/SQL Tables
·
Nested Tables
·
Partitioned Tables
33.What is Data cleaning and
testing.
A) Data Cleaning:
Transformation of data in its current
state to a pre-defined, standardized format
using packaged software or program modules.
Data Testing: The agreed
upon conversion deliverables should be approved by the client representatives
who are responsible for the success of the conversion. In addition, three
levels of conversion testing have been identified and described in the prepare
conversion test plans deliverables.
Eg: for Summary Balances in
GL we set Test Criteria as Record Counts, Hash Totals, Balances, Journal Debit
and Credit.
34. While registering a
report and a pl/sql block we pass some parameters, for any pl/sql block we pass
2 additional parameters. Can u list them?
A) It requires two IN
parameters for a PL/SQL procedure that's registered as a concurrent program in
Apps. They are
1. Errcode IN VARCHAR2
2. Errbuff IN VARCHAR2
35)
what is a trace file?
A) when ever an internal
error is detected by a process in oracle it dumps the information about the
error into a trace file.
Alter session set sql_trace=TRUE
36 ) When
do you use Ref Cursors?
We
base a query on a ref cursor when you want to:
i)
More easily administer SQL
ii)
Avoid the use of lexical parameters in your reports
iii)
Share data sources with other applications, such as Form Builder
iv)
Increase control and security
v)
Encapsulate logic within a subprogram
No comments:
Post a Comment