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;
/