PL/SQL Tutorial - PL SQL Interview Questions

Friday, July 19, 2013

Oracle 11g dba interview questions and answers - PL SQL Interview Question

22) What is System Activity Reporter (SAR) and SHMMAX?

• SAR is a utility to display resource usage on the UNIX system.
• sar –u shows CPU activity.
• Sar –w shows swapping activity
• Sar –b shows buffer activity
• SHMMAX is the maximum size of a shared memory segment on a Linux system.

23) List out some major environment variable used in installation?

• ORACLE_BASE=/u01/app/<installation-directory>
• ORACLE_HOME=$ORACLE_BASE/product/11.2.0(for 11g)/dbhome_1
• ORACLE_SID=<instance-name>
• PATH=$ORACLE_HOME/bin:$PATH
• LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
• TNS_ADMIN=$ORACLE_HOME/network/admin
• These are absolutely critical environment variables in running OUI.

24) What is a control file?

• Control file is a binary file which records the physical structure of a database.
• It includes number of log files and their respective location, Database name and timestamp when database is created, checkpoint information.
• We find CONTROL_FILE parameter in initialization parameter file which stores information about control file location.
• We can multiplex control files, store in different locations to make control files available even if one is corrupted.
• We can also avoid the risk of single point of failure.

25) At what stage of instance, control file information is read and can we recover control file and how to know information in a control file?

• During database mounting, control file information is read.
• We can’t recover or restore lost control file, but we can still startup the database using control files created using multiplexing in different locations.
• We can query the following command
SQL> alter database backup controlfile to trace;
• We find a trace file(.trc) in udump location,we can edit it and find the complete database structure.
• Multiplexing can also be done using Following command
SQL> alter database backup controlfile to <Different location/path>.

26) How can you obtain Information about control file?

• Control file information can be shown in initialization parameter file.
• We can query v$controlfile to display the names of control files
• From sql we can execute
SQL> show parameter control_files;
• The above query gives the name, Location of control files in our physical disk.
• We can edit PFILE using a vi editor and control_files parameter gives us information about number of and location of control files.

27) How do you resize a data file and tablespace?

• Prior to Oracle 7.2 you can’t resize a datafile.
• The solution was to delete the tablespace, recreating it with different sized datafiles.
• After 7.2 you can resize a datafile by using ALTER DATABASE DATAFILE <file_name> RESIZE;
• Resizing Table space includes creation of new data file or resizing existing data file.
• ALTER TABLESPACE <tablespacename> ADD DATAFILE ‘<datafile name> ‘ size M; creates a new datafile.

28) Name the views used to look at the size of a datafile, controlfiles, block size, determine free space in a tablespace ?

• DBA_DATA_FILES or v$datafile view can be used to look at the size of a datafile .
• DBA_FREE_SPACE is used to determine free space in a tablespace.
• V$contolfile used to look at the size of a control file which includes maxlogfiles, maxlogmembers, maxinstances.
• Select * from v$controlfile gives the size of a controlfile.
• From sqlplus, query show parameter block_size to get size of db_block_size.

29) What is archive log file?

• In archive log mode, the database will makes archive of all redo log files that are filled, called as archived redo logs or archive log files.

• By default your database runs in NO ARCHIVE LOG mode, so we can’t perform online backup’s (HOT backup).
• You must shut down database to perform clean backup (COLD backup) and recovery can be done to the previous backup state.
• Archive log files are stored in a default location called FRA (Flash Recovery Area).
• We can also define our own backup location by setting log_archive_dest parameter.

30) Assume you work in an xyz company as senior DBA and on your absence your back up DBA has corrupted all the control files while working with the ALTER DATABASE BACKUP CONTROLFILE command. What do you do?

• As long as all data files are safe and on a successful completion of BACKUP control file command by your Back up DBA you are in safe zone.
• We can restore the control file by performing following commands
1) CONNECT INTERNAL STARTUP MOUNT
2) TAKE ANY OFFLINE TABLESPACE (Read-only)
3) ALTER DATABASE DATAFILE (OFFLINE)
4) RECOVER DATABASE USING BACKUP CONTROL FILE
5) ALTER DATABASE OPEN RESETLOGS
6) BRING READ ONLY TABLE SPACE BACK ONLINE

• Shutdown and back up the system. Then restart.

• Then give the command ALTER DATABSE BACKUP CONTROL FILE TO TRACE

• This output can be used for control file recovery as well.

If control file backup is not available, then the following will be required

1) CONNECT INTERNAL STARTUP NOMOUNT
2) CREATE CONTROL FILE .....;
• But we need to know all of the datafiles, logfiles, and settings of MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES for the database to use the command.

31) Can we reduce the space of TEMP datafile? How?

• Yes, we can reduce the space of the TEMP datafile.

• Prior to oracle 11g,,you had to recreate the datafile.

• In oracle 11g you reduce space of TEMP datafile by shrinking the TEMP tablespace.It is a new feature to 11g.

• The dynamic performance view can be very useful in determining which table space to shrink.

32) What do you mean by database backup and which files must be backed up?

• Database stores most crucial data of business ,so it’s important to keep the data safe and this can be achieved by backup.
• The following files must be backed up

• Database files (Header of datafiles is freezed during backup)
• Control files
• Archived log files
• Parameter files (spfile and pfile)
• Password file

33) What is a full backup and name some tools you use for full backup?

• A full backup is a backup of all the control files, data files, and parameter file (SPFILE or PFILE).
• You must also backup your ORACLE_HOME binaries which are used for cloning.
• A full backup can be performed when our database runs in NON ARCHIVE LOG mode.
• As a thumb rule, you must shutdown your database before you perform full backup.
• Full or COLD backup can be performed by using copy command in unix.

34) What are the different types of backup’s available and also explain the difference between them?

• There are 2 types of backup’s

1) COLD backup(User managed & RMAN)
2) HOT backup(User managed & RMAN)

• Hot backup is taken when the database is still online and database should be in ARCHIVE LOG MODE.
• Cold backup is taken when the database is in offline mode.
• Hot backup is inconsistent backup where as cold backup is consistent backup.
• You can begin backup by using the following command
SQL> alter database begin backup;
• End backup by
SQL> alter database end backup;

35) How to recover database if we lost the control file and we do not have a backup and what is RMAN?

• We can recover our database at any point of time, when we have backup of our control files in different mount points.
• Also check whether the control file is available in trace file located in USERDUMP or the alert log to recover the database.
RMAN

• RMAN called as Recovery manager tool supplied by oracle that can be used to manage backup and recovery activities.
• You can perform both offline (Cold) and online (Hot) backup’s using RMAN.
• We need to configure Flash_Recovery_Area of database to use RMAN.
• RMAN maintains the repository of backup information in control file.

36) Name the architectural components of RMAN

• RMAN executable
• Server process
• Channels
• Target database
• Recovery catalog database
• Media management layer
• Backup sets and backup pieces

37) What is a recovery catalog?

• Recovery catalog is an inventory of backup taken by RMAN for the database.
• The size of the recovery catalog schema depends upon the number of databases monitored by the catalog.
• It is used to restore a physical backup, reconstruct it, and make it available to the oracle server.
• RMAN can be used without recovery catalog.
• Recovery catalog also holds RMAN stored scripts.

38) List some advantages of using RMAN.

• Table spaces are not put in the backup mode ,therefore there is no extra redo log file during online backups.
• Incremental backups that only copy data blocks, which have changed since last backup.
• Detection of corrupt blocks.
• Built in reporting and listing commands.
• Parallelization of I/O operations.

39) How to bring a database from ARCHIVE LOG mode to NON ARCHIVE LOG MODE?

• You should change your init<SID>.ora file with the following information
• log_archive_dest=’/u01/oradata/archlog’ (for example)
• log_archive_format=’%t_%s.dbf’
• log_archive_start=true (prior to 10g)
• sql>shutdown;
• sql> startup mount;
• sql> alter database archivelog;
• sql>alter database open;
• Make sure you backup your database before switching to ARCHIVELOG mode.

