Q1. I can SELECT from SQL*Plus but not from PL/SQL. What is wrong?
PL/SQL respect object privileges given directly to the user, but does not observe privileges given through roles. The consequence is that a SQL statement can work in SQL*Plus, but will give an error in PL/SQL. Choose one of the following solutions:
“Mutating” means “changing”. A mutating table is a table that is currently being modified by an update, delete, or insert statement. When a trigger tries to reference a table that is in state of flux (being changed), it is considered “mutating” and raises an error since Oracle should not return data that has not yet reached its final state.
Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table off which it fires. If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well.
There are several restrictions in Oracle regarding triggers:
Q3. Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
A function and procedure are the same in that they are intended to be a collection of PL/SQL code that carries a single task. While a procedure does not have to return any values to the calling application, a function will return a single value. A package on the other hand is a collection of functions and procedures that are grouped together based on their commonality to a business function or application.
Q4. Is there a PL/SQL Engine in SQL*Plus?
No. Unlike Oracle Forms, SQL*Plus does not have an embedded PL/SQL engine. Thus, all your PL/SQL code is sent directly to the database engine for execution. This makes it much more efficient as SQL statements are not stripped off and sent to the database individually.
Q5. What is the difference between %TYPE and %ROWTYPE?
Both %TYPE and %ROWTYPE are used to define variables in PL/SQL as it is defined within the database. If the datatype or precision of a column changes, the program automatically picks up the new definition from the database without having to make any code changes.
The %TYPE and %ROWTYPE constructs provide data independence, reduces maintenance costs, and allows programs to adapt as the database changes to meet new business needs.
Oracle provides many DBMS_ series of packages, such as DBMS_SQL, DBMS_PIPE, DBMS_TRANSACTION, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_DDL, UTL_FILE.
Q7. 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.
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.
Q8. What is PL/SQL table?
PL/SQL tables are PL/SQL’s way of providing arrays. Arrays are like temporary tables in memory and thus are processed very quickly. It is important for you to realize that they are not database tables, and DML statements cannot be issued against them. This type of table is indexed by a binary integer counter (it cannot be indexed by another type of number) whose value can be referenced using the number of the index.
Remember that PL/SQL tables exist in memory only, and therefore don’t exist in any persistent way, disappearing after the session ends.
Q9. What is a Cursor?
Cursors are work areas used for internal processing in order to execute an SQL statement. In other word, it is a point to context area which has information for Oracle to process SQL statements. It allows fetching rows returned by a select statement.
There are two types of Cursors.
Q10. What is the difference between an explicit cursor and select into statement?
When a cursor name is explicitly assigned to a SELECT statement through CURSOR statement it is called an explicit cursor. Explicit cursors are generally used to work with more than one row within PL/SQL. It has got the following attributes:
1. Defining the Cursor
2. Opening the Cursor
3. Fetching rows(values) from Cursor (one at a time)
4. Closing the Cursor
SELECT into statement allows you to retrives the query result in user defined variables. But the limitation here is that the query must return a single row. Its a best practice to handle NO_DATA_FOUND and TOO_MANY_ROWS exception when dealing with “SELECT INTO” statement.
PL/SQL respect object privileges given directly to the user, but does not observe privileges given through roles. The consequence is that a SQL statement can work in SQL*Plus, but will give an error in PL/SQL. Choose one of the following solutions:
- Grant direct access on the tables to your user. Do not use roles!
GRANT select ON scott.emp TO my_user;- Define your procedures with invoker rights (Oracle 8i and higher);
create or replace procedure proc1
authid current_user is
begin
...- Move all the tables to one user/schema.
“Mutating” means “changing”. A mutating table is a table that is currently being modified by an update, delete, or insert statement. When a trigger tries to reference a table that is in state of flux (being changed), it is considered “mutating” and raises an error since Oracle should not return data that has not yet reached its final state.
Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table off which it fires. If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well.
There are several restrictions in Oracle regarding triggers:
- A row-level trigger cannot query or modify a mutating table. (Of course, NEW and OLD still can be accessed by the trigger).
- A statement-level trigger cannot query or modify a mutating table if the trigger is fired as the result of a CASCADE delete.
Q3. Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
A function and procedure are the same in that they are intended to be a collection of PL/SQL code that carries a single task. While a procedure does not have to return any values to the calling application, a function will return a single value. A package on the other hand is a collection of functions and procedures that are grouped together based on their commonality to a business function or application.
Q4. Is there a PL/SQL Engine in SQL*Plus?
No. Unlike Oracle Forms, SQL*Plus does not have an embedded PL/SQL engine. Thus, all your PL/SQL code is sent directly to the database engine for execution. This makes it much more efficient as SQL statements are not stripped off and sent to the database individually.
Q5. What is the difference between %TYPE and %ROWTYPE?
Both %TYPE and %ROWTYPE are used to define variables in PL/SQL as it is defined within the database. If the datatype or precision of a column changes, the program automatically picks up the new definition from the database without having to make any code changes.
The %TYPE and %ROWTYPE constructs provide data independence, reduces maintenance costs, and allows programs to adapt as the database changes to meet new business needs.
- %TYPE is used to declare a field with the same type as that of a specified table’s column.
- %ROWTYPE is used to declare a record with the same types as found in the specified database table, view or cursor.
Oracle provides many DBMS_ series of packages, such as DBMS_SQL, DBMS_PIPE, DBMS_TRANSACTION, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_DDL, UTL_FILE.
Q7. 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.
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.
Q8. What is PL/SQL table?
PL/SQL tables are PL/SQL’s way of providing arrays. Arrays are like temporary tables in memory and thus are processed very quickly. It is important for you to realize that they are not database tables, and DML statements cannot be issued against them. This type of table is indexed by a binary integer counter (it cannot be indexed by another type of number) whose value can be referenced using the number of the index.
Remember that PL/SQL tables exist in memory only, and therefore don’t exist in any persistent way, disappearing after the session ends.
Q9. What is a Cursor?
Cursors are work areas used for internal processing in order to execute an SQL statement. In other word, it is a point to context area which has information for Oracle to process SQL statements. It allows fetching rows returned by a select statement.
There are two types of Cursors.
- Implicit Cursor which occurs on behalf of every SQL statement and it is declared automatically.
- Explicit Cursor is a Named Cursor used in PL SQL which is defined by programmer.
Q10. What is the difference between an explicit cursor and select into statement?
When a cursor name is explicitly assigned to a SELECT statement through CURSOR statement it is called an explicit cursor. Explicit cursors are generally used to work with more than one row within PL/SQL. It has got the following attributes:
1. Defining the Cursor
2. Opening the Cursor
3. Fetching rows(values) from Cursor (one at a time)
4. Closing the Cursor
SELECT into statement allows you to retrives the query result in user defined variables. But the limitation here is that the query must return a single row. Its a best practice to handle NO_DATA_FOUND and TOO_MANY_ROWS exception when dealing with “SELECT INTO” statement.
No comments:
Post a Comment