How To Install NRPE On Redhat or CentOS Linux Server

Need personal assistance on Nagios? Please contact me at immukesh72@gmail.com at very nominal charges

Step 01: Install NRPE Plugins in Nagios monitoring host

wget http://downloads.sourceforge.net/project/nagios/nrpe-2.x/nrpe-2.15/nrpe-2.15.tar.gz

tar -zxvf nrpe-2.15.tar.gz

cd nrpe-2.15

./configure --enable-command-args --with-nagios-user=nagios --with-nagios-group=nagios --with-ssl=/usr/bin/openssl --with-ssl-lib=/usr/lib/x86_64-linux-gnu

Now build and install NRPE and its xinetd startup script with these commands:

make all
sudo make install
sudo make install-xinetd
sudo make install-daemon-config

  
Step 01: On client server that you want to monitor, install the EPEL repository:
    NRPE packages and plugins are available under epel yum repository

sudo yum install epel-release
or
rpm -Uvh http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

RPM (Red Hat Package Manager) is an default open source and most popular package management utility for Red Hat based systems like (RHEL, CentOS and Fedora). The tool allows system administrators and users to install, update, uninstall, query, verify and manage system software packages in Unix/Linux operating systems.
   
Now install Nagios Plugins and NRPE:

yum --enablerepo=epel -y install nrpe nagios-plugins

update private IP address of your Nagios server in allowed host

sudo vi /etc/nagios/nrpe.cfg
allowed_hosts=127.0.0.1,10.132.224.168

Add new commands or update existing commands like below in same file.

 command[check_root_disk]=/usr/lib/nagios/plugins/check_disk -w 20% -c 10% -p /
 command[check_load]=/usr/lib/nagios/plugins/check_load -w 15,10,5 -c 30,25,20

Step 02: Start NRPE Service:
Reboot nrpe once to read new configuration, Also configure nrpe to auto start on system boot.

service nrpe start
chkconfig nrpe on

Step 03: Test NRPE from Nagios server
Login to Nagios server and run the following command. On successful connection it will print the version of nrpe package.

/usr/local/nagios/libexec/check_nrpe -H 10.132.224.168
NRPE v2.15

Need personal assistance on Nagios? Please contact me at immukesh72@gmail.com at very nominal charges

How to Install Nagios on RHEL,CentOS or Fedora Linux Operating System.

In this tutorial i will tell you how to install Nagios Core 4.1.1 and Nagios Plugins 2.1.1 on RHEL Linux server. Following all steps were performed and tested on RHEL 7.3 Linux Server.

Step 01 : Disable SELinux
Disable the SELinux by running the following command with root user.

$ setenforce 0

Basically this a security feature of the Linux kernel. It is designed to protect the server against misconfigurations and/or compromised daemons.

Note: setenforce is a command line utility that is used to switch the mode SELinux is running in from enforcing to permissive and vice versa without requiring a reboot.

Modify /etc/selinux/config and change enforcing to disabled

Need personal assistance on Nagios? Please contact me at immukesh72@gmail.com at very nominal charges

Step 02: Install the required packages
Make sure you have installed the following packages on your Linux system before continuing.
Apache 2
PHP
GCC compiler and development libraries
GD development libraries

If you did not install then please run the following commands:

sudo yum install httpd php php-cli gcc glibc glibc-common gd gd-devel net-snmp openssl-devel wget unzip -y

Note:
GCC compiler and development libraries :
GNU Compiler Collection. gcc, formerly known as the GNU C Compiler, compiles multiple languages (C, C++, Objective-C, Ada, FORTRAN, and Java) to machine code.

GD development libraries :
The GD library is a graphics drawing library that provides tools for manipulating image data. In Shopp, the GD library is used to process images for generating gallery preview and thumbnail size images automatically.

net-snmp:
Simple Network Management Protocol (SNMP) is a widely used protocol for monitoring the health and welfare of network equipment (eg. routers), computer equipment and even devices like UPSs.
And helps Nagios for notification emails.

openssl-devel:
OpenSSL is a toolkit for the Transport Layer Security (TLS) and Secure Sockets Layer (SSL) protocols. It is also a general-purpose cryptography library.

