Explore topic-wise InterviewSolutions in .

The IDEPTREE view is a pre-indented REPRESENTATION of the HIERARCHICAL STRUCTURE of the schema objects, It is a sorted version of the DEPTREE view.

The IDEPTREE view is a pre-indented representation of the hierarchical structure of the schema objects, It is a sorted version of the DEPTREE view.

NESTED_LEVEL 2 for an object in the dependency TREE means that the object is DEPENDENT on another object, which has NESTED_LEVEL 1.

NESTED_LEVEL 2 for an object in the dependency tree means that the object is dependent on another object, which has NESTED_LEVEL 1.

The NESTED_LEVEL column shows the level of nesting of a particular OBJECT within the DEPENDENCY tree. The parent object has a NESTED_LEVEL of 0 and all other objects are NUMBERED as PER their level in RELATION to the parent object.

The NESTED_LEVEL column shows the level of nesting of a particular object within the dependency tree. The parent object has a NESTED_LEVEL of 0 and all other objects are numbered as per their level in relation to the parent object.

These VIEWS are created by executing the utldtree.sql FILE which is FOUND in the $ORACLE_HOME/rdbms/admin FOLDER.

These views are created by executing the utldtree.sql file which is found in the $ORACLE_HOME/rdbms/admin folder.

These views contain information on the object DIRECT and INDIRECT DEPENDENCY tree.

These views contain information on the object direct and indirect dependency tree.

All the DEPENDENT objects become INVALID if an OBJECT, which the dependent objects reference, is RENAMED.

All the dependent objects become invalid if an object, which the dependent objects reference, is renamed.

Suppose object A REFERENCES object B, which in turn references object C, then A is an indirect dependent of object C. If object C is changed, then object B is invalidated; and therefore, INVALIDATES object A as well. This is called cascading invalidation.

Suppose object A references object B, which in turn references object C, then A is an indirect dependent of object C. If object C is changed, then object B is invalidated; and therefore, invalidates object A as well. This is called cascading invalidation.

If the STUDENT_GRADES view Includes the STUDENT_NAME COLUMN then it will BECOME invalid; and THEREFORE, needs to be recompiled. If the view does not include the column, which is MODIFIED, then it is valid.

If the STUDENT_GRADES view Includes the STUDENT_NAME column then it will become invalid; and therefore, needs to be recompiled. If the view does not include the column, which is modified, then it is valid.

The FOLLOWING query will DISPLAY the object TYPES that are REFERENCED by other OBJECTS in the database:

The following query will display the object types that are referenced by other objects in the database:

The REFERENCED_TYPE COLUMN of the USER_DEPENDENCIES view INDICATES the type of the REFERENCED object.

The REFERENCED_TYPE column of the USER_DEPENDENCIES view indicates the type of the referenced object.

The USER_DEPENDENCIES, DBA_DEPENDENCIES, and ALL_DEPENDENCIES DATA dictionary VIEWS display the dependencies among the SCHEMA objects.

The USER_DEPENDENCIES, DBA_DEPENDENCIES, and ALL_DEPENDENCIES data dictionary views display the dependencies among the schema objects.

Whenever an invalid object is CALLED again, the Oracle SERVER automatically recompiles the object and it GETS a valid status. For example, if a VIEW with an invalid status is QUERIED, it will be recompiled before the results are displayed.

Whenever an invalid object is called again, the Oracle server automatically recompiles the object and it gets a valid status. For example, if a view with an invalid status is queried, it will be recompiled before the results are displayed.

If there is any change in any of the referenced object, the dependent object will get an INVALID STATUS. For example, if the structure of base table of a view is MODIFIED, the view will get an invalid status.

If there is any change in any of the referenced object, the dependent object will get an invalid status. For example, if the structure of base table of a view is modified, the view will get an invalid status.

When any procedure or function references another SCHEMA object through an intermediate procedure, function, or VIEW, then the REFERENCE is called INDIRECT reference.

When any procedure or function references another schema object through an intermediate procedure, function, or view, then the reference is called indirect reference.

Any procedure or FUNCTION can be referenced directly or INDIRECTLY using the following objects:

