Using a packaged procedure.

Using a packaged procedure:
RAISE_APPLICATION_ERROR(error number,’Error message’);

RAISE APPLICATION ERROR:

• This Built-in procedure is used to create your own error message, which can be more descriptive than named exceptions.
• It is used to communicate a predefined exception interactively by returning a non standard error code and error message.
• Using this procedure we can report error to application and avoid returning unhandled exception.

Note:

• Error number must exists between -20,000 and -20,999
• Error_message is the text associate with this error, and keep_errors is Boolean value.
• The error_message parameter must be less than 512 characters.

SQLCODE FUNCTION:

• It returns the current error code.
• For a user defined exception it returns 1, +100 NO_DATA_FOUND exception.

SQLERRM:

• It returns the current error message text.
• SQLERRM returns the message associated with the error number.
• The maximum length of a message returned by the SQLERRM functions is 512 bytes.

User-defined Exception.

User-defined Exception:

• A user-defined exception is an error that is defined by the program.
• The developers to handle the business situations define user-defined exceptions during the execution of the PL/SQL block.
• User defined exceptions are defined by the following two techniques:
• Using a flow control statement RAISE:
• Raise statement transfer the control of the block from the execution part of the PL/SQL block to the exception handing part of the block.

Steps:

1. Declare Exception
2. Raise in Executable section explicitly  using RAISE<Exception_handler_name>;
3. Handle the raised exception.

Pre defined Exception.

Pre defined (System defined) Exception:

• Predefined exception are raised automatically by the system during run time.
• Predefined exception are already available in the program it is not necessary to declare them in the declarative section like user_defined exception.

Predefined Exception List:

Exception Name	Error No	Description
ORA-0001	DUP_VAL_ON_INDEX	Unique constraint violated.
ORA-1001	INVALID_CURSOR	Illegal cursor operation.
ORA-1403	NO_DATA_FOUND	No data found.
ORA-1422	TOO_MANY_ROWS	A SELECT INTO statement Matches more than one row.
ORA-1722	INVALID_NUMBER	Conversion to a number Failed for example, ‘netlojava street 1’  not valid.
ORA-6502	VALUE_ERROR	Truncation, arithmetic, or Conversion error.
ORA-01476	ZERO_DIVIDE	Divisor is equal to zero.
ORA-06511	CURSOR_ALREADY_OPEN	This exception raised when we try to open a cursor   which is already opened.
ORA-01017	LOGIN_DENIED	This exception is raised When we try to enter oracle using invalid username/password.

 
Examples:
SQL>DECLARE
       v_empno emp.empno%TYPE:=&empno;
       v_ename emp.ename%TYPE;
       v_job emp.job%TYPE;
    BEGIN
       SELECT empno=v_empno;
       DBMS_OUTPUT.PUT_LINE(‘The empno detail are ‘||v_ename||’ ‘||v_job);
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
       DBMS_OUTPUT.PUT_LINE(‘The empno is not found.’);
    END;
SQL>DECLARE
       v_accno kcb_acc_tab.accno%TYPE:=&accno;
       v_name kcb_acc_tab.name%TYPE:=&name;
       v_bal kcb_acc_tab.bal%TYPE:=&bal;
    BEGIN
       INSERT INTO kcb_acc_tab(accno,name,bal)
       VALUES(v_accno,v_name,v_bal);
       DBMS_OUTPUT.PUT_LINE(‘Account detailes are inserted successfully’);
    EXCEPTION
       WHEN DUP_VAL_ON_INDEX THEN
       DBMS_OUTPUT.PUT_LINE(‘accno already exists’);
    END;
SQL>DECLARE
       v_empno emp.empno%TYPE;
       v_ename emp.ename%TYPE;
       v_deptno emp.deptno%TYPE;
    BEGIN
       SELECT empno,ename,deptno INTO v_empno,v_ename,v_deptno
       FROM emp
       WHERE empno=7788 AND ename=’SCOTT’;
       DBMS_OUTPUT.PUT_LINE(‘The scott works in department number:’||v_deptno);
       Select empno,ename,deptno into v_empno,v_ename,v_deptno
       FROM emp
       Where deptno=10;
       DBMS_OUTPUT.PUT_LINE(‘The Employee number:’||v_empno);
       DBMS_OUTPUT.PUT_LINE(‘The Employee name:’||v_ename);
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
       DBMS_OUTPUT.PUT_LINE(‘Error:There is no such empno or ename or deptno’);
       WHEN TOO_MANY_ROWS THEN
       DBMS_OUTPUT.PUT_LINE(‘Error:More than one Employee works in department number 10’);
      WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE(‘Error occurred while processing the program’);    
    END;