php-cli : PHP Command Line Interface

Step 03: Create Account Information
Create the Nagios user

  useradd nagios

Create a new nagcmd group for allowing external commands to be submitted through the web interface.

  groupadd nagcmd

Add both the nagios user and the apache user to the group nagcmd.

  usermod -a -G nagcmd nagios
  usermod -a -G nagcmd apache

Where :
The usermod command modifies the system account files to reflect the changes that are specified on the command line.
-a, --append
           Add the user to the supplementary group(s). Use only with the -G option.

-G, --groups GROUP1[,GROUP2,...[,GROUPN]]]
           A list of supplementary groups which the user is also a member of.
         
Step 04: Download and Install Nagios
Run the following commands to download and extract Nagios and Nagios plugins in /tmp directory.

cd /tmp

wget https://assets.nagios.com/downloads/nagioscore/releases/nagios-4.1.1.tar.gz

wget http://www.nagios-plugins.org/download/nagios-plugins-2.1.1.tar.gz

tar zxf nagios-4.1.1.tar.gz

tar zxf nagios-plugins-2.1.1.tar.gz

Go to nagios-4.1.1 directory and start compiling Nagios.
    cd nagios-4.1.1

Run the Nagios configure script, passing the name of the group you created earlier like so:

./configure --with-command-group=nagcmd

Compile the Nagios source code.

make all

Install binaries, init script, sample config files and set permissions on the external command directory.

make install
make install-init
make install-config
make install-commandmode

Configure the Web Interface

make install-webconf

Same way install the Nagios plugin as well.

cd /tmp/nagios-plugins-2.1.1

./configure --with-nagios-user=nagios --with-nagios-group=nagios --with-openssl

make all

make install

Step 05: Creating a password for nagiosadmin
We now need to create a password for the nagiosadmin user. This will be used to login to your core web GUI.

htpasswd -c /usr/local/nagios/etc/htpasswd.users nagiosadmin

Step 06: Start Nagios and Apache server

service httpd start
service nagios start (or) /etc/init.d/nagios start

Step 07: Login to the Web Interface
Enter the following url into the browser, replace your public IP of nagios server. It will ask to enter username and password. In this configuration we have username as nagiosadmin and password also as nagiosadmin.

http://<public_ip_of_nagios_server>/nagios/

Step 08: Troubleshooting
If you cannot access the Nagios web page, it may be related to your firewall rules. The following command will open up port 80 on your Nagios Core machine.

firewall-cmd --zone=public --add-port=80/tcp --permanent
firewall-cmd --reload

If you don't have firewall-cmd then try the following rule into your iptables

iptables -A INPUT -p tcp --dport http -j ACCEPT
iptables -A INPUT -p tcp --dport https -j ACCEPT
iptables-save > /etc/sysconfig/iptables
/etc/init.d/iptables restart

If you are still unable to access the web GUI, your web server may be only listening on IPv6. Modify /etc/httpd/conf/httpd.conf
and look for the part that says 'Listen :80'. Modify it to be 'Listen 0.0.0.0:80'. Then, run service httpd restart.

Need personal assistance on Nagios? Please contact me at immukesh72@gmail.com at very nominal charges

PL/SQL Examples

PL/SQL Example:

     DECLARE
          v_productcategory VARCHAR2(20) :='Mobile Phone';
          v_itemqty PLS_INTEGER := 1000;
          v_itemprice NUMBER(7,2) DEFAULT 60.50;
          v_transactiondate DATE:= SYSDATE;
          v_billingdate TIMESTAMP := SYSTIMESTAMP;
          v_test BOOLEAN:=TRUE;
        BEGIN
          DBMS_OUTPUT.PUT_LINE(v_productcategory);
          DBMS_OUTPUT.PUT_LINE(v_transactiondate);
          DBMS_OUTPUT.PUT_LINE (v_billingdate);
          DBMS_OUTPUT.PUT_LINE ('qty ' ||v_itemqty);
          END;
         /
