Oracle PL/SQL | Sorting with NULL

The query to sort nulls in a particular column

select <Column Names> from <Table>
order by <Column List> {asc/desc} {nulls (first/last)};

Oracle PL/SQL | To returns single line query output as an XML

create or replace procedure QueryToXML(
-- Date: October 23, 2008
-- Programmer: Ranjeeth Shetty
-- Parameter: 1. pvar_rowTag varchar2
-- To Set the Row Tag for XML Created
-- 2. pvar_SQLQuery varchar2
-- To Send Single row Query
-- 3. pclb_resultXML Clob
-- Output variable that returns clob XML
pvar_rowTag in varchar2 := 'RowDetail',
pvar_SQLQuery in varchar2,
pclb_resultXML out clob
) as
ctx dbms_xmlgen.ctxHandle;
xml CLOB;
BEGIN
ctx := dbms_xmlgen.newContext( pvar_SQLQuery );
dbms_xmlgen.setRowTag(ctx, pvar_rowTag );
xml := dbms_xmlgen.getXML(ctx);
dbms_xmlgen.closeContext(ctx);
pclb_resultXML := xml;
END;
/

Oracle PL/SQL | Finding Errors in compiled Objects

It is very common to compile more than on oracle Objects. When you need to trace object that failed compilation then following query can be handy:

select * from user_errors;

Oracle PL/SQL | A Debugger Procedure

-- Creating De-bug Table
create table Debug_table(
field01 varchar2(2000),
field02 varchar2(2000)
);
-- Creating De-Bug Procedure
create or replace procedure debugger(
value01 varchar2,
value02 varchar2
) as
PRAGMA AUTONOMOUS_TRANSACTION;
begin
insert into Debug_table
values ( value01, value02 );
commit;
end;
/
-- Viewing Debug Contents
select * from debug_table;
-- Drop Procedure
drop procedure debugger;
-- Drop De-bug table
drop table debug_table;
-- Remove old De-bugs
TRUNCATE TABLE debug_table;

Oracle PL/SQL | Query to Find loacked Objects

SELECT object_name "Object", session_id, NVL (lockwait, 'ACTIVE') "Wait",
DECODE (locked_mode,
2, 'ROW SHARE',
3, 'ROW EXCLUSIVE',
4, 'SHARE',
5, 'SHARE ROW EXCLUSIVE',
6, 'EXCLUSIVE',
'UNKNOWN'
) "Lockmode",
os_user_name "Terminal", oracle_username "Locker", program "Program",
object_type "Object Type", serial# "Serial", c.SID
FROM v$locked_object a, all_objects b, v$session c
WHERE a.object_id = b.object_id AND c.SID = a.session_id
ORDER BY 1 ASC;

Oracle PL/SQL | Calling Java function in Oracle Procedure and Function

Use following steps:

1. Load Java Class

loadjava -user

2. Create Oracle Procedure/Function pointing to Java Function

create or replace procedure HelloWorld
as language java
name '<>'

3. Execute the procedure

Note: To map Java output to DBMS Output use:

SQL> SET SERVEROUTPUT ON SIZE 5000
SQL> CALL dbms_java.set_output(5000);

Reference: http://download.oracle.com/docs/cd/B19306_01/java.102/b14187/chthree.htm

Oracle PL/SQL | Script to generate SQL for Primary contraint for all tables

declare
cursor get_cons is
select distinct dc.table_name table_name, decode ( substr(dc.constraint_name,1,3), 'SYS', ' ', 'CONSTRAINT ' dc.constraint_name ) contraint, wmsys.wm_concat(ucc.column_name) over ( partition by dc.constraint_name order by dc.constraint_name ) cols
from user_constraints dc,
user_cons_columns ucc
where dc.table_name like '%'
and dc.constraint_type = 'P'
and dc.table_name = ucc.table_name
and dc.constraint_name = ucc.constraint_name;
lv_sql varchar2(2000);
begin
for i in get_cons
loop
lv_sql := 'ALTER TABLE '
I.TABLE_NAME
' ADD ('
i.contraint
' PRIMARY KEY ( '
I.COLS
' )); ';
dbms_output.put_line( lv_sql );
end loop;
end;

PL/SQL | Query to find Primary Key columns of a Table

select DISTINCT dc.table_name, dc.constraint_name, ucc.column_name
from dba_constraints dc,
user_cons_columns ucc
where dc.table_name = ''
and dc.constraint_type = 'P'
and dc.table_name = ucc.table_name
and dc.constraint_name = ucc.constraint_name

Oracle PL/SQL | Using wmsys.wm_concat()

There is always a need when you have to concatinate values of more than one row. this can be achived by following query:

with table01 as(
select 1 field01, 'One' field02 from dual union
select 1 field01, 'Two' field02 from dual union
select 1 field01, 'Three' field02 from dual union
select 2 field01, 'Four' field02 from dual union
select 2 field01, 'Five' field02 from dual union
select 2 field01, 'Six' field02 from dual union
select 3 field01, 'Seven' field02 from dual union
select 3 field01, 'Eight' field02 from dual
) select distinct field01,
wmsys.wm_concat( field02 ) over ( partition by field01 order by field01 ) field02
from table01;