The Affluent Society

I happen to come across this link and found these quotes really intresting.

"Popularity is not always a test of needed intelligence"

"There remains always the possibility, even the probability, that they(Books) do more for the self-esteem of the author than for the fate of the world"

Hope fully i will get to read this book some time soon.

Oracle Article Links.

The below links have listed some of the commonly used linux commands.
Linux Commands - Part 1
Linux Commands - Part 2

Merge and Invalid Identifier Error.

Today early in the morning my boss called me and gave me a requirement to load a file into a table. And this new table has to be merged with the existing table in the database. i.e if it has any new rows then insert into the existing table and for the existing rows if there are new additions then update the existing table. I was so happy that i knew the oracle's merge command and thought I would finish my work in no time. My happiness lasted only till i encountered the "ORA-00904: invalid identifier" error.

Well here is what happened.

SQL> desc ms_zip_code_dtl_main;
Name Null? Type
----------------------------------------- -------- ------------------

COUNTRY_CD NOT NULL CHAR(2)
STATE_ID NOT NULL CHAR(2)
ZIP NOT NULL VARCHAR2(30)
CITY NOT NULL VARCHAR2(35)
COUNTY_NM VARCHAR2(50)
ENTRY_DT NOT NULL DATE
ENTRY_USR NOT NULL CHAR(8)

SQL> drop table ms_zip_code_dtl_main_temp;

Table dropped.

Elapsed: 00:00:01.09
SQL> CREATE TABLE ms_zip_code_dtl_main_temp AS
2 SELECT *
3 FROM ms_zip_code_dtl_main;

Table created.

SQL> merge
2 into ms_zip_code_dtl_main a
3 using ms_zip_code_dtl_main_temp b
4 on(a.zip = b.zip
5 and a.country_cd = b.country_cd
6 and a.state_id = b.state_id
7 and a.city = b.city)
8 when matched then
9 update
10 set a.country_cd = b.country_cd,
11 a.state_id = b.state_id,
12 a.city = b.city,
13 a.county_nm = b.county_nm,
14 a.entry_dt = b.entry_dt,
15 a.entry_usr = b.entry_usr
16 when not matched then
17 insert(a.country_cd,
18 a.state_id,
19 a.zip,
20 a.city,
21 a.county_nm,
22 a.entry_dt,
23 a.entry_usr)
24 values(b.country_cd,
25 b.state_id,
26 b.zip,
27 b.city,
28 b.county_nm,
29 b.entry_dt,
30 b.entry_usr)
31 /
and a.country_cd = b.country_cd
*
ERROR at line 5:
ORA-00904: "A"."COUNTRY_CD": invalid identifier

And then i saw this invalid identifier error and then i was thinking why the hell is it giving me this error. Because country_cd is very well a column in both the table. And then after some juggling i went on the all answers site Google and found this link which opened my eyes.

The problem in my case is that i am updating the columns that i am using in the join between the two tables. Actually if you think it really doesn't make any sense updating the columns when you got the rows on the condition that both the columns are equal.

What next, i made the change and my code works just fine.
SQL> ed
Wrote file afiedt.buf

1 merge
2 into ms_zip_code_dtl_main a
3 using ms_zip_code_dtl_main_temp b
4 on(a.zip = b.zip
5 and a.country_cd = b.country_cd
6 and a.state_id = b.state_id
7 and a.city = b.city)
8 when matched then
9 update
10 set a.county_nm = b.county_nm,
11 a.entry_dt = b.entry_dt,
12 a.entry_usr = b.entry_usr
13 when not matched then
14 insert(a.country_cd,
15 a.state_id,
16 a.zip,
17 a.city,
18 a.county_nm,
19 a.entry_dt,
20 a.entry_usr)
21 values(b.country_cd,
22 b.state_id,
23 b.zip,
24 b.city,
25 b.county_nm,
26 b.entry_dt,
27* b.entry_usr)

And time and again it thought me not to assume things.

sys_connect_by_path

The function sys_connect_by_path is a analytical function used in hierarchical queries. This was explained nicely in one of Tom's articles in Oracle Magazine. Here is the link
http://www.oracle.com/technology/oramag/oracle/07-jul/o47asktom.html

Serially Reusable Packages

Serially Reusable packages can be used in scenarios where we dont want the packages to store the global variable through the session. The below code demonstrates the use of PRAGMA SERIALLY_REUSABLE clause in packages. More information is available at Oracle Documentation

SQL> create or replace package test_serially_reusable as
2
3 var1 number;
4 var2 number;
5 procedure setvar1(p_num number);
6 procedure setvar2(p_num number);
7 end test_serially_reusable ;
8 /