---------------------------------
--use of anchored variable
DECLARE
    v_studentid student.studentid%TYPE; --Mapping from database
    v_marks NUMBER(3) NOT NULL :=89;
    v_totalmarks v_marks%TYPE :=350; --mapping from previously declared variable
BEGIN
    v_studentid :='S001';
    DBMS_OUTPUT.PUT_LINE(v_studentid);
    DBMS_OUTPUT.PUT_LINE(v_marks);
    DBMS_OUTPUT.PUT_LINE(v_totalmarks);
END;
-----------------------------------------------------------------------
--Bind variable
VARIABLE g_doctorid VARCHAR2(20);
BEGIN
    :g_doctorid :='&docid';
END;
SQL> PRINT g_itemid
    G_ITEMID
    ----------------
    STN001
-----------------------------------------------------------------------
--subtitutional variable
DEFINE g_itemid ="PEN";
DECLARE
    v_itemname VARCHAR2(20) :='&g_itemid';
BEGIN
    DBMS_OUTPUT.PUT_LINE(v_itemname);
END;
/
-----------------------------------------------------------------------
--IF ,ELSIF ,ELSE EXAMPLE : to find the largest number among 3
DECLARE
    v_num1 NUMBER :='&num1';
    v_num2 v_num1%TYPE :='&num2';
    v_num3 v_num2%TYPE :='&num3';
BEGIN
    DBMS_OUTPUT.PUT_LINE('The 3 numbers are :'||v_num1||',  '||v_num2||',  '||v_num3);
    IF (v_num1 > v_num2 AND v_num1 > v_num3) THEN
        DBMS_OUTPUT.PUT_LINE(v_num1 ||' is the largest number');
    ELSIF ( v_num2 > v_num1 AND v_num2 > v_num3) THEN
        DBMS_OUTPUT.PUT_LINE(v_num2 ||' is the largest number');
    ELSE
        DBMS_OUTPUT.PUT_LINE(v_num3 ||' is the largest number');
    END IF;
END;
-----------------------------------------------------------------------
--LOOP EXAMPLE(1)

DECLARE
    v_number NUMBER(10) :=1;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE('NUMBER: '||v_number);
        v_number := v_number + 1;
        EXIT WHEN v_number > 5;
    END LOOP;
END;

--LOOP EXAMPLE(2) --Numeric FOR Loop
DECLARE
    v_num NUMBER ;--declaration is optional
BEGIN
    FOR v_price IN 1..5
    LOOP
        DBMS_OUTPUT.PUT_LINE('v_price: '||v_price);
    END LOOP;
END;

--LOOP EXAMPLE(3) --Numeric FOR Loop IN reverese

BEGIN
  FOR v_price IN REVERSE 1..5
  LOOP
    DBMS_OUTPUT.PUT_LINE('Price: '||v_price);
  END LOOP;
END;
       
--LOOP EXAMPLE(3) -- while loop
DECLARE
    v_price NUMBER(5) :=1;
BEGIN
    WHILE v_price <= 5
    LOOP
        DBMS_OUTPUT.PUT_LINE('Price: '||v_price);
        v_price := v_price + 1;
    END LOOP;
END;
--------------------------------------------------------------
--use of select statement
--fetching few columns
DECLARE
    v_hostelid hostel.hostelid%TYPE;
    v_hostelfee hostel.hostelfee%TYPE;
BEGIN
    SELECT hostelid,hostelfee INTO v_hostelid,v_hostelfee FROM hostel WHERE studentid='S001';
    DBMS_OUTPUT.PUT_LINE('hostel id :=' || v_hostelid);
    DBMS_OUTPUT.PUT_LINE('hostel fee :=' || v_hostelfee);
END;

Rules :
1.Only one row value can be returned to the variable_list
2.If no value is returned then the “No data found” exception is thrown
3.If more than one record is returned then the “Exact fetch returns more than requested number of rows” exception is thrown.


--Composite datatype : USING ROWTYPE
DECLARE
    v_hostelrec hostel%ROWTYPE;
