Few Useful Object Implementation Snippet

Hi Friends,

Today i’m going to discuss few useful object implementation snippets with you. How, you can use objects in your Oracle programming and also demonstrate some object oriented approaches implement in Oracle using Objects.

Let’s concentrate on our first case.

Type – 1,

test_m@ORCL>
test_m@ORCL>select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

Elapsed: 00:00:00.00
test_m@ORCL>
test_m@ORCL>
test_m@ORCL>create or replace type address_type as object
2 (
3 street_code varchar2(10),
4 street_name varchar2(50)
5 );
6 /

Type created.

Elapsed: 00:00:00.10
test_m@ORCL>
test_m@ORCL>create table test_obj
2 (
3 empno number(4),
4 ename varchar2(40),
5 address address_type,
6 city varchar2(30),
7 constraints pk_empno primary key(empno)
8 );

Table created.

Elapsed: 00:00:02.40
test_m@ORCL>
test_m@ORCL>
test_m@ORCL>insert into test_obj values(&eno,'&enm',address_type('&st_code','&st_name'),'&city');
Enter value for eno: 1001
Enter value for enm: SATYAKI
Enter value for st_code: 700010
Enter value for st_name: BELEGHATA
Enter value for city: KOLKATA
old 1: insert into test_obj values(&eno,'&enm',address_type('&st_code','&st_name'),'&city')
new 1: insert into test_obj values(1001,'SATYAKI',address_type('700010','BELEGHATA'),'KOLKATA')

1 row created.

Elapsed: 00:00:00.01
test_m@ORCL>/
Enter value for eno: 1002
Enter value for enm: ATANU
Enter value for st_code: 700100
Enter value for st_name: DLF
Enter value for city: KOLKATA
old 1: insert into test_obj values(&eno,'&enm',address_type('&st_code','&st_name'),'&city')
new 1: insert into test_obj values(1002,'ATANU',address_type('700100','DLF'),'KOLKATA')

1 row created.

Elapsed: 00:00:00.01
test_m@ORCL>/
Enter value for eno: 1003
Enter value for enm: PRANAB
Enter value for st_code: 700079
Enter value for st_name: VIP ROAD
Enter value for city: KOLKATA
old 1: insert into test_obj values(&eno,'&enm',address_type('&st_code','&st_name'),'&city')
new 1: insert into test_obj values(1003,'PRANAB',address_type('700079','VIP ROAD'),'KOLKATA')

1 row created.

Elapsed: 00:00:00.01
test_m@ORCL>
test_m@ORCL>commit;

Commit complete.

Elapsed: 00:00:00.01
test_m@ORCL>
test_m@ORCL>select e.empno,
2 e.ename,
3 cast(e.address.street_code as varchar2(10)) street_code,
4 cast(e.address.street_name as varchar2(50)) street_name,
5 e.city
6 from test_obj e;

EMPNO ENAME STREET_COD STREET_NAME CITY
---------- ---------------------------------------- ---------- -------------------------------------------------- ------------------------------
1001 SATYAKI 700010 BELEGHATA KOLKATA
1002 ATANU 700100 DLF KOLKATA
1003 PRANAB 700079 VIP ROAD KOLKATA

Elapsed: 00:00:00.03
test_m@ORCL>
test_m@ORCL>
test_m@ORCL>

Lets concentrate on our second ventures where we will look into other way of accessing these various objects –

Type – 2 (Overloading),

test_m@ORCL>
test_m@ORCL>create or replace package test_overloadng
2 is
3 x number(5);
4 procedure test_a(
5 a in number,
6 b in number,
7 c out number
8 );
9
10 procedure test_a(
11 a in varchar2,
12 b in varchar2,
13 c out varchar2
14 );
15 end;
16 /

Package created.

Elapsed: 00:00:00.05
test_m@ORCL>
test_m@ORCL>
test_m@ORCL>create or replace package body test_overloadng
2 is
3 procedure test_a(
4 a in number,
5 b in number,
6 c out number
7 )
8 is
9 x number(10);
10 begin
11 x := a + b;
12 c := x;
13 end;
14 procedure test_a(
15 a in varchar2,
16 b in varchar2,
17 c out varchar2
18 )
19 is
20 x varchar2(300);
21 begin
22 x := a||b;
23 c := x;
24 end;
25 end;
26 /

Package body created.

Elapsed: 00:00:00.04
test_m@ORCL>
test_m@ORCL>
test_m@ORCL>set serveroutput on
test_m@ORCL>
test_m@ORCL>
test_m@ORCL>declare
2 v number(10);
3 w varchar2(300);
4 begin
5 test_overloadng.test_a(10,20,v);
6 dbms_output.put_line( 'Value of V : ' ||v);
7 test_overloadng.test_a( 'Satyaki ' , 'De' ,w);
8 dbms_output.put_line( 'Value of W : ' ||w);
9 end;
10 /
Value of V : 30
Value of W : Satyaki De

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.10
test_m@ORCL>
test_m@ORCL>

Type – 3 (Polymorphism),

test_m@ORCL>
test_m@ORCL>select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

Elapsed: 00:00:00.00
test_m@ORCL>
test_m@ORCL>CREATE TYPE DEMO_ST AS OBJECT(
2 ID NUMBER ,
3 MEMBER PROCEDURE THIS_PROC
4 )
5 NOT FINAL NOT INSTANTIABLE
6 /

Type created.

Elapsed: 00:00:00.42
test_m@ORCL>
test_m@ORCL>CREATE TYPE DEMO_T1 UNDER DEMO_ST(
2 OVERRIDING MEMBER PROCEDURE THIS_PROC
3 )
4 /

Type created.

