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');

How to suppress output of duplicate values - Oracle SQL

SQL> select deptno, ename
  2  from emp
  3  order by deptno
  4  ;

    DEPTNO ENAME                                                                
---------- ----------                                                           
        10 CLARK                                                                
        10 KING                                                                 
        10 MILLER                                                               
        20 JONES                                                                
        20 FORD                                                                 
        20 ADAMS                                                                
        20 SMITH                                                            
        20 SCOTT                                                                
        30 WARD                                                                 
        30 TURNER                                                               
        30 ALLEN                                                                
        30 JAMES                                                           
        30 BLAKE                                                                
        30 MARTIN                                                               

14 rows selected.

SQL> -- We want to produce report with only new values of DEPTNO column,
SQL> -- without repetition. SQL*Plus provides BREAK ON feature.

SQL> break on deptno
SQL> select deptno, ename
  2  from emp
  3  order by deptno
  4  ;

    DEPTNO ENAME                                                                
---------- ----------                                                           
        10 CLARK                                                                
           KING                                                                 
           MILLER                                                               
        20 JONES                                                                
           FORD                                                                 
           ADAMS                                                                
           SMITH                                                            
           SCOTT                                                                
        30 WARD                                                                 
           TURNER                                                               
           ALLEN                                                                
           JAMES                                                           
           BLAKE                                                                
           MARTIN                                                               

14 rows selected.

SQL> clear breaks
breaks cleared
SQL> -- Without any help from SQL*Plus I tried two approaches.
SQL> -- Both with Oracle SQL analytic functions.

SQL> select decode (
  2    row_number() over (partition by deptno order by ename)
  3    ,1,deptno
  4    ) AS DEPTNO
  5    ,ename
  6  from emp
  7  order by emp.deptno
  8  ;

    DEPTNO ENAME                                                                
---------- ----------                                                           
        10 CLARK                                                                
           KING                                                                 
           MILLER                                                               
        20 ADAMS                                                                
           FORD                                                                 
           JONES                                                                
           SCOTT                                                                
           SMITH                                                            
        30 ALLEN                                                                
           BLAKE                                                                
           JAMES                                                        
           MARTIN                                                               
           TURNER                                                               
           WARD                                                                 

14 rows selected.

SQL> -- It is mandatory to use name of the table in the ORDER BY clause.
SQL> -- Next approach - to use LAG function to compare current
SQL> -- value of DEPTNO column with previous. If it is the same
SQL> -- then there is no output (null).
SQL>
SQL> select decode (
  2    lag(deptno) over (order by deptno)
  3    ,deptno,null,deptno
  4    ) as deptno
  5    ,ename
  6  from emp
  7  order by emp.deptno
  8  ;

    DEPTNO ENAME                                                                
---------- ----------                                                           
        10 CLARK                                                                
           KING                                                                 
           MILLER                                                               
        20 ADAMS                                                                
           FORD                                                                 
           JONES                                                                
           SCOTT                                                                
           SMITH                                                            
        30 ALLEN                                                                
           BLAKE                                                                
           JAMES                                                        
           MARTIN                                                               
           TURNER                                                               
           WARD                                                                 

14 rows selected.

SQL> -- Use TO_NUMBER function or another way to adjust the size
SQL> -- of DEPTNO column

Palindrome detector

The string is palindrom if reverted it equals to source string. Here is simple algorithm to detect is the source string a palindrome or not.

CREATE OR REPLACE PROCEDURE isPalindrom(l_string VARCHAR2)
IS
  src_string VARCHAR2(32767) := l_string;
  len_src_string INTEGER := LENGTH(src_string);
  j INTEGER;
  
  FUNCTION getChar(i INTEGER) RETURN CHAR
  IS
  BEGIN
    RETURN SUBSTR(src_string, i, 1);
  END;

BEGIN
  
  j := 1;
  LOOP
    EXIT WHEN (j = ROUND(len_src_string/2) + 1) 
              OR getChar(j) <> getChar(len_src_string-j+1);
    j := j+1;
  END LOOP;
  
  IF getChar(j) = getChar(len_src_string-j+1) THEN
    DBMS_OUTPUT.PUT_LINE(l_string||' IS POLYNDROM');
  ELSE
    DBMS_OUTPUT.PUT_LINE(l_string||' IS NOT POLYNDROM');
  END IF;


  EXCEPTION
  WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
     DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);     
END;

Multiset operations with nested tables

Daily quizzes provided by www.plsqlchallenge.com are the right way to learn PL/SQL language, to know about many features, tips and trics. The site also allows to communicate with developers and learn the best practices.

PL/SQL Challenge suggested interesting task yesterday - to develop code "to compare row counts of all tables in two schema and display the names and counts of the tables where there is a disagreement." PL/SQL and SQL are both possible. I prefered PL/SQL way and tried to play around with nested tables and multiset operations. The procedure is shown below.

