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 ———- … Continue reading How to find row that has non-numeric value in a character column?

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 … Continue reading How to find average row length for a table?

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 { // … Continue reading Sample program that transfers data from MySQL to Oracle (CLOB)