Programming

select ro.name table_name,
substr(initcap(cc.column_name), 1, 30) column_name, cc.position,
cn.name constraint_name, ru.name owner
from sys.obj$ o, sys.user$ u, sys.con$ cn, sys.cdef$ c, sys.obj$ ro,
dba_cons_columns cc, sys.con$ rcn, sys.user$ ru
where o.owner# = u.user#
and u.name = ‘&&SCHEMANAME’
and o.name = ‘&&TABLENAME’
and c.robj# = o.obj#
and c.con# = cn.con#
and ro.obj# = c.obj#
and cc.owner = u.name
and cc.table_name = o.name
and rcn.con# = c.rcon#
and cn.owner# = ru.user#
and c.type# = 4
and cc.constraint_name = rcn.name
order by u.name, ro.name, cn.name, cc.position;

To Identify the Tables referred by the Given Table:

select a.table_name, initcap(b.column_name) column_name, b.position,
a.constraint_name, a.owner
from (select ao.name table_name,
au.name owner,
bcn.name constraint_name,
decode(ac.type#, 4,
decode(ac.refact, 1, ‘CASCADE’, 2, ‘SET NULL’, ‘NO ACTION’),
null) delete_rule,
bo.name tn
from sys.cdef$ bc, sys.con$ bcn, sys.obj$ bo, sys.user$ bu,
sys.con$ brc, sys.user$ bru, sys.obj$ bro,
sys.cdef$ ac, sys.con$ acn, sys.obj$ ao, sys.user$ au
where bc.con# = bcn.con#
and bc.obj# = bo.obj#
and bo.owner# = bu.user#
and bu.name = ‘&&SCHEMANAME’
and bo.name = ‘&&TABLENAME’
and bc.rcon# = brc.con#(+)
and brc.owner# = bru.user#(+)
and bc.robj# = bro.obj#(+)
and ac.con# = bc.rcon#
and ac.con# = acn.con#
and ao.obj# = ac.obj#
and ao.owner# = au.user#
and ac.type# in (2,3)) a,
dba_cons_columns b
where b.table_name = a.tn
and b.constraint_name = a.constraint_name
order by a.table_name, a.constraint_name, b.position;

Hope this is useful for you at times

cursor emp_cur is
select employee_id
from employee where department_id = dept_in;

begin
for rec in emp_cur loop
update employee set salary = salary + 1000
where employee_id = rec.employee_id;
end loop;
end;

select employee_id
bulk collect into collection_var
from employee where department_id = dept_in;

begin
forall i in collection_var.first .. collection_var.last
update employee set salary = salary + 1000
where employee_id = collection_var(i);
end;

Generating CSV files from tables is a task which we do many a times. But as I said Oracle 10g gives many features , this is really cool, simple solution using xml and regular expressions in a single query.

Withouth specifying the column names is the good point to note

I am in a Oracle 10g developer’s workshop. In these two days some interesting things in Oracle 10g which I found are,

• Automatic SQL Tuning in Oracle Database 10g using the DBMS_SQLTUNE. On good illustrations you can use this.
• Resumable timeout, this really saves most of the time in redoing things due to a small mistake we failed to notice. At least for me it will save loads of time on schema imports. You must have a deep look into this.
• File Transfers, using oracle this will really make database programming stronger.
• Conditional Compilation will really make debugging easier (Inherits A ‘C’ Language #ifdef Feature).
• Pattern Matching, this regular expression will give some Java edge.

To try out new features of 10g, you can always request a workspace from oracle at this place.

Though I work with Oracle DB for the past nine months religiously this is my first post anywhere related to oracle. Its high time , I thought to give some post on Oracle ,which are though not something which was never found by any other people but it was quite interesting for me when I first tried it. Though there are many virtuosos on oracle blogging around the world vigorously on oracle, I also wanted to be a small part on this big knowledge sharing base.
Concatenating the rows of the table will always be required when you are working with database. At those time the following query would be handy for you.

hari9ir2@GENEVART>create table t(
2 name varchar2(20));
Table created.
hari9ir2@GENEVART>INSERT INTO t VALUES(’Hari’);
1 row created.
hari9ir2@GENEVART>INSERT INTO t VALUES(’Haran’);
1 row created.
hari9ir2@GENEVART>INSERT INTO t VALUES(’Ragunathan’);
1 row created.

SELECT LTRIM(MAX(
SYS_CONNECT_BY_PATH(NAME , ‘ ‘)),’ ‘)AS Full_Name
FROM (SELECT NAME , ROWNUM rn FROM t)
START WITH rn = 1
CONNECT BY PRIOR rn = rn -1;

FULL_NAME
————————
Hari Haran Ragunathan

To know more on the this refer this column in oracle magazine - On Favorites and Connect by .
Hope this tip could be useful for you.