Change in blog address

Novice a has progressed to http://oracle-sequel.blogspot.com

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;

Oracle PL/SQL | Question

/* To grnerate report for student Marks
Input Data:
STUDENT_ID MARKS SUBJECT
---------- ---------- --------------------
1 20 Maths
1 70 English
1 50 Science
1 45 Biology
2 50 Maths
2 30 English
2 50 Science
2 45 Biology
3 90 Maths
3 70 English
3 50 Science
3 35 Biology
Output Data:
1 145, 45.66, F ( if one of the marks < 40 then F(fail) else P (pass))
2 180,67.88 , P */
set line 3000;
set pagesize 100;
set head on;
with table01 as(
select 1 STUDENT_ID, 20 MARKS, 'Maths' SUBJECT from dual
union all
select 1,70, 'English' from dual
union all
select 1,50, 'Science' from dual
union all
select 1,45, 'Biology' from dual
union all
select 2, 50, 'Maths' from dual
union all
select 2, 30, 'English' from dual
union all
select 2, 50, 'Science' from dual
union all
select 2, 45, 'Biology' from dual
union all
select 3, 90, 'Maths' from dual
union all
select 3,70, 'English' from dual
union all
select 3, 50, 'Science' from dual
union all
select 3,35, 'Biology' from dual
)
select student_id, Total, Average,
case
when Minimum < 40
then
'Fail'
else
'Pass'
end Result
from (select distinct student_id,
min(marks) over (partition by student_id order by student_id ) Minimum,
sum(marks) over (partition by student_id order by student_id ) Total,
avg(marks) over (partition by student_id order by student_id ) Average
from table01
);

Oracle PL/SQL | Tools For Query Tunning

For any PL/SQL developer, there is needs analyse his queries befor it is put to use. Oracle provides follwoing tools, to the same:
1. DBMS_PROFILER
2. Statspack
3. SQL*Trace /tkprof
4. Explian Plan
DBMS Profiler
Details can be found in following link: http://www.oracle-base.com/articles/9i/DBMS_PROFILER.php
Statpacks
Details can be found in follwoing link: http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/statspac.htm
Explain plan
in SQL*Plus you have to type:
explain plan for ;select * from table(dbms_xplan.display);
When you get error messages or a message complaining about an old version of plan_table, make sure you run the script utlxplan.sql.
The output you get here basically shows you what the cost based optimizer expects. It gives you an idea on why the cost based optimizer chooses an access path.
SQL*Trace/tkprof
For this you have to type in SQL*Plus:- alter session set sql_trace true;
disconnect (this step is important, because it ensures all cursors get closed, and "row source operation" is generated) identify your trace file in the server directory as specified in the parameter user_dump_dest on your operating system: tkprof a.txt sys=no sort=prsela exeela fchela
The file a.txt will now give you valuable information on what has actually happened. No predictions but the truth.

Oracle PL/SQL | Tree Traverse

-- Example to traverse Tree using Oracle PL/SQLs
with table01
as(
select 1 field01, 2 field02 from dual Union all
select 2 field01, 3 field02 from dual Union all
select 3 field01, 4 field02 from dual Union all
select 4 field01, 5 field02 from dual Union all
select 100 field01, 101 field02 from dual Union all
select 101 field01, 102 field02 from dual Union all
select 101 field01, 103 field02 from dual Union all
select 103 field01, 104 field02 from dual Union all
select 103 field01, 105 field02 from dual Union all
select 105 field01, 106 field02 from dual
) select connect_by_root field01 field03, sys_connect_by_path( field02, '->' ) field04, field02 from table01
connect by prior field02 = field01

Oracle PL/SQL | Question

Question
Input Data:
Col1 col2 col3
1 2 1
1 2 2
1 2 3
2 3 7
2 3 8
2 3 9

Output Data
1 2 1,2,3
2 3 7,8,9
Answer:
create table table01 as
with table02 as(
select '1' field01, '2' field02, '1' field03 from dual union
select '1' field01, '2' field02, '2' field03 from dual union
select '1' field01, '2' field02, '3' field03 from dual union
select '2' field01, '3' field02, '7' field03 from dual union
select '2' field01, '3' field02, '8' field03 from dual union
select '2' field01, '3' field02, '9' field03 from dual
)select * from table02;

