pl/sql questions

Types of PL/SQL Triggers

There are two types of triggers based on the which level it is triggered.

1) Row level trigger - An event is triggered for each row updated, inserted or deleted. 
2) Statement level trigger - An event is triggered for each sql statement executed. 

Cursor: It is automatically created by oracle for all sql dml statements including the query that returns one row.
Explicit cursor: These are created and managed by the user. And used for multi row select statement.

What are the cursors attributes in plsql

%ISOPEN - to check whether cursor is open or not

% ROWCOUNT - number of rows fetched/updated/deleted.

% FOUND - to check whether cursor has fetched any row. True if rows are fetched.

% NOT FOUND - to check whether cursor has fetched any row. True if no rows are fetched.

These attributes are proceeded with SQL for Implicit Cursors and with Cursor name for Explicit Cursors.

Stored Procedure/Function is a compiled database object, which is used for fast response from Oracle Engine. Difference is Stored Procedure must return multiple value and function must return single value.

How to debug the procedure
We can also do line by line execution using tools such as Pl/SQL Developer, Toad etc

If you want to tune a package then you can use dbms_profiler.

if you   want to debug use dbms_debug procedure.

 To see the user priviliges

Select *from user_SYS_PRIVS

What are difference between a cursor declared in a procedure and cursor declared in a package specification

A cursor declared in a package specification is global and can be accessed by other procedures or procedures in a package.
A cursor declared in a procedure is local to the procedure that can not be accessed by other procedures.
One more difference is cursor declared in a package specification must have RETURN type
Or 

·        A cursor declares in the package specification that can be accessed in the other procedure or procedures of the package.

·        A cursor declares in the procedure
that can't be accessed by other procedure.

IN, OUT, IN-OUT parameters.

1) IN parameter is the default mode which acts as a constant inside calling environment. value passed in this parameter can not be changed.
OUT parameter mode is used to pass value from calling environment into main block, here we can change the value. It acts as a variable inside calling environment.

INOUT parameter mode which pass value into calling environment and will get the value back in main block.

IN parameter mode uses call by reference method to pass value from formal parameter to actual parameter.
OUT & INOUT parameter mode uses call by value method to pass values.
Or 
2) You can also give a compiler hint - 'NOCOPY' when defining function parameters.
e.g. : create or replace function add(var1 in number,var2 innumber, result inout nocopy number)

Main purpose of using NOCOPY:

Even if exception occured in function block your result state is maintained in defined variable (parameter: result).

If you haven't declared NOCOPY then the processed value for 'result'  parameter will gets copied only when function ends.
Or 
3) IN ,OUT,and INOUT

parameters are actual and formal

-- IN mode is default and is passed as constant
IN parameters cannot change their values in procedure
--OUT parameters can change their values in procedure and ispassed as a variable
--INOUT parameters can change their values in procedure and arepassed back to the calling environment as a new value. a nocopy hint can also be used 

What is overloading of procedure

Same procedure name with different parameters called procedure overloading, parameters may different by their data types, sequence and position.

What is sql*loader

SQL*Loader is a product for moving data in external files into tables in an Oracle database. To load data from external files into an Oracle database, two types of input must be provided to SQL*Loader : the data itself and the control file. The control filedescribes the data to be loaded. It describes the Names and format of the data files, Specifications for loading data and the Data to be loaded (optional). Invoking the loader sqlload username/password controlfilename <options>.

What is the use of dynamic sql in oracle

Dynamic SQL enables you to write programs that reference SQL statement whose full text is not known until runtime.

Write a pl/sql block to update the sal of an emp whose empno is 7369 and print the msg as ? record updated

begin
update employee(tablename) set salary=(salary)*o.1 where empno=7839;
dbms_output.put_line('record updated');
end;

If the application is running very slow? At what points you need to go about the database in order to improve the performance

For improving performance, we need to check the sql statementblocks , because for every sql satement execution transfor to sql engine and come back to plsq engine that process takes more time to process the plsql block

What is a database trigger ?name some usage of db trigger

Database trigger is stored PL/SQL program unit associated with a specific database table. Usages are Audit data modifications, Log events transparently, Enforce complex business rules Derive column values automatically, Implement complex security authorizations. Maintain replicate tables.

Is it possible to use tcl statements in db trigger? Why?

It is not possible. As triggers are defined for each table, if you use COMMIT of ROLLBACK in a trigger, it affects logical transaction processing.

yes WE can use COMMIT and  ROLLBACK triggers, but by using PRAGAMA AUTONAMOUS_TRANSATIONS. Now the transation treated as a autonomous tansation.

Advantages of package:

A. Modularity

  - Encapsulate related constructs.

B. Easier Application Design

  - Code and compile specification and body separately.

C. Hiding Information

  - Only the declarations in the pacakge specification

    are visible and accessible to application.

  - Private constructs in the package body are hidden

    and inaccesible.

  - All coding is hidden in the package body.

D. Added Functionality

  - Persistency of variables and cursors.

E. Better Performance

  - The entire package is loaded into memory when the

    package is first referenced.

  - There is only one copy in memory for all users.

  - The dependency hierarchy is simplified.

F. Overloading

  - Multiple subprograms of the same

ODBC Provider: ODBC Provides access to any data source that has an ODBC driver.

Unit Testing:

Unit testing focuses verification efforts on the smallest unit of software design module. The unit test is always white box oriented. The tests that occur as part of unit testing are testing the module interface, examining the local data structures, testing the boundary conditions, execution all the independent paths and testing error-handling paths.

      

 Integration Testing:

Integration testing is a systematic technique or construction the program structure while at the same time conducting tests to uncover errors associated with interfacing. Scope of testing summarizes the specific functional, performance, and internal design characteristics that are to be tested. It employs top-down testing and bottom-up testing methods for this case.

Performance Testing:

          Timing for both read and update transactions should be gathered to determine whether system functions are being performed in an acceptable timeframe