Oracle Applications: Oracle PL SQL Faqs

1. What is anonymous block?

A. Anonymous block is a block structure but it is not store any where it can’t have any name.

2. What is Exception what are they?

A. Exception is error handler statement it is used when the program terminates abnormally at the time the exception will do the abnormal termination to normal termination. They are mainly two types

System defined exception and user defined exception.

The system defined exceptions are:

1. NO_DATA_FOUND

2. DUP_VAL_ON_INDEX

3. CURSOR_ALREADY_OPEN

4. INVALID_CURSOR

5. ZERO_DEVIDE

6. VALUE_ERROR

7. STORAGE_ERROR

8. PROGRAM_ERROR

9. OTHERS etc.

User defined exceptions are:

Generally the oracle is provided some exception names nearly below 50. Suppose we got error out of system defined exception at the time we will have to define the name of the exception. This is called user named exception. We have set the name of exception by using pragma exception_init. User defined exceptions where ever we want to raise exception we will raise the exception.

3. What is Raise_application_error?

A. Raise application error is used to terminate the programe at any point of time.

4. What is Pragma exception init?

A. Pragma exception init tells the compiler to associate an exception with an oracle error it is used to get the error message of specific oracle error.

5. What is Pragma?

A. Pragma is pre compiler directive.

6. What is Exception propagation?

A. Exception propagation is a master and child exception handler statement. Suppose we have one parent and child block. There are occurred the error in child block but we are not specified exception in the block the compiled will search the main block for the exception handler statement.

7. What is a Cursor?

A. Cursor is private area. This area will provide by oracle engine it is a temporary memory. It is used to get the data from the database. There are two types.

1. Implicit cursor.

2. Explicit cursor.

What is implicit cursor?

A. It will fire implicitly when the DML operations are doing. We can’t control this cursor.

10. What is the cursor controls?

A. Open, Fetch, Close

11. What is the implicit cursor attributes?

A. SQL%ISOPEN, SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT.

12. What is explicit cursor?

A. Explicit cursor is a cursor we can control explicitly. The explicit cursor attributes are %IS_OPEN, %FOUND, %NOT_FOUND, and % ROW_COUNT.

The explicit cursors are:

1. General Cursor: In this cursor we will open, fetch, close the cursor manually. In this cursor we will write the select statement with in the declaration part.

2. For Cursor: This is a explicit cursor. In this cursor we will not open, fetch, close the cursor manually. When we are using for cursor it will open and it will fetch all records and it will exit the loop and it will close automatically. There is a one drawback that is it won’t come in between the fetching all records.

Ex: Step1: Define cursor and write sql statement in the declaration part.

Step2: for <variable name> in <cursor name>

loop

end loop;

3. For Update Cursor: This cursor is used to update a single record. When we are updating the record in this cursor that record will be lock. No body will not use this record this is called record level lock. In this cursor we will use where current off. This command will lock the specific record column value. It will frees the remaining column values. But it is not recommended by using rowed we will update the records.

4. Ref Cursor: Ref cursor is a dynamic cursor. It will populate dynamically we can get a data through by address or pointers. In general cursor we will specify the select statement in the declaration part in the ref cursor we will specify the select statement in the body of the program. They are two types

1. Strong Cursor: It is returning a value.

2. Weak Cursor: It should not return a value.

13. What is Procedure?

A. Procedure is a data base object. It performs a specific action. It contains a multiple statements.

14. What is Function?

A. Function is a data base object. It calculates the value and it must be return a value.

15. What are parameter modes in the procedures and functions?

A. They are three parameter modes in procedure and functions. IN, OUT, INOUT. IN parameter is used to pass a value into procedures and functions. The OUT parameter is used to get the value from procedures and functions. The INOUT parameter is used to pass the input value and get the output value from the procedures and functions. By default parameter is IN.

16. What is difference between procedure and function?

A. Generally procedure should not return a value the function it must be return a value. Functions we can call at select statements directly. Procedures we will execute at PLSQL block only.

