1. Named system exceptions
Ex: NO_DATA_FOUND,
TOO_MANY_ROWS, OTHERS, etc.
2. User defined exceptions.
Ex:.
declare
user_excep EXCEPTION;
begin
....
raise user_excep;
excption
when user_excep then ..... end;
3. Unnamed system exceptions.
Ex: declare
exce_name EXCEPTION;
PROGMA EXCEPTION_INIT (exce_name,error_code);
Begin ... end;
4. Unnamed user defined exception.
Ex:
exception
when exce_name then
raise application_error(error_number,message);
end;
52. What are OPTIMIZER HINTS?
Specifies a hint string that Oracle Forms passes on to the RDBMS optimizer when constructing queries. Using the optimizer can improve the performance of database transactions.
53. How can I protect my PL/SQL source code?
PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to protect the source code. This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original).
This way you can distribute software without having to worry about exposing your proprietary algorithms and methods. SQL*Plus and SQL*DBA will still understand and know how to execute such scripts. Just be careful, there is no "decode" command available.
The syntax is : WRAP INAME=MYSCRIPT.SQL ONAME=XXXX.PLB
54. Can we define structure like objects in PL/SQL?
If the structure is what we define in ‘C’ then we can create objects of type structure using RECORD
variable available in PL/SQL.
Yes, Using the PL/SQL tables. PL/SQL tables are temporary array like objects used in a PL/SQL block. PL/SQL tables can have one column and a primary key.
The column data type can belong to any scalar data type, but the primary key must only belong to the type binary_integer. Size - UNLIMITED.
55. What is anonymous block in PL/SQL?
The text of an Oracle Forms trigger is an anonymous PL/SQL block.
It consists of three sections :
· A declaration of variables, constants,cursors and exceptions which is optional.
· A section of executable statements.
· A section of exception handlers, which is optional.
syntax: DECLARE
--- declarartive statements ( optional )
BEGIN
--- executable statements ( required )
EXCEPTION
--- exception handlers ( optional )
END;
56. Can we define our OWN EXCEPTION? How to raise it?
In the DECLARATION part define a variable of type exception. In the excecution part call the exception using RAISE exception_name. In the exception part handle the exception using WHEN exception_name.
57. What is a PRAGMA?
It is a directive to the COMPILER, rather than a piece of executable code. Eventhough it appears in the program, it is not executable. It gives instructions to the compiler.
58. What is APPLICATION PARTITIONING?
PL/SQL is the language used for both client-side Oracle forms applications and server-side database triggers and stored procedures and there is a PL/SQL engine in both Oracle forms Run form and the Oracle7 Server.
This means that you can take advantage of application petitioning to execute application code on either the client or the server. Application partitioning allows you to optimize performance and resource usage by storing and executing procedures either locally or at the server, which makes the most sense for your particular application and configuration.
59. Difference between a STORED PROCEDURE and a STORED FUNCTION ?
Unlike procedures, FUNCTIONS returns a VALUE to the caller. This value is returned thro’ the RETURN command/keyword within the function.
Functions don’t use the IN, OUT IN OUT arguments, which are available for PROCEDURES.
60. How to TRAP ERRORS in procedures?
Use SHOW_ERRORS. This will display all the errors associated with the most recently created procedural object. This command will check the VIEW_ERRORS data dictionary for the ERRORS associated with the most recent compilation attempt for that procedural object.
SHOW_ERRORS will display the LINE and COLUMN NUMBER for each error, as well as the text of the error message.
Ex: SELECT LINE, POSITION, TEXT
FROM USER_ERRORS
WHERE NAME = ‘balance_check’ AND TYPE = PROCEDURE/FUNCTION/PACKAGE ORDER BY SEQUENCE;
NOTE: We can use ALL_ERRORS & DBA_ERRORS to view errors.
TRAPPING ERORS: DBMS_OUTPUT package allows you to use 3 debugging functions within your package. You must set ‘SERVER OUTPUT ON’ before executing the procedure object you will be debugging.
PUT - Puts multiple o/p’s on same line.
PUT_LINE - Puts each o/p on a separate line.
NEW_LINE - Used with PUT; Signals the END of current o/p line.
61. When do we get a MUTATING ERROR?
This happens with TRIGGERS. It occurs because the trigger is trying to update a row it is currently using. The usual fix involves either use of VIEWS or TEMPORARY TABLES so the database is selecting from one while updating the other.
62. How to DISABLE REFERENTIAL INTEGRITY?
Use the DIABLE option in CREATE TABLE or ALTER TABLE or using
DISABLE {{UNIQUE (column) (column)... PRIMARY KEY
CONSTRAIN [CASCADE] ALL TRIGGERS;
NOTE: For disabling REFERENTIAL INTEGRITY we have to include CASCADE option.
63. What is the DIFFERENCE between EXPLICIT CURSOR & IMPLICIT CURSOR?
IMPLICIT CURSORS are automatically opened by issuing a SELECT statement. But the EXPLICIT cursors are to be opened using OPEN, fetching is done using FETCH and closing using CLOSE.
64. Difference between SQL and PL/SQL?
SQL is the ANSI industry standard language, used to manipulate information in a relational database. PL/SQL is the procedural language extension to Oracle’s SQL language.
65. How to fetch description of a code in the base table block where code is a base table field and the description is a non-base table field?
Use SELECT with INTO clause to fetch the description value into the NON-BASE table field.
66. What packages (if any) has Oracle provided for use by developers?
Oracle provides the DBMS_ series of packages. There are many which developers should be aware of such as DBMS_SQL, DBMS_PIPE, DBMS_TRANSACTION, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_DDL, UTL_FILE.
If they can mention a few of these and describe how they used them, even better. If they include the SQL routines provided by Oracle, great, but not really what was asked.
67. Describe the use of PL/SQL tables
PL/SQL tables are scalar arrays that can be referenced by a binary integer. They can be used to hold values for use in later queries or calculations. In Oracle 8 they will be able to be of the %ROWTYPE designation, or RECORD.
68. When is a declare statement needed?
The DECLARE statement is used in PL/SQL anonymous blocks such as with stand alone, non-stored PL/SQL procedures. It must come first in a PL/SQL stand-alone file if it is used.
69. In what order should a open/fetch/loop set of commands in a PL/SQL block be implemented if you use the %NOTFOUND cursor variable in the exit when statement? Why?
OPEN then FETCH then LOOP followed by the exit when. If not specified in this order will result in the final return being done twice because of the way the %NOTFOUND is handled by PL/SQL.
70. What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?
SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered.
They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.
71. How can you find within a PL/SQL block, if a cursor is open?
Use the %ISOPEN cursor status variable.
72. How can you generate debugging output from PL/SQL?
Use the DBMS_OUTPUT package. Another possible method is to just use the SHOW ERROR command, but this only shows errors. The DBMS_OUTPUT package can be used to show intermediate results from loops and the status of variables as the procedure is executed. The new package UTL_FILE can also be used.
73. What are the types of triggers?
There are 12 types of triggers in PL/SQL that consist of combinations of the BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and ALL key words:
BEFORE ALL ROW INSERT
AFTER ALL ROW INSERT
BEFORE INSERT AFTER INSERT
74. How PL/SQL is procedural?
Pl/sql bridges the gap between the database functionality and procedural languages. It has got the feature of 'cursors' where you can process set of rows one by one
Manipulation of variables of different data types is possible.
Different control statements like if/end if, for loop/end loop, while loop/end loop and goto <
Raising the user-defined, system-defined exceptions are possible.
Calling a PL/SQL block from another PL/SQL block is possible.
These features make the PL/SQL a procedural language.
75. What are stored procedure and 3 advantages?
Stored procedures are nothing but PL/SQL blocks that can take parameters.
Which will be compiled and stored in the database as an object which can be explicitly invoked anywhere in the application.
Advantages:
1. Reduces the network traffic
2. Improves the performance of the database read.
3. Reduction in memory since only one copy of the procedure needs to be loaded for execution by multiple users.
76. Can we open a cursor in the exception section?
YES
For Example:
DECLARE
ANBU EXCEPTION;
CURSOR CE IS SELECT ENAME FROM EMP;
N EMP.ENAME%TYPE;
PRAGMA EXCEPTION_INIT (ANBU, -2292);
BEGIN
RAISE ANBU;
EXCEPTION
WHEN ANBU THEN
OPEN CE;
LOOP
FETCH CE INTO N;
EXIT WHEN CE%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (N);
END LOOP;
CLOSE CE;
END;
77. A stored procedure or function you have written commit or rollback. How can u disable those commit and rollback, which you have written in a stored procedure or function?
Alter session disable commit/rollback in procedure
78. How does one get the value of a sequence into a PL/SQL variable?
As you might know, oracle prohibits this: i := sq_sequence.NEXTVAL; (for some silly reason). But you can do this: select sq_sequence. NEXTVAL into: i from dual;
79. Explain the Purpose of SQL%NOTFOUND cursor attribute?
If the DML statement doesn't process any row, it will return FALSE. Otherwise it is TRUE.
80. How to manipulate explicit cursors?
OPEN, FETCH and CLOSE statements.
81. Can u use multiple exceptions in single handler?
We can use multiple exceptions in single handler.
· eg.,
EXCEPTION
WHEN invalid_number OR STANDARD.INVALID_NUMBER THEN
handle the error
END;
82. What is the use of cursor FOR LOOP?
In cursor FOR loop, no need of OPEN, FETCH, and CLOSE statements.
· A cursor FOR loop implicitly declares its loop index as a %ROWTYPE record, opens a cursor, repeatedly fetches rows of values from the result set into fields in the record, and closes the cursor when all rows have been processed.
Ex: DECLARE
result temp.col1%TYPE;
CURSOR c1 IS
SELECT n1, n2, n3 FROM data_table WHERE exper_num = 1;
BEGIN
FOR c1_rec IN c1 LOOP
/* calculate and store the results */
result := c1_rec.n2 / (c1_rec.n1 + c1_rec.n3);
INSERT INTO temp VALUES (result, NULL, NULL);
END LOOP;
COMMIT;
END;
83. How Exceptions Are Raised?
Using RAISE statement.
Ex:
DECLARE
out_of_stock EXCEPTION;
number_on_hand NUMBER(4);
BEGIN
...
IF number_on_hand < 1 THEN
RAISE out_of_stock;
END IF;
EXCEPTION
WHEN out_of_stock THEN
handle the error
END;
84. What is the difference between PACKAGE Specification and PACKAGE Body?
The package specification contains public declarations of procedures, functions, cursors, global variables and other packages also.
The package specification contains all the code behind the package spec.
85. What are the types of PL/SQL blocks?
There are two type.
1.Anonymous block
Ex:
declare
.....
begin
...
end;
2.Stored PL/SQL block
Ex:
create procedure proc_name is
.....
begin
...
end;
86. Is there a PL/SQL Engine in SQL*Plus?
No. Unlike Oracle Forms, SQL*Plus does not have a PL/SQL engine. Thus, all your PL/SQL are send directly to the database engine for execution. This makes it much more efficient as SQL statements are not stripped off & send to the database individually.
87. Is it possible to use parameter in cursor? If it is possible, how?
Possible.
Ex:
declare
cursor c1(para1 number) is
select * from emp where empno = para1;
begin
open c1(7369);
loop
fetch c1 into .....
.....
end loop;
close c1;
end;
88. I write a db trigger on table_1, which will fire when I update or delete or insert into table_1. In the db trigger, if I update the table I should call procedure_1, if I delete the rows in the table I should call procedure_2, if I insert into the table I should call procedure_3. Is it possible?
Yes. It is possible.
89. What are Database Triggers and how many types are there?
A database trigger is a stored PL/SQL block that is associated with a table. Oracle automatically executes a trigger when a specified SQL statement is issued against the table.
Parts of a Trigger: A trigger has three basic parts:
o a triggering event or statement
o a trigger restriction
o a trigger action
Using the options listed in the previous two sections, you can create four types of triggers:
o BEFORE statement trigger Before executing the triggering statement, the trigger action is executed.
o BEFORE row trigger Before modifying each row affected by the triggering statement and before checking appropriate integrity constraints, the trigger action is executed provided that the trigger restriction was not violated.
o AFTER statement trigger After executing the triggering statement and applying any deferred integrity constraints, the trigger action is executed.
o AFTER row trigger After modifying each row affected by the triggering statement and possibly applying appropriate integrity constraints, the trigger action is executed for the current row provided the trigger restriction was not violated. Unlike BEFORE row triggers, AFTER row triggers lock rows.
90. What is user_exit?
A user exit is a precoded routine your form calls to perform application processing
91. Can one read/write files from PL/SQL?
Included in Oracle 7.3 is an UTL_FILE package that can read and write operating system files. The directory you intend writing to has to be in your INIT.ORA file (see UTL_FILE_DIR=... parameter). Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('/home/oracle/tmp', 'myoutput', 'W');
UTL_FILE.PUTF(fileHandler, 'Value of func1 is %s\n', func1(1));
UTL_FILE.FCLOSE(fileHandler);
END;
92. Can you override the 'IN' parameter, passed to a procedure program unit?
We cant override the IN parameter passed to a procedure.
93. What is current of? Where do we use?
UPDATE {table_reference (subquery)} [alias]
SET { column_name = {sql_expression (subquery)}
(column_name[, column_name]...) = (subquery)}
[, { column_name = {sql_expression (subquery)}
(column_name[, column_name]...) = (subquery)}]...
[WHERE {search_condition CURRENT OF cursor_name}];
WHERE CURRENT OF cursor_name
This clause refers to the latest row processed by the FETCH statement associated with the cursor identified by cursor_name. The cursor must be FOR UPDATE and must be open and positioned on a row. If the cursor is not open, the CURRENT OF clause causes an error. It can be used with delete as well.
94. I use a 'cursor for loop' in my PL/SQL block. I leave the loop prematurely using 'exit' statement. What will happen to the cursor?
Nothing will happen to the cursor. When you are exiting the loop prematurely it is automatically closed.
95. I have product master (product_master) TABLE to store information about products with columns product_no, description, profit_percent, unit_measure, qty_on_hand, reorder_lvl, sell_price, cost_price. Whenever I issue any product, I update the table. But, if the 'qty_on_hand' becomes less than zero, I don't want the transaction to take place. How can I do that?
You can do this by creating a database trigger BEFORE UPDATE for the table product_master to check the 'qty_on_hand', If it is less than zero the trigger wont allow the transaction otherwise it allows.
96. Is it possible to use RAISE exception-handler inside the WHEN Clause?
It is possible.
Example program:
DECLARE
V_EMPNO EMP.EMPNO%TYPE;
HANDLER_1 EXCEPTION;
BEGIN
BEGIN
SELECT EMPNO INTO V_EMPNO FROM EMP
WHERE EMPNO = 7368;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE HANDLER_1;
END;
EXCEPTION
WHEN HANDLER_1 THEN
DBMS_OUTPUT.PUT_LINE ('HANDLER IS WORKING');
END;
97. When locks are released?
Locks are automatically released when the DML command completes its processing.
98. Can a procedure call itself?
Possible, we can.
99. What are the differences between the stored procedures and database triggers?
[Many are there, important is, you can not pass parameters to database trigger]
100. I write a PL/SQL block to update 'EMP' table for a given condition. Is there any way to find out whether exactly any rows were updated?
Yes, we can by using %SQL ROWCOUNT attribute
No comments:
Post a Comment