select distinct field01 || field02 || wmsys.wm_concat(field03) over( partition by field01 || field02 order by field01 || field02) field01 from table01

drop table table01

Oracle PL/SQL | Guide

If you are new to Oracle, wish to become an expert.

Please find Oracle Guide in following link

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm

Oracle PL/SQL | Object Pipelined Function Example

-- Creating Object Type
create or replace type object01 as Object(
field01 varchar2(10),
field02 varchar2(50),
field03 Address01
);
-- Creating Table of Object Type
create or replace type TableObject01 as table of Object01;
-- Defination Of Piplined Function using Object
create or replace function function01(vi_count in Number)
return TableObject01 pipelined
is
vs_row object01 := Object01('0', '0', Address01( '0', '0', '0', '0'));
begin
for i in 1 .. vi_count
loop
vs_row.field01 := to_char( i, '0000' );
vs_row.field02 := 'Name ' || to_char( i, '00' );
vs_row.field03 := Address01(
'Block ' || to_char( i, '00' ),
'Street' || to_char( i, '00' ),
'City' || to_char( i, '00' ),
'Country' || to_char( i, '00' ) || ' - 000 ' || to_char( i, '000' )
);
pipe row ( vs_row );
end loop;
end function01;
/
-- Using Pipelined Function.
select * from table( function01( 99 ) )

Oracle PL/SQL | Object Usage Example

-- Definition of Object
drop type Address01

create or replace type Address01 as object(
field01 varchar2(50),
field02 varchar2(50),
field03 varchar2(50),
field04 varchar2(50),
member procedure ShowValue(self in out nocopy Address01)
);
-- Body of Object
create type body Address01
as
member procedure ShowValue( self in out nocopy Address01)
is
begin
dbms_output.put_line( 'House Name: ' || self.field01 );
dbms_output.put_line( 'Street Name: ' || self.field02 );
dbms_output.put_line( 'City Name: ' || self.field03 );
dbms_output.put_line( 'Country: ' || self.field04 );
end;
end;
/
-- Creation of Table having Object Column
drop table table01;

create table table01(
field01 varchar2(10),
field02 varchar2(50),
field03 Address01
);
-- Inserting values to Object using table
insert into table01
values(
'0002',
'Name 02',
Address01( 'Block 02', 'Street02', 'City02', 'Country02 - 000 002' )
)
-- Updating values into Object using table
update table01 t
set t.field03.Field01 = 'Block 03'
where t.field01 = '0002'
-- To use Object member procedure
declare
cursor FetchAddress01 is
select Field01, Field02, Field03
from Table01;
begin
for i in FetchAddress01
loop
dbms_output.put_line('Person Id: ' || i.Field01);
dbms_output.put_line('Person Id: ' || i.Field02);
i.Field03.ShowValue();
end loop;
end;
/
-- To use Dynamic SQL with Objects
declare
cursor FetchAddress01 is
select Field01, Field02, Field03
from Table01;

v_address01 Address01;
v_field01 varchar2(10);
v_field02 varchar2(50);
v_sql varchar2(255);
begin
v_sql := ' Insert into table01
values( :1, :2, :3)';
for i in 3 .. 10
loop
v_field01 := to_char( i, '0000' );
v_field02 := 'Name ' || to_char( i, '00' );
v_address01 := Address01(
'Block ' || to_char( i, '00' ),
'Street' || to_char( i, '00' ),
'City' || to_char( i, '00' ),
'Country' || to_char( i, '00' ) || ' - 000 ' || to_char( i, '000' )
);
execute immediate v_sql using v_field01, v_field02, v_address01;
commit;
end loop;
for i in FetchAddress01
loop
dbms_output.put_line('Person Id: ' || i.Field01);
dbms_output.put_line('Person Id: ' || i.Field02);
i.Field03.ShowValue();
end loop;
end;
/