BEGIN
    v_hostelrec.studentid :='&hostelid';
    SELECT * INTO v_hostelrec FROM hostel WHERE studentid=v_hostelrec.studentid;
    DBMS_OUTPUT.PUT_LINE('hostel id :=' || v_hostelrec.hostelid);
    DBMS_OUTPUT.PUT_LINE('room no :=' || v_hostelrec.roomno);
    DBMS_OUTPUT.PUT_LINE('student id :=' || v_hostelrec.studentid);
    DBMS_OUTPUT.PUT_LINE('hostel fee :=' || v_hostelrec.hostelfee);
END;

--------------------------------------------------------------------------------------------------------
--Using SQL INSERT in PL/SQL 
--Can’t I write a insert statement as shown below to insert the record into the supplier table?
--yes we can insert
    INSERT INTO supplier(supplierid, suppliername, suppliercontactNo ) 
                                                VALUES ('&supplierid', '&suppliername', '&suppliercontactno');

--UPDATE
 BEGIN
   UPDATE supplier SET suppliercontactno = '0012244' WHERE supplierid='S001';
 END;
Write a PL/SQL block to insert the values from user.

--Using SQL DELETE in PL/SQL
BEGIN
  DELETE FROM supplier WHERE supplierid='S001';
END;

---------------------------------------------------------------------------
                      --PREDEFINED EXCEPTION--
---------------------------------------------------------------------------
--Predefined Oracle Server Exception
ORA-1403    NO_DATA_FOUND         SELECT statement matches no rows
ORA-1422    TOO_MANY_ROWS         SELECT statement matches more than one rows
ORA-0001    DUP_VAL_ON_INDEX      Uniq constraints voilated
ORA-1476    ZERO_DIVIDE           Division by zero
ORA-6502    VALUE_ERROR           Truncation,arithmetic error
ORA-1722    INVALID_NUMBER        Conversion to the number failed ex.2A is not valid

--NO_DATA_FOUND - Predefined Exception
DECLARE
    v_hostelid hostel.hostelid%TYPE;
    v_hostelfee hostel.hostelfee%TYPE;
    v_studentid hostel.studentid%TYPE;
BEGIN
    v_studentid :='&stdid';
    SELECT hostelid,hostelfee INTO v_hostelid,v_hostelfee FROM hostel WHERE studentid=v_studentid;
    DBMS_OUTPUT.PUT_LINE('hostel id :=' || v_hostelid);
    DBMS_OUTPUT.PUT_LINE('hostel fee :=' || v_hostelfee);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('STUDENT IS NOT EXIST');
    WHEN VALUE_ERROR THEN
        DBMS_OUTPUT.PUT_LINE('STUDENT ID IS TOO LARGE');
END;
/

--If the input entered is very large, truncation happens resulting in value error
--INVALID_NUMBER predefined exception
--Inserting a record to billing table As ‘X’ a character value cannot be converted to integer value for billno an INVALID_NUMBER exception is thrown

----------------------------------------------------------------------------------------------
                                --NON-PREDEFINED EXCEPTION
----------------------------------------------------------------------------------------------
--USE OF PRAGMA EXCEPTION_INIT

DECLARE
    e_MissingNull Exception;
    PRAGMA EXCEPTION_INIT(e_MissingNull,-1400);
BEGIN
    INSERT INTO applicant (applicantid) VALUES (NULL);
EXCEPTION
    WHEN e_MissingNull THEN
    DBMS_OUTPUT.PUT_LINE('NULL VALUE CANT INSERT');
END;
/

--User-defined Exception
--Given an invalid itemid display appropriate error message If the given itemid is invalid, display Invalid Itemid
SET VERIFY OFF
SET SERVEROUTPUT ON
DECLARE
    v_studentid hostel.studentid%TYPE;
    v_count NUMBER;
    e_Invalid_Studentid EXCEPTION;
BEGIN
    v_studentid :='&studentid';
    SELECT count(*) INTO v_count FROM hostel WHERE studentid=v_studentid;
    IF v_count = 0 THEN
        RAISE e_Invalid_Studentid;
    END IF;
        DBMS_OUTPUT.PUT_LINE('Valid student_d');
EXCEPTION
        WHEN e_Invalid_Studentid THEN
        DBMS_OUTPUT.PUT_LINE('invalid student id');
