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…

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…

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