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.