The Service on local computer started and then stopped Problum

Introduction:

Here I will explain how to solve the Service on local computer started and then stopped, some services stop automatically if there are not in use by other services or programs

Description:

In previous article I explained clearly how to create windows service and how to run windows service in scheduled intervals and how to install windows service in system. In my system I tried to install windows service completion writing the code but at that time I was unable to install windows service I got error like this


The Service on local computer started and then stopped ,Some services stop automatically if there are not in use by other services or programs.”


Now I will explain how to solve the Service on local computer started and then stopped, some services stop automatically if there are not in use by other services or programs.

To solve this problem we have two ways 

First Way

Start --> Run --> Type Services.msc and click enter button now you will get all the services in your computer after that select your service and right click on that and go to Properties

After that open Select Log On tab in that select Local System Account and click ok now your problem will solve


Otherwise check second way.

Second Way

First right click on My Computer and select Manage now computer management window will open
In that window select Application log and right click on it and select properties now another window will open in that change maximum log size to bigger value and check Overwrite events when needed

How to Install,UnInstall and Run Windows service using command Prompt

Introduction:


Here I will explain how to install windows service and how to start the windows service in our local machine.

Description:

In previous article I explained clearly how to create windows service and how to run windows service in scheduled intervals. Now I will explain how to install windows service in our system.

To install windows service in your follow these steps

Start --> All Programs --> Microsoft Visual Studio 2008 --> Visual Studio Tools --> Open Visual Studio Command Prompt

After open command prompt point to your windowsservice.exe file in your project
Initially in our command prompt we are able to see path like this

C:\Program Files\ Microsoft Visual Studio 9.0\VC > 

This path is relating to our visual studio installation path because during installation if you give different path this path should be different now we can move to folder which contains our windowsservice.exe file. After moving to exe file exists path my command prompt like this

After moving to our windowsservice.exe contains folder now type 

Installutil windowsservicesample.exe (Give your windows service exe file name) and now press enter button.

After type Installutil windowsservicesample.exe file that would be like this

After that the service will install successfully in your system.

Now I have question do you have idea on how to see installed windows services and how to start our windows service if you have idea good otherwise no need to panic just follow below steps

Start --> Control Panel --> Open Control Panel --> Select Administrative Tools --> Computer Management --> Services and Applications --> Services --> Open services

Now check for your windows service name and right click on that and select option start your windows service has started successfully 

That would be like this 

 
If we want to uninstall the installed windows service you just point to your service same as what I explained previously and type statement installutil /u and your service name
Installutil /u windowsservicesample.exe

How to Creatre Windows Service in C#.Net

Introduction:

Here I will explain what windows service is, uses of windows service and how to create windows service in c#.

Description:



Today I am writing article to explain about windows services. First we will see what a window service is and uses of windows service and then we will see how to create windows service.

What is Windows Service?

Windows Services are applications that run in the background and perform various tasks. The applications do not have a user interface or produce any visual output. Windows Services are started automatically when computer is booted. They do not require a logged in user in order to execute and can run under the context of any user including the system. Windows Services are controlled through the Service Control Manager where they can be stopped, paused, and started as needed.

Create a Windows Service 

Creating Windows Service is very easy with visual studio just follow the below steps to create windows service
Open visual studio --> Select File --> New -->Project--> select Windows Service
And give name as WinServiceSample



After give WinServiceSample name click ok button after create our project that should like this



In Solution explorer select Service1.cs file and change Service1.cs name to ScheduledService.cs because in this project I am using this name if you want to use another name for your service you should give your required name.

After change name of service open ScheduledService.cs in design view right click and select Properties now one window will open in that change Name value to ScheduledService and change ServiceName to ScheduledService. Check below properties window that should be like this


After change Name and ServiceName properties again open ScheduledService.cs in design view and right click on it to Add Installer files to our application.

The main purpose of using Windows Installer is an installation and configuration service provided with Windows. The installer service enables customers to provide better corporate deployment and provides a standard format for component management.

After click on Add installer a designer screen added to project with 2 controls: serviceProcessInstaller1 and ServiceInstaller1