Types of Exceptions.

Types of Exceptions:
1) Predefined Exception.
2) User-defined Exception.

1) Predefined Exception:

• Oracle has predefined several exceptions that correspond to the most common oracle errors.
• A predefined oracle server error is trapped by referencing its standard name within the corresponding exception handling at runtime.
• As soon as the Exception occurs oracle implicitly raise the exception and jumps into the EXCEPTION handle block, if proper EXCEPTION handle is found manages the specified steps.
• In predefined oracle server exceptions only one exception is raised and handled at any time.

2) User-defined Exception:

• A user-defined exception is an error that is defined by the program.
• The developers to handle the business situations define user-defined exceptions during the execution of the PL/SQL block.

The Others Exception Handler and Exception Guidelines.

The Others Exception Handler:

• The OTHERS exception is an optional EXCEPTION handling clause that traps unspecified exceptions.
• The OTHER should always be the last handler in the block.
• It is good programming practice to have an OTHERS handler at the top level of your program to ensure that no errors go undetected.

Exception Guidelines:

• Begin the EXCEPTION handling section of the block with the keywords EXCEPTION.
• Define several EXCEPTION HANDLERS, each with its own set of action for the block.
• Avoiding unhandled exceptions, this can be done via an OTHERS handler at the topmost level program.
• When an EXCEPTION occurs PL/SQL processes only one handle before leaving the block.

Trapping Exceptions.

Trapping Exceptions:

• The exception handling section consists of handlers for all the exceptions.
• An exception handler contains the code that is executed when the error associated with the exception occurs, and the exception raised.
• Each exception handler consists of a WHEN clause which specifies an EXCEPTION that has to be handled.

Syntax:
    Exception
    When <exception1> [or exception2…] then
        SQL statement1
        SQL statement2
    When <exception3> [or exception4…] then
        SQL statement1
        SQL statement2
    When others then
        SQL statement1
        SQL statement2
    End;

Exception Handling.

Exception Handling:

• An exception in PL/SQL block is raised during execution of a Block.
• Once an EXCEPTION arises it terminates the main body of actions performed by the PL/SQL block.
• A Block always terminates when PL/SQL Raises an Exception.
• We can specify an exception handler to perform final action.
• An Exception is raised when an error occurs.
• In case of an error, normal exception stops and the control is immediately transferred to the exception handling part of the PL/SQL block.

Note:

• Every PL/SQL block can  have only one Exceptional section.
• If the exception is raised in the executable section of the Block and there is no corresponding handler, the PL/SQL Block terminates with failure.
• If the exception is handled then PL/SQL Block terminates successfully.

Save your gmail contacts in your pc or laptop

Save your gmail contacts in your pc or laptop using the steps below.

·         1. Click here to access your Gmail contacts.

·         2. Click on More in the top menu, and then click on Export in the menu that appears.

·         3. Choose which contacts you want to download.

     

     

     

     4. Click the Export button to download your contacts. Follow the instructions on your screen and save the file to your computer. By default the file will be named 'google.csv'.

Hope this helps you.

Dear Visitor your comment make us happy, so don't forget to leave a Comment.

Write a program to display scope variable can be nexted.

/* Write a program to display scope variable can be nexted. */

class Scopevar{
    public static void main(String args[]){
        int x;
        x=10;
        if(x==10){
            int y;
            y=100;
            System.out.println("y="+y);
            }
        System.out.println("x="+x);
        }
    }

/*
Output:

E:>javac Scopevar.java
E:>java Scopevar

y=100
x=10

*/

Write a program to demonstrate scope of a variable with in a block.

/* Write a program to demonstrate scope of a variable with in a block. */

class Scope{
    public static void main(String args[]){
        int x;
        for(x=0;x<3;x++){
            int y=-1;
            System.out.println("y="+y);
            y=100;
            System.out.println("y="+y);
            }
        }
    }