END;
/

--NOTE : exception can be raise in the DECLARATIVE,EXECUTABLE and EXCEPTION section

PL/SQL Tutorial ( Exception, Cursor ) Part - 02

---------------------------------------------------------------------------
                      --PREDEFINED EXCEPTION--
---------------------------------------------------------------------------
--Predefined Oracle Server Exception
ORA-1403    NO_DATA_FOUND         SELECT statement matches no rows
ORA-1422    TOO_MANY_ROWS         SELECT statement matches more than one rows
ORA-0001    DUP_VAL_ON_INDEX      Uniq constraints voilated
ORA-1476    ZERO_DIVIDE           Division by zero
ORA-6502    VALUE_ERROR           Truncation,arithmetic error
ORA-1722    INVALID_NUMBER        Conversion to the number failed ex.2A is not valid

--NO_DATA_FOUND - Predefined Exception
DECLARE
    v_hostelid hostel.hostelid%TYPE;
    v_hostelfee hostel.hostelfee%TYPE;
    v_studentid hostel.studentid%TYPE;
BEGIN
    v_studentid :='&stdid';
    SELECT hostelid,hostelfee INTO v_hostelid,v_hostelfee FROM hostel WHERE studentid=v_studentid;
    DBMS_OUTPUT.PUT_LINE('hostel id :=' || v_hostelid);
    DBMS_OUTPUT.PUT_LINE('hostel fee :=' || v_hostelfee);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('STUDENT IS NOT EXIST');
    WHEN VALUE_ERROR THEN
        DBMS_OUTPUT.PUT_LINE('STUDENT ID IS TOO LARGE');
END;
/

--If the input entered is very large, truncation happens resulting in value error
--INVALID_NUMBER predefined exception
--Inserting a record to billing table As ‘X’ a character value cannot be converted to integer value for billno an INVALID_NUMBER exception is thrown

----------------------------------------------------------------------------------------------
                                --NON-PREDEFINED EXCEPTION
----------------------------------------------------------------------------------------------
--USE OF PRAGMA EXCEPTION_INIT

DECLARE
    e_MissingNull Exception;
    PRAGMA EXCEPTION_INIT(e_MissingNull,-1400);
BEGIN
    INSERT INTO applicant (applicantid) VALUES (NULL);
EXCEPTION
    WHEN e_MissingNull THEN
    DBMS_OUTPUT.PUT_LINE('NULL VALUE CANT INSERT');
END;
/

--User-defined Exception
--Given an invalid itemid display appropriate error message If the given itemid is invalid, display Invalid Itemid
SET VERIFY OFF
SET SERVEROUTPUT ON
DECLARE
    v_studentid hostel.studentid%TYPE;
    v_count NUMBER;
    e_Invalid_Studentid EXCEPTION;
BEGIN
    v_studentid :='&studentid';
    SELECT count(*) INTO v_count FROM hostel WHERE studentid=v_studentid;
    IF v_count = 0 THEN
        RAISE e_Invalid_Studentid;
    END IF;
        DBMS_OUTPUT.PUT_LINE('Valid student_d');
EXCEPTION
        WHEN e_Invalid_Studentid THEN
        DBMS_OUTPUT.PUT_LINE('invalid student id');
END;
/

--NOTE : exception can be raise in the DECLARATIVE,EXECUTABLE and EXCEPTION section

--------------------------------------------------------------------------------------
                                --CURSOR
--------------------------------------------------------------------------------------
--Implicit Cursor Attributes
Using SQL cursor attributes, you can test the outcome of your SQL statements
implicit Cursor Attribute          Meaning
----------------------------------------------------------------------------------
SQL%ROWCOUNT              Number of records affected by the most recent SQL statement
SQL%FOUND                  Evaluates to TRUE if the most recent SQL statement affects one or more rows
SQL%NOTFOUND              Evaluates to TRUE if the most recent SQL statement does not affect any rows
SQL%ISOPEN               Always evaluates to FALSE because PL/SQL closes implicit cursors immediately after they are executed

