PLSQL Refresh

## What is the significance of the & and && operators in PL SQL ?
The & operator means that the PL SQL block requires user input for a variable. The && operator means that the value of this variable should be the same as inputted by the user previously for this same variable.

## Is there a PL/SQL Engine in SQL*Plus?
No. Unlike Oracle Forms, SQL*Plus does not have a PL/SQL engine. Thus, all your PL/SQL are send directly to the database engine for execution. This makes it much more efficient as SQL statements are not stripped off and send to the database individually.

## Describe Oracle database's physical and logical structure ?
Physical : Data files, Redo Log files, Control file.
Logical : Tables, Views, Table spaces, etc.

## What are the Pct Free and Pct Used?
Pct Free is used to denote the percentage of the free space that is to be left when creating a table. Similarly Pct Used is used to denote the percentage of the used space that is to be used when creating a tableeg.:: Pctfree 20, Pctused 40

## What is Row Chaining?
The data of a row in a table may not be able to fit the same data block. Data for row is stored in a chain of data blocks .

## SET SERVEROUTPUT
One can use the DBMS_OUTPUT package to write information to an output buffer. This buffer can be displayed on the screen from SQL*Plus by using the SET SERVEROUTPUT ON; command.
However, if you print too much, the output buffer will overflow. In that case, set the buffer size to a larger value, eg.: set serveroutput on size 200000

## Autonomous transactions:
As workaround, one can use autonomous transactions. Autonomous transactions execute separate from the current transaction.

Unlike regular triggers, autonomous triggers can contain COMMIT and ROLLBACK statements. Example:

SQL> CREATE OR REPLACE TRIGGER tab1_trig
AFTER insert ON tab1
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO log VALUES (SYSDATE, 'Insert on TAB1');
COMMIT; -- only allowed in autonomous triggers
END;
/

Trigger created.

SQL> INSERT INTO tab1 VALUES (1);

1 row created.

## What are the Back ground processes in Oracle and what are they?
1) This is one of the most frequently asked questions. There are basically 9 Processes but in a general system we need to mention the first five background processes. They do the house keeping activities for the Oracle and are common in any system.The various background processes in oracle are
a) Data Base Writer(DBWR) :: Data Base Writer Writes Modified blocks from Database buffer cache to Data Files. This is required since the data is not written whenever a transaction is committed.
b)LogWriter(LGWR) :: LogWriter writes the redo log entries to disk. Redo Log data is generated in redo log buffer of SGA. As transaction commits and log buffer fills, LGWR writes log entries into a online redo log file.
c) System Monitor(SMON) :: The System Monitor performs instance recovery at instance startup. This is useful for recovery from system failure.
d)Process Monitor(PMON) :: The Process Monitor performs process recovery when user Process fails. Pmon clears and Frees resources that process was using.
e) CheckPoint(CKPT) :: At Specified times, all modified database buffers in SGA are written to data files by DBWR at Checkpoints and Updating all data files and control files of database to indicate the most recent checkpoint.
f)Archieves(ARCH) :: The Archiver copies online redo log files to archival storal when they are busy.
g) Recoveror(RECO) :: The Recoveror is used to resolve the distributed transaction in network.
h) Dispatcher (Dnnn) :: The Dispatcher is useful in Multi Threaded Architecturei) Lckn :: We can have up to 10 lock processes for inter instance locking in parallel sql.

## Can one use dynamic SQL within PL/SQL? OR Can you use a DDL in a procedure ? How ?
From PL/SQL V2.1 one can use the DBMS_SQL package to execute dynamic SQL statements. Eg:
CREATE OR REPLACE PROCEDURE DYNSQLAS
cur integer;
rc integer;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur,'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
END;

## How can I protect my PL/SQL source code?
PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to protect the source code. This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original). This way you can distribute software without having to worry about exposing your proprietary algorithms and methods. SQL*Plus and SQL*DBA will still understand and know how to execute such scripts. Just be careful, there is no "decode" command available.The syntax is: wrap iname=myscript.sql oname=xxxx.yyy

## What is the difference between foreign key and reference key ?
Foreign key is the key i.e. attribute which refers to another table primary key.Reference key is the primary key of table referred by another table.

## Can dual table be deleted, dropped or altered or updated or inserted ?
Yes

## When to create indexes ?
To be created when table is queried for less than 2% or 4% to 25% of the table rows.

## What are the parts of a database trigger ?
The parts of a trigger are: A triggering event or statement A trigger restriction A trigger action15.What are the various types of database triggers ?There are 12 types of triggers, they are combination of :Insert, Delete and Update Triggers.Before and After Triggers.Row and Statement Triggers.(3*2*2=12)

## What are the values of :new and :old in Insert/Delete/Update Triggers ?
INSERT : new = new value, old = NULL
DELETE : new = NULL, old = old value
UPDATE : new = new value, old = old value

## What are mutating tables ?
When a table is in state of transition it is said to be mutating.
eg :: If a row has been deleted then the table is said to be mutating and no operations can be done on the table except select.

## What is Normalization ?
Normalization is the process of organizing the tables to remove the redundancy. There are mainly 5 Normalization rules.
a) 1 Normal Form :: A table is said to be in 1st Normal Form when the attributes are atomic
b) 2 Normal Form :: A table is said to be in 2nd Normal Form when all the candidate keys are dependant on the primary key
c) 3rd Normal Form :: A table is said to be third Normal form when it is not dependant transitively

## What’s a PL/SQL table? Its purpose and Advantages?
A. 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.

## 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.

## 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.

## 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.

## 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).

## 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.

## How to display messages in Log file and Output file?
Using FND_FILE.PUT_LINE

## What is Explain Plan? How do u use Explain Plan in TOAD?
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

## What is a TK-PROF and its usage?
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.

## What is Optimization? How many types of Optimization are there?
Rule based Optimization and Cost Based Optimization.

## What is the default optimization chosen by Oracle?
Cost based Optimization.

## What is the difference between When no data Found and cursor attribute % DATA FOUND?
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.

## 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?
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 [WAIT= [START=] [REPEAT_DAYS=] [REPEAT_END=]
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), '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '');

## What is the significance of _all tables?
_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'

## What are mutating tables? And what is mutating error?
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.

## What is a trace file?
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

## 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