Package created.

SQL>
SQL> create or replace package body test_serially_reusable as
2
3 procedure setvar1(p_num number) is
4 begin
5 var1:=p_num ;
6 dbms_output.put_line('var1->'||var1);
7 dbms_output.put_line('var2->'||var2);
8 end;
9 procedure setvar2(p_num number) is
10 begin
11 var2:=p_num ;
12 dbms_output.put_line('var1->'||var1);
13 dbms_output.put_line('var2->'||var2);
14 end;
15 end test_serially_reusable ;
16 /

Package body created.

SQL> show err
No errors.
SQL>
SQL> exec test_serially_reusable.setvar1(100);
var1->100
var2->

PL/SQL procedure successfully completed.

SQL>
SQL> exec test_serially_reusable.setvar2(200);
var1->100
var2->200

PL/SQL procedure successfully completed.

SQL>
SQL> create or replace package test_serially_reusable as
2 pragma serially_reusable;
3 var1 number;
4 var2 number;
5 procedure setvar1(p_num number);
6 procedure setvar2(p_num number);
7 end test_serially_reusable ;
8 /

Package created.

SQL>
SQL> create or replace package body test_serially_reusable as
2 pragma serially_reusable;
3 procedure setvar1(p_num number) is
4 begin
5 var1:=p_num ;
6 dbms_output.put_line('var1->'||var1);
7 dbms_output.put_line('var2->'||var2);
8 end;
9 procedure setvar2(p_num number) is
10 begin
11 var2:=p_num ;
12 dbms_output.put_line('var1->'||var1);
13 dbms_output.put_line('var2->'||var2);
14 end;
15 end test_serially_reusable ;
16 /

Package body created.

SQL>
SQL>
SQL> exec test_serially_reusable.setvar1(100);
var1->100
var2->

PL/SQL procedure successfully completed.

SQL>
SQL> exec test_serially_reusable.setvar2(200);
var1->
var2->200

PL/SQL procedure successfully completed.

begin
test_serially_reusable.setvar2(200);
dbms_output.put_line('newvar2->'||test_serially_reusable.var2);
test_serially_reusable.setvar1(400);
dbms_output.put_line('newvar1->'||test_serially_reusable.var1);
dbms_output.put_line('newvar2->'||test_serially_reusable.var2);
end;
/

begin
dbms_output.put_line('newvar1->'||test_serially_reusable.var1);
dbms_output.put_line('newvar2->'||test_serially_reusable.var2);
end;
/

begin
test_serially_reusable.setvar2(500);
dbms_output.put_line('newvar1->'||test_serially_reusable.var1);
dbms_output.put_line('newvar2->'||test_serially_reusable.var2);
test_serially_reusable.setvar1(600);
dbms_output.put_line('newvar1->'||test_serially_reusable.var1);
dbms_output.put_line('newvar2->'||test_serially_reusable.var2);
end;
/


SQL> spool off

Group by

Group by is a requirement for aggregate functions but that is not true the other way around. It is not necessary to have a aggregate functions to have group by in the query.

Eg :

select empno , count(*)
from emp
group by empno

To use count(*) or sum or any other aggregate function we need to use the group by clause.

select empno
from emp
group by empno

We need not require a aggregate function to use a group by clause. The above query just returns all the distinct empnos.

Defining Variables in sqlplus

The following is an example of how variables can be declared in sqlplus sessions.

var col1 char(2000)
var col2 char(2000)
exec ms_prc(123,123,:col1,:col2);
set pagesize 0
set linesize 10000
spool on
print col1
print col2
spool off

sqlplus - Termout

Termout is one of those sqlplus settings which tend to cause confusion. It only applies to output from running script files.

abc.sql:
set termout off
select 'abc' from dual

and run it like this in sqlplus:
@abc.sql

and no output is generated in the terminal. But this does'nt work for scripts which contain anonymous pl\sql blocks etc.

Multitable Insert.

Multi table insert seems to be a pretty cool method of inserting into multiple tables while selecting from a single source table. Following are some examples that i tried out.

create table emp_exec as
select *
from emp
where 1=3;

create table emp_nonexec as
select *
from emp
where 1=3;

-- This below one is a simple method of inserting into two table based on certain criteria. The all clause is used to execute all the inserts.

insert all
when sal > 3000 then
into emp_exec
when sal <= 3000 then into emp_nonexec select * from emp; -- 14 rows inserted The first clause is used to execute the first part first and when the condition is not met then execute the next part. insert first when sal > 3000 then
into emp_exec
else
into emp_nonexec
select * from emp;

