Dropping Constraints.

I was working on dropping a primary key constraint on a table today and when I was recreating the constraint back my create statement was failing saying that an object already exists with that name. When I checked I found that the index on the table used for the primary key was not dropped when I dropped the constraint. This I thought was a little strange. So I tried to retest why this was happening, but the next time I created and dropped the primary key constraint both the constraint and the index were dropped. When I googled for the possible cause of this kind of phenomenon I found that when the table is created in oracle 9 and we are tying to drop the constraint in oracle 10 the indexes are not dropped. To ensure that the index is also dropped below statement can be used.

alter table X drop constaint x_pk drop index;

Updating partition key column

Recently i was working on a generating some test data into a large table which is partitioned by year. I was supposed to generate some data for the current year. And i thought i would just update the date part to current year and i would have the data. But since the table was partitioned and i was trying to update the partitioned column i got the below error and the update failed.
ORA-14402: updating partition key column would cause a partition change
Taking a look at the error it looked more like a warning and less a error. So i googled to find how i could fix the issue.
Below is the alter statement that I applied on the table.
alter table table_name enable row movement;
I did my update and restored the table back by applying the below statement.
alter table table_name disable row movement;

Malireddy Look-alike Meter

MyHeritage: Family tree - Genealogy - Celebrity - Collage - Morph

PL/SQL: numeric or value error: host bind array too small

I was getting this error today when i was debugging one bug in my code. On goggling i found that this is related to the dbms_output package that is being called in my code. The error turns out to be that when dbms_output.put_line proc is execute with a parameter whose length is more than 255 characters then the below error is generated.

SQL> exec dbms_output.put_line('123456789012345678901234567890123456789012345678
90123456789012345678901234567890123456789012345678901234567890123456789012345678
90123456789012345678901234567890123456789012345678901234567890123456789012345678
9012345678901234567890123456789012345678901234567890');

ERROR:
ORA-06502: PL/SQL: numeric or value error: host bind array too small
ORA-06512: at line 1


So when ever you execute dbms_output take care to see that the parameter is not more than 255 chanracter. I ran this on Oracle Database 10g Enterprise Edition Release 10.2.0.3.0.

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.