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 ----------------------------------------- --------... Continue Reading →

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... Continue Reading →

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... Continue Reading →

How to find a length of LOB column?

Using dbms_lob.getlength(column name) one can find the length of the LOB (CLOB/BLOB/BFILE) data type. Example: SQL> SELECT dbms_lob.getlength(lob_column) FROM scott.lob_table; DBMS_LOB.GETLENGTH(lob_column) -------------------------------- 358 153 ...

How to truncate time in date field?

In Oracle: SQL> ALTER SESSION SET nls_date_format = 'YYYY-MON-DD HH24:MI:SS'; Session altered. SQL> SELECT TRUNC(sysdate) FROM DUAL; TRUNC(SYSDATE) -------------------- 2009-MAY-10 00:00:00 In SQL Server: SELECT CAST(CONVERT(varchar, GetDate(), 101) AS DateTime) 2009-05-10 00:00:00.000

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

Create a free website or blog at WordPress.com.

Up ↑