SQL
SQL is an English like language consisting of commands to store, retrieve, maintain & regulate access to your database.
SQL*Plus
SQL*Plus is an application that recognizes & executes SQL commands & specialized SQL*Plus commands that can customize reports, provide help & edit facility & maintain system variables.
NVL
NVL : Null value function converts a null value to a non-null value for the purpose of evaluating an expression. Numeric Functions accept numeric I/P & return numeric values. They are MOD, SQRT, ROUND, TRUNC & POWER.
Date Functions
Date Functions are ADD_MONTHS, LAST_DAY, NEXT_DAY, MONTHS_BETWEEN & SYSDATE.
Character Functions
Character Functions are INITCAP, UPPER, LOWER, SUBSTR & LENGTH. Additional functions are GREATEST & LEAST. Group Functions returns results based upon groups of rows rather than one result per row, use group functions. They are AVG, COUNT, MAX, MIN & SUM.
TTITLE & BTITLE
TTITLE & BTITLE are commands to control report headings & footers.
COLUMN
COLUMN command define column headings & format data values.
BREAK
BREAK command clarify reports by suppressing repeated values, skipping lines & allowing for controlled break points.
COMPUTE
command control computations on subsets created by the BREAK command.
SET
SET command changes the system variables affecting the report environment.
SPOOL
SPOOL command creates a print file of the report.
JOIN
JOIN is the form of SELECT command that combines info from two or more tables.
Types of Joins are Simple (Equijoin & Non-Equijoin), Outer & Self join.
Equijoin returns rows from two or more tables joined together based upon a equality condition in the WHERE clause.
Non-Equijoin returns rows from two or more tables based upon a relationship other than the equality condition in the WHERE clause.
Outer Join combines two or more tables returning those rows from one table that have no direct match in the other table.
Self Join joins a table to itself as though it were two separate tables.
Union
Union is the product of two or more tables.
Intersect
Intersect is the product of two tables listing only the matching rows.
Minus
Minus is the product of two tables listing only the non-matching rows.
Correlated Subquery
Correlated Subquery is a subquery that is evaluated once for each row processed by the parent statement. Parent statement can be Select, Update or Delete. Use CRSQ to answer multipart questions whose answer depends on the value in each row processed by parent statement.
Multiple columns
Multiple columns can be returned from a Nested Subquery.
Sequences
Sequences are used for generating sequence numbers without any overhead of locking. Drawback is that after generating a sequence number if the transaction is rolled back, then that sequence number is lost.
Synonyms
Synonyms is the alias name for table, views, sequences & procedures and are created for reasons of Security and Convenience.
Two levels are Public - created by DBA & accessible to all the users. Private - Accessible to creator only. Advantages are referencing without specifying the owner and Flexibility to customize a more meaningful naming convention.
Indexes
Indexes are optional structures associated with tables used to speed query execution and/or guarantee uniqueness. Create an index if there are frequent retrieval of fewer than 10-15% of the rows in a large table and columns are referenced frequently in the WHERE clause. Implied tradeoff is query speed vs. update speed. Oracle automatically update indexes. Concatenated index max. is 16 columns.
Data types
Max. columns in a table is 255. Max. Char size is 255, Long is 64K & Number is 38 digits.
Cannot Query on a long column.
Char, Varchar2 Max. size is 2000 & default is 1 byte.
Number(p,s) p is precision range 1 to 38, s is scale -84 to 127.
Long Character data of variable length upto 2GB.
Date Range from Jan 4712 BC to Dec 4712 AD.
Raw Stores Binary data (Graphics Image & Digitized Sound). Max. is 255 bytes.
Mslabel Binary format of an OS label. Used primarily with Trusted Oracle.
Order of SQL statement execution
Where clause, Group By clause, Having clause, Order By clause & Select.
Transaction
Transaction is defined as all changes made to the database between successive commits.
Commit
Commit is an event that attempts to make data in the database identical to the data in the form. It involves writing or posting data to the database and committing data to the database. Forms check the validity of the data in fields and records during a commit. Validity check are uniqueness, consistency and db restrictions.
Posting
Posting is an event that writes Inserts, Updates & Deletes in the forms to the database but not committing these transactions to the database.
Rollback
Rollback causes work in the current transaction to be undone.
Savepoint
Savepoint is a point within a particular transaction to which you may rollback without rolling back the entire transaction.
Set Transaction
Set Transaction is to establish properties for the current transaction.
Locking
Locking are mechanisms intended to prevent destructive interaction between users accessing data. Locks are used to achieve.
Consistency
Consistency : Assures users that the data they are changing or viewing is not changed until the are thro’ with it.
Integrity
Assures database data and structures reflects all changes made to them in the correct sequence. Locks ensure data integrity and maximum concurrent access to data. Commit statement releases all locks. Types of locks are given below.
Data Locks protects data i.e. Table or Row lock.
Dictionary Locks protects the structure of database object i.e. ensures table’s structure does not change for the duration of the transaction.
Internal Locks & Latches protects the internal database structures. They are automatic.
Exclusive Lock allows queries on locked table but no other activity is allowed.
Share Lock allows concurrent queries but prohibits updates to the locked tables.
Row Share allows concurrent access to the locked table but prohibits for a exclusive table lock.
Row Exclusive same as Row Share but prohibits locking in shared mode.
Shared Row Exclusive locks the whole table and allows users to look at rows in the table but prohibit others from locking the table in share or updating them.
Share Update are synonymous with Row Share.
Deadlock
Deadlock is a unique situation in a multi user system that causes two or more users to wait indefinitely for a locked resource. First user needs a resource locked by the second user and the second user needs a resource locked by the first user. To avoid dead locks, avoid using exclusive table lock and if using, use it in the same sequence and use Commit frequently to release locks.
Mutating Table
Mutating Table is a table that is currently being modified by an Insert, Update or Delete statement. Constraining Table is a table that a triggering statement might need to read either directly for a SQL statement or indirectly for a declarative Referential Integrity constraints. Pseudo Columns behaves like a column in a table but are not actually stored in the table. E.g. Currval, Nextval, Rowid, Rownum, Level etc.
SQL*Loader
SQL*Loader is a product for moving data in external files into tables in an Oracle database. To load data from external files into an Oracle database, two types of input must be provided to SQL*Loader : the data itself and the control file. The control file describes the data to be loaded. It describes the Names and format of the data files, Specifications for loading data and the Data to be loaded (optional). Invoking the loader sqlload username/password controlfilename.
Oracle on Linux/Unix questions
Database, Unix/Linux interview questions
How many memory layers are in the shared pool?
How do you find out from the RMAN catalog if a particular archive log has been backed-up?
How can you tell how much space is left on a given file system and how much space each of the file system’s subdirectories take-up?
Define the SGA and how you would configure SGA for a mid-sized OLTP environment? What is involved in tuning the SGA?
What is the cache hit ratio, what impact does it have on performance of an Oracle database and what is involved in tuning it?
Other than making use of the statspack utility, what would you check when you are monitoring or running a health check on an Oracle 8i or 9i database?
How do you tell what your machine name is and what is its IP address?
How would you go about verifying the network name that the local_listener is currently using?
You have 4 instances running on the same UNIX box. How can you determine which shared memory and semaphores are associated with which instance?
What view(s) do you use to associate a user’s SQLPLUS session with his o/s process?
What is the recommended interval at which to run statspack snapshots, and why?
What spfile/init.ora file parameter exists to force the CBO to make the execution path of a given statement use an index, even if the index scan may appear to be calculated as more costly?
Assuming today is Monday, how would you use the DBMS_JOB package to schedule the execution of a given procedure owned by SCOTT to start Wednesday at 9AM and to run subsequently every other day at 2AM.
How would you edit your CRONTAB to schedule the running of /test/test.sh to run every other day at 2PM?
What do the 9i dbms_standard.sql_txt() and dbms_standard.sql_text() procedures do?
In which dictionary table or view would you look to determine at which time a snapshot or MVIEW last successfully refreshed?
How would you best determine why your MVIEW couldn’t FAST REFRESH?
How does propagation differ between Advanced Replication and Snapshot Replication (read-only)?
Which dictionary view(s) would you first look at to understand or get a high-level idea of a given Advanced Replication environment?
How would you begin to troubleshoot an ORA-3113 error?
Which dictionary tables and/or views would you look at to diagnose a locking issue?
An automatic job running via DBMS_JOB has failed. Knowing only that “it’s failed”, how do you approach troubleshooting this issue?
How would you extract DDL of a table without using a GUI tool?
You’re getting high “busy buffer waits” - how can you find what’s causing it?
What query tells you how much space a tablespace named “test” is taking up, and how much space is remaining?
Database is hung. Old and new user connections alike hang on impact. What do you do? Your SYS SQLPLUS session IS able to connect.
Database crashes. Corruption is found scattered among the file system neither of your doing nor of Oracle’s. What database recovery options are available? Database is in archive log mode.
Illustrate how to determine the amount of physical CPUs a Unix Box possesses (LINUX and/or Solaris).
How do you increase the OS limitation for open files (LINUX and/or Solaris)?
Provide an example of a shell script which logs into SQLPLUS as SYS, determines the current date, changes the date format to include minutes & seconds, issues a drop table command, displays the date again, and finally exits.
Explain how you would restore a database using RMAN to Point in Time?
How does Oracle guarantee data integrity of data changes?
Which environment variables are absolutely critical in order to run the OUI?
What SQL query from v$session can you run to show how many sessions are logged in as a particular user account?
Why does Oracle not permit the use of PCTUSED with indexes?
What would you use to improve performance on an insert statement that places millions of rows into that table?
What would you do with an “in-doubt” distributed transaction?
What are the commands you’d issue to show the explain plan for “select * from dual”?
In what script is “snap$” created? In what script is the “scott/tiger” schema created?
If you’re unsure in which script a sys or system-owned object is created, but you know it’s in a script from a specific directory, what UNIX command from that directory structure can you run to find your answer?
How would you configure your networking files to connect to a database by the name of DSS which resides in domain icallinc.com?
You create a private database link and upon connection, fails with: ORA-2085: connects to . What is the problem? How would you go about resolving this error?
I have my backup RMAN script called “backup_rman.sh”. I am on the target database. My catalog username/password is rman/rman. My catalog db is called rman. How would you run this shell script from the O/S such that it would run as a background process?
Explain the concept of the DUAL table.
What are the ways tablespaces can be managed and how do they differ?
From the database level, how can you tell under which time zone a database is operating?
What’s the benefit of “dbms_stats” over “analyze”?
Typically, where is the conventional directory structure chosen for Oracle binaries to reside?
You have found corruption in a tablespace that contains static tables that are part of a database that is in NOARCHIVE log mode. How would you restore the tablespace without losing new data in the other tablespaces?
How do you recover a datafile that has not been physically been backed up since its creation and has been deleted. Provide syntax example
Tuesday, July 31, 2007
ORACLE UNIX Q's
1. List components of an Oracle instance?
2. Which background process and associated database component guarantees that committed data is saved even when the changes have not been recorded in the data files?
3. What is the maximum number of database writer processes allowed in an Oracle instance?
4. Which background process is not started by default when you start up the Oracle instance?
5. Describe a parallel server configuration.
6. Choose the right hierarchy, from largest to smallest, from this list of logical database structures.
7. Which component of the SGA contains the parsed SQL code?
8. Name the stages of processing a DML statement. What stages are part of processing a query?
9. Which background process is responsible for writing the dirty buffers to the database files?
10. Which component in the SGA has the dictionary cache?
11. When a server process is terminated abnormally, which background process is responsible for releasing the locks held by the user?
12. What is a dirty buffer?
13. If you are updating one row in a table using the ROWID in the WHERE clause (assume that the row is not already in the buffer cache), what will be the minimum amount of information read to the database buffer cache?
14. What happens next when a server process is not able to find enough free buffers to copy the blocks from disk?
15. Which memory structures are shared? Name two.
16. When a SELECT statement is issued, which stage checks the user’s privileges?
17. Which memory structure records all database changes made to the instance?
18. What is the minimum number of redo log files required in a database?
19. When are the system change numbers assigned?
20. Name the parts of the database buffer pool.
21. List all the valid database start-up option?
22. Which two values from the V$SESSION view are used to terminate a user session?
23. To use operating system authentication to connect the database as an administrator, what should the value of the parameter REMOTE_LOGIN_PASSWORDFILE be set to?
24. What information is available in the alert log files?
25. Which parameter value is use to set the directory path where the alert log file is written?
26. Which SHUTDOWN option requires instance recovery when the database is started the next time?
27. Which SHUTDOWN option will wait for the users to complete their uncommitted transactions?
28. How do you make a database read-only?
29. Which role is created by default to administer databases?
30. Which parameter in the ORAPWD utility is optional?
31. Which priviledge do you need to connect to the database, if the database is started up by using STARTUP RESTRICT?
32. At which stage of the database start-up is the control file opened?
33. 13. User SCOTT has opened a SQL * Plus session and left for lunch. When you queired the V$SESSION view, the STATUS was INACTVE. You terminated SCOTT’s session in V$SESSION?
34. Which command will “bounce” the database-that is, shut down the database and start up the database in a single command?
35. 15. When performing the command SHUTDOWN TRANASACTIONAL, Oracle performs the following tasks in what order?
36. How many panes are there in the Enterprise Manager console?
37. Using SQL*Plus, list two options which show the value of the parameter DB_BLOCK_SIZE?
38. When you issue the command ALTER SYSTEM ENABLE RESTRICTED SESSION, what happens to the users who are connected to the database?
39. Which view has information about users who are ghranted SYSDBA or SYSOPER privilege?
40. Which DB administration tools are included in the DBA Studio Pack?
41. How many control files are required to create a database?
42. Which environment variable or registry entry variable is used to represent the instance name?
43. . What is the recommended configuration for control files?
44. You have specified the LOGFILE clause in the CREATE DATABASE command as follows. What happens if the size of the log file redo0101.log, which already exists, is 10MB?
45. Which command should be issued before you can execute the CREATE DATABASE command?
46. Which initialization parameter cannot be changed after creating the database?
47. What does OFA stand for?
48. When creating a database, where does Oracle find information about the control files that need to be created?
49. Which script creates the data dictionary views?
50. Which prefix for the data dictionary views indicate that the contents of the view belong to the current user?
51. Which data dictionary view shows information about the status of a procedure?
52. How do you correct a procedure that has become invalid when one of the tables it is referring to was altered to drop a constraint?
53. Which event trigger from the following cannot be created at the database level?
54. How many data files can be specified in the DATAFILE clause when creating a database?
55. Who owns the data dictionary?
56. What is the default password for the SYS user?
57. Which data dictionary view provides information on the version of the database and installed components?
58. What is the prefix for dynamic performance views?
59. Which clauses in the CREATE DATABASE command specify limits for the database?
60. Which clauses in the CREATE DATABASE command specify limits for the database?
61. Which optional component in the database creation process sets up functions and procedures to store, access, and analyze data needed for Geographical Information Systems (GIS)?
62. What is the best method to rename a control file?
63. What piece of information is not available in the control file?
64. When you create a control file, the database has to be:
65. Which data dictionary view provides the names of the control files?
66. The initialization parameter file has LOG_CHECKPOINT_INTERVAL = 60; What does this mean?
67. Which data dictionary view shows that the database is in ARCHIVELOG mode?
68. What is the biggest advantage of having the control files on different disks?
69. Which file is used to record all changes made to the database and is used only when performing an instance recovery?
70. What will happen if ARCn could not write to a mandatory archive destination?
71. How many ARCn processes can be associated with an instance?
72. What are the valid status codes in the V$LOGFILE view?
73. If you have two redo log groups with four members each, how many disks does Oracle recommend to keep the redo log files?
74. What happens if you issue the following command? ALTER DATABASE ADD LOGFILE (’/logs/file1′ REUSE, ‘/logs/file2′ REUSE)
75. Which two parameters cannot be used together to specify the archive destination?
76. What packages are associated with the LogMiner utility?
77. Querying which view will show whether automatic archiving is enabled?
78. If you need to have your archive log files named with the log sequence numbers as arch_0000001, arch_0000002, and so on (zero filled, fixed width), what should be the value of the LOG_ARCHIVE_FORMAT parameter?
79. List the steps needed to rename a redo log file.
80. Which parameter is used to limit the number of dirty buffers in the buffer cache, thereby limiting the time required for instance recovery?
81. Create a statement that will add a member /logs/redo22.log to log file group 2.
82. When does the SMON process automatically coalesce the tablespaces?
83. Which operation is permitted on a read – only tablespace?
84. How would you drop a tablespace if the tablespace were not empty?
85. Which command is used to enable the auto-extensible feature for a file, if the file is already part of a tablespace?
86. 13. The database block size is 4KB. You created a tablespace using the following command. CREATE TABLESPACE USER_DATA DATAFILE ‘C:/DATA01.DBF’; If you create an object in the database without specifying any storage parameters, what will be the size of the third extent that belongs to the object?
87. 14. Which statement is false? A. Dictionary-managed temporary tablespace can be made permanent. B. The size of the locally managed temporary tablespace file cannot be changed. C. Once created, the extent management of a tablespace cannot be altered. D. A locally managed permanent tablespace cannot be made temporary.
88. Which statement is true regarding the SYSTEM tablespace? A. Can be me made read only. B. Can be offline. T C. Data files can be renamed. D. Data files cannot be resized
89. What are the recommended INITIAL and NEXT values for a temporary tablespace, to reduce fragmentation?
90. How would you determine how much sort space is used by a user session?
91. 19. If you issue ALTER TABLESPACE USERS OFFLINE IMMEDIATE, which of the following statements is true? A. All data files belonging to the tablespace must be online. B. Does not ensure that the data files are available. C. Need not doe media recovery when bringing the tablespace online. D. Need to do media recovery when bringing the tablespace online.
92. Place the following logical storage structures in order-from the smallest logical storage unit to the largest. A. Segment B. Block C. Tablespace D. Extent
93. When a table is updated, where is the before image information (which can be used for undoing the changes) stored?
94. Which parameter specifies the number of transaction slots in a data block?
95. What happens if you create a rollback segment in the same tablespace where application data is stored? Choose the best answer. A. The tablespace will be fragmented. B. Performance improves, because when changes are made, undo information can be written to the same tablespace. C. There should be a minimum of two data files associated with the tablespaces when rollback segments are created. D. None of the above is true.
96. 5. Which storage parameter is applicable only to rollback segments? A. PCTINCREASE B. MAXEXTENTS C. TRANASACTIONS D. None of the above.
97. 6. Choose the statement used to manually de-allocate the extents used by a rollback segment. A. ALTER ROLLBACK SEGMENT R01 DEALLOCATE; B. ALTER ROLLBACK SEGMENT RO1 DROP EXTENTS; C. ALTER ROLLBACK SEGMENT RO1 SHRINK; D. ALTER ROLLBACK SEGMENT RO1 SIZE 10K.
98. Which data dictionary view would you query to see the free extents in a tablespace?
99. What is the minimum number of extents a rollback segment can have?
100. Which portion of the data block stores information about the table having rows in this block?
2. Which background process and associated database component guarantees that committed data is saved even when the changes have not been recorded in the data files?
3. What is the maximum number of database writer processes allowed in an Oracle instance?
4. Which background process is not started by default when you start up the Oracle instance?
5. Describe a parallel server configuration.
6. Choose the right hierarchy, from largest to smallest, from this list of logical database structures.
7. Which component of the SGA contains the parsed SQL code?
8. Name the stages of processing a DML statement. What stages are part of processing a query?
9. Which background process is responsible for writing the dirty buffers to the database files?
10. Which component in the SGA has the dictionary cache?
11. When a server process is terminated abnormally, which background process is responsible for releasing the locks held by the user?
12. What is a dirty buffer?
13. If you are updating one row in a table using the ROWID in the WHERE clause (assume that the row is not already in the buffer cache), what will be the minimum amount of information read to the database buffer cache?
14. What happens next when a server process is not able to find enough free buffers to copy the blocks from disk?
15. Which memory structures are shared? Name two.
16. When a SELECT statement is issued, which stage checks the user’s privileges?
17. Which memory structure records all database changes made to the instance?
18. What is the minimum number of redo log files required in a database?
19. When are the system change numbers assigned?
20. Name the parts of the database buffer pool.
21. List all the valid database start-up option?
22. Which two values from the V$SESSION view are used to terminate a user session?
23. To use operating system authentication to connect the database as an administrator, what should the value of the parameter REMOTE_LOGIN_PASSWORDFILE be set to?
24. What information is available in the alert log files?
25. Which parameter value is use to set the directory path where the alert log file is written?
26. Which SHUTDOWN option requires instance recovery when the database is started the next time?
27. Which SHUTDOWN option will wait for the users to complete their uncommitted transactions?
28. How do you make a database read-only?
29. Which role is created by default to administer databases?
30. Which parameter in the ORAPWD utility is optional?
31. Which priviledge do you need to connect to the database, if the database is started up by using STARTUP RESTRICT?
32. At which stage of the database start-up is the control file opened?
33. 13. User SCOTT has opened a SQL * Plus session and left for lunch. When you queired the V$SESSION view, the STATUS was INACTVE. You terminated SCOTT’s session in V$SESSION?
34. Which command will “bounce” the database-that is, shut down the database and start up the database in a single command?
35. 15. When performing the command SHUTDOWN TRANASACTIONAL, Oracle performs the following tasks in what order?
36. How many panes are there in the Enterprise Manager console?
37. Using SQL*Plus, list two options which show the value of the parameter DB_BLOCK_SIZE?
38. When you issue the command ALTER SYSTEM ENABLE RESTRICTED SESSION, what happens to the users who are connected to the database?
39. Which view has information about users who are ghranted SYSDBA or SYSOPER privilege?
40. Which DB administration tools are included in the DBA Studio Pack?
41. How many control files are required to create a database?
42. Which environment variable or registry entry variable is used to represent the instance name?
43. . What is the recommended configuration for control files?
44. You have specified the LOGFILE clause in the CREATE DATABASE command as follows. What happens if the size of the log file redo0101.log, which already exists, is 10MB?
45. Which command should be issued before you can execute the CREATE DATABASE command?
46. Which initialization parameter cannot be changed after creating the database?
47. What does OFA stand for?
48. When creating a database, where does Oracle find information about the control files that need to be created?
49. Which script creates the data dictionary views?
50. Which prefix for the data dictionary views indicate that the contents of the view belong to the current user?
51. Which data dictionary view shows information about the status of a procedure?
52. How do you correct a procedure that has become invalid when one of the tables it is referring to was altered to drop a constraint?
53. Which event trigger from the following cannot be created at the database level?
54. How many data files can be specified in the DATAFILE clause when creating a database?
55. Who owns the data dictionary?
56. What is the default password for the SYS user?
57. Which data dictionary view provides information on the version of the database and installed components?
58. What is the prefix for dynamic performance views?
59. Which clauses in the CREATE DATABASE command specify limits for the database?
60. Which clauses in the CREATE DATABASE command specify limits for the database?
61. Which optional component in the database creation process sets up functions and procedures to store, access, and analyze data needed for Geographical Information Systems (GIS)?
62. What is the best method to rename a control file?
63. What piece of information is not available in the control file?
64. When you create a control file, the database has to be:
65. Which data dictionary view provides the names of the control files?
66. The initialization parameter file has LOG_CHECKPOINT_INTERVAL = 60; What does this mean?
67. Which data dictionary view shows that the database is in ARCHIVELOG mode?
68. What is the biggest advantage of having the control files on different disks?
69. Which file is used to record all changes made to the database and is used only when performing an instance recovery?
70. What will happen if ARCn could not write to a mandatory archive destination?
71. How many ARCn processes can be associated with an instance?
72. What are the valid status codes in the V$LOGFILE view?
73. If you have two redo log groups with four members each, how many disks does Oracle recommend to keep the redo log files?
74. What happens if you issue the following command? ALTER DATABASE ADD LOGFILE (’/logs/file1′ REUSE, ‘/logs/file2′ REUSE)
75. Which two parameters cannot be used together to specify the archive destination?
76. What packages are associated with the LogMiner utility?
77. Querying which view will show whether automatic archiving is enabled?
78. If you need to have your archive log files named with the log sequence numbers as arch_0000001, arch_0000002, and so on (zero filled, fixed width), what should be the value of the LOG_ARCHIVE_FORMAT parameter?
79. List the steps needed to rename a redo log file.
80. Which parameter is used to limit the number of dirty buffers in the buffer cache, thereby limiting the time required for instance recovery?
81. Create a statement that will add a member /logs/redo22.log to log file group 2.
82. When does the SMON process automatically coalesce the tablespaces?
83. Which operation is permitted on a read – only tablespace?
84. How would you drop a tablespace if the tablespace were not empty?
85. Which command is used to enable the auto-extensible feature for a file, if the file is already part of a tablespace?
86. 13. The database block size is 4KB. You created a tablespace using the following command. CREATE TABLESPACE USER_DATA DATAFILE ‘C:/DATA01.DBF’; If you create an object in the database without specifying any storage parameters, what will be the size of the third extent that belongs to the object?
87. 14. Which statement is false? A. Dictionary-managed temporary tablespace can be made permanent. B. The size of the locally managed temporary tablespace file cannot be changed. C. Once created, the extent management of a tablespace cannot be altered. D. A locally managed permanent tablespace cannot be made temporary.
88. Which statement is true regarding the SYSTEM tablespace? A. Can be me made read only. B. Can be offline. T C. Data files can be renamed. D. Data files cannot be resized
89. What are the recommended INITIAL and NEXT values for a temporary tablespace, to reduce fragmentation?
90. How would you determine how much sort space is used by a user session?
91. 19. If you issue ALTER TABLESPACE USERS OFFLINE IMMEDIATE, which of the following statements is true? A. All data files belonging to the tablespace must be online. B. Does not ensure that the data files are available. C. Need not doe media recovery when bringing the tablespace online. D. Need to do media recovery when bringing the tablespace online.
92. Place the following logical storage structures in order-from the smallest logical storage unit to the largest. A. Segment B. Block C. Tablespace D. Extent
93. When a table is updated, where is the before image information (which can be used for undoing the changes) stored?
94. Which parameter specifies the number of transaction slots in a data block?
95. What happens if you create a rollback segment in the same tablespace where application data is stored? Choose the best answer. A. The tablespace will be fragmented. B. Performance improves, because when changes are made, undo information can be written to the same tablespace. C. There should be a minimum of two data files associated with the tablespaces when rollback segments are created. D. None of the above is true.
96. 5. Which storage parameter is applicable only to rollback segments? A. PCTINCREASE B. MAXEXTENTS C. TRANASACTIONS D. None of the above.
97. 6. Choose the statement used to manually de-allocate the extents used by a rollback segment. A. ALTER ROLLBACK SEGMENT R01 DEALLOCATE; B. ALTER ROLLBACK SEGMENT RO1 DROP EXTENTS; C. ALTER ROLLBACK SEGMENT RO1 SHRINK; D. ALTER ROLLBACK SEGMENT RO1 SIZE 10K.
98. Which data dictionary view would you query to see the free extents in a tablespace?
99. What is the minimum number of extents a rollback segment can have?
100. Which portion of the data block stores information about the table having rows in this block?
PL/SQL interview qiuestions
1. Which of the following statements is true about implicit cursors?
1. Implicit cursors are used for SQL statements that are not named.
2. Developers should use implicit cursors with great care.
3. Implicit cursors are used in cursor for loops to handle data processing.
4. Implicit cursors are no longer a feature in Oracle.
2. Which of the following is not a feature of a cursor FOR loop?
1. Record type declaration.
2. Opening and parsing of SQL statements.
3. Fetches records from cursor.
4. Requires exit condition to be defined.
3. A developer would like to use referential datatype declaration on a variable. The variable name is EMPLOYEE_LASTNAME, and the corresponding table and column is EMPLOYEE, and LNAME, respectively. How would the developer define this variable using referential datatypes?
1. Use employee.lname%type.
2. Use employee.lname%rowtype.
3. Look up datatype for EMPLOYEE column on LASTNAME table and use that.
4. Declare it to be type LONG.
4. Which three of the following are implicit cursor attributes?
1. %found
2. %too_many_rows
3. %notfound
4. %rowcount
5. %rowtype
5. If left out, which of the following would cause an infinite loop to occur in a simple loop?
1. LOOP
2. END LOOP
3. IF-THEN
4. EXIT
6. Which line in the following statement will produce an error?
1. cursor action_cursor is
2. select name, rate, action
3. into action_record
4. from action_table;
5. There are no errors in this statement.
7. The command used to open a CURSOR FOR loop is
1. open
2. fetch
3. parse
4. None, cursor for loops handle cursor opening implicitly.
8. What happens when rows are found using a FETCH statement
1. It causes the cursor to close
2. It causes the cursor to open
3. It loads the current row values into variables
4. It creates the variables to hold the current row values
9. Read the following code:
10. CREATE OR REPLACE PROCEDURE find_cpt
11. (v_movie_id {Argument Mode} NUMBER, v_cost_per_ticket {argument mode} NUMBER)
12. IS
13. BEGIN
14. IF v_cost_per_ticket > 8.5 THEN
15. SELECT cost_per_ticket
16. INTO v_cost_per_ticket
17. FROM gross_receipt
18. WHERE movie_id = v_movie_id;
19. END IF;
20. END;
Which mode should be used for V_COST_PER_TICKET?
1. IN
2. OUT
3. RETURN
4. IN OUT
21. Read the following code:
22. CREATE OR REPLACE TRIGGER update_show_gross
23. {trigger information}
24. BEGIN
25. {additional code}
26. END;
The trigger code should only execute when the column, COST_PER_TICKET, is greater than $3. Which trigger information will you add?
1. WHEN (new.cost_per_ticket > 3.75)
2. WHEN (:new.cost_per_ticket > 3.75
3. WHERE (new.cost_per_ticket > 3.75)
4.
5. WHERE (:new.cost_per_ticket > 3.75)
27. What is the maximum number of handlers processed before the PL/SQL block is exited when an exception occurs?
1. Only one
2. All that apply
3. All referenced
4. None
28. For which trigger timing can you reference the NEW and OLD qualifiers?
1. Statement and Row
2. Statement only
3. Row only
4. Oracle Forms trigger
29. Read the following code:
30. CREATE OR REPLACE FUNCTION get_budget(v_studio_id IN NUMBER)
31. RETURN number IS
32.
33. v_yearly_budget NUMBER;
34.
35. BEGIN
36. SELECT yearly_budget
37. INTO v_yearly_budget
38. FROM studio
39. WHERE id = v_studio_id;
40.
41. RETURN v_yearly_budget;
42. END;
Which set of statements will successfully invoke this function within SQL*Plus?
1. VARIABLE g_yearly_budget NUMBER
EXECUTE g_yearly_budget := GET_BUDGET(11);
2. VARIABLE g_yearly_budget NUMBER
EXECUTE :g_yearly_budget := GET_BUDGET(11);
3. VARIABLE :g_yearly_budget NUMBER
EXECUTE :g_yearly_budget := GET_BUDGET(11);
4. VARIABLE g_yearly_budget NUMBER
:g_yearly_budget := GET_BUDGET(11);
43. CREATE OR REPLACE PROCEDURE update_theater
44. (v_name IN VARCHAR v_theater_id IN NUMBER) IS
45. BEGIN
46. UPDATE theater
47. SET name = v_name
48. WHERE id = v_theater_id;
49. END update_theater;
50. When invoking this procedure, you encounter the error:
ORA-000: Unique constraint(SCOTT.THEATER_NAME_UK) violated.
How should you modify the function to handle this error?
1. An user defined exception must be declared and associated with the error code and handled in the EXCEPTION section.
2. Handle the error in EXCEPTION section by referencing the error code directly.
3. Handle the error in the EXCEPTION section by referencing the UNIQUE_ERROR predefined exception.
4. Check for success by checking the value of SQL%FOUND immediately after the UPDATE statement.
51. Read the following code:
52. CREATE OR REPLACE PROCEDURE calculate_budget IS
53. v_budget studio.yearly_budget%TYPE;
54. BEGIN
55. v_budget := get_budget(11);
56. IF v_budget < 30000
57. THEN
58. set_budget(11,30000000);
59. END IF;
60. END;
You are about to add an argument to CALCULATE_BUDGET. What effect will this have?
1. The GET_BUDGET function will be marked invalid and must be recompiled before the next execution.
2. The SET_BUDGET function will be marked invalid and must be recompiled before the next execution.
3. Only the CALCULATE_BUDGET procedure needs to be recompiled.
4. All three procedures are marked invalid and must be recompiled.
61. Which procedure can be used to create a customized error message?
1. RAISE_ERROR
2. SQLERRM
3. RAISE_APPLICATION_ERROR
4. RAISE_SERVER_ERROR
62. The CHECK_THEATER trigger of the THEATER table has been disabled. Which command can you issue to enable this trigger?
1. ALTER TRIGGER check_theater ENABLE;
2. ENABLE TRIGGER check_theater;
3. ALTER TABLE check_theater ENABLE check_theater;
4. ENABLE check_theater;
63. Examine this database trigger
64. CREATE OR REPLACE TRIGGER prevent_gross_modification
65. {additional trigger information}
66. BEGIN
67. IF TO_CHAR(sysdate, DY) = MON
68. THEN
69. RAISE_APPLICATION_ERROR(-20000,Gross receipts cannot be deleted on Monday);
70. END IF;
71. END;
This trigger must fire before each DELETE of the GROSS_RECEIPT table. It should fire only once for the entire DELETE statement. What additional information must you add?
1. BEFORE DELETE ON gross_receipt
2. AFTER DELETE ON gross_receipt
3. BEFORE (gross_receipt DELETE)
4. FOR EACH ROW DELETED FROM gross_receipt
72. Examine this function:
73. CREATE OR REPLACE FUNCTION set_budget
74. (v_studio_id IN NUMBER, v_new_budget IN NUMBER) IS
75. BEGIN
76. UPDATE studio
77. SET yearly_budget = v_new_budget
78. WHERE id = v_studio_id;
79.
80. IF SQL%FOUND THEN
81. RETURN TRUEl;
82. ELSE
83. RETURN FALSE;
84. END IF;
85.
86. COMMIT;
87. END;
Which code must be added to successfully compile this function?
1. Add RETURN right before the IS keyword.
2. Add RETURN number right before the IS keyword.
3. Add RETURN boolean right after the IS keyword.
4. Add RETURN boolean right before the IS keyword.
88. Under which circumstance must you recompile the package body after recompiling the package specification?
1. Altering the argument list of one of the package constructs
2. Any change made to one of the package constructs
3. Any SQL statement change made to one of the package constructs
4. Removing a local variable from the DECLARE section of one of the package constructs
89. Procedure and Functions are explicitly executed. This is different from a database trigger. When is a database trigger executed?
1. When the transaction is committed
2. During the data manipulation statement
3. When an Oracle supplied package references the trigger
4. During a data manipulation statement and when the transaction is committed
90. Which Oracle supplied package can you use to output values and messages from database triggers, stored procedures and functions within SQL*Plus?
1. DBMS_DISPLAY
2. DBMS_OUTPUT
3. DBMS_LIST
4. DBMS_DESCRIBE
91. What occurs if a procedure or function terminates with failure without being handled?
1. Any DML statements issued by the construct are still pending and can be committed or rolled back.
2. Any DML statements issued by the construct are committed
3. Unless a GOTO statement is used to continue processing within the BEGIN section, the construct terminates.
4. The construct rolls back any DML statements issued and returns the unhandled exception to the calling environment.
92. Examine this code
93. BEGIN
94. theater_pck.v_total_seats_sold_overall := theater_pck.get_total_for_year;
95. END;
For this code to be successful, what must be true?
1. Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function must exist only in the body of the THEATER_PCK package.
2. Only the GET_TOTAL_FOR_YEAR variable must exist in the specification of the THEATER_PCK package.
3. Only the V_TOTAL_SEATS_SOLD_OVERALL variable must exist in the specification of the THEATER_PCK package.
4. Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function must exist in the specification of the THEATER_PCK package.
96. A stored function must return a value based on conditions that are determined at runtime. Therefore, the SELECT statement cannot be hard-coded and must be created dynamically when the function is executed. Which Oracle supplied package will enable this feature?
1. DBMS_DDL
2. DBMS_DML
3. DBMS_SYN
4. DBMS_SQL
1. Implicit cursors are used for SQL statements that are not named.
2. Developers should use implicit cursors with great care.
3. Implicit cursors are used in cursor for loops to handle data processing.
4. Implicit cursors are no longer a feature in Oracle.
2. Which of the following is not a feature of a cursor FOR loop?
1. Record type declaration.
2. Opening and parsing of SQL statements.
3. Fetches records from cursor.
4. Requires exit condition to be defined.
3. A developer would like to use referential datatype declaration on a variable. The variable name is EMPLOYEE_LASTNAME, and the corresponding table and column is EMPLOYEE, and LNAME, respectively. How would the developer define this variable using referential datatypes?
1. Use employee.lname%type.
2. Use employee.lname%rowtype.
3. Look up datatype for EMPLOYEE column on LASTNAME table and use that.
4. Declare it to be type LONG.
4. Which three of the following are implicit cursor attributes?
1. %found
2. %too_many_rows
3. %notfound
4. %rowcount
5. %rowtype
5. If left out, which of the following would cause an infinite loop to occur in a simple loop?
1. LOOP
2. END LOOP
3. IF-THEN
4. EXIT
6. Which line in the following statement will produce an error?
1. cursor action_cursor is
2. select name, rate, action
3. into action_record
4. from action_table;
5. There are no errors in this statement.
7. The command used to open a CURSOR FOR loop is
1. open
2. fetch
3. parse
4. None, cursor for loops handle cursor opening implicitly.
8. What happens when rows are found using a FETCH statement
1. It causes the cursor to close
2. It causes the cursor to open
3. It loads the current row values into variables
4. It creates the variables to hold the current row values
9. Read the following code:
10. CREATE OR REPLACE PROCEDURE find_cpt
11. (v_movie_id {Argument Mode} NUMBER, v_cost_per_ticket {argument mode} NUMBER)
12. IS
13. BEGIN
14. IF v_cost_per_ticket > 8.5 THEN
15. SELECT cost_per_ticket
16. INTO v_cost_per_ticket
17. FROM gross_receipt
18. WHERE movie_id = v_movie_id;
19. END IF;
20. END;
Which mode should be used for V_COST_PER_TICKET?
1. IN
2. OUT
3. RETURN
4. IN OUT
21. Read the following code:
22. CREATE OR REPLACE TRIGGER update_show_gross
23. {trigger information}
24. BEGIN
25. {additional code}
26. END;
The trigger code should only execute when the column, COST_PER_TICKET, is greater than $3. Which trigger information will you add?
1. WHEN (new.cost_per_ticket > 3.75)
2. WHEN (:new.cost_per_ticket > 3.75
3. WHERE (new.cost_per_ticket > 3.75)
4.
5. WHERE (:new.cost_per_ticket > 3.75)
27. What is the maximum number of handlers processed before the PL/SQL block is exited when an exception occurs?
1. Only one
2. All that apply
3. All referenced
4. None
28. For which trigger timing can you reference the NEW and OLD qualifiers?
1. Statement and Row
2. Statement only
3. Row only
4. Oracle Forms trigger
29. Read the following code:
30. CREATE OR REPLACE FUNCTION get_budget(v_studio_id IN NUMBER)
31. RETURN number IS
32.
33. v_yearly_budget NUMBER;
34.
35. BEGIN
36. SELECT yearly_budget
37. INTO v_yearly_budget
38. FROM studio
39. WHERE id = v_studio_id;
40.
41. RETURN v_yearly_budget;
42. END;
Which set of statements will successfully invoke this function within SQL*Plus?
1. VARIABLE g_yearly_budget NUMBER
EXECUTE g_yearly_budget := GET_BUDGET(11);
2. VARIABLE g_yearly_budget NUMBER
EXECUTE :g_yearly_budget := GET_BUDGET(11);
3. VARIABLE :g_yearly_budget NUMBER
EXECUTE :g_yearly_budget := GET_BUDGET(11);
4. VARIABLE g_yearly_budget NUMBER
:g_yearly_budget := GET_BUDGET(11);
43. CREATE OR REPLACE PROCEDURE update_theater
44. (v_name IN VARCHAR v_theater_id IN NUMBER) IS
45. BEGIN
46. UPDATE theater
47. SET name = v_name
48. WHERE id = v_theater_id;
49. END update_theater;
50. When invoking this procedure, you encounter the error:
ORA-000: Unique constraint(SCOTT.THEATER_NAME_UK) violated.
How should you modify the function to handle this error?
1. An user defined exception must be declared and associated with the error code and handled in the EXCEPTION section.
2. Handle the error in EXCEPTION section by referencing the error code directly.
3. Handle the error in the EXCEPTION section by referencing the UNIQUE_ERROR predefined exception.
4. Check for success by checking the value of SQL%FOUND immediately after the UPDATE statement.
51. Read the following code:
52. CREATE OR REPLACE PROCEDURE calculate_budget IS
53. v_budget studio.yearly_budget%TYPE;
54. BEGIN
55. v_budget := get_budget(11);
56. IF v_budget < 30000
57. THEN
58. set_budget(11,30000000);
59. END IF;
60. END;
You are about to add an argument to CALCULATE_BUDGET. What effect will this have?
1. The GET_BUDGET function will be marked invalid and must be recompiled before the next execution.
2. The SET_BUDGET function will be marked invalid and must be recompiled before the next execution.
3. Only the CALCULATE_BUDGET procedure needs to be recompiled.
4. All three procedures are marked invalid and must be recompiled.
61. Which procedure can be used to create a customized error message?
1. RAISE_ERROR
2. SQLERRM
3. RAISE_APPLICATION_ERROR
4. RAISE_SERVER_ERROR
62. The CHECK_THEATER trigger of the THEATER table has been disabled. Which command can you issue to enable this trigger?
1. ALTER TRIGGER check_theater ENABLE;
2. ENABLE TRIGGER check_theater;
3. ALTER TABLE check_theater ENABLE check_theater;
4. ENABLE check_theater;
63. Examine this database trigger
64. CREATE OR REPLACE TRIGGER prevent_gross_modification
65. {additional trigger information}
66. BEGIN
67. IF TO_CHAR(sysdate, DY) = MON
68. THEN
69. RAISE_APPLICATION_ERROR(-20000,Gross receipts cannot be deleted on Monday);
70. END IF;
71. END;
This trigger must fire before each DELETE of the GROSS_RECEIPT table. It should fire only once for the entire DELETE statement. What additional information must you add?
1. BEFORE DELETE ON gross_receipt
2. AFTER DELETE ON gross_receipt
3. BEFORE (gross_receipt DELETE)
4. FOR EACH ROW DELETED FROM gross_receipt
72. Examine this function:
73. CREATE OR REPLACE FUNCTION set_budget
74. (v_studio_id IN NUMBER, v_new_budget IN NUMBER) IS
75. BEGIN
76. UPDATE studio
77. SET yearly_budget = v_new_budget
78. WHERE id = v_studio_id;
79.
80. IF SQL%FOUND THEN
81. RETURN TRUEl;
82. ELSE
83. RETURN FALSE;
84. END IF;
85.
86. COMMIT;
87. END;
Which code must be added to successfully compile this function?
1. Add RETURN right before the IS keyword.
2. Add RETURN number right before the IS keyword.
3. Add RETURN boolean right after the IS keyword.
4. Add RETURN boolean right before the IS keyword.
88. Under which circumstance must you recompile the package body after recompiling the package specification?
1. Altering the argument list of one of the package constructs
2. Any change made to one of the package constructs
3. Any SQL statement change made to one of the package constructs
4. Removing a local variable from the DECLARE section of one of the package constructs
89. Procedure and Functions are explicitly executed. This is different from a database trigger. When is a database trigger executed?
1. When the transaction is committed
2. During the data manipulation statement
3. When an Oracle supplied package references the trigger
4. During a data manipulation statement and when the transaction is committed
90. Which Oracle supplied package can you use to output values and messages from database triggers, stored procedures and functions within SQL*Plus?
1. DBMS_DISPLAY
2. DBMS_OUTPUT
3. DBMS_LIST
4. DBMS_DESCRIBE
91. What occurs if a procedure or function terminates with failure without being handled?
1. Any DML statements issued by the construct are still pending and can be committed or rolled back.
2. Any DML statements issued by the construct are committed
3. Unless a GOTO statement is used to continue processing within the BEGIN section, the construct terminates.
4. The construct rolls back any DML statements issued and returns the unhandled exception to the calling environment.
92. Examine this code
93. BEGIN
94. theater_pck.v_total_seats_sold_overall := theater_pck.get_total_for_year;
95. END;
For this code to be successful, what must be true?
1. Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function must exist only in the body of the THEATER_PCK package.
2. Only the GET_TOTAL_FOR_YEAR variable must exist in the specification of the THEATER_PCK package.
3. Only the V_TOTAL_SEATS_SOLD_OVERALL variable must exist in the specification of the THEATER_PCK package.
4. Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function must exist in the specification of the THEATER_PCK package.
96. A stored function must return a value based on conditions that are determined at runtime. Therefore, the SELECT statement cannot be hard-coded and must be created dynamically when the function is executed. Which Oracle supplied package will enable this feature?
1. DBMS_DDL
2. DBMS_DML
3. DBMS_SYN
4. DBMS_SQL
PL/SQL interview questions...
o Normalize many to many relationships
o Difference - Equijoin and union
o What is TEMP table space in Oracle, what is rollback segment
o How do we find row chaining?
o Pattern matching operators
o Features in oracle 9i and 10g
o Why truncating table is faster than delete
o copy commit syntax
o Convert Zulu time zone to US Eastern time zone
o Difference - union and union all
o Difference - Group by, Order by clause
o Which Ranking functions are available?
o Difference - Decode, NVL, NVL2
o Tradeoffs of using partitioned tables
o How can we call stored procedure in SQL query
o What are the restrictions on calling PL/SQL from SQL
o Why EXISTS is preferable to distinct
o Give 2 examples of avoiding unnecessary parsing.
o Difference - Equijoin and union
o What is TEMP table space in Oracle, what is rollback segment
o How do we find row chaining?
o Pattern matching operators
o Features in oracle 9i and 10g
o Why truncating table is faster than delete
o copy commit syntax
o Convert Zulu time zone to US Eastern time zone
o Difference - union and union all
o Difference - Group by, Order by clause
o Which Ranking functions are available?
o Difference - Decode, NVL, NVL2
o Tradeoffs of using partitioned tables
o How can we call stored procedure in SQL query
o What are the restrictions on calling PL/SQL from SQL
o Why EXISTS is preferable to distinct
o Give 2 examples of avoiding unnecessary parsing.
SQL General Interview Questions
o What are the different types of joins?
o Explain normalization with examples.
o What cursor type do you use to retrieve multiple recordsets?
o Diffrence between a "where" clause and a "having" clause
o What is the difference between "procedure" and "function"?
o How will you copy the structure of a table without copying the data?
o How to find out the database name from SQL*PLUS command prompt?
o Tadeoffs with having indexes
o Talk about "Exception Handling" in PL/SQL?
o What is the diference between "NULL in C" and "NULL in Oracle?"
o What is Pro*C? What is OCI?
o Give some examples of Analytical functions.
o What is the difference between "translate" and "replace"?
o What is DYNAMIC SQL method 4?
o How to remove duplicate records from a table?
o What is the use of ANALYZing the tables?
o How to run SQL script from a Unix Shell?
o What is a "transaction"? Why are they necessary?
o Explain Normalizationa dn Denormalization with examples.
o When do you get contraint violtaion? What are the types of constraints?
o How to convert RAW datatype into TEXT?
o Difference - Primary Key and Aggregate Key
o How functional dependency is related to database table design?
o What is a "trigger"?
o Why can a "group by" or "order by" clause be expensive to process?
o What are "HINTS"? What is "index covering" of a query?
o What is a VIEW? How to get script for a view?
o What are the Large object types suported by Oracle?
o What is SQL*Loader?
o Difference between "VARCHAR" and "VARCHAR2" datatypes.
o What is the difference among "dropping a table", "truncating a table" and "deleting all records" from a table.
o Difference between "ORACLE" and "MICROSOFT ACCESS" databases.
o How to create a database link ?
o Explain normalization with examples.
o What cursor type do you use to retrieve multiple recordsets?
o Diffrence between a "where" clause and a "having" clause
o What is the difference between "procedure" and "function"?
o How will you copy the structure of a table without copying the data?
o How to find out the database name from SQL*PLUS command prompt?
o Tadeoffs with having indexes
o Talk about "Exception Handling" in PL/SQL?
o What is the diference between "NULL in C" and "NULL in Oracle?"
o What is Pro*C? What is OCI?
o Give some examples of Analytical functions.
o What is the difference between "translate" and "replace"?
o What is DYNAMIC SQL method 4?
o How to remove duplicate records from a table?
o What is the use of ANALYZing the tables?
o How to run SQL script from a Unix Shell?
o What is a "transaction"? Why are they necessary?
o Explain Normalizationa dn Denormalization with examples.
o When do you get contraint violtaion? What are the types of constraints?
o How to convert RAW datatype into TEXT?
o Difference - Primary Key and Aggregate Key
o How functional dependency is related to database table design?
o What is a "trigger"?
o Why can a "group by" or "order by" clause be expensive to process?
o What are "HINTS"? What is "index covering" of a query?
o What is a VIEW? How to get script for a view?
o What are the Large object types suported by Oracle?
o What is SQL*Loader?
o Difference between "VARCHAR" and "VARCHAR2" datatypes.
o What is the difference among "dropping a table", "truncating a table" and "deleting all records" from a table.
o Difference between "ORACLE" and "MICROSOFT ACCESS" databases.
o How to create a database link ?
Monday, July 30, 2007
GeekInterview.com Weekly Digest July 30, 2007
| ||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||
|
| ||||||||||||||||||||||||||||
| Copyright © 2005 - 2007 GeekInterview.com |
Wednesday, July 25, 2007
Structure of PL/SQL
Structure of PL/SQL
Standard PL/SQL code segment is called a Block
A block consists of three parts or sections
Declaration Part
Executable Part
Exception Handling Part
Declaration Part
optional part where variables are defined
Executable Part
mandatory part which consists of executable statements
Exception Handling Part
optional part which consists of code for handling errors (runtime)
Pl/SQL Files -à
PL/SQL programs can be written in any editor and saved as files with .sql extension
Can also use “ED” command in SQL*Plus to create a PL/SQL program file
Use the “@” command to execute a PL/SQL program file
Variables --
Used to store results of a query for later processing, or to calculate values to be inserted into database tables
Can be used anywhere in an expression, either in SQL or PL/SQL statements
Must be declared before referencing it in other statements, including other declarative statements
Are declared by specifying the name along with the datatype
Can be declared to be of any datatype native to Oracle
Examples
oldfare NUMBER(5);
m_name VARCHAR(15);
(Note – Set Serveroutput On has to be given when a session starts for displaying the output statements_)
declare
x number;
begin
x := 67;
dbms_output.put_line(x);
dbms_output.put_line('The value of x is '|| x);
end;
Declaring variable in declare block.
Assigning value in in begin block using := .
Output statement is dbms_output.put_line
Concatenation operator is ||
Command terminator is ; after end
Declaring and initializing variables together
declare
y number := 100;
begin
dbms_output.put_line('The value of y is '|| y);
end;
-------------------------------------------------------------------------------
Taking value from the user using &
declare
z number;
a varchar2(10);
begin
z := &z;
a := '&a';
dbms_output.put_line('Z is '|| z);
dbms_output.put_line('A is '|| a);
end;
-------------------------------------------------------------------------------
/*Cannot declare or initialize more than one variable simultaneously*/
declare
a number;
b number;
c number;
begin
a := 67; b := 90; c := 87;
dbms_output.put_line(a);
dbms_output.put_line(b);
end;
A constant number has to declared and initialized in the declare block only using
CONSTANT keyword. Value cannot be changed
declare
r CONSTANT number :=100;
begin
/* r := r + 100; Not possible*/
dbms_output.put_line(r);
end;
/*Assigning value to variable from a column of a table using select into clause*/
declare
x number;
begin
Select sal Into x from emp
where ename = 'SMITH';
dbms_output.put_line('Salary of Smith is '|| x);
end;
/* Selecting ename,sal from emp
Use of more than one columns value with Into clause*/
declare
n varchar2(50);
s number;
begin
select ename, sal Into n, s
from emp
where ename = 'SMITH';
dbms_output.put_line(n);
dbms_output.put_line(s);
end;
% Type Attribute –
Provides datatype of a variable or column
Useful when declaring a variable that refers to a column in a database
exact datatype of column need not be known
if column definition changes, variable datatype changes accordingly at runtime
Example
oldfare fare.first_fare%TYPE;
newfare oldfare%TYPE;
declare
a emp.ename%type;
b emp.sal%type;
c emp.deptno%type;
/*Using %TYPE attribute for variable data type*/
begin
select ename,sal,deptno
into a,b,c
from emp
where ename = 'KING';
dbms_output.put_line(a ||'-'|| b ||'-' || c);
end;
%RowType Attribute –
Useful when declaring a record variable having same structure as a row in a table or view, or as a row fetched from a cursor
Fields in the record have same names and datatypes as the columns in the table/view
Example
emp_rec employee%ROWTYPE;
A specific field can be referenced using
emp_rec.emp_num;
declare
E emp%rowtype;
/*rowtype attribute holds the datatype of the columns of the
entire row*/
begin
select * INTO E
from emp
where ename = 'MARTIN';
dbms_output.put_line(E.sal);
dbms_output.put_line(E.ename);
dbms_output.put_line(e.deptno);
end;
Conditional Statements – IF
The selection structure tests a condition, then executes one sequence of statements instead of another, depending on the condition
There are three forms of statements
IF-THEN
IF-THEN-ELSE
IF-THEN-ELSIF
Sequence of statements is executed only if the condition evaluates to TRUE
If condition evaluates to FALSE or NULL, it does nothing
In either case control passes to next statement after the IF-THEN structure
IF THEN
statements;
END IF;
Sequence of statements in the ELSE clause is executed only if the condition evaluates to FALSE or NULL
IF THEN
statements;
ELSE
statements;
END IF;
--------------------------------------------------------------------------------
declare
/*Simple if condition */
x number;
begin
x := &x;
if x >= 35 then
dbms_output.put_line('Passed');
else
dbms_output.put_line('Failed');
end if;
end;
-----------------------------------------------------
IF-THEN-ELSIF Structure
This construct allows selection of action from several mutually exclusive alternatives
The IF statement can have any number of ELSIF clauses
The final ELSE is optional
Conditions are evaluated one by one from top to bottom
Syntax
IF THEN
statements;
ELSIF THEN
statements;
ELSIF THEN
statements;
ELSE
statements;
END IF;
Example 1 –
Declare
y number;
/*Multiple ifs */
Begin
y := &y;
if y >= 70 then
dbms_output.put_line('Distinction');
elsif y >= 60 then
dbms_output.put_line('First class');
elsif y >= 50 then
dbms_output.put_line('Second class');
elsif y >= 35 then
dbms_output.put_line('Passed');
else
dbms_output.put_line('Failed');
end if;
end;
Example 2
create table adm
(Name varchar2(30),
Marks number(3),
College varchar2(30),
Fees number(5));
/*Use of multiple if's
Accept name and marks from user.
Depending upon marks entered the college and fees should be decided
and the record should be entered in the adm table.*/
Declare
n adm.name%type;
m adm.marks%type;
c adm.college%type;
f adm.fees%type;
Begin
n := '&n';
m := &m;
if m >= 95 then
c := 'COEP';
f := 10000;
elsif m >= 90 then
c := 'MIT';
f := 15000;
elsif m >= 85 then
c := 'VIT';
f := 22000;
elsif m >= 80 then
c := 'D Y Patil';
f := 27000;
elsif m >= 75 then
c := 'Pune Vidyarthi';
f := 33000;
else
dbms_output.put_line('Cannot get admission');
end if;
if c is not null and f is not null then
dbms_output.put_line('Your College is '|| c || ' and fees are ' || f);
Insert into adm
values(n,m,c,f);
commit;
end if;
end;
Standard PL/SQL code segment is called a Block
A block consists of three parts or sections
Declaration Part
Executable Part
Exception Handling Part
Declaration Part
optional part where variables are defined
Executable Part
mandatory part which consists of executable statements
Exception Handling Part
optional part which consists of code for handling errors (runtime)
Pl/SQL Files -à
PL/SQL programs can be written in any editor and saved as files with .sql extension
Can also use “ED” command in SQL*Plus to create a PL/SQL program file
Use the “@
Variables --
Used to store results of a query for later processing, or to calculate values to be inserted into database tables
Can be used anywhere in an expression, either in SQL or PL/SQL statements
Must be declared before referencing it in other statements, including other declarative statements
Are declared by specifying the name along with the datatype
Can be declared to be of any datatype native to Oracle
Examples
oldfare NUMBER(5);
m_name VARCHAR(15);
(Note – Set Serveroutput On has to be given when a session starts for displaying the output statements_)
declare
x number;
begin
x := 67;
dbms_output.put_line(x);
dbms_output.put_line('The value of x is '|| x);
end;
Declaring variable in declare block.
Assigning value in in begin block using := .
Output statement is dbms_output.put_line
Concatenation operator is ||
Command terminator is ; after end
Declaring and initializing variables together
declare
y number := 100;
begin
dbms_output.put_line('The value of y is '|| y);
end;
-------------------------------------------------------------------------------
Taking value from the user using &
declare
z number;
a varchar2(10);
begin
z := &z;
a := '&a';
dbms_output.put_line('Z is '|| z);
dbms_output.put_line('A is '|| a);
end;
-------------------------------------------------------------------------------
/*Cannot declare or initialize more than one variable simultaneously*/
declare
a number;
b number;
c number;
begin
a := 67; b := 90; c := 87;
dbms_output.put_line(a);
dbms_output.put_line(b);
end;
A constant number has to declared and initialized in the declare block only using
CONSTANT keyword. Value cannot be changed
declare
r CONSTANT number :=100;
begin
/* r := r + 100; Not possible*/
dbms_output.put_line(r);
end;
/*Assigning value to variable from a column of a table using select into clause*/
declare
x number;
begin
Select sal Into x from emp
where ename = 'SMITH';
dbms_output.put_line('Salary of Smith is '|| x);
end;
/* Selecting ename,sal from emp
Use of more than one columns value with Into clause*/
declare
n varchar2(50);
s number;
begin
select ename, sal Into n, s
from emp
where ename = 'SMITH';
dbms_output.put_line(n);
dbms_output.put_line(s);
end;
% Type Attribute –
Provides datatype of a variable or column
Useful when declaring a variable that refers to a column in a database
exact datatype of column need not be known
if column definition changes, variable datatype changes accordingly at runtime
Example
oldfare fare.first_fare%TYPE;
newfare oldfare%TYPE;
declare
a emp.ename%type;
b emp.sal%type;
c emp.deptno%type;
/*Using %TYPE attribute for variable data type*/
begin
select ename,sal,deptno
into a,b,c
from emp
where ename = 'KING';
dbms_output.put_line(a ||'-'|| b ||'-' || c);
end;
%RowType Attribute –
Useful when declaring a record variable having same structure as a row in a table or view, or as a row fetched from a cursor
Fields in the record have same names and datatypes as the columns in the table/view
Example
emp_rec employee%ROWTYPE;
A specific field can be referenced using
emp_rec.emp_num;
declare
E emp%rowtype;
/*rowtype attribute holds the datatype of the columns of the
entire row*/
begin
select * INTO E
from emp
where ename = 'MARTIN';
dbms_output.put_line(E.sal);
dbms_output.put_line(E.ename);
dbms_output.put_line(e.deptno);
end;
Conditional Statements – IF
The selection structure tests a condition, then executes one sequence of statements instead of another, depending on the condition
There are three forms of statements
IF-THEN
IF-THEN-ELSE
IF-THEN-ELSIF
Sequence of statements is executed only if the condition evaluates to TRUE
If condition evaluates to FALSE or NULL, it does nothing
In either case control passes to next statement after the IF-THEN structure
IF
statements;
END IF;
Sequence of statements in the ELSE clause is executed only if the condition evaluates to FALSE or NULL
IF
statements;
ELSE
statements;
END IF;
--------------------------------------------------------------------------------
declare
/*Simple if condition */
x number;
begin
x := &x;
if x >= 35 then
dbms_output.put_line('Passed');
else
dbms_output.put_line('Failed');
end if;
end;
-----------------------------------------------------
IF-THEN-ELSIF Structure
This construct allows selection of action from several mutually exclusive alternatives
The IF statement can have any number of ELSIF clauses
The final ELSE is optional
Conditions are evaluated one by one from top to bottom
Syntax
IF
statements;
ELSIF
statements;
ELSIF
statements;
ELSE
statements;
END IF;
Example 1 –
Declare
y number;
/*Multiple ifs */
Begin
y := &y;
if y >= 70 then
dbms_output.put_line('Distinction');
elsif y >= 60 then
dbms_output.put_line('First class');
elsif y >= 50 then
dbms_output.put_line('Second class');
elsif y >= 35 then
dbms_output.put_line('Passed');
else
dbms_output.put_line('Failed');
end if;
end;
Example 2
create table adm
(Name varchar2(30),
Marks number(3),
College varchar2(30),
Fees number(5));
/*Use of multiple if's
Accept name and marks from user.
Depending upon marks entered the college and fees should be decided
and the record should be entered in the adm table.*/
Declare
n adm.name%type;
m adm.marks%type;
c adm.college%type;
f adm.fees%type;
Begin
n := '&n';
m := &m;
if m >= 95 then
c := 'COEP';
f := 10000;
elsif m >= 90 then
c := 'MIT';
f := 15000;
elsif m >= 85 then
c := 'VIT';
f := 22000;
elsif m >= 80 then
c := 'D Y Patil';
f := 27000;
elsif m >= 75 then
c := 'Pune Vidyarthi';
f := 33000;
else
dbms_output.put_line('Cannot get admission');
end if;
if c is not null and f is not null then
dbms_output.put_line('Your College is '|| c || ' and fees are ' || f);
Insert into adm
values(n,m,c,f);
commit;
end if;
end;
Subscribe to:
Comments (Atom)