Now right click on serviceProcessInstaller1 and select properties in that change Account to LocalSystem



After set those properties now right click on ServiceInstaller1 and change following StartType property to Automatic and give proper name for DisplayName property


After completion of setting all the properties now we need to write the code to run the windows services at scheduled intervals.

If you observe our project structure that contains Program.cs file that file contains Main() method otherwise write the Main() method like this in Program.cs file

/// <summary>
/// The main entry point for the application.
/// </summary>
static void Main()
{
ServiceBase[] ServicesToRun;
ServicesToRun = new ServiceBase[]
{
new ScheduledService()
};
ServiceBase.Run(ServicesToRun);
}
After completion of adding Main() method open ScheduledService.cs file and add following namespaces in codebehind of ScheduledService.cs file

using System.IO;
using System.Timers;
If you observe code behind file you will find two methods those are

protected override void OnStart(string[] args)
{
}

protected override void OnStop()
{
}
We will write entire code in these two methods to start and stop the windows service. Write the following code in code behind to run service in scheduled intervals
 
//Initialize the timer
Timer timer = new Timer();
public ScheduledService()
{
InitializeComponent();
}
//This method is used to raise event during start of service
protected override void OnStart(string[] args)
{
//add this line to text file during start of service
TraceService("start service");

//handle Elapsed event
timer.Elapsed += new ElapsedEventHandler(OnElapsedTime);

//This statement is used to set interval to 1 minute (= 60,000 milliseconds)

timer.Interval = 60000;

//enabling the timer
timer.Enabled = true;
}
//This method is used to stop the service
protected override void OnStop()
{
timer.Enabled = false;
TraceService("stopping service");
}
private void OnElapsedTime(object source, ElapsedEventArgs e)
{
TraceService("Another entry at "+DateTime.Now);
}
private void TraceService(string content)
{

//set up a filestream
FileStream fs = new FileStream(@"d:\ScheduledService.txt",FileMode.OpenOrCreate, FileAccess.Write);

//set up a streamwriter for adding text
StreamWriter sw = new StreamWriter(fs);

//find the end of the underlying filestream
sw.BaseStream.Seek(0, SeekOrigin.End);

//add the text
sw.WriteLine(content);
//add the text to the underlying filestream

sw.Flush();
//close the writer
sw.Close();
}

If you observe above code in OnStart method I written event ElapsedEventHandler this event is used to run the windows service for every one minute

After completion code writing build the application and install windows service. To install windows service check this post here I explained clearly how to install windows service and how to start windows service. 

Now the service is installed. To start and stop the service, go to Control Panel --> Administrative Tools --> Services.  Right click the service and select Start. 

Now the service is started, and you will be able to see entries in the log file we defined in the code.

Now open the log file in your folder that Output of the file like this

Reading from excel without Worksheet Name using oledbcommand in C#.Net

