How to find row that has non-numeric value in a character column?

There can be a scenario where the datatype of the column is VARCHAR or CHAR but values that are suppose to be in there are numeric values in that column.

In the case when that happens and you would want to detect the row with that issue.

SQL> desc test;
Name Null? Type
—————————————– ——– ————–
X VARCHAR2(10)

SQL> select * from test;

X
———-
1
2
N/A
2A
10

Using one of the methods below you can identify the row(s).
Method 1: Works in 10g and up
SQL> select rowid, x
from test
WHERE x is not null
and NOT regexp_like(x, '^[0-9]+$');

ROWID X
—————— ———-
AAE3GaAAEAAAQOYAAC N/A
AAE3GaAAEAAAQOYAAD 2A

Method 2: Works in 10g and up
select rowid, x
from test
WHERE x is not null
and NOT regexp_like(x, '^[[:digit:]]+$');

ROWID X
—————— ———-
AAE3GaAAEAAAQOYAAC N/A
AAE3GaAAEAAAQOYAAD 2A

Method 3: Works in all version
SQL> set serveroutput on
SQL> declare
cursor l_cursor is select rowid, x from test;
l_num number;
begin
for l_rec in l_cursor
loop
begin
l_num := to_number(l_rec.x);
exception
when others then
dbms_output.put_line('Row id: ' || l_rec.rowid || ' ' || l_rec.x);
end;
end loop;
end;

Row id: AAE3GaAAEAAAQOYAAC N/A
Row id: AAE3GaAAEAAAQOYAAD 2A

Advertisements

How to find average row length for a table?

Using the following PL/SQL code one can find average size of a row in a table, the following code samples the first 100 rows. It expects 2 parameters table owner and table_name.

DECLARE
l_vc2_table_owner VARCHAR2(30) := '&table_owner';
l_vc2_table_name VARCHAR2(30) := '&table_name';
/* sample number of rows */
l_nu_sample_rows NUMBER := 100;

/* loop through columns in the table */
CURSOR l_cur_columns IS
SELECT column_name, data_type FROM dba_tab_columns
WHERE owner = l_vc2_table_owner
AND table_name = l_vc2_table_name;
l_rec_columns l_cur_columns%ROWTYPE;
l_vc2_sql VARCHAR2(10000);
l_avg_row_size NUMBER(10,2);
BEGIN
l_vc2_sql := '';
OPEN l_cur_columns;
FETCH l_cur_columns INTO l_rec_columns;
/* loop through columns */
WHILE l_cur_columns%FOUND
LOOP
/* if LOB datatype use dbms_log.get_length to find length */
IF l_rec_columns.data_type = 'CLOB' OR l_rec_columns.data_type = 'BLOB' THEN
l_vc2_sql := l_vc2_sql || 'NVL(dbms_lob.getlength(' || l_rec_columns.column_name || '), 0) + 1';
ELSE
l_vc2_sql := l_vc2_sql || 'NVL(VSIZE(' || l_rec_columns.column_name || '), 0) + 1';
END IF;
FETCH l_cur_columns INTO l_rec_columns;
IF l_cur_columns%FOUND THEN
l_vc2_sql := l_vc2_sql || ' + ';
END IF;
END LOOP;
IF l_vc2_sql IS NOT NULL THEN
l_vc2_sql := 'SELECT 3 + AVG(' || l_vc2_sql || ') FROM ' || l_vc2_table_owner || '.' || l_vc2_table_name
|| ' WHERE rownum < ' || l_nu_sample_rows;
EXECUTE IMMEDIATE l_vc2_sql INTO l_avg_row_size;
dbms_output.put_line(l_vc2_table_owner || '.' || l_vc2_table_name || ' average row length: ' || l_avg_row_size);
ELSE
dbms_output.put_line('Table ' || l_vc2_table_owner || '.' || l_vc2_table_name || ' not found');
END IF;
END;
/
Output
Enter value for table_name: TEST_OBJECTS
old 3: l_vc2_table_name VARCHAR2(30) := ‘&table_name’;
new 3: l_vc2_table_name VARCHAR2(30) := ‘TEST_OBJECTS’;
SCOTT.TEST_OBJECTS average row length: 76.88

PL/SQL procedure successfully completed.

Sample program that transfers data from MySQL to Oracle (CLOB)

Table structure in Oracle
SQL> desc scott.oracletable;
Name Null? Type
—————————————– ——– ———-
TRANSDATE NOT NULL DATE
SEQUENCE_NO NOT NULL NUMBER(6)
LOBDATA NOT NULL CLOB

Table structure in MySQL:
Name Type
—————————————– ——–
trandate Date
sequence_no smallint(6)
textfield longtext

— Code: TransferMySQLToOracle.java
import java.sql.*;
import java.io.*;
import java.lang.StringBuffer;
import java.util.Date;
import java.text.SimpleDateFormat;

