Not an infinte loop

If you know why the below piece of code wont go into an infinite loop in Oracle, then you know a very important concept of Oracle database.

BEGIN
FOR x in (SELECT * from t) LOOP
INSERT INTO t values (x.user_name, x.user_id, x.created);
END LOOP;
END;

Its a direct lift from Thomas Kyte's text book.

Buy the book if you don't know why or thinks that it will go into infinite loop.

SET DEFINE OFF

In the last post I mentioned about shell script for converting excel to insert statements. I came across one more problem with that task. The values in insert statement had "&" (ampersand) and the sqlplus was prompting to enter a value. There are slick ways to encode ampersand using sed, but the simplest one is to turn that behavior off in sqlplus. The following command will do the trick.

SET DEFINE OFF.

My first SED-AWK script

I have heard a lot about SED-AWK scripts but never had a chance to work on it. Recently I was assigned a mundane task of importing excel sheet to Oracle database table. Of course there are plenty of tools available in market but my case is little complex as the data I had was denormalized. So I must find ID value for some column and also some formatting/calculation was required for other columns. There are many ways to solve this problem but this time I took an approach of solving it using SED-AWK.

AWK Script to create insert statements.

/a*/ {
print "insert into <TABLE> (<COLUMNLIST>) values (";
for (i=1; i< NF; ++i)
{
    if ($i=="SYSDATE")
    {
         print $i",";
    }
    else
    {
        if (i >= 2 && i <= 7)
        {
            print $i"*3600,";
        }
   else
      {
           print "'"$i"',";
   }
 }
}
print "(select item_id from ic_item_mst where item_no = "$i"))";
print "/";}

Shell script to converts CSV to Insert SQL.

#
# CSV to INSERT SQL
# param 1 TABLE NAME
# param 2 COMMA SEPERATED COLUMN LIST
# param 3 awk script file
# parma 4 CSV values
#

USAGE="Usage: 0-ScriptName 1-Table Name 2- Comma seperated column list 3- awk script 4- values csv file";

if test $# != 4
then
  echo "$0: Error: Incorrect Arguments.."
  echo $USAGE
  exit 1
fi

cp $3 temp.awk

#
# Encode spaces with @@ token
#
sed 's/ /@@/g' $4 > temp1

#
# Replace , with spaces so that awk can read it
#
sed 's/,/ /g' temp1 > tempvalues.txt

#
# Replace table name in AWK script
#
sed 's/<TABLE>/'$1'/g' temp.awk > test.awk
rm temp.awk

#
# Replace column list in AWK script
#
sed 's/<COLUMNLIST>/'$2'/g' test.awk > final.awk
rm test.awk

#
# AWK and generate the insert stmt
#
awk -f final.awk tempvalues.txt > temp.sql
rm final.awk
rm tempvalues.txt
rm temp1

#
# Decode spaces back
#
sed 's/@@/ /g' temp.sql > insertstmt.sql
rm temp.sql

In the AWK script I have written code to multiply columns 2 to 7 with 3600 and I am getting ID value for the last column.

Example:

./ci.sh xxtmg_sl_std_maintenance "RUN_SPEED,CT_6ORLESS,CT_6TO15,
CT_15ORMORE,CCT_6ORLESS,CCT_6TO15,CCT_15ORMORE,CREATED_BY,CREATION_DATE,
LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,ORGN_CODE,RESOURCES,ITEM_ID" 
xsm.awk
final.csv

Sample insert statement from the output:

insert into xxtmg_sl_std_maintenance (RUN_SPEED,CT_6ORLESS,CT_6TO15,
CT_15ORMORE,CCT_6ORLESS,CCT_6TO15,CCT_15ORMORE,CREATED_BY,CREATION_DATE,
LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,ORGN_CODE,RESOURCES,ITEM_ID)
values (
'800',
0.5*3600,
0.75*3600,
1*3600,
0.2*3600,
0.25*3600,
0.33*3600,
'1913',
SYSDATE,
'1913',
SYSDATE,
'-1',
'LIB',
'SLITTER',
(select item_id
from ic_item_mst
where item_no = '0315MS600 000'))

Ask Tom first

Oracle histogram statistics helps CBO to choose the right/best execution plan for the SQL. There are plenty of articles on it in the net and most of deals with it really deep. And of course the authoritative one is the Oracle Ref Guide . So I was planning to write a blog entry with simple example to explain histograms. Ok, then I thought let me quickly search asktom and make sure it don't have similar entry .. and hola ...there was this discussion on histogram ... much better and simpler ....boy .....that site is the best .....

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:707586567563