/*
Output:

E:>javac Scope.java
E:>java Scope

y=-1
y=100
y=-1
y=100
y=-1
y=100

*/

Write a program to compute the area of the circle.

/*  Write a program to compute the area of the circle. */

class Area{
    public static void main(String args[]){
        double pi,r,a;
        pi=3.1416;
        r=10.8;
        a=pi*r*r;
        System.out.println("Area of circle:"+a);
        }
    }

/*
Output:

E:>javac Area.java
E:>java Area

Area of circle:366.436224


*/

Write a program to compute the distance of light travels.

/* Write a program to compute the distance of light travels. */

class Light
{
    public static void main(String args[]){
        int lightspeed;
        long days;
        long seconds;
        long distance;
        lightspeed=186000;
        days=1000;
        seconds=days*24*60*60;
        distance=lightspeed*seconds;
        System.out.println("Lightspeed:"+lightspeed);
        System.out.println("Number of seconds:"+seconds);
        System.out.println("Total distance:"+distance);
        }
    }


/*
Output:

E:>javac Light.java
E:>java Light

Lightspeed:186000
Number of seconds:86400000
Total distance:16070400000000

*/

How to set a password for BSNL Modem.

How to set a password for BSNL Modem.

1.Go to any web browser.

2.First logon to website 192.168.1.1  in URL bar after  enter “Enter key”.

                The bellow page is open.

                Enter User name and Password.

By default the username and password is admin/admin

For Example:

Username: admin

Password:  *****

3. After click login button.

4. Now click o the Wireless link

Under the Wireless link you will see Security link, click on Security link

5. So you will see some more fields to be entered

Select SSD                                           : UTStream

Network Authentication                      : Open

WEP Encreption                                 : Enabled

Encreption Strength                            : 64-bit / 128 bit

Currect Network Key                         : 1

Network key 1                                    : NETLOJAVA1234   

Network key should be in 13 Hexa digits i.e. (A-Z)(0-9)

For example : ABCDEFGHZRQ98, letters are case sensitive.

After you have entered this settings click on save/applybutton.
 

6. And here you go, your BSNL WiFi network is safe and protected.

7.Now go to your Network Connections and search for a wireless netwrok, you will see your name that you entered in SSID field, click on that name enter the 13 digit key and relax and surf the protected and problem free internet.

Click Connect button.

Hope this helps you.

Dear Visitor your comment make us happy, so don't forget to leave a Comment.

Write a PL/SQL Cursor to display the different active sets at run time according to the department number. i.If department number 10 display employee name, job, salary of the 10 department. ii.If department number 20 display employee name, job, salary of the 20 department. iii.Other than this 2 departments display employee name, salary, department number of other departments.

Write a PL/SQL Cursor to display the different active sets at run time according to the department number.

i.If department number 10 display employee name, job, salary of the 10 department.

ii.If department number 20 display employee name, job, salary of the 20 department.

iii.Other than this 2 departments display employee name, salary, department number of other departments.

SQL>DECLARE
          vempno emp.empno%type;
          vempno emp.empno%type;
          vsal emp.sal%type;
          vjob emp.job%type;
          vdeptno emp.deptno%type;
          TYPE ref_c IS REF CURSOR;
          c1 ref_c;  --it is data type of ref_c
          begin
          vdeptno:=&dno;
          IF vdeptno=10 THEN
    OPEN c1 FOR SELECT empno,ename,sal
    FROM EMP WHERE deptno=vdeptno;
          ELSIF vdeptno=20 THEN
    OPEN c1 FOR SELECT ename,job,sal
    FROM emp WHERE deptno=vdeptno;
          ELSE
    OPEN c1 FOR SELECT ename,sal,deptno
    FROM EMP
    WHERE deptno=vdeptno;
          END IF;
          IF vdeptno=10 THEN
          LOOP
    FETCH c1 INTO vempno,vename,vsal;
    EXIT WHEN C1%NOTFOUND;
    Display(vempno||’ ‘||vename||’ ‘||vsal);
          END LOOP;
          CLOSE c1;
          ELSIF vdeptno=20 THEN
          LOOP
    FETCH c1 INTO vename,vjob,vsal;
    EXIT WHEN C1%NOTFOUND;
    Display(vename||’ ‘||vjob||’ ‘||vsal);
          END LOOP;
          CLOSE c1;
          ELSE
    LOOP
    FETCH c1 INTO vename,vsal,vdeptno;
    EXIT WHEN c1%NOTFOUND;
        display(vename||’ ‘||vsal||’ ‘||vdepto);
          END LOOP;
          END IF;
          END;

