Oracle Questions

We need to first login to the data base as: SQLPLUS followed by user_name/password. Then we need to execute the following query:

CREATE USER user_name IDENTIFIED BY abcd; in this query a user_name suggest the user that is created which has a password abcd which is required for login in database.

Even though both PL/SQL and Java (or) .NET can be used, PL/SQL stands above these two in terms of integration overhead. This is because Java is an open source proprietary and Data manipulation is slightly faster in PL/SQL than in Java. 

-  A stored procedure can accept parameters while a trigger cannot.
-  A trigger can’t return any value while stored procedures can.
-  A trigger is executed automatically on some event while a stored procedure needs to be explicitly called.
-  Triggers are used for insertions, update and deletions on tables while stored procedures are often using independently in the database.
-  A trigger cannot be written in a stored procedure. However, the reverse is not possible.

Joins are used to combine data of one or more tables. Joins should be used when there is abundant data. Joins can be LEFT, RIGHT, OUTER, INNER or even SELF JOIN. The purpose is to bind data from multiple tables without any receptivity

The CACHE option means how many sequences will be stored in memory for access by the application objects. The performance is faster. However in case of the database is down the data is memory is lost for the sequence.

The NO CACHE option means values are not stored in memory. So there might be some performance issue. 

Oracle database comprises of three kinds of files:
- Datafiles
- Redo log files
- Control files

The UTL_FILE database package can be used to read and write operating system files. You need to have read /write access rights in that directory before the package can be used.

Example to write file:

Fhandler is a variable of type UTL_FILE.FILE_TYPE

UTL_FILE.PUTF(fHandler, 'Im writing to a file\n');

Example to read file:

UTL_FILE.GET_LINE(fHandler, buf);

A unique constraint on a column uniquely identifies the record by a combination of one or more fields. Few unique constraint fields can have a NULL value as long as the combination of values is unique.

Example:
create table employee ( id number NOT NULL, dob DATE, professor_id NOT NULL, Name varchar(200) Constraint id_unique UNIQUE(id,dob) );