-- 14 rows inserted

create table emp_name as
select empno , ename
from emp
where 1=3;

create table emp_dept as
select empno , deptno
from emp
where 1=3;

insert all
into emp_name values (empno , ename)
into emp_dept values (empno , deptno)
select * from emp;

-- 28 rows inserted

Just a little saying....

It may not be finished in first hundred days. It may not be finished in first thousand days. ... It may not be finished in our lives on this planet. But let us begin

zcat in Bash

zcat is a command to display the contents of a file that is zipped using compress in unix.
Eg:
zcat log
The above commad looks for a file of the format log.Z in the directory and decompresses it and then displays to the standard output.

PL\SQL Program to get the counts of all the tables in a schema.

Here is a small PL\SQL program to get the number of rows in all the tables accessible from a scheam. It uses dyanmic SQL in the program.

*************************************************************************************

Rem This program get the number of rows in each table that is accessible from a schema.

SET FEEDBACK OFF;
SET TERM OFF
SET ECHO OFF;
SET SERVEROUTPUT ON size 1000000
SPOOL countall.csv
--CREATE OR REPLACE PROCEDURE countall AS
DECLARE
t_c1_tname all_tables.table_name%TYPE;
t_owner_name all_tables.owner%TYPE;
t_command varchar2(200);
t_cid integer;
t_total_records number(10);
stat integer;
row_count integer;

CURSOR c1 IS SELECT owner , table_name
FROM all_tables
WHERE owner = user
ORDER BY table_name;
BEGIN
DBMS_OUTPUT.PUT_LINE('OWNER,TABLE NAME,NUMBER OF ROWS');
FOR r_c1 IN c1
LOOP
t_command := 'SELECT COUNT(0) FROM '||r_c1.owner||'.'||r_c1.table_name;
t_cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(t_cid,t_command,dbms_sql.native);
DBMS_SQL.DEFINE_COLUMN(t_cid,1,t_total_records);
stat := DBMS_SQL.EXECUTE(t_cid);
row_count := DBMS_SQL.FETCH_ROWS(t_cid);
DBMS_SQL.COLUMN_VALUE(t_cid,1,t_total_records);
DBMS_OUTPUT.PUT_LINE(r_c1.owner||','||r_c1.table_name||','||t_total_records);
DBMS_SQL.CLOSE_CURSOR(t_cid);
END LOOP;
END;
/
SPOOL OFF;
SET TERM ON;
SET ECHO ON;

*************************************************************************************

Updating from Two Tables

We can update data from one table using data from another table. The following set of statements explains how it is done. One requirement fro updates like this is that both the tables should be key preserved.

SQL> create table tab1 ( col1 number , col2 varchar(20), constraint pk_col1 primary key (col1) );

Table created.

SQL> insert into tab1 values (1 , 'nirmala');

1 row created.

SQL> insert into tab1 values (2 , 'rahul');

1 row created.

SQL> insert into tab1 values (3 , 'rahul');

1 row created.

SQL> insert into tab1 values (4 , 'kumar');

1 row created.

SQL> insert into tab1 values (5 , 'pragati');

1 row created.

SQL> commit;

Commit complete.

SQL> create table tab2 as select * from tab1;

Table created.

SQL> select * from tab1;
cls COL1 COL2
---------- --------------------
1 nirmala
2 rahul
3 rahul
4 kumar
5 pragati

SQL> select * from tab1;
cls COL1 COL2
---------- --------------------
1 nirmala
2 rahul
3 rahul
4 kumar
5 pragati

SQL> update tab2 set col2 = 'chaitu' where col1 = 2;

1 row updated.

SQL> select * from tab2;
cls COL1 COL2
---------- --------------------
1 nirmala
2 chaitu
3 rahul
4 kumar
5 pragati

SQL> update (select a.col2 target, b.col2 source from tab1 a, tab2 b where a.col1 = b.col1) set target = source;
update (select a.col2 target, b.col2 source from tab1 a, tab2 b where a.col1 = b.col1) set target = source
*
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table


SQL> create unique index idx_tab2 on tab2 (col1);

Index created.

SQL> update (select a.col2 target, b.col2 source from tab1 a, tab2 b where a.col1 = b.col1) set target = source;

5 rows updated.

SQL> commit;

Commit complete.

SQL> select * from tab1;
cls COL1 COL2
---------- --------------------
1 nirmala
2 chaitu
3 rahul
4 kumar
5 pragati

Unix scripts