40) What are the different stages of database startup?

• Database undergoes different stages before making itself available to end users
• Following stages are involved in the startup of database
? NoMount
? Mount
? Open

• NoMount – Oracle Instance is available based on the parameters defined in SPFile.
• Mount - Based on the Information from parameter control files location in spfile, it opens and reads them and available to next stage.
• Open - Datafiles, redo log files are available to the end users.

41) Name some of the important dynamic performance views used in Oracle.

• V$Parameter
• V$Database
• V$Instance
• V$Datafiles
• V$controlfiles
• V$logfiles

42) What are the different methods we can shutdown our database?

• SHUTDOWN (or) SHUTDOWN NORMAL
No new connections are accepted and wait for the user to close the session.
• SHUTDOWN TRANSACTIONAL
No new connections are accepted and wait for the existing transactions to commit and logouts the session without the permission of user.
• SHUTDOWN IMMEDIATE
No new connections are accepted and all committed transactions are reflected in database and all the transactions are about to commit are rolled back to previous value.
• SHUTDOWN ABORT

It’s just like an immediate power off for a database, it doesn’t mind what are the transactions running it just stops entire activity -(even committed transactions are not reflected in database) and make database unavailable. SMON process takes responsibility for recovery during next startup of database.

• SHUTDOWN NORMAL, TRANSACTIONAL, IMMEDIATE are clean shutdown methods as database maintains its consistency.
• SHUTDOWN ABORT leaves our database in an inconsistent state,data integrity is lost.

43) What are the different types of indexes available in Oracle?

Oracle provides several Indexing schemas
• B-tree index – Retrieves a small amount of information from a large table.
• Global and Local index – Relates to partitioned tables and indexes.
• Reverse Key Index - It Is most useful for oracle real application clusters applications.
• Domain Index – Refers to an application
• Hash cluster Index – Refers to the index that is defined specifically for a hash cluster.

44) What is the use of ALERT log file? Where can you find the ALERT log file?

• Alert log file is a log file that records database-wide events which is used for trouble shooting.
• We can find the Log file in BACKGROUND_DUMP_DEST parameter.
• Following events are recorded in ALERT log file:
• Database shutdown and startup information.
• All non-default parameters.
• Oracle internal (ORA-600) errors.
• Information about a modified control file.
• Log switch change.

45) What is a user process trace file?

• It is an optional file which is produced by user session.
• It is generated only if the value of SQL_TRACE parameter is set to true for a session.
• SQL_TRACE parameter can be set at database, instance, or session level.
• If it set at instance level, trace file will be created for all connected sessions.
• If it is set at session level, trace file will be generated only for specified session.
• The location of user process trace file is specified in the USER_DUMP_DEST parameter.

46) What are different types of locks?

There are different types of locks, which are given as follows:
• System locks – controlled by oracle and held for a very brief period of time.
• User locks – Created and managed using dbms_lock package.
• Different types of user locks are given as follows

• UL Lock – Defined with dbms_lock package.
• TX Lock – Acquired once for every transaction. It isa row transaction lock.
• TM Lock – Acquired once for each object, which is being changed. It is a DML lock. The ID1 column identifies the object being modified.

47) What do db_file_sequential_read and db_file_scattered_read events define?

• Db_file_sequential_read event generally indicates index usage.
• It shows an access by row id.
• While the db_file-scattered_read event indicates full table scan.
• Db_file_sequential_read event reads a single block at one time.
• Whereas db_file_scattered_read event reads multiple blocks.

48) What is a latch and explain its significance?

• Latch is an on/off switch in oracle that a process must access in order to perform certain type of activities.
• They enforce serial access to the resources and limit the amount of time for which a single process can use a resource.
• A latch is acquired for a very short amount of time to ensure that the resource is allocated.
• We may face performance issues which may be due to either of the two following reasons
• Lack of availability of resource.
• Poor application programming resulting in high number of requests for resource.
• Latch information is available in the v$LATCH and v$LATCHHOLDER dynamic performance views.

49) Explain the architecture of data guard?

Data guard architecture includes the following components

• Primary database – Refers to the production database.
• Standby Database – Refers to a copy of primary or production database.It may have more than one standby database.
• Log transport service – Manages transfer of archive log files primary to standby database.
• Network configuration – Refers to the network connection between primary and standby database.
• Applies archived logs to the standby database.
• Role management services – Manages the role change between primary and standby database.
• Data guard broker – Manages data guard creation process and monitors the dataguard.

50) What is role transition and when does it happen?

• Database operates in one of the following mutually exclusive roles
Primary
Standby
• Role transition is the change of role between primary and standby databases.
• Data guard enables you to change this roles dynamically by issuing the sql statements.
• Transition happens in between primary and standby databases in the following way
• Switchover, where primary database is switched to standby database and standby to primary database.
• Failover, where a standby database can be used as a disaster recovery solution in case of a failure in primary database.
• DRM allows you to create resource plans, which specify resource allocation to various consumer groups.
• DRM offers an easy-to-use and flexible system by defining distinct independent components.
• Enables you to limit the length of time a user session can stay idle and automatically terminates long-running SQL statement and users sessions.
• Sets the initial login priorities for various consumer groups.
• DRM will automatically queue all the subsequent requests until the currently running sessions complete.

Oracle 11g dba interview questions and answers - PL SQL Interview Question

1) What is a database?

• Database offer a single point of mechanism for storing and retrieving information with the help of tables.
• Table is made up of columns and rows where each column stores specific attribute and each row displays a value for the corresponding attribute.
• It is a structure that stores information about the attributes of the entities and relationships among them.
• It also stores data types for attributes and indexes.
• Well known DBMS include Oracle, ibm db2, Microsoft sql server, Microsoft access, mysql and sqlLite.

2) What are the different types of storage systems available and which one is used by Oracle?

Two types of storage systems are available
• Relational Database Management System (RDBMS) and Hierarchical Storage Management System (HSM)

• Most databases use RDBMS model, Oracle also uses RDBMS model.

• Hierarchical Storage Management System (HSM)
• Information Management System (IMS) from IBM.
• Integrated Database Management System (IDMS) from CA.

3) Explain some examples of join methods

• Join methods are of mainly 3 types
• Merge Join – Sorting both the tables using join key and then merge the rows which are sorted.
• Nested loop join – It gets a result set after applying filter conditions based on the outer table.
• Then it joins the inner table with the respective result set.
• Hash join - It uses hash algorithm first on smaller table and then on the other table to produce joined columns. After that matching rows are returned.

4) What are the components of logical data model and list some differences between logical and physical data model?

Components of logical data model are
• Entity – Entity refers to an object that we use to store information. It has its own table.
• Attribute – It represents the information of the entity that we are interested in. It is stored as a column of the table and has specific datatype associated with it.
• Record – It refers to a collection of all the properties associated with an entity for one specific condition, represented as row in a table.
• Domain – It is the set of all the possible values for a particular attribute.
• Relation – Represents a relation between two entities.
Difference between Logical and Physical data model

• Logical data model represents database in terms of logical objects, such as entities and relationships.
• Physical data model represents database in terms of physical objects, such as tables and constraints.

5) What is normalization? What are the different forms of normalization?

• Normalization is a process of organizing the fields and tables of a relational database to minimize redundancy and dependency.
• It saves storage space and ensures consistency of our data.

There are six different normal forms

• First Normal Form – If all underlying domains contain atomic values only.
• Second Normal Form – If it is in first normal form and every non key attribute is fully functionally dependent on primary key.
• Third Normal Form - If it is in 2nd normal form and every non key attribute is non transitively dependent on the primary key.
• Boyce Codd Normal Form - A relation R is in BCNF if and only every determinant is a candidate key.

• Fourth Normal Form
• Fifth Normal Form

6) Differentiate between a database and Instance and explain relation between them?

• Database is a collection of three important files which include data files, control files and redo log files which physically exist on a disk
• Whereas instance is a combination of oracle background process (SMON, PMON, DBWR, LGWR) and memory structure (SGA, PGA).
• Oracle background processes running on a computer share same memory area.
• An instance can mount and open only a single database, ever.
• A database may be mounted and opened by one or more instances (using RAC).