I decided to look at that site first for all my Oracle database problems .....

TNS

Have been using the abbrevation TNS in Oracle context for last four years but never cared about what actually it meant. Well by chance I learned today that TNS stands for Transparent Network Substrate.

IS_NUMBER()

If we you are storing NUMBER and character in a VARCHAR2 column and you want to validate if the value is number or not you can use the following regex based SQL.

SELECT 
 nvl(regexp_substr(:test_number,
     '^(\+|-)?([0-9])*(.)?([eE])?(\+|-)?([0-9])+$'),              
    'Not a number') 
FROM   DUAL 

You can read more about it in asktom

Bind Peeking

We have this really dandy performance monitoring tool which breaks down database activity (wait events) time wise, module wise, user wise etc. I am usually concerned with Top SQL tab which shows most non performing SQL (Top SQL !! misnomer I guess). So out of no where for few days one SQL got this top most spot and disappeared automagically. I decided to sleuth a little bit about this mysterious SQL.


The suspect

SELECT LOT_CREATED,
EXPIRE_DATE,
RETEST_DATE,
EXPACTION_DATE,
CREATION_DATE
FROM IC_LOTS_MST
WHERE LOT_ID = :B1


The Investigation


IC_LOTS_MST is Oracle Process Manufacturing table which stores lot information for the item. ITEM_ID and LOT_ID forms combinational primary key and there exists a unique index, IC_LOTS_MST_PK, on these columns. LOT_ID is unique but for the fact that there can be non lot controlled item for which LOT_ID = 0. So we also have non-unique index IC_LOTS_MST_I2 on LOT_ID.


select lot_dist, count(*)
from (select decode (lot_id,0,'ZERO','NONZERO') lot_dist
from ic_lots_mst)
group by lot_dist

LOT_DIST    COUNT
---------------------
ZERO       14,432
NONZERO  2,31,886

SELECT LOT_CREATED,
EXPIRE_DATE,
RETEST_DATE,
EXPACTION_DATE,
CREATION_DATE
FROM IC_LOTS_MST
WHERE LOT_ID = 0


Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS 13 K 1734
TABLE ACCESS FULL GMI.IC_LOTS_MST 13 K 604 K 1734


If I pass 0 as literal, CBO performs a FTS on IC_LOTS_MST. Make sense


SELECT LOT_CREATED,
EXPIRE_DATE,
RETEST_DATE,
EXPACTION_DATE,
CREATION_DATE
FROM IC_LOTS_MST
WHERE LOT_ID = 12345

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS 1 4
TABLE ACCESS BY INDEX ROWID GMI.IC_LOTS_MST 1 45 4
INDEX RANGE SCAN GMI.IC_LOTS_MST_I2 1 3


If I pass a non zero as literal, CBO picks a index. Make more sense.

The Findings
When we bounced database, the query in question was executed with a value 0. CBO peeked into the value first time, created a plan with FTS and continued using this. Until we bounced the database again and this time a non zero value was bound.

CUBE function and Pivot Table in SQL

I am currently working on a new OA component, pivot table. To mimic the pivot table in Microsoft Excel, I decided to design the functionality in Java rather than using Oracle's powerful CUBE functionality. I can definitely blog about the reasons behind this decision later, but I am overwhelmed by the power of using CUBE and decided to ramble something about it.

1. Its syntax is very simple
2. Its pretty fast

Illustration:

select e.dept_no, e.job_type, e.salary
from emp_table e

DEPT_NO   JOB_TYPE               SALARY
---------------------------------------
10                Manager         10,000
10                Clerk            3,000
10                Clerk            4,000
20                Manager         12,000
20                Clerk            3,000
 20                Clerk            5,000
20                Foreman          1,000

Say we want to see a tabular information of sum of salary for department wise and again group on it based on job type

select e.dept_no, e.job_type, sum(e.salary)
 from emp_table e
group by cube(e.dept_no, e.job_type)

DEPT_NO   JOB_TYPE   SALARY
 ---------------------------------------
          Manager         22,000
          Clerk           15,000
          Foreman          1,000
10        Manager         10,000
10        Clerk            7,000
 10        Foreman              0
10                        17,000
20        Manager         12,000
 20        Clerk            8,000
  20        Foreman          1,000
 20                        21,000
                          38,000

Now we can use the famous pivoting technique in SQL to get the same information like Excel.