CREATE OR REPLACE PROCEDURE compare_table_counts(
   schema1_in IN VARCHAR2,
   schema2_in IN VARCHAR2,
   filter_in IN VARCHAR2)
IS

   TYPE type_aat IS TABLE OF VARCHAR2(100);
   tab_list_1 type_aat;
   tab_list_2 type_aat;
   tab_except type_aat;

   PROCEDURE make_tab_list(
      loc_tab IN OUT type_aat,schema_name IN VARCHAR2
   )
   IS
      sql_stmt VARCHAR2(200);
      loc_cnt PLS_INTEGER;
   BEGIN
      SELECT table_name
      BULK COLLECT INTO loc_tab
      FROM all_tables
      WHERE owner = schema_name
            AND table_name LIKE filter_in;

      FOR indx IN loc_tab.FIRST..loc_tab.LAST
      LOOP
         sql_stmt := 'SELECT COUNT(*) FROM '||
                      schema_name||'.'||
                      loc_tab(indx);
         EXECUTE IMMEDIATE sql_stmt INTO loc_cnt;
         loc_tab(indx) := loc_tab(indx) || ' (' ||
                                    loc_cnt || ')';
      END LOOP;
   END;

   PROCEDURE show_disagreement(
      schema_name IN VARCHAR2, loc_tab IN type_aat
   )
   IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE('In schema '||schema_name||
                           ' differing tables...');
      DBMS_OUTPUT.PUT_LINE('(Row counts in parentheses)');
      FOR indx IN loc_tab.FIRST..loc_tab.LAST
      LOOP
         DBMS_OUTPUT.PUT_LINE(' ' || loc_tab(indx));
      END LOOP;
   END;

BEGIN
   make_tab_list(tab_list_1,schema1_in);
   make_tab_list(tab_list_2,schema2_in);

   tab_except := tab_list_1 MULTISET EXCEPT tab_list_2;
   IF tab_except.COUNT > 0 THEN
      show_disagreement(schema1_in,tab_except);
   END IF;

   tab_except := tab_list_2 MULTISET EXCEPT tab_list_1;
   IF tab_except.COUNT > 0 THEN
      show_disagreement(schema2_in,tab_except);
   END IF;

   EXCEPTION
      WHEN OTHERS THEN
         DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
         DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);     
END;

"The keys to Oracle" by Tom Kyte

The very useful guide "how-to" bridle Oracle was written by Tom Kyte - http://tkyte.blogspot.com/2012/08/the-keys-to-oracle.html
There are also few very interesting comments including Steven Feuerstein's.

Multiset Operations with Associative Arrays

It would be convenient to use multiset operations not only with nested tables but with associative arrays too. According to Oracle® Database SQL Language Reference multiset operations apply to nested tables only. For now. Some help would be possible to get from the package show below. The function AA_STRNDX build associative array as intersection or not intersection of two arrays. The OPER_ID parameter can take value 'INTERSECT' or 'NOT INTERSECT'.

create or replace
PACKAGE compare
IS

  SUBTYPE strndx_type IS VARCHAR2(1000);
  TYPE aa_strndx_type IS TABLE OF PLS_INTEGER
                          INDEX BY strndx_type;

  FUNCTION aa_strndx(
    array_in_1  IN  aa_strndx_type,
    array_in_2  IN  aa_strndx_type,
    array_out   OUT aa_strndx_type,
    oper_id     IN  VARCHAR2 := 'INTERSECT'
  ) RETURN PLS_INTEGER;

END;

Q2 PL/SQL Challenge Championship Playoff

Set of links for OCP Advanced PL/SQL developer

I've finished creating all-in-one training kit for 1Z0-146 Oracle Exam. It is just a list of topics which completely corresponds to the exam. 1Z0-146 is the final step to become Oracle Advanced PL/SQL Developer Certified Professional.

Straight link to the Oracle documentation is created for each topic. For some subtopics there are recommended links. In some cases the one link can match to a few topics.

This all-in-one training kit was created using only free information sources. Self-made study guide was used by me to prepare for 1Z0-146. I have passed the exam successfully. If some links are not already active please contact me - kgkuzmin@gmail.com.

Good luck to you in your endeavor to become Oracle Certified Professional!

https://docs.google.com/document/d/1UU3UbXlOsb_iohonoBr9UDZhEyzTUUtw52TsESvEgVM/edit

How to delete 10 oldest records from many sessions

 I was asked to create PL/SQL procedure to clear warehouse table. The procedure when started should delete 10 oldest records from table. Oldest by the field, say, INSERTDATA. Also, deletion must be faster when the procedure is launched from more than one session. Obviously, I had to solve problems of blocked records. The code of procedure is shown below (documents - name of the table).