public class TransferMySQLToOracle {
public static void main(String argv[]) throws Exception {
// DB connection for MySQL
Connection mysqlConn = null;
Statement mysqlStmt = null;
ResultSet mysqlRS = null;

// DB connection for Oracle
Connection oraConn = null;
PreparedStatement oraPstmt = null;

// parameters passed in start date and end date
String dtStart=argv[0];
String dtEnd=argv[1];

// print date
System.out.println(“: \n dtStart=” + dtStart);
System.out.println(“: \n dtEnd=” + dtEnd);

// set connection string for Oracle
String oradriverName = “oracle.jdbc.driver.OracleDriver”;
String oraurl = “jdbc:oracle:thin:@hostname:OraclePort:ORACLE_SID”;

// format to print timestamp
Date todaysDate;
SimpleDateFormat formatter = new SimpleDateFormat(“dd-MMM-yyyy HH:mm:ss”);
String formattedDate;

// default values
java.sql.Date dtTransactionDate = null;
int iSequenceNo = -1;

// print today date
todaysDate = new java.util.Date();
formattedDate = formatter.format(todaysDate);
System.out.println(“Start Time: ” + formattedDate);

try {
// connect to MySQL
String mysqlurl = “jdbc:mysql://mysqlserver:mysqlport/database?user=username&password=userpassword”;
Class.forName(“com.mysql.jdbc.Driver”).newInstance();
mysqlConn = DriverManager.getConnection(mysqlurl);

// connect to Oracle
Class.forName(oradriverName);
oraConn = DriverManager.getConnection(oraurl, “scott”, “scottpassword”);
String oraInsert = “insert into scott.oracletable (trandate, sequence_no, lobdata) values(?, ?, ?)”;

if ( mysqlConn != null )
// Get a statement from the connection and prepare statement
// mysqlStmt = mysqlConn.createStatement();
mysqlStmt = mysqlConn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
mysqlStmt.setFetchSize(Integer.MIN_VALUE);
oraPstmt = oraConn.prepareStatement(oraInsert);

// date in yyyy-mm-dd format in mysql
// extract records from MySQL for the date specified
String msqlQry = “SELECT trandate, sequence_no, textfield FROM mysqltable” ;
msqlQry += ” Where trandate between ‘” + dtStart + “‘ and ‘” + dtEnd + “‘”;
mysqlRS = mysqlStmt.executeQuery(msqlQry) ;
todaysDate = new java.util.Date();

// print date for logging start time
formattedDate = formatter.format(todaysDate);
System.out.println(“Starting loop: ” + formattedDate);

// Loop through the result set
while( mysqlRS.next() ) {
// get and set trandate and sequence number
dtTransactionDate = mysqlRS.getDate(1);
iSequenceNo = mysqlRS.getInt(2);
oraPstmt.setDate(1, dtTransactionDate);
oraPstmt.setInt(2, iSequenceNo);

// loop through the lob stream and convert to string
Reader in = mysqlRS.getCharacterStream(3);
StringBuffer sb = new StringBuffer();
int buf = -1;
while((buf = in.read()) > -1) {
sb.append((char)buf);
}
in.close();

// ObjectInput in = new ObjectInputStream(mysqlRS.getCharacterStream(5));
oraPstmt.setString(3, sb.toString());

// insert record in oracle
oraPstmt.executeUpdate();
}
oraPstmt.close();
mysqlRS.close();
mysqlStmt.close();

// print end time
todaysDate = new java.util.Date();
formattedDate = formatter.format(todaysDate);
System.out.println(“End Time: ” + formattedDate);
}
catch(Exception e)
{
// print the record it failed on as date and sequence # was unique so one know the offending record in source
System.out.println(“Failed on Record:”);
System.out.println(“Transaction Date: ” + dtTransactionDate.toString());
System.out.println(“Sequence No: ” + iSequenceNo);

throw e;
}
finally {
if ( mysqlRS != null ) mysqlRS.close();
mysqlRS = null;
if ( mysqlStmt != null ) mysqlStmt.close();
mysqlStmt = null;
if ( mysqlConn != null ) mysqlConn.close();
mysqlConn = null;
if ( oraConn != null ) oraConn.close();
oraConn = null;
}
}
}

c:\> SET CLASSPATH=.;C:\driver\mysql-connector-java-5.0.8-bin.jar;C:\driver\ojdbc14.jar
c:> javac TransferMySQLToOracle.java

How to calculate end of day?

There are few ways to calculate end of day i.e when the time is 23:59:59. In the example below it will return a value a value of 01-APR-2009 23:59:59.

to_date(’01-APR-2009′) + 1 – 1/86400
to_date(’01-APR-2009′) + 0.99999
to_date(’01-APR-2009 23:59:59′, ‘DD-MON-YYYY HH24:MI:SS’)
trunc(sysdate) + 0.99999 – end of today