7) What are the components of SGA?

• SGA is used to store shared information across all database users.
• It mainly includes Library cache, Data Dictionary cache, Database Buffer Cache, Redo log Buffer cache, Shared Pool.
• Library cache – It is used to store Oracle statements.
• Data Dictionary Cache – It contains the definition of Database objects and privileges granted to users.
• Data Base buffer cache – It holds copies of data blocks which are frequently accessed, so that they can be retrieved faster for any future requests.
• Redo log buffer cache – It records all changes made to the data files.

8) Difference between SGA and PGA.

• SGA (System Global Area) is a memory area allocated during an instance start up.
• SGA is allocated as 40% of RAM size by default.
• SGA size is controlled by DB_CACHE_SIZE parameter defined in initialization parameter file (init.ora file or SPFILE).

• PGA (Program or Process Global Area) is a memory area that stores a user session specific information.

• PGA is allocated as 10% of RAM size by default.

9) What are the disk components in Oracle?

These are the physical components which gets stored in the disk.
• Data files
• Redo Log files
• Control files
• Password files
• Parameter files

10) What is System Change Number (SCN)?

• SCN is a unique ID that Oracle generates for every committed transaction.
• It is recorded for every change in the redo entry.
• SCN is also generated for every checkpoint (CKPT) occurred.
• It is an ever increasing number which is updated for every 3 seconds
• You can get the SCN number by querying select SCN from v$database from SQLPLUS.

11) What is Database Writer (DBWR) and when does DBWR write to the data file?

• DBWR is a background process that writes data blocks information from Database buffer cache to data files.
There are 4 important situations when DBWR writes to data file
• Every 3 seconds
• Whenever checkpoint occurs
• When server process needs free space in database buffer cache to read new blocks.
• Whenever number of changed blocks reaches a maximum value.

12) What is Log Writer and when does LGWR writes to log file?

• LGWR writes redo or changed information from redo log buffer cache to redo log files in database.
• It is responsible for moving redo buffer information to online redo log files, when you commit and a log switch also occurs.
• LGWR writes to redo files when the redo log buffer is 1/3 rd full.
• It also writes for every 3 seconds.

• Before DBWR writes modified blocks to the datafiles, LGWR writes to the
log file

13) Which Table spaces are created automatically when you create a database?

• SYSTEM tablespace is created automatically during database creation.
• It will be always online when the database is open.
Other Tablespaces include
• SYSAUX tablespace
• UNDO tablespace
• TEMP tablespace
• UNDO & TEMP tablespace are optional when you create a database.

14) Which file is accessed first when Oracle database is started and What is the difference between SPFILE and PFILE?

• Init<SID>.ora parameter file or SPFILE is accessed first .( SID is instance name)
• Settings required for starting a database are stored as parameters in this file.

• SPFILE is by default created during database creation whereas PFILE should be created from SPFILE.
• PFILE is client side text file whereas SPFILE is server side binary file.
• SPFILE is a binary file (it can’t be opened) whereas PFILE is a text file we can edit it and set parameter values.
• Changes made in SPFILE are dynamically effected with running database whereas PFILE changes are effected after bouncing the database.
• We can backup SPFILE using RMAN.

15) What are advantages of using SPFILE over PFILE?

• SPFILE is available from Oracle 9i and above.
• Parameters in SPFILE are changed dynamically.
• You can’t make any changes to PFILE when the database is up.
• RMAN cant backup PFILE, It can backup SPFILE.
• SPFILE parameters changes are checked before they are accepted as it is maintained by Oracle server thereby reducing the human typo errors.

16) How can you find out if the database is using PFILE or SPFILE?

• You can query Dynamic performance view (v$parameter) to know your database is using PFILE or SPFILE.

• SQL> select value from V$parameter where name= ‘SPFILE’;
• A non-null value indicates the database is using SPFILE.
• Null value indicates database is using PFILE.
• You can force a database to use a PFILE by issuing a startup command as
• SQL> startup PFILE = ‘full path of Pfile location’;

17) Where are parameter files stored and how can you start a database using a specific parameter file?

• In UNIX they are stored in the location $ORACLE_HOME/dbs and ORACLE_HOME/database for Windows directory.

• Oracle by default starts with SPFILE located in $ORACLE_HOME/dbs.
• If you want to start the database with specific file we can append it at the startup command as
SQL > startup PFILE = ‘full path of parameter file ‘;

• You can create PFILE from SPFILE as create PFILE from SPFILE;
• All the parameter values are now updated with SPFILE.
• Similarly, create SPFILE from PFILE; command creates SPFILE from PFILE.

18) What is PGA_AGGREGATE_TARGET parameter?

• PGA_AGGREGATE TARGET parameter specifies target aggregate PGA memory available to all server process attached to an instance.
• Oracle sets its value to 20% of SGA.
• It is used to set overall size of work-area required by various components.
• Its value can be known by querying v$pgastat dynamic performance view.
• From sqlplus it can be known by using SQL> show parameter pga.

19) What is the purpose of configuring more than one Database Writer Processes? How many should be used? (On UNIX)

• DBWn process writes modified buffers in Database Buffer Cache to data files, so that user process can always find free buffers.

• To efficiently free the buffer cache to make it available to user processes, you can use multiple DBWn processes.

• We can configure additional processes (DBW1 through DBW9 and DBWa through DBWj) to improve write performance if our system modifies data heavily.

• The initialization parameter DB_WRITER_PROCESSES specifies the number of DBWn processes upto a maximum number of 20.

• If the Unix system being used is capable of asynchronous input/output processing then only one DBWn process is enough, if not the case the total DBWn processes required will be twice the number of disks used by oracle, and this can be set with DB_WRITER_PROCESSES initialization parameter.

20) List out the major installation steps of oracle software on UNIX in brief?

• Set up disk and make sure you have Installation file (run Installer) in your dump.
• Check the swap and TEMP space .
• Export the following environment variables
1) ORACLE_BASE
2) ORACLE_HOME
3) PATH
4) LD_LIBRARY_PATH
5) TNS_ADMIN

• Set up the kernel parameters and file maximum descriptors.
• Source the Environment file to the respective bash profile and now run Oracle Universal Installer.

21) Can we check number of instances running on Oracle server and how to set kernel parameters in Linux?

• Editing the /etc/oratab file on a server gives the list of oracle instances running on your server.

• Editing /etc/sysctl.conf file with vi editor will open a text file listing out kernel level parameters.

• We can make changes to kernel parameters as required for our environment only as a root user.
• To make the changes affected permanently to kernel run the command /sbin/sysctl –p.
• We must also set file maximum descriptors during oracle installation which can be done by editing /etc/security/limits.conf as a root user.

Tuesday, June 11, 2013

Oracle PL / SQL Interview Question - New Interview Questions

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:
1 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.
2 Global temporary tables act as performance enhancers when compared to standard tables as they greatly reduce the disk IO.
3 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
1 Implicit cursors – created automatically by PL/SQL for all SQL Dml statements such as
Insert Update, delete and Select
2 Explicit cursors – Created explicitly. They create a storage area where the set of rows
Returned by a query are placed.
3 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?
1..Procedure is a sub program written to perform a set of actions and returns multiple valuesUsing out parameters or return no value at all.
2..Function is a subprogram written to perform certain computations and return a single value.

What are the modes for passing parameters to Oracle?
There are three modes for passing parameters to subprograms
1.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.
2. 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.
3.INOUT – An in-out parameter lets you pass initial values to the subprogram being called and returns updated values to the caller.

What is the difference between Truncate and Delete Statement?
1.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.
2. 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.

What are Exceptions? How many types of Exceptions are there?
Exceptions are conditions that cause the termination of a block. There are two types of exceptions
1.Pre-Defined – Predefined by PL/SQL and are associated with specific error codes.
2.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.

What is a Pragma Exception_Init? Explain its usage?
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?
1.Raise statement is used to raise a user defined exception.
2. 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.