The following is a sample shell scripts useful with oracle

#!/bin/ksh
sqlplus /nolog << EOF
CONNECT scott/tiger
SPOOL /u01/emp.lst
SET LINESIZE 100
SET PAGESIZE 50
SELECT *
FROM emp;
SPOOL OFF
EXIT;
EOF

JDBC program to download LOBs

The following is the sample program to download LOBs using JDBC.

import java.sql.*;
import java.io.*;
import java.util.Date;
import java.text.*;
/*
* This program is used to download Large Bill Images from Bill Image table. This program reads the input file which is given as
* the first parameter. In the input file the first line is the date string i.e the where clause for the statement date
* (Eg >='01-mar-2006', between '01-mar-2006 and '01-apr-2006' etc. From the second line onwards each line contains the userid
* of the users for which the invoice needs to be pulled. The images are stored in the database in the compressed format. Most of
* time there are two images that are generated for each user. One in text format and the other in html format. This program is
* designed in such a way that it will pull only the text image(image type 0). If there is no image generatd in the text format
* then will it download the html image.
*/
public class PullImage {
public static void main(String[] args) throws Exception{

SimpleDateFormat df = new SimpleDateFormat("dd-MM-yyyy ss");
String date = df.format(new Date());
File newdir = new File(date);
if(newdir.mkdir())
System.out.println("Created the direcotry"+date);
else {
System.out.println("Can not Create the direcotry"+date);
}

for(int a=0;a BufferedReader br = new BufferedReader(new FileReader(new File(args[a])));
String datestring = br.readLine();
String userid;





while((userid=br.readLine())!=null) {
DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
Connection conn ;
Statement stmt;
ResultSet rs;
int serverid=0;
int accountno=0;
try {
conn =DriverManager.getConnection("jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = gbcat.vip.ebay.com)(PORT = 1521))(CONNECT_DATA =(SID = gbcat)))","arborsv","arborsv");
stmt = conn.createStatement();
rs = stmt.executeQuery("select server_id , account_no"+
" from external_id_acct_map "+
" where external_id = '" + userid+ "'"+
" and external_id_type = 3 "+
" and inactive_date is null " );

rs.next();
serverid = rs.getInt(1)-2;
accountno = rs.getInt(2);

rs.close();
stmt.close();
conn.close();

}catch(SQLException e){
System.out.println("There is a SQL Exception ->"+e);

}

try{
System.out.println("Account No = "+accountno);
conn = DriverManager.getConnection("jdbc:oracle:thin:@gbcust"+serverid+".vip.ebay.com:1521:gbcust"+serverid,"arborsv","arborsv");
stmt=conn.createStatement();
rs = stmt.executeQuery( "select bill_image ,to_char(bin.to_date , 'MONdd') " +
"from bill_image bim, bill_invoice bin "+
"where bim.account_no = bin.account_no " +
"and bim.bill_ref_no = bin.bill_ref_no "+
"and bin.statement_date " + datestring +
"and bin.account_no = '"+accountno +"' order by image_type ");

rs.next();
//String st = rs.getString(1);
Blob blob = rs.getBlob(1);
InputStream is = blob.getBinaryStream() ;
FileOutputStream fos = new FileOutputStream(new File(date+"/"+userid+rs.getString(2)+"_Invoice"));
int i = (int)blob.length();
int j = i/1048576;
int completed=0;
for(int k=0;k<=j;k++) {

int first = k*1048576+1;
int last;

if(k==j)
last = i%1048576;
else
last = 1048576;

completed=completed+last;
byte data[] = blob.getBytes(first, last);
fos.write(data);
System.out.println("Completed Downloading "+ completed/1048576 + " MB of Data");
}

is.close();
fos.close();

rs.close();
stmt.close();
conn.close();

System.out.println("---------------------------------------");

System.out.println("Completed Downloading Image for "+userid);

System.out.println("---------------------------------------");

}catch(SQLException e) {
System.out.println("Encountered a SQL Exception for the userid "+userid+"-->"+e);
}catch(IOException e ) {
System.out.println("Encountered a I/O Exception for the userid "+userid+"-->"+e);
}


}
}

}
}

Insert from list of values

The following link from asktom has a beautiful way of inserting a list of values direclty into a table with just one insert statement

Click Here

In this technique rownum column is used to restrict the number of output rows retrived.

Date in Sqlldr