•  TABLES
•  VIEWS
•  PROCEDURES
•  Functions
•  Sequences
•  Package procedures or functions

Any procedure or function can be referenced directly or indirectly using the following objects:

Yes, Oracle automatically records the DEPENDENCIES among objects through the USER_OBJECTS DATA dictionary view, where the SCHEMA objects have a valid or INVALID status.

Yes, Oracle automatically records the dependencies among objects through the USER_OBJECTS data dictionary view, where the schema objects have a valid or invalid status.

If the definition of an object A references object B, then A is the DEPENDENT object and B is the REFERENCED object. If a table is being queried in a procedure, then the table is the referenced object and the procedure is the dependent object. However, if the definition of the table is modified, the procedure MAY or may not execute CORRECTLY.

If the definition of an object A references object B, then A is the dependent object and B is the referenced object. If a table is being queried in a procedure, then the table is the referenced object and the procedure is the dependent object. However, if the definition of the table is modified, the procedure may or may not execute correctly.

When INTERNAL LOB DATATYPE (BLOB, CLOB, and NCLOB) column values are DELETED, both locator and values are deleted. If an EXTERNAL LOB (BFILE) value is deleted, then only the locator value is deleted. The external file has to be manually deleted from the SERVER file system.

When Internal LOB datatype (BLOB, CLOB, and NCLOB) column values are deleted, both locator and values are deleted. If an external LOB (BFILE) value is deleted, then only the locator value is deleted. The external file has to be manually deleted from the server file system.

FOLLOWING are the procedures or functions of the DBMS_LOB package that should be used before manipulating the BFILE DATA:

•  FILEOPEN— Opens files
•  FILEGETNAME—GeXs the filename
•  FILEEXISTS —CHECKS whether the file exists
•  FILEISOPEN— Checks whether the file is OPEN

These procedures are used, as Oracle does not maintain the INTEGRITY of the external file; only the pointer is maintained.

Following are the procedures or functions of the DBMS_LOB package that should be used before manipulating the BFILE data:

These procedures are used, as Oracle does not maintain the integrity of the external file; only the pointer is maintained.

Yes, BFILE can be set to NULL VALUES; whereas, BLOB and CLOB cannot be set to NULL values. They have to be INITIALIZED USING the EMPTY_BLOB or EMPTY_CLOB FUNCTION.

Yes, BFILE can be set to NULL values; whereas, BLOB and CLOB cannot be set to NULL values. They have to be initialized using the EMPTY_BLOB or EMPTY_CLOB function.

This ALLOWS the database to AVOID scanning LOB data each time it reads rows from the table. The LOB data will be READ only when it is required; however, at other times, only the LOB locator VALUES are read.

This allows the database to avoid scanning LOB data each time it reads rows from the table. The LOB data will be read only when it is required; however, at other times, only the LOB locator values are read.

Yes, Oracle MAINTAINS data integrity and concurrency for INTERNAL LOBs (BLOBs and CLOBs) but not for BFILEs, which are EXTERNAL LOBs.

Yes, Oracle maintains data integrity and concurrency for internal LOBs (BLOBs and CLOBs) but not for BFILEs, which are external LOBs.

No, the BLOB and CLOB COLUMNS cannot be UPDATED until the value is set using the EMPTY_BLOB or EMPTY_CLOB FUNCTION through an UPDATE STATEMENT.

No, the BLOB and CLOB columns cannot be updated until the value is set using the EMPTY_BLOB or EMPTY_CLOB function through an UPDATE statement.

No, the BLOB and BFILE data cannot be seen using the SELECT STATEMENT query. RELEVANT software, such as ORACLE FORMS or Reports, should be used for this purpose.

No, the BLOB and BFILE data cannot be seen using the SELECT statement query. Relevant software, such as Oracle Forms or Reports, should be used for this purpose.

A LOB COLUMN can be added using the following syntax:

A LOB column can be added using the following syntax:

The DBMS_LOB package has functions and procedures to MANIPULATE LOB objects. These can be categorized into mutators and observers. Mutators can modify LOB values, such as APPEND, COPY, WRITE, FILECLOSE, and FILEOPEN; whereas, observers can only READ the LOB values, such as COMPARE, INSTR, GETLENGTH, FILEEXISTS, and FILEGETNAME.