What is the difference between Package, Procedure and Functions?
1.A package is a database objects that logically groups related PL/SQL types, objects, and Subprograms.
2.Procedure is a sub program written to perform a set of actions and can return multiple values.
3.Function is a subprogram written to perform certain computations and return a single value. Unlike subprograms packages cannot be called, passed parameters or nested.

How do you make a Function and Procedure as a Private?
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.

How do you kick a Concurrent program from PL/SQL?
Using FND_REQUEST.SUBMIT_REQUEST.

What is an Anonymous block?
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.

What are the two basic parameters that we have to pass while registering PL/SQL procedure?
Error code and Error Buffer.

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

What is a Trigger ? How many types of Triggers are there?
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

Can we use Commit in a Database Trigger, if ‘No’ then why?
No. Committing in a trigger will violate the integrity of the transaction.

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.

What is the difference between DDL, DML and DCL structures?
DDL statements are used for defining data. Ex: Create, Alter, Drop,Truncate,Rename.
DML statements are used for manipulating data. Ex: Insert, update, truncate.
DCL statements are used for to control the access of data. Ex; Grant, Revoke.
TCL statements are used for data saving.Ex; Commit,Rollback,Savepoint.

How can u create a table in PL/SQL procedure?
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.

How do we Tune the Queries?
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.

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 the snapshot and synonym?
7 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.
8 A synonym is an alias for table, view, sequence or program unit. They are of two types private and public.

What is the difference between data types char and varchar?
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).

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?
1.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
2. 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)

How can the duplicate records be deleted from the table?
delete from t1 a where rowid not in (select max(rowid) from t1 b where a.no=b.no)

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 difference between oracle 7 andoracle 8i?
A) Oracle 7 is a simple RDBMS, where as Oracle 8i 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

What is Data cleaning and testing.
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.
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?
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
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:
1.More easily administer SQL
2. Avoid the use of lexical parameters in your reports
3. Share data sources with other applications, such as Form Builder
4. Increase control and securityv) Encapsulate logic within a subprogram

Sunday, March 31, 2013

Oracle and PL/SQL Interview Questions

Q.What is the purpose of database links in Oracle?

Database links are created to establish communication between different databases or different environments such as development, test and production of the same database. The database links are usually designed to be read-only to access other database information . They are also useful when you want to copy production data into test environment for testing.

Q. What is Oracle's data dictionary used for?

Data dictionary in Oracle contains information about all database objects such as tables, triggers, stored procedures, functions, indexes, constraints, views, users, roles, monitoring information, etc.

Q. Which data dictionary objects are used to retrieve the information about the following objects from a given schema?
1) tables
2) views
3) triggers
4) procedures
5) constraints
6) all of the above mentioned objects

The objects used are:
a> user_tables or tabs
b> user_views
c> user_triggers
d> user_procedures
e> user_constraints
f> user_objects

fferent SQL queries in the same PL/SQL program vs. design time declared explicit cursors with an association to only one query.

Q. You want to view top 50 rows from Oracle table. How do I this?

Use ROWNUM, the pseudo column in where clause as follows:
Where rownum < 51

After complete execution of query and before displaying output of SQL query to the user oracle internally assigns sequential numbers to each row in the output. These numbers are held in the hidden column or pseudo column that is a ROWNUM column. Now it is so simple to apply the above logical condition, as you would have done to any other column of the table.

Q. How do you reference column values in BEFORE and AFTER insert and delete triggers?

The BEFORE and AFTER insert triggers can reference column values by new collection using keyword “:new.column name”. The before and after delete triggers can reference column values by old collection using keyword “:old. column name”.

Q. Can you change the inserted value in one of the columns in AFTER insert trigger code?

This is not possible as the column values supplied by the insert SQL query are already inserted into the table. If you try to assign new value to the column in AFTER insert trigger code then oracle error would be raised. To alter any values supplied by insert SQL query create BEFORE insert trigger.

Q. Explain use of SYSDATE and USER keywords.

SYSDATE is a pseudo column and refers to the current server system date. USER is a pseudo column and refers to the current user logged onto the oracle session. These values come handy when you want to monitor changes happening to the table.

Q. What is the difference between explicit cursor and implicit cursor?

When a single insert, delete or update statement is executed within PL/SQL program then oracle creates an implicit cursor for the same, executes the statement, and closes the cursor. You can check the result of execution using SQL%ROWCOUNT function.

Explicit cursors are created programmatically. The cursor type variable is declared and associated with SQL query. The program then opens a cursor, fetches column information into variables or record type variable, and closes cursor after all records are fetched. To check whether cursor is open or not use function SQL%ISOPEN and to check whether there are any records to be fetched from the cursor use function SQL%FOUND.

Q. Why does a query in Oracle run faster when ROWID is used as a part of the where clause?

ROWID is the logical address of a row - it is not a physical column. It is composed of file number, data block number and row number within data block. Therefore I/O time is minimized retrieving the row, resulting in a faster query.

Q. What type of exception will be raised in the following situations:

a> select..into statement returns more than one row.

b> select..into statement does not return any row.

c> insert statement inserts a duplicate record.

The errors returned are:
a> TOO_MANY_ROWS

b> NO_DATA_FOUND

c> DUP_VAL_ON_INDEX

Oracle PL / SQL Interview Question

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:
1 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.
2 Global temporary tables act as performance enhancers when compared to standard tables as they greatly reduce the disk IO.
3 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
1 Implicit cursors – created automatically by PL/SQL for all SQL Dml statements such as
Insert Update, delete and Select
2 Explicit cursors – Created explicitly. They create a storage area where the set of rows
Returned by a query are placed.
3 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?
1..Procedure is a sub program written to perform a set of actions and returns multiple valuesUsing out parameters or return no value at all.
2..Function is a subprogram written to perform certain computations and return a single value.

What are the modes for passing parameters to Oracle?
There are three modes for passing parameters to subprograms
1.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.
2. 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.
3.INOUT – An in-out parameter lets you pass initial values to the subprogram being called and returns updated values to the caller.

What is the difference between Truncate and Delete Statement?
1.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.
2. 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.

What are Exceptions? How many types of Exceptions are there?
Exceptions are conditions that cause the termination of a block. There are two types of exceptions
1.Pre-Defined – Predefined by PL/SQL and are associated with specific error codes.
2.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.

What is a Pragma Exception_Init? Explain its usage?
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?
1.Raise statement is used to raise a user defined exception.
2. 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.

What is the difference between Package, Procedure and Functions?
1.A package is a database objects that logically groups related PL/SQL types, objects, and Subprograms.
2.Procedure is a sub program written to perform a set of actions and can return multiple values.
3.Function is a subprogram written to perform certain computations and return a single value. Unlike subprograms packages cannot be called, passed parameters or nested.

How do you make a Function and Procedure as a Private?
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.

How do you kick a Concurrent program from PL/SQL?
Using FND_REQUEST.SUBMIT_REQUEST.

What is an Anonymous block?
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.

What are the two basic parameters that we have to pass while registering PL/SQL procedure?
Error code and Error Buffer.

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

What is a Trigger ? How many types of Triggers are there?
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

Can we use Commit in a Database Trigger, if ‘No’ then why?
No. Committing in a trigger will violate the integrity of the transaction.

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.

What is the difference between DDL, DML and DCL structures?
DDL statements are used for defining data. Ex: Create, Alter, Drop,Truncate,Rename.
DML statements are used for manipulating data. Ex: Insert, update, truncate.
DCL statements are used for to control the access of data. Ex; Grant, Revoke.
TCL statements are used for data saving.Ex; Commit,Rollback,Savepoint.

How can u create a table in PL/SQL procedure?
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.

How do we Tune the Queries?
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.

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 the snapshot and synonym?
7 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.
8 A synonym is an alias for table, view, sequence or program unit. They are of two types private and public.

What is the difference between data types char and varchar?
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).

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?
1.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
2. 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)

