Q&A: Oracle Basic

How to make a procedure private?
Don't declare it in package specification.

What is ref cursor?
It is cursor variable like a pointer in C. can point to any query. Used to pass data set between program units.
http://www.oracle-base.com/articles/misc/UsingRefCursorsToReturnRecordsets.php

When a ref cursor open, who use and close this ref cursor?
The program unit who receive the data set will use and close the cursor, it may be other packages or front end code or Reporting tools.

What is an autonomous transaction?
Autonomous transaction is the transaction initiated by another transaction. The key point is it allows the separate commit/rollback transactions.

What are two exceptions to be careful of when using a select INTO statement?
1. When records not return, it would generate NOT_DATA_FOUND Found error message.
2. TOO_NAMY_ROWS can be another problem.

What is %type? What are the advantages of using this over datatypes?
%type means matching the date type of original data source. It can make sure there would be no problem with date type matching issue. On other hand, if declare an own datatype, when source data by chance is different from this declaration, there would be a data type consistence issue.

In PL/SQL how can you test that an update statement updated no rows?
SQL%ROWCOUNT can be used to test for this purpose.

Give examples of collections in oracle and their differences?
# Index-by tables, also known as associative arrays, let you look up elements using arbitrary numbers and strings for subscript values. (They are similar to hash tables in other programming languages.)
# Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.
# Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.

Have you used Bulk Collection? If yes then:"Why is bulk collection more efficient"?
Without bulk collection, every single row would request PL/SQL server to call SQL server one by one. By using bulk collection, say 5000 in a patch, the PL/SQL server will call SQL server for every 5000 rows. This can be very efficient.

What is the difference between a function and a procedure?
1. Functions are normally used for computations where as procedures are normally used for executing business logic.
2. Function is mainly used in the case where it must return 1 value, which can be scalar value or table or table values. Where as a procedure may not return a value (except through out parameter). If procedure mistakenly called, is returns always integer zero.
3. Procedure can call in another project but function work in same project.
4. We can't have any DDL, DML and TLC command inside a function, if that function is called from a SQL query as a UDF(user defined function). But if the function is not called from SQL query then we can have all transactional statement(DDL, DML and TLC) inside a function. Procedure can not be called from the SQL statements.

When a procedure within a package (containting multiple procedures) is called, what will get loaded into memory, the whole package or just the procedure?
Whole package will be loaded into memory.

How many triggers exist?
Trigger Types
A DML trigger is fired by a DML statement, a DDL trigger is fired by a DDL statement, a DELETE trigger is fired by a DELETE statement, and so on. An INSTEAD OF trigger is a DML trigger that is defined on a view (not a table). The database fires the INSTEAD OF trigger instead of executing the triggering DML statement.
A system trigger is defined on a schema or the database. A trigger defined on a schema fires for each event associated with the owner of the schema (the current user). A trigger defined on a database fires for each event associated with all users.
A simple trigger can fire at exactly one of the following timing points:
•Before the triggering statement executes
•After the triggering statement executes
•Before each row that the triggering statement affects
•After each row that the triggering statement affects
A compound trigger can fire at more than one timing point. Compound triggers make it easier to program an approach where you want the actions you implement for the various timing points to share common data.

How to know if index is being used?
One can use the index monitoring feature to check if indexes are used by an application or not. When the MONITORING USAGE property is set for an index, one can query the v$object_usage to see if the index is being used or not. Here is an example: SELECT table_name, index_name, monitoring, used FROM v$object_usage;