Elapsed: 00:00:00.36
test_m@ORCL>
test_m@ORCL>CREATE TYPE DEMO_T2 UNDER DEMO_ST(
2 OVERRIDING MEMBER PROCEDURE THIS_PROC
3 )
4 /

Type created.

Elapsed: 00:00:00.18
test_m@ORCL>
test_m@ORCL>CREATE TYPE BODY DEMO_T1
2 AS
3 OVERRIDING MEMBER PROCEDURE THIS_PROC
4 AS
5 BEGIN
6 DBMS_OUTPUT.PUT_LINE('DEMO_T1');
7 END;
8 END;
9 /

Type body created.

Elapsed: 00:00:00.09
test_m@ORCL>
test_m@ORCL>CREATE TYPE BODY DEMO_T2
2 AS
3 OVERRIDING MEMBER PROCEDURE THIS_PROC
4 AS
5 BEGIN
6 DBMS_OUTPUT.PUT_LINE('DEMO_T2');
7 END;
8 END;
9 /

Type body created.

Elapsed: 00:00:00.03
test_m@ORCL>
test_m@ORCL>CREATE OR REPLACE PROCEDURE DEMO_PRC(
2 P_OBJ DEMO_ST
3 )
4 AS
5 V_OBJ DEMO_ST := P_OBJ ;
6 BEGIN
7 V_OBJ.THIS_PROC;
8 END;
9 /

Procedure created.

Elapsed: 00:00:00.04
test_m@ORCL>
--
-- TAKES AN OBJECT, NOT A PROCEDURE PER SE
-- BUT ALLOWS THE PROCEDURE TO BE PASSED WRAPPED IN AN
-- OBJECT
--
test_m@ORCL>
test_m@ORCL>BEGIN
2 DEMO_PRC(DEMO_T1(1));
3 DEMO_PRC(DEMO_T2(1));
4 END;
5 /
DEMO_T1
DEMO_T2

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09
test_m@ORCL>
test_m@ORCL>
test_m@ORCL>

Type – 4 (Use Of Member Function),

scott@ORCL>
scott@ORCL>create table employees
2 (
3 id number primary key,
4 name varchar2(30) not null,
5 emp_status varchar2(1) not null
6 );

Table created.

Elapsed: 00:00:00.19
scott@ORCL>
scott@ORCL>
scott@ORCL>create or replace type employee as object
2 (
3 id number,
4 name varchar2(30),
5 emp_status varchar2(1),
6 member function exempt return varchar2
7 );
8 /

Type created.

Elapsed: 00:00:00.15
scott@ORCL>
scott@ORCL>
scott@ORCL>create or replace type body employee
2 is
3 member function exempt return varchar2
4 is
5 begin
6 return self.name||' '||self.emp_status;
7 end;
8 end;
9 /

Type body created.

Elapsed: 00:00:00.11
scott@ORCL>

Now, to create a view called employees_view (Which will be shown later in this thread) we need the following privileges given by sys as follows –
In Sys,

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

sys@ORCL>grant create any view to scott, test_m;

Grant succeeded.

sys@ORCL>
sys@ORCL>
sys@ORCL>

Now, lets move into our user –
In Scott,

scott@ORCL>
scott@ORCL>create or replace view employees_view of employee
2 with object identifier(id)
3 as
4 select id,
5 name,
6 emp_status
7 from employees;

View created.

Elapsed: 00:00:00.31
scott@ORCL>
scott@ORCL>
scott@ORCL>select id,
2 name,
3 emp_status,
4 o.exempt()
5 from employees_view o;

no rows selected

Elapsed: 00:00:00.04
scott@ORCL>
scott@ORCL>
scott@ORCL>declare
2 l_var varchar2(100);
3 l_obj employee := employee(1, 'E1', 'a');
4 begin
5 l_var := l_obj.exempt(); -- works
6 l_var := l_obj.exempt; -- works with out parentheses too
7 dbms_output.put_line(l_var);
8 end;
9 /
E1 a

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02
scott@ORCL>
scott@ORCL>

Type – 5 (Use Of Member Procedure),

scott@ORCL>create or replace type TMyType is object(  
2 some_value number,
3
4 member
function Display return varchar2,
5
static procedure MyProc( n number )
6 );
7 /

Type created.

scott@ORCL>
scott@ORCL>
create or replace type body TMyType as
2
3 member
function Display return varchar2 is
4
begin
5
return(
6 TO_CHAR( self.some_value,
'999,999,999,990.00' )
7 );
8
end;
9
10
static procedure MyProc( n number ) is
11
begin
12 DBMS_OUTPUT.put_line(
13 TO_CHAR( n,
'999,999,999,990.00' )
14 );
15
end;
16
end;
17 /

Type body created.

scott@ORCL>
scott@ORCL>
scott@ORCL>
set serveroutput on
scott@ORCL>
begin
2 TMyType.MyProc( 123456789.12 );
3
end;
4 /
123,456,789.12

PL/
SQL procedure successfully completed.
scott@ORCL>

Hope this thread will give some wise ways of implementing Oracle Objects.

Keep following – very soon i’ll be coming back with another topic here. Till then – Bye.

Satyaki.

7 thoughts on “Few Useful Object Implementation Snippet

  1. Good post and this fill someone in on helped me alot in my college assignement. Thank you seeking your information.

  2. Amiable fill someone in on and this mail helped me alot in my college assignement. Thank you as your information.

  3. i honestly adore your writing kind, very useful.
    don't give up and keep posting due to the fact that it just that is worth to look through it,
    excited to browse through alot more of your own web content, good bye 😉

  4. this is really a great artical … after reading this blog, I am really excited while I think we worked together for 3 years. It was a great pleasure for me. Wish you all the best. I will request you to update this blog with more advanced tricks.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s