How can the duplicate records be deleted from the table?
delete from t1 a where rowid not in (select max(rowid) from t1 b where a.no=b.no)

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 difference between oracle 7 andoracle 8i?
A) Oracle 7 is a simple RDBMS, where as Oracle 8i 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

What is Data cleaning and testing.
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.
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?
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
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:
1.More easily administer SQL
2. Avoid the use of lexical parameters in your reports
3. Share data sources with other applications, such as Form Builder
4. Increase control and securityv) Encapsulate logic within a subprogram

SQL Developer Tutorial: Creating Objects for a Small Database

In this tutorial, you will use SQL Developer to create objects for a simplified library database, which will include tables for books, patrons (people who have library cards), and transactions (checking a book out, returning a book, and so on).

Note:

Other SQL Developer tutorials, including Oracle By Example (OBE) lessons, are available from the Start Page. If the tab for that page is not visible, click Help, then Start Page.

The tables are deliberately oversimplified for this tutorial. They would not be adequate for any actual public or organizational library. For example, this library contains only books (not magazines, journals, or other document formats), and it can contain no more than one copy of any book.
You will perform the following major steps:

Create a Table (BOOKS).
Create a Table (PATRONS).
Create a Table (TRANSACTIONS).
Create a Sequence.
Insert Data into the Tables.
Create a View.
Create a PL/SQL Procedure.
Debug a PL/SQL Procedure (optional).
Use the SQL Worksheet for Queries (optional).

Note:

To delete the objects that you create for this tutorial, you can use the DROP statements at the beginning of the script in Section 4.10, "Script for Creating and Using the Library Tutorial Objects".

4.1 Create a Table (BOOKS)

The BOOKS table contains a row for each book in the library. It includes columns of character and number types, a primary key, a unique constraint, and a check constraint. You will use the Create Table dialog box to create the table declaratively; the table that you create will be essentially the same as if you had entered the following statement using the SQL Worksheet:

CREATE TABLE books (
   book_id VARCHAR2(20),
   title VARCHAR2(50)
      CONSTRAINT title_not_null NOT NULL,
   author_last_name VARCHAR2(30)
      CONSTRAINT last_name_not_null NOT NULL,
   author_first_name VARCHAR2(30),
   rating NUMBER,
   CONSTRAINT books_pk PRIMARY KEY (book_id),
   CONSTRAINT rating_1_to_10 CHECK (rating IS NULL OR
      (rating >= 1 and rating <= 10)),
   CONSTRAINT author_title_unique UNIQUE (author_last_name, title));

To create the BOOKS table, connect to the database as the user in the schema you want to use for this tutorial. Right-click the Tables node in the schema hierarchy on the left side, select New Table, and enter the following information. (If a tab or field is not mentioned, do not enter anything for it. Be sure that the Advanced box is not checked when you start creating the table.)
For detailed information about the table dialog box and its tabs, see Section 5.52, "Create Table (quick creation)" and Section 5.53, "Create/Edit Table (with advanced options)".
Schema: Specify your current schema as the schema in which to create the table.
Name: BOOKS
Create the table columns using the following information. After creating each column except the last one (rating), click Add Column to add the next column. (If you accidentally click OK instead of Add Column, right-click the BOOKS table in the Connections navigator display, select Edit, and continue to add columns.)

