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