The DBMS_LOB package has functions and procedures to manipulate LOB objects. These can be categorized into mutators and observers. Mutators can modify LOB values, such as APPEND, COPY, WRITE, FILECLOSE, and FILEOPEN; whereas, observers can only read the LOB values, such as COMPARE, INSTR, GETLENGTH, FILEEXISTS, and FILEGETNAME.

No, the DBMS_LOB PACKAGE does not support CONCURRENCY control. The user is responsible for LOCKING the ROW explicitly before any TRANSACTIONS. The DBMS_LOB package does not provide implicit locking of the row with LOB values.

No, the DBMS_LOB package does not support concurrency control. The user is responsible for locking the row explicitly before any transactions. The DBMS_LOB package does not provide implicit locking of the row with LOB values.

The DBMS_LOB PACKAGE HELPS PL/SQL PROGRAMS to ACCESS and manipulate the LOB objects.

The DBMS_LOB package helps PL/SQL programs to access and manipulate the LOB objects.

Yes, it POSSIBLE to convert CLOB to CHAR TYPE using the TO_CHAR function.

Yes, it possible to convert CLOB to CHAR type using the TO_CHAR function.

The TO_CLOB FUNCTION is used to CONVERT the LONG, VARCHAR2, and CHAR datatypes to the CLOB datatype.

The TO_BLOB function is used to convert the LONG RAW and RAW datatype to the BLOB datatype.

The TO_CLOB function is used to convert the LONG, VARCHAR2, and CHAR datatypes to the CLOB datatype.

The TO_BLOB function is used to convert the LONG RAW and RAW datatype to the BLOB datatype.

The NULL and NOT NULL CONSTRAINTS are ALLOWED for LOB OBJECTS.

The NULL and NOT NULL constraints are allowed for LOB objects.

The number of BFILES that can be opened in a SESSION is dependent on the parameter SESSION_MAX_OPEN_FILES, which is set in the init.ora FILE. Its default value is 10. The MAXIMUM value is EITHER 50 or the value set in the parameter.

The number of BFILEs that can be opened in a session is dependent on the parameter SESSION_MAX_OPEN_FILES, which is set in the init.ora file. Its default value is 10. The maximum value is either 50 or the value set in the parameter.

When the LOB RECORD is deleted, the BFILE type of external file is not ACTUALLY deleted; only the locator to the location in the DATABASE table is deleted.

When the LOB record is deleted, the BFILE type of external file is not actually deleted; only the locator to the location in the database table is deleted.

No, BFILES are read-only and cannot PARTICIPATE in TRANSACTIONS.

No, BFILEs are read-only and cannot participate in transactions.

LOB datatype can be internal or external. Internal LOBS are CLOB, NCLOB, and BLOB that are stored in the database; WHEREAS, external LOBs are BFILEs that are stored outside the database as external files. Although both LOB types have the pointer to the LOCATION, the BFILEs are stored EXTERNALLY across hard disks or on external storage; whereas, internal LOBs have to be stored in the different location within the database itself.

LOB datatype can be internal or external. Internal LOBs are CLOB, NCLOB, and BLOB that are stored in the database; whereas, external LOBs are BFILEs that are stored outside the database as external files. Although both LOB types have the pointer to the location, the BFILEs are stored externally across hard disks or on external storage; whereas, internal LOBs have to be stored in the different location within the database itself.

Yes, a LOB DATATYPE can be USED as a PL/SQL variable or PARAMETER.

Yes, a LOB datatype can be used as a PL/SQL variable or parameter.

The two components of the LOB datatype are given as FOLLOWS:

•  LOB locator — Refers to a locator, which points to the LOCATION in the DATABASE where the value is stored. This value is stored along with the RECORD in the table row and is similar to a pointer to the ACTUAL location of LOB value.
•  LOB value — Refers to an actual image or value of the LOB datatype.

The two components of the LOB datatype are given as follows:

Yes, it is possible to CONVERT LONG and LONG RAW OBJECTS to LOB objects USING the TO_LOB FUNCTION.

Yes, it is possible to convert LONG and LONG RAW objects to LOB objects using the TO_LOB function.

The BLOB datatype is USED to store a BINARY LARGE OBJECT, such as a VIDEO image file; whereas, the CLOB datatype is used to store a character large object.

The BLOB datatype is used to store a binary large object, such as a video image file; whereas, the CLOB datatype is used to store a character large object.

This PACKAGE queries the DICTIONARY tables of the CURRENT DATABASE and CREATES text based file with their contents.

This package queries the dictionary tables of the current database and creates text based file with their contents.

The DBMS_LOCK package is used to manage the Oracle LOCK MANAGEMENT services. This package is used to request, convert, and release the LOCKS using the Oracle Lock Management services.

The DBMS_LOCK package is used to manage the Oracle Lock Management services. This package is used to request, convert, and release the locks using the Oracle Lock Management services.

YES, there is a built-in RANDOM NUMBER GENERATOR in the DBMS_RANDOM PACKAGE.

Yes, there is a built-in random number generator in the DBMS_RANDOM package.

Yes, it is POSSIBLE to ACCESS the SQL sessions USING the DBMS_SESSION PACKAGE.

Yes, it is possible to access the SQL sessions using the DBMS_SESSION package.

The DBMS_TRANSACTION package CONTROLS LOGICAL TRANSACTIONS and IMPROVES the performance of short and non-distributed transactions.

The DBMS_TRANSACTION package controls logical transactions and improves the performance of short and non-distributed transactions.

The DBMS_UTILITY PACKAGE is used to CHECK the status of a SCHEMA and ANALYZE the objects of the schema.

The DBMS_UTILITY package is used to check the status of a schema and analyze the objects of the schema.

The CLOSE_CONNECTION procedure closes the CONNECTION WHOSE details are provided as INPUT where as the CLOSE_ALL_CONINJECTIONS procedure closes all the opened CONNECTIONS.

The CLOSE_CONNECTION procedure closes the connection whose details are provided as input where as the CLOSE_ALL_CONINJECTIONS procedure closes all the opened connections.

An exception is raised using the UTL_TCP package in the following situations:

•  The buffer SIZE is too small for the input
•  A NETWORK error occurs
•  No more DATA is available to read from the connection
•  INVALID ARGUMENTS are passed to the function

An exception is raised using the UTL_TCP package in the following situations:

The UTL_TCP package is USED by PL/SQL applications to access external Transmission Control Protocol/Internet Protocol (TCP/IP) based servers using the TCP/IP protocol.

The connection is opened using the OPEN_CONNECTION FUNCTION, which opens a TCP/IP connection when the REMOTE host, local host, and both the port numbers are specified. It returns a connection of PL/SQL RECORD type.

The connection is closed using the CLOSE_CONNECTION function, which closes the connection of the port whose details are PROVIDED as input.

The UTL_TCP package is used by PL/SQL applications to access external Transmission Control Protocol/Internet Protocol (TCP/IP) based servers using the TCP/IP protocol.

The connection is opened using the OPEN_CONNECTION function, which opens a TCP/IP connection when the remote host, local host, and both the port numbers are specified. It returns a connection of PL/SQL RECORD type.

The connection is closed using the CLOSE_CONNECTION function, which closes the connection of the port whose details are provided as input.

If WRONG URL is specified in the UTL_HTTP package CALL, the REQUEST_FAILED EXCEPTION is RAISED.

If wrong URL is specified in the UTL_HTTP package call, the REQUEST_FAILED exception is raised.

The size of the data that can be accessed from the INTERNET is based on the functions USED. The REQUEST FUNCTION returns up to the FIRST 2000 BYTES of data retrieved from the website. The REQUEST_PIECES function returns a PL/SQL table of 2000 bytes from the accessed URL.

The size of the data that can be accessed from the Internet is based on the functions used. The REQUEST function returns up to the first 2000 bytes of data retrieved from the website. The REQUEST_PIECES function returns a PL/SQL table of 2000 bytes from the accessed URL.