ORA-01426 when TRUNCATE TABLE

As part of the process to populate tables with some common dictionary data we have used interim tables. So, data was derived from source tables, put into interim tables, then cleared and go into final dictionary tables. After a while, the process starts fail sometimes. We found growing size of interim table due to failure of wiping out it.

truncate table DATA_INTERIM;

Error report:
SQL Error: ORA-01426: numeric overflow
01426. 00000 -  "numeric overflow"
*Cause:    Evaluation of an value expression causes an overflow/underflow.
*Action:   Reduce the operands.

It turns out to be an Oracle bug (https://forums.oracle.com/thread/1094144)

The following info with ID was received from MOS: ORA-01426 While Truncating A Table Or Exchanging Partitions (Doc ID 882997.1) Bug 8618856 : TRUNCATE OF SPECFIC TABLE FAILS WITH ORA-01426 NUMERIC OVERFLOW

The workaround is to drop and recreate the table. Another way is to delete all rows, but it is too long due to huge amount of data.

drop table DATA_INTERIM;
create table DATA_INTERIM
(...)
;

Some differencies in Oracle and Teradata SQL queries

This is not complete list of course. There are a lot of other features which work differently in these databases. For instance, analytic functions of Oracle will definitely require extra manual work if you are going to migrate queries. From the other hand, Oracle as well as Teradata are constantly updating list of available SQL features. For example, DECODE function had to be transformed to CASE statement in Teradata, but now version 14 knows how to handle it. 

Oracle	Teradata	Possible error message
Allows WITH clause without column names: with SUBQUERY_NAME as (select a,b from c) select * from SUBQUERY_NAME;	Subquery name in WITH clause must be provided by column names: with SUBQUERY_NAME(a,b) as (select a,b from c) select * from SUBQUERY_NAME;	[Teradata Database] [3706] Syntax error: expected something between the word 'SUBQUERY_NAME' and the 'as' keyword.
Allows ORDER BY clause in subqueries: select * from (select column1, column2 from table_name order by column1);	There might be only one ORDER - in the main query.	[Teradata Database] [3706] Syntax error: ORDER BY is not allowed in subqueries.
SYSDATE	CAST(CURRENT_TIMESTAMP(0) AS TIMESTAMP(0))	[Teradata Database] [5628] Column sysdate not found in table_name.
DateTime +/- n/86400	DateTime +/- interval 'n' second	[Teradata Database] [5407] Invalid operation for DateTime or Interval.
DateTime +/- n/1440	DateTime +/- interval 'n' minute	[Teradata Database] [5407] Invalid operation for DateTime or Interval.
DateTime +/- n/24	DateTime +/- interval 'n' hour	[Teradata Database] [5407] Invalid operation for DateTime or Interval.
DateTime +/- n	DateTime +/- interval 'n' day	[Teradata Database] [5407] Invalid operation for DateTime or Interval.
Subqueries are allowed without aliases: select * from (select * from table_name);	All subqueries must be provided by alias: select * from (select * from table_name) t1;	[Teradata Database] [3707] Syntax error, expected something like a name or a Unicode delimited identifier or an 'UDFCALLNAME' keyword between ')' and ';'.

Java connect to Oracle using jdbc

import java.sql.*;

public class TestDBConnection {
static public void main(String args[]) throws Exception {
  String connStr = "jdbc:oracle:thin:@[host]:[port]:[sid]";
  DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
  Connection conn = DriverManager.getConnection(connStr,"[user]","[password]");
  
  Statement statement = conn.createStatement();
  String sqlText = "select user, sysdate from dual";
  ResultSet resultSet = statement.executeQuery(sqlText);
  while (resultSet.next()) {
    String userName = resultSet.getString("user");
    Date currentDate = resultSet.getDate("sysdate");
    System.out.println("User: " + userName);
    System.out.println("Date: " + currentDate);
}
conn.close();
}
}

Possible output:

c:\java\java TestDBConnection

User: scott
Date: 2013-06-05

Prerequisities: install jdbc driver, setup CLASSPATH environment variable.

ORA-00932 when search in all_views

"ORA-00932: inconsistent datatypes: expected NUMBER got LONG" is returned when try to search in TEXT column of ALL_VIEW dictionary view.

SQL> select * from all_views  2  where upper(text) like '%SOMETHING%'
  3  /
where upper(text) like '%SOMETHING%'
      *
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

TEXT column has LONG datatype which is superseeded with BLOB. Quick solution is to use TO_LOB function. But it works only within INSERT ... AS SELECT statement. So, create global temporary table for this purpose:

create global temporary table views_list (
  owner varchar2(30) not null
  ,view_name varchar2(30) not null
  ,text_length number
  ,text clob
);

insert into views_list 
select owner, view_name, text_length, to_lob(text) text
from all_views
where owner not like 'SYS%' -- assuming we exclude SYS views only
;

select * from views_list
where upper(text) like '%SOMETHING%'
;

Another solution is to create function which converts LONG to VARCHAR2 or CLOB.

Pre-interview questions

This is updated post with all pre-interview questions discovered in recruiter's emails. Here is no questions asked on interview with pre-signed agreement do not share its details.

Set #1

• What is the difference between a Matrix and Matrix with group report?
• Can you  explain the difference between Group By and Partition By clauses?
• What are the analytical functions available in Oracle?
• What is the difference between Single and Correlated sub-queries?
• What is query to get first day of the last quarter?
• How can you tell if an UPDATE updated no rows, in SQL and PL/SQL?
• Difference between DELETE, TRUNCATE and DROP?
• Difference between Primary, Unique and Foreign keys?
• How to debug PL/SQL code?
• Explain the difference between implicit and explicit cursors?

Set #2

• What are clusters?
• Explain some of the commonly used Predefined PL/SQL Exceptions
• What is a Materialized view?
• What is the Networkdblink?
• Predefined exceptions and explanation?
• Difference b/w view and meterialized view?

My daughter's drawing

Revert string with SQL

SELECT LISTAGG (
                 SUBSTR('&str', level, 1)
                 ,''
               )
       WITHIN GROUP (ORDER BY level DESC) 
FROM DUAL
CONNECT BY LEVEL <= LENGTH('&str');