CREATE OR REPLACE PROCEDURE delete_oldest
IS
   locked_row_founded EXCEPTION;
   PRAGMA EXCEPTION_INIT(locked_row_founded, -54);
   counter INTEGER := 0;
   rec documents%ROWTYPE;
BEGIN
   FOR cur IN (SELECT * FROM documents ORDER BY insertdata)
   LOOP
      BEGIN
         SELECT * INTO rec
         FROM documents
         WHERE id = cur.id FOR UPDATE NOWAIT;
         DELETE FROM documents WHERE id = cur.id;
         counter := counter + 1;
      EXCEPTION
         WHEN locked_row_founded THEN NULL;
      END;
   EXIT WHEN counter = 10;
   END LOOP;
   COMMIT;
   EXCEPTION
      WHEN OTHERS THEN
         DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
         DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);     
END;

FORALL with arrays indexed by string

INDICES OF or VALUES OF clauses could not be used in FORALL with associative arrays indexed by strings. When it is necessary to load associative array into the table you should 'parse' it to another collection which use PLS_INTEGER as indexes.

CREATE TABLE experts (
  firstname VARCHAR2(30)
  ,lastname VARCHAR2(30)
);

create or replace
PROCEDURE load_experts
IS

  SUBTYPE experts_type IS experts%ROWTYPE;
  SUBTYPE name_type IS experts.firstname%TYPE;

  TYPE aat_namename_type IS TABLE OF name_type
                          INDEX BY name_type;
  TYPE nt_rec_type IS TABLE OF experts_type;
                        
  src_array   aat_namename_type;
  ndx_table   nt_rec_type := nt_rec_type();
  loc         name_type;
  item        experts_type;

BEGIN

  src_array('Steven') := 'Feuerstein';
  src_array('Tom')    := 'Kyte';
  src_array('Larry')  := 'Ellison';
  
  loc := src_array.FIRST;
  WHILE loc IS NOT NULL LOOP
    item.firstname  := loc;
    item.lastname   := src_array(loc);
    ndx_table.EXTEND;
    ndx_table(ndx_table.LAST) := item;
    loc := src_array.NEXT(loc);
  END LOOP;
  
  FORALL k IN INDICES OF ndx_table
    INSERT INTO experts VALUES ndx_table(k);
    
END;

Continuing with the implicit datatype conversion topic

According to Oracle Documentation there will be implicit datatype conversion when try to assign 1 to NUMBER variable. The literal is considered as PLS_INTEGER datatype.

It is one of possible tricks for PL/SQL code tuning. Below are two sample anonymous blocks with/without implicit conversion. As the result of my tests the average execution time of the correct one (with assigning " := 1.0" ) is less then time of execution of the block with conversion.

DECLARE
   n   NUMBER := 1;
BEGIN
   FOR i IN 1..100000 LOOP
      CASE MOD(i,2)
         WHEN 0 THEN n := n + 1.0;
         WHEN 1 THEN n := n - 1.0;
      END CASE;
   END LOOP;
END;

DECLARE
   n   NUMBER := 1;
BEGIN
   FOR i IN 1..100000 LOOP
      CASE MOD(i,2)
         WHEN 0 THEN n := n + 1;
         WHEN 1 THEN n := n - 1;
      END CASE;
   END LOOP;
END;

Implicit data conversion: harmful & malicious

I was asked about implicit data conversion in the Oracle during one of my job interview. It is not good, but I forgot about this feature at that time. So, I give the next answer: "If i would be a team leader, I would have terribly scolded developer, who use implicit data conversion".

Certainly, I used more rude expression. What do you think about the snippet of code below? (p2 - is a column with NUMBER type)

SELECT * FROM some_table WHERE p2 = '520';

Sure, Oracle is the best database server and it allows implicit data conversion. But also Oracle documentation gives us strong recommendations do not use such inefficient kind of code.

http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements002.htm

Some UPDATE trigger can fire without direct order

Suppose we have UPDATE ... FOR EACH ROW trigger for the table. If we add new column by ALTER TABLE with some default value than the trigger fires as many times as many rows in the table. Adding new column without default value doesn't work. The case with statement level trigger doesn't work.

Report with two columns from one column of data

I received a few tasks to test my skills while looking for job. These tasks have been solved by my. Now, just wanna share my solutions. The first task requires to show list of names in two columns as showed below.

ENAME      ENAME                                                                
---------- ----------                                                           
ADAMS      JAMES                                                                
CLARK      SCOTT                                                                
FORD       MILLER                                                               
KING       TURNER                                                               
MARTIN     BLAKE                                                                
SMITH      ALLEN                                                                
WARD       JONES