select nvl(job_type, 'Grand Total') JOB_TYPEX
       decode(dept_no,10,max(s),0) DEPT_10,
       decode(dept_no,20,max(s),0) DEPT_20,
       decode(nvl(dept_no,-999),-999,max(s),0) ALL_DEPT
from (select e.dept_no, e.job_type, sum(e.salary) s
from emp_table e
group by cube(e.dept_no, e.job_type))
group by job_type
order by job_type

JOB_TYPEX    DEPT_10   DEPT_20  ALL_DEPT
-----------------------------------------
Clerk         7,000    8,000     15,000
Foreman           0    1,000      1,000
Manager      10,000   12,000     22,000  
Grand Total  17,000   21,000     38,000

But the problem is that, we must know all departments to pivot it.

set serveroutput on by default

DBMS_OUTPUT.PUT_LINE is unavoidable tool for an Oracle developer to debug issues. We must issue a sqlplus comman "SET SERVEROUT ON" to see the output from the above function in our console. By default it is off and we must issue the command explicitly to turn it on. Here is a small trick to turn it on by default.

1. Open login.sql/glogin.sql from SQL_PATH. (Search or grep for that file in the directory you have installed sqlplus.)
2. Add SET SERVEROUT ON command to that file.

Thats it. glogin.sql/login.sql will be executed every time you open a new sqlplus session.

Variable IN List

Another classic performance problem in OAF is to add a dynamic WHERE clause to VO with a variable IN list. i.e we need to add some thing like

<column_name> IN (:1, :2, :3 ...:N)

Where N can vary.


Illustrating various options with an example.

Assume we have a VO, ExampleVO with following SQL

select emp_name, dept_no
from emp_table

And we want to add a filter of dynamic list of dept_no.

Option 1: Use SQL Literal.
Build the WHERE clause with String literal.

public void initQuery(String depNoList)
{
String whereClause = "dept_no IN " + "(" + depNoList " + ")";
...
}

But this is a big NO-NO if depNoList changes, as it forces hard parse of the SQL. So this should never be considered as an option.


Option 2: Fix Max(N) at design time.
Fix maximum number of variables at design time, say M.
If N <= M, then
bind 1..N from the list
bind N+1 ..M with a dummy value or one value from the list.
ELSE
// pick a M such that this will never happen in real time
// if it happens then the choice is yours, you can either throw an error
// or always just bind N




public void initQuery(String depNoList)
{
String whereClause = "dept_no IN (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10)";
Vector inList = strToVector(depNoList); // simple method to convert str to vector
Vector bindValues = ne Vector(10);
if(inList != null && inList.size() > 0)
{
Enumeration e = m_inList.elements();
int i=0;
while(e.hasMoreElements() && i < 10)
{
bindValues.addElement(e.nextElement());
i++;
}
for(int j=bindValues.size(); j< 10 ; j++)
{
bindValues.addElement(bindValues.elementAt(0)); // fill rest with first value
}

}
else
{
for(int i=0; i<10 ; i++)
bindValues.addElement("DUMMY");
}
...
}

Option 3: Use Global Temporary Table.

Create a Global Temporary Table

CREATE GLOBAL TEMPORARY TABLE xx_bind_value_table
(
key VARCHAR2(50),
value VARCHAR2(100),
) ON COMMIT PRESERVE ROWS


Before calling initQuery method insert these values into GTT and pass the key


public void initQuery(String key)
{
String whereClause = "dept_no IN (SELECT value FROM xx_bind_value_table WHERE key = :1)";
..
}

This option have lots of disadvantages
1. We must generate a unique key per session
2. Some PLSQL/EO code required to insert the value into GTT
3. GTT is not recommended by OAF and by using GTT our code will not be Connection Agnostic.


Option 4: TABLE CAST Operator.

Reference: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:110612348061

a. Create a TABLE TYPE

create or replace type inListTableType as table of VARCHAR2;

b. Create a PLSQL function which converts comma separated list to the above table type object. (Code from asktom)

create or replace function toTable( p_str in varchar2 ) return inListTableType
as
l_str varchar2(4000);
l_n number;
l_data inListTableType := inListTableType();
begin
l_str := l_str || ',';
loop
l_n := instr( l_str, ',' );
exit when (nvl(l_n,0) = 0);
l_data.extend;
l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
l_str := substr( l_str, l_n+1 );
end loop;
return l_data;
end;



c. Add the following where clause


public void initQuery(String depNoList)
{
String whereClause = "dept_no in ( select * from THE ( select cast( toTable( :1 ) as inListTableType ) from dual ) )";
// bind the depNoList

..
}


By far the best option I guess.