--Do not use implicit cursor attribute to check the unsuccessfulness of SELECT statement, because PL/SQL returns exception when the SELECT statement
--fails. check below example
DECLARE
    v_hostelid hostel.hostelid%TYPE;
    v_hostelfee hostel.hostelfee%TYPE;
    v_studentid hostel.studentid%TYPE;
BEGIN
    v_studentid :='&studentid';
    SELECT hostelid,hostelfee INTO v_hostelid,v_hostelfee FROM hostel WHERE studentid=v_studentid;
    IF SQL%NOTFOUND THEN
        DBMS_OUTPUT.PUT_LINE('NO RECORD FOUND');
    ELSE
            DBMS_OUTPUT.PUT_LINE('FOLLOWING RECORD FOUND');
            DBMS_OUTPUT.PUT_LINE('hostel id :=' || v_hostelid);
            DBMS_OUTPUT.PUT_LINE('hostel fee :=' || v_hostelfee);
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No data found exception thrown');
END;

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

DECLARE
    v_studentid hostel.studentid%TYPE;
BEGIN
    v_studentid :='&studentid';
    UPDATE hostel SET hostelfee=hostelfee+100 WHERE studentid > v_studentid;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT ||'Rows has been updated');
    IF SQL%NOTFOUND THEN
        DBMS_OUTPUT.PUT_LINE('NO RECORD FOUND');
    END IF;
    COMMIT;
EXCEPTION
    WHEN VALUE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('VALUE IS TO LARGE');
END;
  
--Explicit Cursors :
--------------------
--Explicit cursors example - Simple loop (1 of 2)
DECLARE
    CURSOR cur_hosteldet IS SELECT studentid,userid FROM student WHERE studentid IN (SELECT studentid FROM hostel);
    v_studentid hostel.studentid%TYPE;
    v_userid student.userid%TYPE;
BEGIN
    OPEN cur_hosteldet;
    LOOP
        FETCH cur_hosteldet INTO v_studentid,v_userid;
        EXIT WHEN cur_hosteldet%NOTFOUND;
        IF v_studentid > 'S007' THEN
            UPDATE hostel SET hostelfee=hostelfee+100;
        END IF;
        DBMS_OUTPUT.PUT_LINE(v_studentid || ' : ' || v_userid);
    END LOOP;
    CLOSE cur_hosteldet;
COMMIT;
END;

--explicit cursur attributes
----------------------------
                    %FOUND      %ISOPEN  %NOTFOUND  %ROWCOUNT
After OPEN          NULL          TRUE       NULL             0
After 1st FETCH     TRUE          TRUE       FALSE         1
After 2nd FETCH     TRUE          TRUE       FALSE         2
After Last FETCH     FALSE          TRUE      TRUE          Data dependent
After CLOSE         exception     FALSE       exception  exception
      
--Explicit Cursor - Record variables (1 of 2)
CURSOR cur_itemdet2 IS SELECT itemid, qtypurchased, netprice FROM Customerpurchase WHERE netprice > 20;
v_itemrec cur_itemdet2%ROWTYPE;
v_olditemrec v_itemrec%TYPE;

--Example of Explicit cursors – Record variables (2 of 2)
DECLARE
    CURSOR cur_hosteldet IS SELECT studentid,userid FROM student WHERE studentid IN (SELECT studentid FROM hostel);
    v_hostelrec cur_hosteldet%ROWTYPE;
BEGIN
    OPEN cur_hosteldet;
    LOOP
        FETCH cur_hosteldet INTO v_hostelrec;
        EXIT WHEN cur_hosteldet%NOTFOUND;
        IF v_hostelrec.studentid > 'S007' THEN
            UPDATE hostel SET hostelfee=hostelfee+100;
        END IF;
        DBMS_OUTPUT.PUT_LINE(v_hostelrec.studentid || ' : ' || v_hostelrec.userid);
    END LOOP;
    CLOSE cur_hosteldet;
COMMIT;
END;

--Explicit Cursor – WHILE LOOP (1 of 2)

DECLARE
    CURSOR cur_studentdet IS SELECT studentid FROM student WHERE studentid IN (SELECT studentid FROM hostel);
    v_studentid student.studentid%TYPE;