Column Name	Type	Size	Other Information and Notes
book_id	VARCHAR2	20	Primary Key (Automatically checks Not Null; an index is also created on the primary key column. This is the Dewey code or other book identifier.)
title	VARCHAR2	50	Not Null
author_last_name	VARCHAR2	30	Not Null
author_first_name	VARCHAR2	30
rating	NUMBER		(Librarian's personal rating of the book, from 1 (poor) to 10 (great))

After you have entered the last column (rating), check Advanced (next to Schema). This displays a pane for more table options. For this table, you will use the Unique Constraints and Check Constraints panes.

Unique Constraints pane

Click Add to add a unique constraint for the table, namely, that the combination of author_last_name and title must be unique within the table. (This is deliberately oversimplified, since most major libraries will have allow more than one copy of a book in their holdings. Also, the combination of last name and title is not always a "foolproof" check for uniqueness, but it is sufficient for this simple scenario.)
Name: author_title_unique
In Available Columns, double-click TITLE and then AUTHOR_LAST_NAME to move them to Selected Columns.

Check Constraints pane

Click Add to add a check constraint for the table, namely, that the rating column value is optional (it can be null), but if a value is specified, it must be a number from 1 through 10. You must enter the condition using SQL syntax that is valid in a CHECK clause (but do not include the CHECK keyword or enclosing parentheses for the entire CHECK clause text).
Name: rating_1_to_10
Condition: rating is null or (rating >= 1 and rating <= 10)
Click OK to finish creating the table.
Go to Section 4.2, "Create a Table (PATRONS)" to create the next table.

4.2 Create a Table (PATRONS)

The PATRONS table contains a row for each patron who can check books out of the library (that is, each person who has a library card). It includes an object type (MDSYS.SDO_GEOMETRY) column. You will use the Create Table dialog box to create the table declaratively; the table that you create will be essentially the same as if you had entered the following statement using the SQL Worksheet:

CREATE TABLE patrons (
   patron_id NUMBER,
   last_name VARCHAR2(30)
      CONSTRAINT patron_last_not_null NOT NULL,
   first_name VARCHAR2(30),
   street_address VARCHAR2(50),
   city_state_zip VARCHAR2(50),
   location MDSYS.SDO_GEOMETRY,
   CONSTRAINT patrons_pk PRIMARY KEY (patron_id));

The use of single city_state_zip column for all that information is not good database design; it is done here merely to simplify your work in the tutorial.
The location column (Oracle Spatial geometry representing the patron's geocoded address) is merely to show the use of a complex (object) type.
To create the PATRONS table, if you are not already connected, connect to the database as the user for the schema you are using for this tutorial. Right-click the Tables node in the schema hierarchy on the left side, select New Table, and enter the following information. (If a tab or field is not mentioned, do not enter anything for it. Be sure that the Advanced box is not checked when you start creating the table.)
Schema: Specify your current schema as the schema in which to create the table.
Name: PATRONS
Create most of the table columns using the following information. After creating each column except the city_state_zip column, click Add Column to add the next column. (If you accidentally click OK instead of Add Column, right-click the PATRONS table in the Connections navigator display, select Edit, and continue to add columns.)

Column Name	Type	Size	Other Information and Notes
patron_id	NUMBER		Primary Key. (Unique patron ID number, with values to be created using a sequence that you will create)
last_name	VARCHAR2	30	Not Null
first_name	VARCHAR2	30
street_address	VARCHAR2	30
city_state_zip	VARCHAR2	30

The last column in the table (location) requires a complex data type, for which you must use the Columns tab with advanced options. Check Advanced (next to Schema). This displays a pane for selecting more table options.
In the Columns pane, click the city_state_zip column name, and click the Add Column (+) icon to add the following as the last column in the table.

Column Name	Type	Other Information and Notes
location	Complex type Schema: MDSYS Type: SDO_GEOMETRY	(Oracle Spatial geometry object representing the patron's geocoded address)

After you have entered the last column (location), click OK to finish creating the table.
Go to Section 4.3, "Create a Table (TRANSACTIONS)" to create the next table.

4.3 Create a Table (TRANSACTIONS)

The TRANSACTIONS table contains a row for each transaction involving a patron and a book (for example, someone checking a book out or returning a book). It includes two foreign key columns. You will use the Create Table dialog box to create the table declaratively; the table that you create will be essentially the same as if you had entered the following statement using the SQL Worksheet:

CREATE TABLE transactions (
   transaction_id NUMBER,
   patron_id CONSTRAINT for_key_patron_id
      REFERENCES patrons(patron_id),
   book_id CONSTRAINT for_key_book_id
      REFERENCES books(book_id),
   transaction_date DATE
      CONSTRAINT tran_date_not_null NOT NULL,
   transaction_type NUMBER
      CONSTRAINT tran_type_not_null NOT NULL,
   CONSTRAINT transactions_pk PRIMARY KEY (transaction_id));

To create the TRANSACTIONS table, if you are not already connected, connect to the database as the user for the schema you are using for this tutorial. Right-click the Tables node in the schema hierarchy on the left side, select New Table, and enter the following information. (If a tab or field is not mentioned, do not enter anything for it. Be sure that the Advanced box is not checked when you start creating the table.)
Schema: Specify your current schema as the schema in which to create the table.
Name: TRANSACTIONS
Create the table columns using the following information. After creating each column except the last one (transaction_type), click Add Column to add the next column. (If you accidentally click OK instead of Add Column, right-click the TRANSACTIONS table in the Connections navigator display, select Edit, and continue to add columns.)

Column Name	Type	Size	Other Information and Notes
transaction_id	NUMBER		Primary Key. (Unique transaction ID number, with values to be created using a trigger and sequence that will be created automatically)
patron_id	NUMBER		(Foreign key; must match a patron_id value in the PATRONS table)
book_id	VARCHAR2	20	(Foreign key; must match a book_id value in the BOOKS table)
transaction_date	DATE		(Date and time of the transaction)
transaction_type	NUMBER		(Numeric code indicating the type of transaction, such as 1 for checking out a book)

After you have entered the last column (transaction_type), check Advanced (next to Schema). This displays a pane for selecting more table options. For this table, you will use the Column Sequences and Foreign Keys panes.

Column Sequences pane

You have already specified TRANSACTION_ID as the primary key, and you will use this pane only to specify that the primary key column values are to be populated automatically. This convenient approach uses a trigger and a sequence (both created automatically by SQL Developer), and ensures that each transaction ID value is unique.
Column: TRANSACTION_ID
Sequence: New Sequence
Trigger: TRANSACTIONS_TRG (The default; a before-insert trigger with this name will be created automatically.)

Foreign Keys tab

1. Click Add to create the first of the two foreign keys for the TRANSACTIONS table.
Name: for_key_patron_id
Referenced Schema: Name of the schema containing the table with the primary key or unique constraint to which this foreign key refers. Use the schema you have been using for this tutorial.
Referenced Table: PATRONS
Referenced Constraint: PATRONS_PK (The name of the primary key constraint for the PATRONS table. Be sure that the Referenced Column on PATRONS displayed value is PATRON_ID.)
Associations: Local Column: PATRON_ID
Associations: Referenced Column on PATRONS: PATRON_ID
2. Click Add to create the second of the two foreign keys for the TRANSACTIONS table.
Name: for_key_book_id
Referenced Schema: Name of the schema containing the table with the primary key or unique constraint to which this foreign key refers. Use the schema you have been using for this tutorial.
Referenced Table: BOOKS
Referenced Constraint: BOOKS_PK (The name of the primary key constraint for the BOOKS table. Be sure that the Referenced Column on BOOKS displayed value is BOOK_ID.
Associations: Local Column: BOOK_ID
Associations: Referenced Column on BOOKS: BOOK_ID
3. Click OK to finish creating the table.
You have finished creating all the tables. To create a sequence for use in generating unique primary key values for the PATRONS table, go to Section 4.4, "Create a Sequence".

4.4 Create a Sequence

Create one sequence object, which will be used in INSERT statements to generate unique primary key values in the PATRONS table. (You do not need to create a sequence for the primary key in the TRANSACTIONS table, because you used the SQL Developer feature that enables automatic population of primary key values for that table.) You will use the Create Sequence dialog box to create the sequence declaratively; the sequence that you create will be essentially the same as if you had entered the following statements using the SQL Worksheet:

CREATE SEQUENCE patron_id_seq 
   START WITH 100
   INCREMENT BY 1;

After creating the sequence, you can use it in INSERT statements to generate unique numeric values. The following example uses the patron_id_seq sequence in creating a row for a new patron (library user), assigning her a patron ID that is the next available value of the patron_id_seq sequence:

INSERT INTO patrons VALUES  (patron_id_seq.nextval, 
   'Smith', 'Jane', '123 Main Street', 'Mytown, MA 01234', null);

To create the sequence, if you are not already connected, connect to the database as the user for the schema you are using for this tutorial. Right-click the Sequences node in the schema hierarchy on the left side, select New Sequence, and enter information using the Create Sequence dialog box.
Schema: Specify your current schema as the schema in which to create the sequence.
Name: patron_id_seq
Increment: 1
Start with: 100
Min value: 100
Click OK to finish creating the sequence.
To insert sample data into the tables, go to Section 4.5, "Insert Data into the Tables".

4.5 Insert Data into the Tables

For your convenience in using the view and the PL/SQL procedure that you will create, add some sample data to the BOOKS, PATRONS, and TRANSACTIONS tables. (If you do not add sample data, you can still create the remaining objects in this tutorial, but the view and the procedure will not return any results.)
Go to the SQL Worksheet window associated with the database connection you have been using. (For information about using the SQL Worksheet, see Section 1.7, "Using the SQL Worksheet".) Copy and paste the following INSERT statements into the Enter SQL Statement box:

INSERT INTO books VALUES ('A1111', 'Moby Dick', 'Melville', 'Herman', 10);
INSERT INTO books VALUES ('A2222', 'Get Rich Really Fast', 'Scammer', 'Ima', 1);
INSERT INTO books VALUES ('A3333', 'Finding Inner Peace', 'Blissford', 'Serenity', null);
INSERT INTO books VALUES ('A4444', 'Great Mystery Stories', 'Whodunit', 'Rodney', 5);
INSERT INTO books VALUES ('A5555', 'Software Wizardry', 'Abugov', 'D.', 10);
 
INSERT INTO patrons VALUES  (patron_id_seq.nextval, 
   'Smith', 'Jane', '123 Main Street', 'Mytown, MA 01234', null);
INSERT INTO patrons VALUES  (patron_id_seq.nextval, 
   'Chen', 'William', '16 S. Maple Road', 'Mytown, MA 01234', null);
INSERT INTO patrons VALUES  (patron_id_seq.nextval, 
   'Fernandez', 'Maria', '502 Harrison Blvd.', 'Sometown, NH 03078', null);
INSERT INTO patrons VALUES  (patron_id_seq.nextval, 
   'Murphy', 'Sam', '57 Main Street', 'Mytown, MA 01234', null);
 
INSERT INTO transactions (patron_id, book_id, 
  transaction_date, transaction_type) 
  VALUES (100, 'A1111', SYSDATE, 1);
INSERT INTO transactions (patron_id, book_id, 
  transaction_date, transaction_type) 
  VALUES (100, 'A2222', SYSDATE, 2);
INSERT INTO transactions (patron_id, book_id, 
  transaction_date, transaction_type) 
  VALUES (101, 'A3333', SYSDATE, 3);
INSERT INTO transactions (patron_id, book_id, 
  transaction_date, transaction_type) 
  VALUES (101, 'A2222', SYSDATE, 1);
INSERT INTO transactions (patron_id, book_id, 
  transaction_date, transaction_type) 
  VALUES (102, 'A3333', SYSDATE, 1);
INSERT INTO transactions (patron_id, book_id, 
  transaction_date, transaction_type) 
  VALUES (103, 'A4444', SYSDATE, 2);
INSERT INTO transactions (patron_id, book_id, 
  transaction_date, transaction_type) 
  VALUES (100, 'A4444', SYSDATE, 1);
INSERT INTO transactions (patron_id, book_id, 
  transaction_date, transaction_type) 
  VALUES (102, 'A2222', SYSDATE, 2);
INSERT INTO transactions (patron_id, book_id, 
  transaction_date, transaction_type) 
  VALUES (102, 'A5555', SYSDATE, 1);
INSERT INTO transactions (patron_id, book_id, 
  transaction_date, transaction_type) 
  VALUES (101, 'A2222', SYSDATE, 1);

Click the Run Script icon, or press the F5 key.
To create a view, go to Section 4.6, "Create a View".

4.6 Create a View

Create a view that returns information about patrons and their transactions. This view queries the PATRONS and TRANSACTIONS tables, and returns rows that contain a patron's ID, last name, and first name, along with a transaction and the transaction type. The rows are ordered by patron ID, and by transaction type within patron IDs.
To create the patrons_trans_view view, if you are not already connected, connect to the database as the user for the schema you are using for this tutorial. Right-click the Views node in the schema hierarchy on the left side, select New View, and enter the following information. (If a tab or field is not mentioned, do not enter anything for it.)
Schema: Specify your current schema as the schema in which to create the view.
Name: patrons_trans_view

SQL Query tab

In the SQL Query box, enter (or copy and paste) the following statement:

SELECT p.patron_id,
       p.last_name,
       p.first_name,
       t.transaction_type,
       t.transaction_date
  FROM patrons p, transactions t
 WHERE p.patron_id = t.patron_id
 ORDER BY p.patron_id, t.transaction_type

Then click Test Syntax, and ensure that you have not made any syntax errors. If you made any errors, correct then and click Test Syntax again.

DDL

Review the SQL statement that SQL Developer will use to create the view. If you want to make any changes, go back to the SQL Query tab and make the changes there.
If you want to save the CREATE VIEW statement to a SQL script file, click Save and specify the location and file name.
When you are finished, click OK.
You have finished creating the view. If you inserted data to the underlying tables, as described in Section 4.5, "Insert Data into the Tables", you can see the data returned by this view as follows: in the Connections navigator, expand Views, and select PATRONS_TRANS_VIEW, then click the Data tab.
To create a procedure that lists all books with a specified rating, go to Section 4.7, "Create a PL/SQL Procedure".

4.7 Create a PL/SQL Procedure

Create a procedure that lists all books with a specified rating. You can then call this procedure with an input parameter (a number from 1 to 10), and the output will be all the titles of all books with that rating.
To create the procedure, if you are not already connected, connect to the database as the user for the schema you are using for this tutorial. Right-click the Procedures node in the schema hierarchy on the left side, select New Procedure, and enter the following information using the Create PL/SQL Procedure dialog box.
Object Name: list_a_rating
Click OK. A source window for the new procedure is opened. Enter (or copy and paste) the following procedure text, replacing any existing text:

CREATE OR REPLACE
PROCEDURE list_a_rating(in_rating IN NUMBER) AS
  matching_title VARCHAR2(50);
  TYPE my_cursor IS REF CURSOR;
  the_cursor my_cursor;
BEGIN
  OPEN the_cursor
    FOR 'SELECT title 
           FROM books 
          WHERE rating = :in_rating'
    USING in_rating;
  DBMS_OUTPUT.PUT_LINE('All books with a rating of ' || in_rating || ':');
  LOOP
    FETCH the_cursor INTO matching_title;
    EXIT WHEN the_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(matching_title);
  END LOOP;
  CLOSE the_cursor;
END list_a_rating;

This procedure uses a cursor (named the_cursor) to return only rows where the book has the specified rating (in_rating parameter), and uses a loop to output the title of each book with that rating.
Click the Save icon to save the procedure.
As a usage example, after creating the procedure named LIST_A_RATING, if you have inserted data into the BOOKS table (for example, using the INSERT statements in Section 4.5, "Insert Data into the Tables"), you could use the following statement to return all books with a rating of 10:

CALL list_a_rating(10);

To run this procedure within SQL Developer, right-click LIST_A_RATING in the Connections navigator hierarchy display and select Run. Under PL/SQL Block in the Run PL/SQL dialog box, change IN_RATING => IN_RATING to IN_RATING => 10, and click OK. The Log window display will now include the following output:

All books with a rating of 10:                                                  
Moby Dick                                                                       
Software Wizardry

4.8 Debug a PL/SQL Procedure

If you want to practice debugging a PL/SQL procedure with SQL Developer, create a procedure that is like the list_a_rating procedure that you created in Section 4.7, "Create a PL/SQL Procedure", but with a logic error. (The coding is also deliberately inefficient, to allow the display of the rating in a variable.)
Before you can debug the procedure, you must ensure that the user associated with the database connection has the DEBUG CONNECT SESSION and DEBUG ANY PROCEDURE privileges.
To create this procedure, if you are not already connected, connect to the database as the user for the schema you are using for this tutorial. Right-click the Procedures node in the schema hierarchy on the left side, select New Procedure, and enter the following information using the Create PL/SQL Procedure dialog box.
Object Name: list_a_rating2
Click OK. A source window for the new procedure is opened. Enter (or copy and paste) the following procedure text, replacing any existing text:

CREATE OR REPLACE
PROCEDURE  list_a_rating2(in_rating IN NUMBER) AS
  matching_title VARCHAR2(50);
  matching_rating NUMBER;
  TYPE my_cursor IS REF CURSOR;
  the_cursor my_cursor;
  rating_cursor my_cursor;
BEGIN
  OPEN the_cursor 
    FOR 'SELECT title 
           FROM books 
          WHERE rating <= :in_rating' 
    USING in_rating;
  OPEN rating_cursor FOR 'SELECT rating FROM books WHERE 
    rating <= :in_rating' USING in_rating;
  DBMS_OUTPUT.PUT_LINE('All books with a rating of ' || in_rating || ':');
  LOOP
    FETCH the_cursor INTO matching_title;
    FETCH rating_cursor INTO matching_rating;
    EXIT WHEN the_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(matching_title);
  END LOOP;
  CLOSE the_cursor;
  CLOSE rating_cursor;
END list_a_rating2;

This procedure contains a logic error in the definition of the_cursor: it selects titles where the rating is less than or equal to a specified rating, whereas it should select titles only where the rating is equal to the specified rating.
Click the Save icon to save the procedure.
Assume that you wanted to run this procedure and list all books with a rating of 10. Right-click LIST_A_RATING2 in the Connections navigator hierarchy display and select Run. Under PL/SQL Block in the Run PL/SQL dialog box, change, change IN_RATING => IN_RATING to IN_RATING => 10, and click OK. In the Log window, however, you see unexpected output: many titles are listed, including some with ratings other than 10. So, you decide to debug the procedure.
To debug the procedure, follow these steps:

Click the Compile for Debug icon in the toolbar under the LIST_A_RATING2 tab.
Set two breakpoints by clicking in the left margin (left of the thin vertical line) beside each of these two lines:
```
FETCH the_cursor INTO matching_title;
FETCH rating_cursor INTO matching_rating;
```
Clicking in the left margin toggles the setting and unsetting of breakpoints. Clicking beside these two lines will enable you to see the values of the matching_title and matching_rating variables as execution proceeds in debug mode.
Click the Debug icon, and in the Run PL/SQL dialog box change IN_RATING => IN_RATING to IN_RATING => 10; then click OK
Click View, then Debugger, then Data to display the Data pane. (Tip: Expand the Name column width so that you can see MATCHING_RATING.)
Press the F9 key (or click Debug, then Resume) to have execution proceed, stopping at the next breakpoint.
Repeatedly press the F9 key (or click Debug, then Resume), noticing especially the value of MATCHING_RATING as each row is processed. You will notice the first incorrect result when you see that the title Get Rich Really Fast is included, even though its rating is only 1 (obviously less than 10). (See the screen illustration with debugging information in Section 1.6, "Running and Debugging Functions and Procedures".)
When you have enough information to fix the problem, you can click the Terminate icon in the debugging toolbar.

From this debugging session, you know that to fix the logic error, you should change rating <= :in_rating to rating = :in_rating in the definition of the_cursor.

4.9 Use the SQL Worksheet for Queries

You can use the SQL Worksheet to test SQL statements using a database connection. To display the worksheet, from the Tools menu, select SQL Worksheet. In the Select Connection dialog box, select the database connection that you used to create the BOOKS, PATRONS, and TRANSACTIONS tables for the tutorial in Chapter 4, "SQL Developer Tutorial: Creating Objects for a Small Database".
The SQL Worksheet has the user interface shown in Section 1.7, "Using the SQL Worksheet".
In the Enter SQL Statement box, enter the following statement (the semicolon is optional for the SQL Worksheet):

SELECT author_last_name, title FROM books;

Notice the automatic highlighting of SQL keywords (SELECT and FROM in this example).
Click the Execute SQL Statement icon in the SQL Worksheet toolbar. The results of the query are displayed on the Results tab under the area in which you entered the SQL statement.
In the Enter SQL Statement box, enter (or copy and paste) the following statement, which is the same as the SELECT statement in the view you created in Create a View:

SELECT p.patron_id,
       p.last_name,
       p.first_name,
       t.transaction_type,
       t.transaction_date
  FROM patrons p, transactions t
 WHERE p.patron_id = t.patron_id
 ORDER BY p.patron_id, t.transaction_type;

Click the Execute SQL Statement icon in the SQL Worksheet toolbar, and view the results of the query.
Click the Execute Explain Plan icon in the SQL Worksheet toolbar to see the execution plan (displayed on the Explain tab) that Oracle Database follows to execute the SQL statement. The information includes the optimizer strategy and the cost of executing the statement. (For information about how to generate and interpret execution plans, see Oracle Database Performance Tuning Guide.)

4.10 Script for Creating and Using the Library Tutorial Objects

The following statements create and use the database objects that you have created (or will create) for the tutorial in Chapter 4, "SQL Developer Tutorial: Creating Objects for a Small Database". You can view these commands to help you understand the library database objects that are covered in the tutorial.

-- Clean up from any previous tutorial actions.
DROP TABLE transactions;
DROP TABLE books;
DROP TABLE patrons;
DROP SEQUENCE patron_id_seq;
DROP SEQUENCE transactions_seq;
DROP TRIGGER transactions_trg;
DROP VIEW patrons_trans_view;
DROP PROCEDURE list_a_rating;
DROP PROCEDURE list_a_rating2;
 
set serveroutput on
 
-- Create objects.
 
CREATE TABLE books (
   book_id VARCHAR2(20),
   title VARCHAR2(50)
      CONSTRAINT title_not_null NOT NULL,
   author_last_name VARCHAR2(30)
      CONSTRAINT last_name_not_null NOT NULL,
   author_first_name VARCHAR2(30),
   rating NUMBER,
   CONSTRAINT books_pk PRIMARY KEY (book_id),
   CONSTRAINT rating_1_to_10 CHECK (rating IS NULL OR
      (rating >= 1 and rating <= 10)),
   CONSTRAINT author_title_unique UNIQUE (author_last_name, title));
 
CREATE TABLE patrons (
   patron_id NUMBER,
   last_name VARCHAR2(30)
      CONSTRAINT patron_last_not_null NOT NULL,
   first_name VARCHAR2(30),
   street_address VARCHAR2(50),
   city_state_zip VARCHAR2(50),
   location MDSYS.SDO_GEOMETRY,
   CONSTRAINT patrons_pk PRIMARY KEY (patron_id));
 
CREATE TABLE transactions (
   transaction_id NUMBER,
   patron_id CONSTRAINT for_key_patron_id
      REFERENCES patrons(patron_id),
   book_id CONSTRAINT for_key_book_id
      REFERENCES books(book_id),
   transaction_date DATE
      CONSTRAINT tran_date_not_null NOT NULL,
   transaction_type NUMBER
      CONSTRAINT tran_type_not_null NOT NULL,
   CONSTRAINT transactions_pk PRIMARY KEY (transaction_id));
 
CREATE SEQUENCE patron_id_seq 
   START WITH 100
   INCREMENT BY 1;
 
-- The sequence for the transaction_id 
-- in the tutorial is created automatically,
-- and may have the name TRANSACTIONS_SEQ.
CREATE SEQUENCE transactions_seq 
   START WITH 1
   INCREMENT BY 1;
 
-- The before-insert trigger for transaction ID values
-- in the tutorial is created automatically,
-- and may have the name TRANSACTIONS_TRG.
CREATE OR REPLACE TRIGGER transactions_trg
   BEFORE INSERT ON TRANSACTIONS 
  FOR EACH ROW 
  BEGIN
    SELECT TRANSACTIONS_SEQ.NEXTVAL INTO :NEW.TRANSACTION_ID FROM DUAL;
  END;
/
 
CREATE VIEW patrons_trans_view AS
  SELECT p.patron_id,
         p.last_name,
         p.first_name,
         t.transaction_type,
         t.transaction_date
    FROM patrons p, transactions t
   WHERE p.patron_id = t.patron_id
   ORDER BY p.patron_id, t.transaction_type;
 
-- Procedure: List all books that have a specified rating.
CREATE OR REPLACE PROCEDURE list_a_rating(in_rating IN NUMBER) AS
  matching_title VARCHAR2(50);
  TYPE my_cursor IS REF CURSOR;
  the_cursor my_cursor;
BEGIN
  OPEN the_cursor
    FOR 'SELECT title 
           FROM books 
          WHERE rating = :in_rating'
    USING in_rating;
  DBMS_OUTPUT.PUT_LINE('All books with a rating of ' || in_rating || ':');
  LOOP
    FETCH the_cursor INTO matching_title;
    EXIT WHEN the_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(matching_title);
  END LOOP;
  CLOSE the_cursor;
END;
/
show errors;
 
-- Insert and query data.
 
INSERT INTO books VALUES ('A1111', 'Moby Dick', 'Melville', 'Herman', 10);
INSERT INTO books VALUES ('A2222', 'Get Rich Really Fast', 'Scammer', 'Ima', 1);
INSERT INTO books VALUES ('A3333', 'Finding Inner Peace', 'Blissford', 'Serenity', null);
INSERT INTO books VALUES ('A4444', 'Great Mystery Stories', 'Whodunit', 'Rodney', 5);
INSERT INTO books VALUES ('A5555', 'Software Wizardry', 'Abugov', 'D.', 10);
 
INSERT INTO patrons VALUES  (patron_id_seq.nextval, 
   'Smith', 'Jane', '123 Main Street', 'Mytown, MA 01234', null);
INSERT INTO patrons VALUES  (patron_id_seq.nextval, 
   'Chen', 'William', '16 S. Maple Road', 'Mytown, MA 01234', null);
INSERT INTO patrons VALUES  (patron_id_seq.nextval, 
   'Fernandez', 'Maria', '502 Harrison Blvd.', 'Sometown, NH 03078', null);
INSERT INTO patrons VALUES  (patron_id_seq.nextval, 
   'Murphy', 'Sam', '57 Main Street', 'Mytown, MA 01234', null);
 
INSERT INTO transactions (patron_id, book_id, 
  transaction_date, transaction_type) 
  VALUES (100, 'A1111', SYSDATE, 1);
INSERT INTO transactions (patron_id, book_id, 
  transaction_date, transaction_type) 
  VALUES (100, 'A2222', SYSDATE, 2);
INSERT INTO transactions (patron_id, book_id, 
  transaction_date, transaction_type) 
  VALUES (101, 'A3333', SYSDATE, 3);
INSERT INTO transactions (patron_id, book_id, 
  transaction_date, transaction_type) 
  VALUES (101, 'A2222', SYSDATE, 1);
INSERT INTO transactions (patron_id, book_id, 
  transaction_date, transaction_type) 
  VALUES (102, 'A3333', SYSDATE, 1);
INSERT INTO transactions (patron_id, book_id, 
  transaction_date, transaction_type) 
  VALUES (103, 'A4444', SYSDATE, 2);
INSERT INTO transactions (patron_id, book_id, 
  transaction_date, transaction_type) 
  VALUES (100, 'A4444', SYSDATE, 1);
INSERT INTO transactions (patron_id, book_id, 
  transaction_date, transaction_type) 
  VALUES (102, 'A2222', SYSDATE, 2);
INSERT INTO transactions (patron_id, book_id, 
  transaction_date, transaction_type) 
  VALUES (102, 'A5555', SYSDATE, 1);
INSERT INTO transactions (patron_id, book_id, 
  transaction_date, transaction_type) 
  VALUES (101, 'A2222', SYSDATE, 1);
 
-- Test the view and the procedure.
SELECT * FROM patrons_trans_view;
CALL list_a_rating(10);