Types of cursor variable.

Types of cursor variable:
There are two types of Ref Cursor.

• Weak cursor variable or Weak Ref Cursor
• If we do not include a RETURN clause in the Cursor, then it is a weak REF Cursor.
• Cursor variables declared from weak REF cursors can be associated with any query at runtime.

Syntax:
    Step-1:
    Type <type name> is ref cursor;
    Step-2:
    <cursor variable name> <type name>;
    Step-3:
    Open<cursor variable name>
    for(select statement);
    fetch
    exit
    close;

Strong cursor variable:

• A REF CURSOR with a RETUN clause define a “Strong” REF CURSOR.

Syntax:
    Step-1:
    Type <type name> is ref cursor
    RETURN record_type;
    Step-2:
    <cursor variable name> <type name>;
    Step-3:
    Open<cursor variable name>
    for(select statement);
    fetch
    exit
    close;

Dynamic cursor Ref Cursor or Cursor Variable.

Dynamic cursor Ref Cursor or Cursor Variable:

• Explicit cursor is Static Cursor.
• Explicit cursor referrers always one work area associated with cursor.
• Dynamic cursor is Ref Cursor, It referrers different work area in memory.
• Cursor variables are analogous to PL/SQL variables, which can hold different values at runtime.
• It is used to declare a cursor with out select statement.
• A Ref Cursor can be reused if it is declared in package.
• A Ref Cursor support to return more than 1 row from sub program.
• Deferent select statement can be associated with cursor variable at run time.

Write a PL/SQL Program to display the department number, department name, location and number of employees. The department which is having more than three employees.

Write a PL/SQL Program to display the department number, department name, location and number of employees. The department which is having more than three employees.

    

SQL>DECLARE
    CURSOR cs is
    SELECT d.deptno,d.dname,d.loc,v.noe
    FROM dept d,(SELECT deptno,count(*) noe
            FROM emp
    Group by deptno) v
    WHERE  v.noe>3 AND
    d.deptno=v.deptno;
    BEGIN
    FOR  i in cs
    LOOP
  display(i.deptno||’ ‘||i.dname||’ ‘||i.loc||’ ‘||i.noe);
    END LOOP;
    END; 

Write a PL/SQL Program to display the employee details and any employees work in 50 department remove that employees and display the removing employees with locking system.

Write a PL/SQL Program to display the employee details and any employees work in 50 department remove that employees and display the removing employees with locking system.

SQL>DECLARE
          cursor lc is
          select ename,sal deptno
          from emp
          for update;
          i lc%rowtype;
          BEGIN
          display(‘The emp det are’);
          display(‘EmpName’||’ ‘||’Salary’||’ ‘||’DeptNum’);
          display(‘-------‘||’  ‘||’--------‘||’ ‘||’-------‘);
          open lc;
          loop
          fetch lc into I;
          exit when lc%notfound;
          display(rpad(i.ename,8)||’ ‘||rpad(i.sal,5)||’ ‘||i.deptno);
          if i.deptno=50 then
          delete from emp
          where current of lc;
          end if;
          end loop;
          close lc;
          display(‘After delete the 50th dept emps’);
          open lc;
          loop
          fetch lc into I;
          exit when lc%notfound;
          display(rpad(i.ename,8)||’ ‘||rpad(i.sal,5)||’ ‘||i.deptno);
          end loop;
          close lc;
          end;

FOR UPDATE Clause And Using WHERE CURRENT OF clause

FOR UPDATE Clause:

• FOR UPDATE clause explicitly locks the records stored in the private work area.
• The FOR UPDATE clause in the cursor query is used to LOCLK the affected rows while the cursor is opened.
• You need not need provide an explicit COMMIT command to release the lock acquired by using the FOR UPDATE clause.

Using WHERE CURRENT OF clause:

• WHERE CURRENT OF clause to refer the current record, fetched from the explicit cursor.
• We need to suffix the name of the explicit cursor with the CURRENT OF clause to refer to current record.
• In order to use the WHERE CURRENT OF clause, you need to lock the record fetched from the cursor.