Tag Archives: database

DB Finger Print Utility ( Comparing two database oralce environments ?)

DB Finger Print Utility:

by Roger Chick

This Util gives you a high level fingerprint of a Oracle DB schema.

Hopefully people will find this Util useful when comparing Oracle environments, (between dev, test, prod) and when upgrading stacks to make sure environment control is in place and environments are like for like.

It runs the following SQL against the supplied details

Description       - SQL
Oracle Version    - "select * from v$version"
Number of Tables  - "SELECT COUNT(TABLE_NAME) FROM ALL_TABLES"
Number of Columns - "select count(column_name) from all_tab_columns"
Number of Indexes - "select count(index_name) from all_indexes"
Number of Views   - "select count(view_name) from all_views"
DB Character Set  - "select value from nls_database_parameters where parameter='NLS_CHARACTERSET'"

then displays the details on a single line.

EXAMPLE OUTPUT:-
###############

version.tableCount.columnCount.indexCount.viewCount.characterSet
"Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production".553.13764.1078.825."WE8ISO8859P15"

###############
To run use

Syntax:

java -classpath <oracle-driver> -jar DBFingerPrint.jar
<JDBC URL> <username> <password>

The full path to the Oracle driver jar file – either classes12.zip or ojdbc14.jar will suffice
<oracle-driver> The full path to the Oracle driver jar file – either classes12.zip or ojdbc14.jar will suffice
<JDBC URL> db schema full JDBC URL.
<username> db schema Username.
<password> db schema Password.

Download link is here on Github

 

 

ORA-01722: invalid number root cause and resolution

ORA-01722: invalid number : Stack trace, rootcause and resolutions are given below

java.sql.SQLException: ORA-01722: invalid number

   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:219)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:970)
        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1072)
        at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:854)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1154)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3370)
        at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3415)
        at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.pmiExecuteQuery(WSJdbcPreparedStatement.java:1033)
        at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeQuery(WSJdbcPreparedStatement.java:683)

Possible root causes:

This error occurs when system tries to convert a character string into a number and cannot be converted into a valid number. Valid numbers should contain the digits ‘0’ to ‘9’and possibly decimal point, also a sign ‘+’ or ‘-‘ at the beginning or end of the string. You could also place an ‘E’ or ‘e’ for a floating point number and any other characters will not be accept and you get an ORA-01722 error.

Where does it occur?

It can happen for an INSERT or UPDATE or WHERE clause statements where an invalid number passed in

What do you do it fix it?

check the statement to verify that it only contains 0-9 digits, a sing (+/-), E or e.

Did you find this post useful? Please do not forget to leave a comment