OleDbConnection dbConnection = new OleDbConnection (@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\BAR.XLS;Extended Properties=""Excel 8.0;HDR=Yes;""");
dbConnection.Open ();
try
{
    // Get the name of the first worksheet:
    DataTable dbSchema = dbConnection.GetOleDbSchemaTable (OleDbSchemaGuid.Tables, null);
    if (dbSchema == null || dbSchema.Rows.Count < 1)
    {
        throw new Exception ("Error: Could not determine the name of the first worksheet.");
    }
    string firstSheetName = dbSchema.Rows [0] ["TABLE_NAME"].ToString ();

    // Now we have the table name; proceed as before:
    OleDbCommand dbCommand = new OleDbCommand ("SELECT * FROM [" + firstSheetName + "]", dbConnection);
    OleDbDataReader dbReader = dbCommand.ExecuteReader ();

    // And so on...
}
finally
{
    dbConnection.Close ();
}

Explain About CURSORS in Oracle PL/SQL

Oracle PL/SQL Cursor



Introduction

Cursor is a temporary memory area (context area) where Oracle executes SQL statements. Oracle associates every SELECT statement with a cursor to hold the query information in this context area.

Types of Cursor

There are two types of cursors - implicit and explicit cursor.
Implicit cursor: Oracle automatically (implicit) controls or processes the information of SQL statement executed. In this process, the user is unaware of implicit cursor. Oracle automatically performs the OPEN, FETCH, and CLOSE operations.
Explicit cursor: Explicit cursor is used for the query that returns more than one row of data. These cursors are explicitly declared in the DECLARE section of the PL/SQL block. This declaration allows to sequentially process each row of data as the cursor returns it. In explicit cursor DECLARE,OPEN,FETCH,and CLOSE operations are done by the programmer.
The process of working with an explicit cursor:
  • Declare: The cursor is initialised into temporary memory area.
  • Open: The cursor is opened which is declared, and the temporary memory area is allotted.
  • Fetch: Cursor which is declared and opened can now retrieve rows from data.
  • Close: The CLOSE statement disables the cursor, and releases the temporary memory area.

Cursor Attributes

These attributes return useful information about the execution of a SQL statement. Cursor attributes can be used in procedural statements but not in SQL statements.
CURSOR ATTRIBUTE SYNTAX DESCRIPTION
%NOTFOUND
cursor_name%NOTFOUND
%NOTFOUND returns TRUE if last fetch did not return a row, Else FALSE if last fetch returns row.    
%FOUND
cursor_name%FOUND
%FOUND returns TRUE if the cursor is open, fetches the row till the last fetch. FALSE if last fetch did not return any row.
%ROWCOUNT
cursor_name%ROWCOUNT
%ROWCOUNT keeps track of fetched rows from cursor until it is closed.
%ISOPEN
cursor_name%ISOPEN
%ISOPEN returns TRUE if its cursor or cursor variable is open, otherwise, %ISOPEN returns FALSE.
The general syntax for creating a cursor is as follows :
CURSOR cursor_name IS select_statement;
  • cursor_name : name for the cursor
  • select_statement : select query which returns multiple rows
As we have gone through an introductory part , let's start exploring cursor with examples to understand more in depth. NOTE : In programming language, standardisation is the most important part. When you declare a cursor in declaration part, always start with initial c_cursor_name, it will always be clear that name refers to cursor.
Before we start with Explicit cursor, let us understand...

Record Types

Record is composite data structure, it is group of data items similar to the row of database table, each item with its own name and datatype. Assume the record is a variable which holds a table row or some columns of a table. If you want to learn more about RECORD, you can refer to PL/SQL User's Guide and Reference chapter 5.
PL/SQL supports only three types of records - table based, cursor based and programmer defined.
  • A table based record is one whose entire structure is similar to columns of a table.
  • A cursor based record is one whose structure is similar to items (elements) of a pre-defined cursor.
  • A cursor and record datatype created in declaration part of PL/SQL Block having the same variables with same datatype in the same order is called programmer defined record.
NOTE: To create a table based and cursor based record, always prefer to use %ROWTYPE attribute. The %ROWTYPE attribute provides a record type that represents a row in a database table. The record can store an entire row of data selected from the table or fetched from a cursor or cursor variable. To learn more about %ROWTYPE, refer to PL/SQL User's Guide and Reference chapter 2.

Example 1. Table based record

DECLARE
 vr_emp employees%ROWTYPE;
 /* variable vr_emp is a record type existing database table employees.
    record vr_emp has a similar structure to row of the employees table.*/ 
BEGIN
 SELECT * 
 INTO vr_emp  -- INTO clause always notifies only single row can be fetch
 FROM employees
 WHERE employee_id = 100;
 /* to display each element of record,reference each attribute of record with dot notation*/
 DBMS_OUTPUT.PUT_LINE('Employee Details : '||vr_emp.employee_id
 ||' '||vr_emp.first_name||' '||vr_emp.last_name||' '||vr_emp.salary);
END;
Employee Details : 100 Steven King 24000

Example 2. Cursor based record

DECLARE
 CURSOR c_emp_detail IS 
   SELECT employee_id,first_name,last_name,salary
   FROM employees;
   rec_emp_detail c_emp_detail%ROWTYPE;
   /* A cursor based record is based on elements of pre-Defined cursor.
      A cursor based record can be only declared after its corresponding
      cursor, else an error occurs.*/
BEGIN
 OPEN c_emp_detail;
   LOOP
    FETCH c_emp_detail INTO rec_emp_detail;
    EXIT WHEN c_emp_detail%NOTFOUND; -- cursor attribute to exit when no rows found to fetch.
    DBMS_OUTPUT.PUT_LINE('Employees Details : '||' '||rec_emp_detail.employee_id
    ||' '||rec_emp_detail.first_name||' '||rec_emp_detail.last_name);
   END LOOP;
    DBMS_OUTPUT.PUT_LINE('Total number of rows : '||c_emp_detail%ROWCOUNT);
    -- cursor attribute to find the total number of rows executed.
 CLOSE c_emp_detail;
END;
Employees Details :  198 Donald OConnell
Employees Details :  199 Douglas Grant
Employees Details :  200 Jennifer Whalen

-----------------------------

Employees Details :  196 Alana Walsh
Employees Details :  197 Kevin Feeney
Total number of rows : 107

Example 3. Programmer defined record

DECLARE
 CURSOR c_emp_detail IS
 SELECT employee_id,first_name,last_name,salary
 FROM employees;
/*declaring a record datatype, with same datatype of tables of database using %TYPE attribute,
  with same order of corresponding cursor */
TYPE type_rectype IS RECORD
    (emp_id employees.employee_id%TYPE,
     f_name employees.first_name%TYPE,
     l_name employees.last_name%TYPE,
     s_salary employees.salary%TYPE
    );
 rec_type type_rectype; --variable of record datatype.

BEGIN
 OPEN c_emp_detail;
  LOOP
   FETCH c_emp_detail INTO rec_type; -- Fetches the cursor into record variable.
   EXIT WHEN c_emp_detail%NOTFOUND;
-- variable is part of each record datatype,so to reference it use dot notation in DBMS_OUTPUT.
    DBMS_OUTPUT.PUT_LINE('Employees Details : '||' '||rec_type.emp_id
    ||' '||rec_type.f_name||' '||rec_type.l_name||' '||rec_type.s_salary);        
  END LOOP; 
    DBMS_OUTPUT.PUT_LINE('Total number of Employees : '||c_emp_detail%ROWCOUNT);
 CLOSE c_emp_detail;
END;
Employees Details :  198 Donald OConnell 2600
Employees Details :  199 Douglas Grant 2600
Employees Details :  200 Jennifer Whalen 4400

-----------------------------

Employees Details :  196 Alana Walsh 3100
Employees Details :  197 Kevin Feeney 3000
Total number of Employees : 107

Cursors in Nested Loops

Before going through Example 4, please make sure you have a better understanding of Nested loop concept, because Cursors in Nested Loops concept is absolutely the same as Nested Loops. To learn more about Loops, refer to PL/SQL User's Guide and Reference chapter 4.

Example 4. Cursors in Nested Loops

DECLARE
 CURSOR c_dept IS 
  SELECT * 
  FROM departments
  WHERE manager_id IS NOT NULL
  ORDER BY department_name;
  r_dept c_dept%ROWTYPE;

-- Declaration of department cursor and record variable.
  
  CURSOR c_emp (c_dept_no departments.department_id%TYPE) IS
  SELECT *
  FROM employees
  WHERE department_id = c_dept_no;
  r_emp c_emp%ROWTYPE;

-- Declaration of employees cursor and record variable.
  
BEGIN
   OPEN c_dept;
   LOOP
     FETCH c_dept INTO r_dept;
      EXIT WHEN c_dept%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('----------------------------------');
      DBMS_OUTPUT.PUT_LINE('Department Name : '||r_dept.department_name);
      DBMS_OUTPUT.PUT_LINE('----------------------------------');
          OPEN c_emp(r_dept.department_id);
           LOOP
            FETCH c_emp INTO r_emp;
             EXIT WHEN c_emp%NOTFOUND;
                DBMS_OUTPUT.PUT_LINE('Employees Details : '||r_emp.employee_id
   ||' '||r_emp.first_name||' '||r_emp.last_name||' '||r_emp.salary);
           END LOOP;
          CLOSE c_emp;  
   END LOOP;
   CLOSE c_dept;
END;
----------------------------------
Department Name : Accounting
----------------------------------
Employees Details : 205 Shelley Higgins 12000
Employees Details : 206 William Gietz 8300

-----------------------------

----------------------------------
Department Name : Shipping
----------------------------------
Employees Details : 198 Donald OConnell 2600
Employees Details : 199 Douglas Grant 2600

-----------------------------
The c_emp cursor specifies the parameter in c_dept_no. Each time cursor c_emp is called, it returns only the list of employees of which the department_id specified by the parameter (c_dept_no). In Example 4, if we segregate the individual cursor c_emp, it becomes a parameterised cursor.

Cursor For Loops

In Cursor for loops, the process of opening, fetching and closing is handled implicitly. This makes the programmer code and maintain the blocks easily.
In cursor for loop, before each iteration PL/SQL fetches into implicitly declared record.
The sequence of statements inside the loop is executed once for each row that satisfies the query.
When loop is left, the cursor is automatically closed.
The cursor is closed even if you use an EXIT or GOTO statement to leave the loop before all rows are fetched.

Example 5. Implicit cursor for loop

BEGIN
  FOR item IN(SELECT department_name,d.department_id,last_name,job_id,salary
              FROM departments d JOIN employees e
              ON e.department_id = d.department_id
              WHERE JOB_ID       = 'IT_PROG'
              AND salary         > 4800)
    LOOP
      DBMS_OUTPUT.PUT_LINE(item.last_name||' '||item.department_name
      ||' '||item.department_id||' '||item.job_id||' '||item.salary);
    END LOOP;
END;
Hunold IT 60 IT_PROG 9000
Ernst IT 60 IT_PROG 6000
If you need the same query to reference from different parts of the same procedure, you can declare a cursor with that specific query,and get the results using cursor for loop. For this, I am going to use the same select query from Example 5.

Example 6. Explicit cursor for loop

DECLARE
 CURSOR c_detail IS
  SELECT department_name,d.department_id,last_name,job_id,salary
              FROM departments d JOIN employees e
              ON e.department_id = d.department_id
              WHERE JOB_ID       = 'IT_PROG'
              AND salary         > 4800;
              
BEGIN
  FOR item IN c_detail
    LOOP
      DBMS_OUTPUT.PUT_LINE(item.last_name||' '||item.department_name
      ||' '||item.department_id||' '||item.job_id||' '||item.salary);
    END LOOP;
END;
Hunold IT 60 IT_PROG 9000
Ernst IT 60 IT_PROG 6000

Example 7. Nested Cursors Using Cursor FOR Loops

If you notice, example 4 and example 7 outputs are the same, the difference is example 7. We are using cursor for loop where record variable declares r_dept and r_emp , opening fetching and closing is done automatically by each loop iteration until all rows are fetched according to the specific query in cursor.
The variable v_dept_id is initialized to be the department_id of the current record of the c_dept cursor. The c_dept cursor ties in the c_emp cursor by means of this variable.
Thus, when the cursor c_emp is processed, it is retrieving employees who have the department_id of the current record for the c_dept cursor.
Each iteration of the c_dept cursor will execute the DBMS_OUTPUT only once. The DBMS_OUTPUT will be executed once for each iteration of the c_emp cursor loop, producing a line of output for each employee.
DECLARE
  v_dept_id departments.department_id%TYPE;
 CURSOR c_dept IS
  SELECT * 
  FROM departments
  WHERE manager_id IS NOT NULL
  ORDER BY department_name;
 CURSOR c_emp IS
  SELECT * 
  FROM employees
  WHERE department_id = v_dept_id;
  
BEGIN
    FOR r_dept IN c_dept
    LOOP
      v_dept_id := r_dept.department_id;
      DBMS_OUTPUT.PUT_LINE('----------------------------------');
      DBMS_OUTPUT.PUT_LINE('Department Name : '||r_dept.department_name);
      DBMS_OUTPUT.PUT_LINE('----------------------------------');
           FOR r_emp IN c_emp 
           LOOP
             DBMS_OUTPUT.PUT_LINE('Employee Name : '||r_emp.last_name);
           END LOOP;   
    END LOOP;
END;
----------------------------------
Department Name : Accounting
----------------------------------
Employee Name : Higgins
Employee Name : Gietz
----------------------------------

--------------------

----------------------------------
Department Name : Shipping
----------------------------------
Employee Name : OConnell
Employee Name : Grant

--------------------

Example 8. Using Parameters with Nested Cursors For Loops

CREATE OR REPLACE PROCEDURE print_emp_dept(v_lo_id IN locations.location_id%TYPE)
IS
  v_flag departments.department_id%TYPE;
   CURSOR c_locations IS
   SELECT * 
   FROM locations
   WHERE location_id = v_lo_id;  
     
   CURSOR c_departments(v_loc_id locations.location_id%TYPE) IS
    SELECT l.location_id,department_name,department_id
    FROM locations l JOIN departments d
    ON l.location_id = d.location_id
    WHERE l.location_id = v_loc_id
    AND d.manager_id IS NOT NULL;
    
   CURSOR c_employees (v_dept_id departments.department_id%TYPE,
                       v_loc_id locations.location_id%TYPE) IS
    SELECT d.department_id,employee_id,first_name,last_name,salary,job_id,city
    FROM locations l JOIN departments d
    ON   l.location_id = d.location_id  
    JOIN employees e
    ON  d.department_id = e.department_id
    WHERE d.department_id = v_dept_id
    AND l.location_id = v_loc_id;
BEGIN
     FOR r_location IN c_locations
     LOOP 
         DBMS_OUTPUT.PUT_LINE
         ('Location ID : '||r_location.location_id||
         ' Belong''s to '||r_location.city||' city');
         DBMS_OUTPUT.PUT_LINE('In city '||r_location.city||' '||'Departments are ');
           FOR r_department IN c_departments(r_location.location_id)           
            LOOP 
              DBMS_OUTPUT.PUT_LINE('-----------------------------------------');
              DBMS_OUTPUT.PUT_LINE('Department ID :
              '||r_department.department_id||' '||'Location ID : '||
     r_department.location_id||' '||
     'Department Name : '||r_department.department_name);
              DBMS_OUTPUT.PUT_LINE('-----------------------------------------');   
                 v_flag := r_department.department_id;
                 EXIT WHEN v_flag IS NULL;  
                 
                  FOR r_employee IN c_employees 
                  (r_department.department_id,r_location.location_id)
                  LOOP
                   DBMS_OUTPUT.PUT_LINE(r_employee.employee_id||' 
                   '||r_employee.first_name||' '||
                   r_employee.last_name||' work''s in city '||r_employee.city);
                  END LOOP; 
                 
            END LOOP;           
     END LOOP; 
     IF v_flag IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('Not Available');
     END IF;
END print_emp_dept;
Call the procedure:
CALL print_emp_dept(3000);

BEGIN
 print_emp_dept(1700);
END;
Location ID : 1400 Belong's to Southlake city
In city Southlake Departments are 
-----------------------------------------
Department ID :60 Location ID : 1400 Department Name : IT
-----------------------------------------
103 Alexander Hunold work's in city Southlake
104 Bruce Ernst work's in city Southlake
105 David Austin work's in city Southlake
106 Valli Pataballa work's in city Southlake
107 Diana Lorentz work's in city Southlake
The grandparent cursor, c_locations is declared. It is a collection of locations and takes parameter while call of procedure print_emp_dept with a location_id is available in database.
The parent cursor, c_departments is declared. It takes in the parameter of location_ID to generate a list of departments that belongs to parameter location_ID.
The child cursor, c_employees takes in two parameters, department_id and location_id. In this way, it generates a list of employees working with different departments but in the same location_id.
The grandparent cursor loop begins, and only the city name and location_id is displayed with DBMS_OUTPUT. The parent cursor loop begins. It takes the parameter of location_id from the grandparent cursor where department_id, location_id and department_name is displayed.
The child cursor loop begins. It takes in the parameter of department_id from the parent cursor and location_id from the grandparent cursor.The employee_id and employees name is displayed in which city they work because of the parameter which has been used from grandparent cursor.
In this complete process, the child cursor loop ends first, then parent cursor loop ends, then grandparent cursor loop ends.

Example 9. For UPDATE and WHERE CURRENT CLAUSE

The FOR UPDATE clause is only used in cursor, when update or delete statements are used for database tables. Normally, when programmer executes SELECT statement, there is no locking of rows. The main aim to use FOR UPDATE clause is to lock rows when performing update or delete statements inside the cursor, and restrict other users to perform any updation in particular database tables. Once the updation is done inside the cursor, then COMMIT or ROLLBACK placed inside the execution block releases the lock. Now FOR UPDATE clause with particular Column name i.e.. FOR UPDATE salary will only lock salary column according to select statement even if there is join condition, else FOR UPDATE clause will lock entire row of that particular table.
NOWAIT is an optional keyword, if the rows are already locked by another programmer, then control is immediately returned to programmer so that meanwhile he can do other work before trying again. If you omit the keyword, then the wait may be a long period of time.
The WHERE CURRENT OF clause can be used only if FOR UPDATE clause is used in cursor.
The WHERE current of clause only references the cursor which fetches the latest row.
The WHERE CURRENT OF CLAUSE is useful to eliminate the where condition in update clause.
First, I have created a demo table, because I don't want to change data in the actual table of database.
create table emp1 as select * from employees;
create table dept1 as select * from departments;
DECLARE
 CURSOR c_sal_update IS
   SELECT employee_id,first_name,last_name,job_id,department_name,e.department_id,salary
   FROM dept1 d , emp1 e
   WHERE e.department_id = 80
   FOR UPDATE OF salary NOWAIT;
   
   rec_sal c_sal_update%ROWTYPE;
   
BEGIN
   OPEN c_sal_update;   -- rows are locked.
     LOOP
       FETCH c_sal_update INTO rec_sal;      
       EXIT WHEN c_sal_update%NOTFOUND;
       
       IF rec_sal.job_id = 'SA_MAN' THEN
         UPDATE emp1 
          SET salary = rec_sal.salary + 1000
          WHERE CURRENT OF c_sal_update; 
       END IF;
     END LOOP;
     COMMIT;  -- rows are unlocked.
   CLOSE c_sal_update;
END;
OUTPUT
SELECT employee_id,first_name,last_name,job_id,department_id,salary
FROM emp1
WHERE job_id = 'SA_MAN'
AND department_id = 80;

EMPLOYEE_ID FIRST_NAME      LAST_NAME         JOB_ID     DEPARTMENT_ID   SALARY
----------- --------------- ----------------- ---------- ------------- --------
        145 John            Russell           SA_MAN                80    15000 
        146 Karen           Partners          SA_MAN                80    14500 
        147 Alberto         Errazuriz         SA_MAN                80    13000 
        148 Gerald          Cambrault         SA_MAN                80    12000 
        149 Eleni           Zlotkey           SA_MAN                80    11500 

Example 10. Cursor attributes with explicit cursor

DECLARE
  CURSOR c_high_sal IS
     SELECT *
     FROM (SELECT employee_id,first_name,last_name,salary
           FROM employees ORDER BY salary DESC)
     WHERE ROWNUM < 11;   
     
   high_sal c_high_sal%ROWTYPE;
   
BEGIN
   IF NOT c_high_sal%ISOPEN THEN
     DBMS_OUTPUT.PUT_LINE('Cursor is Closed');
   END IF;  

  OPEN c_high_sal; 
     IF c_high_sal%ISOPEN THEN
        DBMS_OUTPUT.PUT_LINE('Cursor is open');
     END IF;
       
     LOOP
       FETCH  c_high_sal INTO high_sal;  
         IF c_high_sal%FOUND THEN                
           DBMS_OUTPUT.PUT_LINE(high_sal.employee_id||' '||high_sal.first_name
           ||' '||high_sal.last_name||' '||high_sal.salary);
         ELSE   
            EXIT; -- the same as exit when c_high_sal%NOTFOUND;
         END IF;           
     END LOOP;
            DBMS_OUTPUT.PUT_LINE(' Number of rows fetched : '||c_high_sal%ROWCOUNT);
  
  CLOSE c_high_sal;
  
  IF NOT c_high_sal%ISOPEN THEN
     DBMS_OUTPUT.PUT_LINE('Cursor is closed ');
  END IF;     
END;
Cursor is Closed
Cursor is open
100 Steven King 24000
101 Neena Kochhar 17000
102 Lex De Haan 17000
145 John Russell 14000
146 Karen Partners 13500
201 Michael Hartstein 13000
205 Shelley Higgins 12000
108 Nancy Greenberg 12000
147 Alberto Errazuriz 12000
168 Lisa Ozer 11500
 Number of rows fetched : 10
Cursor is closed
The cursor declared in the preceding example executes highest salary paid employees. Using sub-query in cursor. declare high_sal with %ROWTYPE.
The cursor is not yet open, %ISOPEN attribute is used to see whether cursor is open with help of IF-THEN statement, where %ISOPEN turn TRUE.
Cursor is open, now to show with DBMS_OUTPUT, %ISOPEN is used with IF-THEN statement, where %ISOPEN turn TRUE.
Now loop start and cursor c_high_sal is fetched into variable high_sal, again %FOUND attribute is used with IF-THEN-ELSE statement to show the list of highest paid employees, where %FOUND attribute turns true till the last fetch of cursor and exits from the loop.
Once it is out of the loop, DBMS_OUTPUT fetches the last number of row of loop and shows the figure with %ROWCOUNT attribute. Cursor is closed.
After the cursor is closed, %ISOPEN is used with the help of IF-THEN statement to show the cursor is closed.

Example 11. Cursor attributes with Implicit cursor

DECLARE
 v_dept departments.department_id%TYPE := 270;
 v_dept_name departments.department_name%TYPE;
BEGIN

DELETE FROM dept1 WHERE department_id = v_dept;
 IF SQL%FOUND THEN
   INSERT INTO dept1 VALUES(270,'Personnel',200,1700);
 END IF;
  DBMS_OUTPUT.PUT_LINE('Number of rows inserted : '||SQL%ROWCOUNT);
  
  SELECT department_name INTO v_dept_name
  FROM dept1
  WHERE department_id = 270;
    DBMS_OUTPUT.PUT_LINE('Department Name : '||v_dept_name);    
END;
Number of rows inserted : 1
Department Name : Personnel
An implicit cursor can tell you how many rows were affected by an update, insert and delete with the help of %FOUND and %ROWCOUNT attribute.
We have already created a table named dept1 in the previous example. In the following example, variables v_dept and v_dept_name are reference by table departments, as we are working on table dept1,it will not give any error unless the structure or datatype is different from the other table, i.e., departments and dept1.
Deleting from dept1 table where department_id is 270.
Using SQL instead of cursor name as it is implicit cursor with no cursor name.
Use %FOUND attribute with IF-THEN statement to insert row in dept1 table with new department_name, i.e., Personnel. Once it is inserted to know how many rows were affected, use %ROWCOUNT in DBMS_OUTPUT.
In same execution block using v_dept_name, we are fetching new department name with select statement.
I have not placed any commit because I don't want to update any row so later I will rollback it.
To learn more about implicit cursor and its attributes, refer to PL/SQL User's Guide and Reference chapter 6.
NOTE: Between implicit and explicit cursor, the fastest is implicit cursor, because opening and closing of cursor and looping is automatically handled by Oracle. Cursor for loop is also an implicit cursor where opening and closing of cursor in done implicitly. But cursor should be used according to requirement because each type of cursor has its own advantages and disadvantages. To know more about this, you can refer to Steven Feuerstein's book PL-SQL Programming 5th edition.
The concepts like Refs cursor, cursor with bulk operations, cursor with packages and cursor with exception are not covered which is out of scope of this article. I may write my next article covering all these concepts and thank you for reading this article.