17. What is Package?

A. Package is a data base object it is logically related to PLSQL object and sub programs. Package containing the 2 or more procedures and functions. It will give the good performance. They are two parts Specification and Body of the package. In the specification part we have declare all procedures, functions which we are writing this package and global variables, Cursors and user defined exceptions. In body of the package having entire coding of each procedure and function.

18. What is difference between procedure and package?

A. Procedure is a single object package containing a multiple objects. Package containing overloading specialty. When we are compiling the package the package the compilation code will store in SGA(System Global Area) memory permanently. When we are running the package the package will execute from SGA memory it self. When we are calling the procedure at the time the compilation will move to SGA memory temporarily it will execute and it will destroy. That’s why the package is very fast.

19. What is Overloading Procedure or function?

A. Overloading procedure is a procedure we can create more then one procedure with the same name and different in parameters. It will perform different action.

20. What is Trigger?

A. Trigger is a database object. It will fires implicitly when the event occurs.

21. What are types of triggers In PLSQL?

A. They are 12 types that are Before/After, Insert/Update/Delete, For each row and Statement level.

22. What is Mutating error?

A. It wills fires when we are selecting the record while doing the DML operation on the same record and same table. There is no permanent solutions they are having only two partial solutions that’s are: Pragma Autonomous Transaction and Statement level trigger.

23. What is Pragma Autonomous Transaction?

A. Pragma autonomous transaction is a transaction it is used to split the transaction into two different transactions with master and child relation ship. They will execute independently and they will commit independently.

24. What is the Instead Of Trigger?

A. Instead of Trigger is used to do the DML operations on the complex views.

25. What is dynamic SQL?

A. Dynamic SQL is to do the DDL & DML operations with in the PLSQL block. Syntax: EXECUTE IMMIDIAT (‘<ddl/dml statement>’);

26. What is Bind variable?

A. Bind variable is a variable but we are not define the variable any where it can hold the data temporarily it will identified by :< Variable>.

25. What is composite data type?

A. Composite data types are %type and %rowtype.

%type is used to declare the data type of the specific column in the table data type it is used to get the specific column value.

%rowtype is used to declare the specific table entire record data type. It is used to get the entire record at a time.

26. What is PLSQL table?

A. PLSQL table is a collection data type. It is called temporary table. It is like a varray the varray having specific length and plsql table is having dynamic length of memory. We can create the plsql table for the single collection data type or single object and it must be indexed by binary integer.

Syn:

Declare

Type <type name> is table of EMP%type indexed by binary integer;

<Variable name> <table name>;

Begin

<Variable name>(<record number> := <value>;

End:

27. What is the difference between the cursor and plsql table?

A. Both will do same action but when we are using the cursor we will fetch the records statically. When we are using the plsql table we will get the records dynamically..

28. How we can delete the specific record and all records from the plsql table.

A. <table name>.delete (<record number>); is used to delete the specific record.

<Table name>. Delete; is used to delete the all records from the plsql table.

29. What is the Nested table?

A. A table with in table is called a nested table.

30. What is bulk collect? Explain?

A. Bulk collect is used to move bulk of records at a time to variable or plsql table in PLSQL block.

Ex:

Declare

Type emp_type is table of EMP%rowtype index by binary_integer;

T emp_type;

Begin

Select * bulk collect into t from emp;

For i in 1.. 14

Loop

Dbms_output.put_line(t(i).empno||’ ‘||t(i).ename||’ ‘||t(i).sal);

End loop;

Exception

When others then

Dbms_output.put_line(‘Error is :’||sqlerrm);

End;

31. What is bind parameters in PLSQL? What is the use of bind parameters?

A. Bind parameters are temporary parameters we are not create a parameters and we are not set the values into this parameters. There are tow types. : New, old. This parameters are used to call the new or old records in triggers.

32. What are optimization techniques?

A. There are two types of optimization techniques we are followed.

1. Rule based technique

2. Cost based technique.