When loading date formats through sqlldr you can use to_date function. See the example below
LOAD DATA
INFILE data.csv
BADFILE data.bad
DISCARDFILE data.dsc
TRUNCATE INTO TABLE external_id_equip_map
Fields terminated by "," Optionally enclosed by '"'
TRAILING NULLCOLS
(EXTERNAL_ID, SUBSCR_NO, SUBSCR_NO_RESETS, EXTERNAL_ID_TYPE, ACCOUNT_NO, SERVER_ID,
ACTIVE_DATE "to_date(:active_date , 'dd/mm/yyyy')",
INACTIVE_DATE)
BEGINDATA
55349067007,77815573,0,1,77810573,7,31/05/2002
vw-audi-special,77815573,0,3,77810573,7,31/05/2002
77815573,77815573,0,2,77810573,7,31/05/2002

If there are multiple formats in the input data then you can define a custom function and use that function instead of to_date.
Refer to the following link for more details

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1365403168188

Unable to connect 10g database from 8i clinet.

I had oracle 8i client already installed on my machine and then i installed 10g database on my machine and then tried to connect to the 10g database but couls not connect to it. Before that i set my 8i bin folder in the system path variable prior to the 10g bin folder in. After this i was not able to connect through SQL PLUS(though Oracle cleint not command prompt) and through TOAD to the local database. When i tried to reconfigure my tnsnames through tnsnames it hanges. Then i replaced the existing tnsnames with one from a different machine and tries to reconfigure it. -- Lesson "Dont ever try to reconfigure tnsnames manually. Go through Net Configuration Assistant".
Even after configuring through Net configuration Assistant and the connection test being successfull there i was not able to connect.
Then befoer connecting though cmd i set the variable as -- set ORACLE_SID = XE. Then it worked on cmd.
Then i created a new system variable ORACLE_SID and then assigned a value of the SID to it. Idea from this site http://www.cryer.co.uk/brian/oracle/ORA12560.htm
then it worked for sqlplus. For toad i had to give a differnt connet sting to the database similar to the one in the tnsnames file.

Killing Oracle Sessions

Sessions can be killed from within oracle using the ALTER SYSTEM KILL SESSION syntax.

First identify the offending session as follows:

SELECT s.sid,
s.serial#,
s.osuser,
s.program
FROM v$session s;

SID SERIAL# OSUSER PROGRAM
---------- ---------- ------------------------------ ---------------
1 1 SYSTEM ORACLE.EXE
2 1 SYSTEM ORACLE.EXE
3 1 SYSTEM ORACLE.EXE
4 1 SYSTEM ORACLE.EXE
5 1 SYSTEM ORACLE.EXE
6 1 SYSTEM ORACLE.EXE
20 60 SYSTEM DBSNMP.EXE
43 11215 USER1 SQLPLUSW.EXE
33 5337 USER2 SQLPLUSW.EXE

The SID and SERIAL# values of the relevant session can then be substituted into the following statement:

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';

In some situations the Oracle.exe is not able to kill the session immediately. In these cases the session will be "marked for kill". It will then be killed as soon as possible. If the marked session persists for some time you may consider killing the process at the operating system level.
The NT Approach
To kill the session via the NT operating system, first identify the session as follows:

SELECT s.sid,
p.spid,
s.osuser,
s.program
FROM v$process p,
v$session s
WHERE p.addr = s.paddr;

SID SPID OSUSER PROGRAM
---------- --------- ------------------------------ ---------------
1 310 SYSTEM ORACLE.EXE
2 300 SYSTEM ORACLE.EXE
3 309 SYSTEM ORACLE.EXE
4 299 SYSTEM ORACLE.EXE
5 302 SYSTEM ORACLE.EXE
6 350 SYSTEM ORACLE.EXE
20 412 SYSTEM DBSNMP.EXE
43 410 USER1 SQLPLUSW.EXE
33 364 USER2 SQLPLUSW.EXE

The SID and SPID values of the relevant session can then be substituted into the following command issued from the command line:

C:> orakill ORACLE_SID spid

The session thread should be killed immediately and all resources released.
The UNIX Approach
To kill the session via the UNIX operating system, first identify the session in the same way as the NT approach, then substitute the relevant SPID into the following command:

% kill -9 spid

If in doubt check that the SPID matches the UNIX PROCESSID shown using:

% ps -ef | grep ora

The session thread should be killed immediately and all resources released.

see the link http://www.oracle-base.com/articles/8i/KillingOracleSessions.php for more information.

Java Modulo

When the following method is executed it determines the values of add or even only for
+ve integers and not for -Ne integers. Because when we do modulo of -Ve odd numbers we get a remainder of -1 and not 1.

public static boolean isOdd(int i) {
return i%2==1;
}