Select BLOB Data

select substr(utl_raw.cast_to_varchar2(at.column_name), 341, 41) as MSISDN from archive_tbl at where utl_raw.cast_to_varchar2(at.column_name) like '%01287392728%';

OracleValidConnectionChecker - Unexpected error in pingDatabase

Error
[org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker] Unexpected error in pingDatabase

Resolution
Remove the Oracle lib (ojdbc14.jar) from your Ear or War and place it at jboss server/lib directory

Check Oracle DB CharacterSet

SELECT * FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET'

Clean Up Oracle DB Space

Free up oracle db space by delete below log:-
/oracle/oracle/product/10.2.0/db_1/network/log/listener.log
using this command :
cat /dev/null > /oracle/oracle/product/10.2.0/db_1/network/log/listener.log
This log was more than 4GB of size. Emptying this file is harmless as it only contains the connection log to the DB.

Oracle DB Test Client

public class Test{
public static void main(String[] args) {
try {
Connection con=null;
Class.forName("oracle.jdbc.driver.OracleDriver");
con=DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:database_name",
"username",
"password");
Statement s=con.createStatement();
s.execute("
INSERT INTO EMP VALUES
('1', 'William')
");
s.close();
con.close();
} catch(Exception e){e.printStackTrace();}
}
}

Oracle Select Date

select * from audit_log
where CREATED_DATE > to_date('08-12-2010','dd-mm-yyyy')

Export DB Script

exp db_user_name/password@DBSID file=backup_20100810.dmp log=backup_20100810.log owner=db_user_name

Unicode Character Set

Useful link

http://www.querytool.com/help/907.htm

http://www.oracle.com/technology/tech/globalization/pdf/TWP_NCHAR_Migration_10gR1.pdf

Mysql DB Test Client

public class DBConnTestClient{

public static void main(String[] args) {
System.out.println("DB Connect Example.");
Connection conn = null;
//mysql db connection
String url = "jdbc:mysql://localhost:3306/db_name";
String driver = "com.mysql.jdbc.Driver";

String userName = "root";
String password = "root";
try {
Class.forName(driver).newInstance();
conn = DriverManager.getConnection(url,userName,password);

//sql statement here...

System.out.println("Connected to the database");
conn.close();
System.out.println("Disconnected from database");
} catch (Exception e) {
e.printStackTrace();
}
}


}

DB - Import Oracle DB Script

@echo off
SETLOCAL

Set USERNAME=userid
Set PASSWORD=ps
Set SID=artd
Set DATA_FILE=outdat

@rem Check all value exist before we start the process

if "%USERNAME%" == "" goto formaterror
if "%PASSWORD%" == "" goto formaterror
if "%SID%" == "" goto formaterror
if "%DATA_FILE%" == "" goto formaterror

goto main

:formaterror
echo Usage: import [username] [password] [sid] [data_filename]
@echo off
goto finish

:main
SQLPLUS %USERNAME%/%PASSWORD%@%SID%
cd ..
imp %USERNAME%/%PASSWORD%@%SID% file=%DATA_FILE%.dmp log=%USERNAME%imp.log fromuser=%USERNAME% touser=%USERNAME% ignore=y

:finish

DB - Export Oracle DB Script

@echo off
SETLOCAL

Set USERNAME=userid
Set PASSWORD=ps
Set SID=ARTD
Set OUTPUT_FILE=outdat

@rem Check all value exist before we start the process

if "%USERNAME%" == "" goto formaterror
if "%PASSWORD%" == "" goto formaterror
if "%SID%" == "" goto formaterror
if "%OUTPUT_FILE%" == "" goto formaterror

goto process

:formaterror
echo Usage: export [username] [password] [sid] [output_filename]
@echo off
goto finish

:process
exp %USERNAME%/%PASSWORD%@%SID% file=%OUTPUT_FILE%.dmp log=%USERNAME%exp.log owner=%USERNAME%

:finish

ENDLOCAL