BEGIN
    OPEN cur_studentdet;
    FETCH cur_studentdet INTO v_studentid;
    WHILE cur_studentdet%FOUND
    LOOP
        UPDATE student SET password='lion' WHERE studentid=v_studentid;
        DBMS_OUTPUT.PUT_LINE(v_studentid);
        FETCH cur_studentdet INTO v_studentid;
    END LOOP;
    CLOSE cur_studentdet;
    COMMIT;
END;

--Cursor FOR LOOP (1 of 2)
• Implicit open, fetch, exit condition check, close
• Implicit record variable declaration

DECLARE
    CURSOR cur_studentdet IS SELECT studentid FROM student WHERE studentid IN (SELECT studentid FROM hostel);
BEGIN
    FOR v_studentrec IN cur_studentdet
    LOOP
        UPDATE student SET password='tiger' WHERE studentid=v_studentrec.studentid;
        DBMS_OUTPUT.PUT_LINE(v_studentrec.studentid);
    END LOOP;
COMMIT;
END;

--implicit FOR Loops (2 of 2)
the cursor itself can be implicitly declared

BEGIN
    FOR v_studentrec IN (SELECT studentid FROM student WHERE studentid IN (SELECT studentid FROM hostel))
    LOOP
        UPDATE student SET password='lion' WHERE studentid=v_studentrec.studentid;
        DBMS_OUTPUT.PUT_LINE(v_studentrec.studentid);
    END LOOP;
COMMIT;
END;
      
--Predefined Oracle Server Exception –Cursor related
oracle error    predefined exception    description
------------    --------------------    -----------
ORA-1001         INVALID_CURSOR            Illegale cursor operation
ORA-6511         CURSOR_ALREADY_OPEN    Attempt open a cursor that already open

DECLARE
    CURSOR cur_studentdet IS SELECT studentid FROM student WHERE studentid IN (SELECT studentid FROM hostel);
    v_studentid student.studentid%TYPE;
BEGIN
    OPEN cur_studentdet;
    FETCH cur_studentdet INTO v_studentid;
    WHILE cur_studentdet%FOUND
    LOOP
        UPDATE student SET password='lion' WHERE studentid=v_studentid;
        DBMS_OUTPUT.PUT_LINE(v_studentid);
        FETCH cur_studentdet INTO v_studentid;
    END LOOP;
    CLOSE cur_studentdet;
    COMMIT;
EXCEPTION
    WHEN INVALID_CURSOR THEN
    DBMS_OUTPUT.PUT_LINE('Invalid cursor exception thrown');
    WHEN CURSOR_ALREADY_OPEN THEN
    DBMS_OUTPUT.PUT_LINE('Cursor already open exception thrown');
END;

--Parameterized cursors (1 of 2)

DECLARE
    CURSOR cur_studentdet(p_password VARCHAR2) IS SELECT * FROM student WHERE password=p_password;
    v_studentrec cur_studentdet%ROWTYPE;
BEGIN
    OPEN cur_studentdet('lion');
    LOOP
        FETCH cur_studentdet INTO v_studentrec;
        EXIT WHEN cur_studentdet%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_studentrec.studentid ||'  ' ||v_studentrec.userid ||'  '|| v_studentrec.password);
        END LOOP;
    CLOSE cur_studentdet;
END;

--FOR UPDATE cursor declaration (4 of 5)
DECLARE
    CURSOR cur_itemdet1 IS SELECT * FROM ITEM WHERE ITEMID LIKE 'STN%' FOR UPDATE OF unitprice;
    v_itemrec cur_itemdet1%ROWTYPE;
BEGIN
    OPEN cur_itemdet1;
    LOOP
        FETCH cur_itemdet1 INTO v_itemrec;
        EXIT WHEN cur_itemdet1%NOTFOUND;
            UPDATE item SET unitprice=unitprice+1 WHERE CURRENT OF cur_itemdet1;
             DBMS_OUTPUT.PUT_LINE(v_itemrec.itemid);
    END LOOP;
    CLOSE cur_itemdet1;
COMMIT;
END;