SQL and PL/SQL Interview questions-III
85. 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
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
1. DBMS_DISPLAY 2. DBMS_OUTPUT 3. DBMS_LIST 4. DBMS_DESCRIBE
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.
89. Examine this code
71. BEGIN
72. theater_pck.v_total_seats_sold_overall := theater_pck.get_total_for_year;
73. 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.
1. DBMS_DDL
2. DBMS_DML
3. DBMS_SYN
4. DBMS_SQL
1. DBMS_DDL
2. DBMS_DML
3. DBMS_SYN
4. DBMS_SQL
Method 1:
Select length (translate (trim (column_name),' +-.0123456789',' ')) from dual ;
Will give you a zero if it is a number or greater than zero if not numeric (actually gives the count of non numeric characters)
Method 2:
select instr(translate('wwww',
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X')
FROM dual;
It returns 0 if it is a number, 1 if it is not.
92 How to Select last N records from a Table?
select * from (select rownum a, CLASS_CODE,CLASS_DESC from clm)
where a > ( select (max(rownum)-10) from clm)
Here N = 10
The following query has a Problem of performance in the execution of the following query where the table ter.ter_master have 22231 records. So the results are obtained after hours.
Cursor rem_master(brepno VARCHAR2) IS
select a.* from ter.ter_master a
where NOT a.repno in (select repno from ermast) and
(brepno = 'ALL' or a.repno > brepno)
Order by a.repno
What are steps required tuning this query to improve its performance?
-Have an index on TER_MASTER.REPNO and one on ERMAST.REPNO
-Be sure to get familiar with EXPLAIN PLAN. This can help you determine the execution path that Oracle takes. If you are using Cost Based Optimizer mode, then be sure that your statistics on TER_MASTER are up-to-date. -Also, you can change your SQL to:
SELECT a.*
FROM ter.ter_master a
WHERE NOT EXISTS (SELECT b.repno FROM ermast b
WHERE a.repno=b.repno) AND
(a.brepno = 'ALL' or a.repno > a.brepno)
ORDER BY a.repno;
DELETE removes one or more records in a table, checking referential Constraints (to see if there are dependent child records) and firing any DELETE triggers. In the order you are deleting (child first then parent) There will be no problems.
TRUNCATE removes ALL records in a table. It does not execute any triggers. Also, it only checks for the existence (and status) of another foreign key Pointing to the table. If one exists and is enabled, then you will get The following error. This is true even if you do the child tables first.
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
You should disable the foreign key constraints in the child tables before issuing the TRUNCATE command, then re-enable them afterwards.
CLIENT/SERVER
Preemptive refers to the fact that each task is alloted fixed time slots and at the end of that time slot the next task is started.
OLTP stands for On Line Transaction Processing
OLTP requires real time response.
The client deals with the user interface part of the system.
To reduce the network traffic and for application sharing and implementing business rules.
Applications that have outgrown their environment are re-engineered to run in a larger environment. This is upsizing.
With rightsizing, one would move applications to the most appropriate server platforms.
A host based application is re-engineered to run in smaller or LAN based environment.
An event trigger, a segment of code which is associated with each event and is fired when the event occurs.
When a stored procedure is called, only the procedure call is sent to the server and not the statements that the procedure contains.
104. What are the types of processes that a server runs ?
Foreground process and Background process.
An event handler is a routine that is written to respond to a particular event.
An integrity constraint allows the definition of certain restrictions, at the table level, on the data that is entered into a table.
Database triggers can be used to enforce business rules, to maintain derived values and perform value-based auditing.
A transaction is a set of operations that begin when the first DML is issued and end when a commit or rollback is issued. BEGIN COMMIT/ROLLBACK are the boundries of a transaction.
Because it is easier to define an integrity constraint than a database trigger.
Because an integrity constraint is automatically checked while data is inserted into a table. A stored has to be specifically invoked.
A Client,
A Server and
A Network/Communication software.
Flexibility of the system, scalability, cost saving, centralised control and implementation of business rules, increase of developers productivity, portability, improved network and resource utilization.
Heterogeneity of the system results in reduced reliablity. May not be suitable for all applications. Managing and tuning networks becomes difficult.
Star,
Bus,
Ring.
A client process at first establishes connection with the Server.
115. What are the responsibilities of a Server ?
1. Manage resources optimally across multiple clients.
2. Controlling database access and security.
3. Protecting the databse and recovering it from crashes.
4. Enforcing integrity rules globally.
An API, in a Client/Server context, is a specification of a set of functions for communication between the client and the server.
Open Database Connectivity (ODBC),
Integrated Database Application Programming Interface (IDAPI),
XOpen
SQL/CLI
The application can be connected to any back end server that is supported by the API.
The application cannot use any special features of the backend server.
Because an event driven program is always waiting for something to happen before processing.
1. System Events.
2. Control Events
3. User Events
4. Other Events.
A file server just transfers all the data requested by all its client and the client processes the data while a database server runs the query and sends only the query output.
Inheritance is a method by which properties and methods of an existing object are automatically passed to any object derived from it.
1. An ODBC manager/administrator and
2. ODBC driver.
124. What is the function of a ODBC manager ?
The ODBC Manager manages all the data sources that exists in the system.
The ODBC Driver allows the developer to talk to the back end database.
The name of the DBMS, the location of the source and the database dependent information.
ODBC uses the description of the datasource available in the ODBC.INI file to load the required drivers to access that particular back end database.
1 comment:
Great article and right to the point. I don’t know if this is in fact the best place to ask but do you folks have any thoughts on where to get some professional writers? Thx :)
Testing interview questions and answer
Post a Comment