ORACLE: Getting maximum sub string

We can easily find the maximum sub-string from a table of oracle. Let's see the following example..

CREATE table test(
name varchar2(100),
code varchar2(50)
)
INSERT INTO test(name,code) VALUES('Bangladesh','880');
INSERT INTO test(name,code) VALUES('BGD-Mobile','8801');
INSERT INTO test(name,code) VALUES('BGD-GP','88017');
INSERT INTO test(name,code) VALUES('BGD-AKTEL','88018');
INSERT INTO test(name,code) VALUES('BGD-Bangla Link','88019');
INSERT INTO test(name,code) VALUES('BGD-CITYCELL','88011');
INSERT INTO test(name,code) VALUES('BGD-WARID','88016');
INSERT INTO test(name,code) VALUES('BGD-TELETALK','88015');

SELECT * FROM test

SELECT max(code) FROM test WHERE instr('8801234',code)>0;

This will give: 8801


SELECT max(code) FROM test WHERE instr('880234',code)>0;

This will give: 880


SELECT max(code) FROM test WHERE instr('8801123',code)>0;

This will give: 88011

ORA-01704: String literal too long

The Mail cause of this error is stating by the message itself 'String literal too long'. If you try to INSERT more than 4000 characters to a varchar2 field this error will occur. Another case where this may occur is if you try to update a CLOB field. CLOB field doesn't support update.

In Java if you try to execute PreparedStatement.executeUpdate() to INSERT or UPDATE CLOB field of ORACLE with more than 4000 characters, you'll get this error.

To avoid this use PreparedStatement.execute() methode to to INSERT CLOB field.

This may occur in other language also. And you can use same technique to avoid errors.

Happy programming.

Comment my blog/ email me for any problems related to this.

Java by Example: Exception Handling, creating custom exception class

All related Posts
Java by Example: Exception Handling, creating custom exception class
Java by Example: Exception handling – throw custom Exception
Java By Example: Exception Handling – Throwing exception from method
Java by Example: Exception handling – Basic Exception Handling example ‘try catch’
Java by Example: Exception Handling – Java Exception Basics

You can easily create your own Exception Class where you can put your own exception. To do this what you have to do is just you have create an subclass of java.lang.Exception class.

Here is a basic example of creating custom exception class

package example.java.lang;

public
class
ExceptionExp5
extends Exception{

    public ExceptionExp5() {

        super("[ MY EXCEPTION ] ");

    }

    public ExceptionExp5(String exceptionString) {

        super("[ MY EXCEPTION ] "+exceptionString);

    }

    

    public
static
void main(String args[]){

        try{

            new
ExceptionExp5().tryCustomException();

        }catch(Exception e){

            e.printStackTrace();

        }

    }

    public
void tryCustomException() throws Exception{

        int fibs[]={1,2,3,5,8,10,13};

        for(int i=0;i<fibs.length;i++){

            if(isFibs(fibs[i]))

                System.out.println(fibs[i]+" - OK");

            else

                throw
new
ExceptionExp5("Invalid fibonacci number "+fibs[i]);

        }

    }

    private
boolean isFibs(int num){

        int f0 = 1;

        int f1 = 1;

        int fib = 1;

        while(num>=fib){

            if(num==fib)

                return
true;

            fib = f0 + f1;

            f0 = f1;

            f1 = fib;

        }

        return
false;

    }

}

If you execute this you'll get output like..

1 - OK

2 - OK

3 - OK

5 - OK

8 - OK

example.java.lang.ExceptionExp5: [ MY EXCEPTION ] Invalid fibonacci number 10

    at example.java.lang.ExceptionExp5.tryCustomException(ExceptionExp5.java:23)

    at example.java.lang.ExceptionExp5.main(ExceptionExp5.java:12)