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.

How to store data from XML to Tables

In the previous post we have discussed about generating an XML file using Oracle SQL XML functions. Today we will do that in reverse manner. That means we will load the data generated by that query in the database tables.

At the end of this post (Which is a continue of the previous post) – you will be successfully generate an XML file from Oracle Tables & also able to load the data from XML on that same structured tables. So, that will complete the full life cycle of XML in oracle(Obviously concentrate on some basics).

Lets see –

Our main ingredients for this class – is the XML file named – emp_oracle.xml

And, it looks like –




200
<
First>Whalen</First>
4400
1987-09-17


201
<
First>Billy</First>
4500
1985-06-10


202
<
First>Bireswar</First>
9000
1978-06-10


We need to create one Oracle Directories to map with the Operating System directories in the following manner ->

sys@ORCL>
sys@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
sys@ORCL>
sys@ORCL>
sys@ORCL>CREATE OR REPLACE DIRECTORY SATY_DIR AS 'D:\XML_Output'
2 /

Directory created.

Elapsed: 00:00:00.23
sys@ORCL>
sys@ORCL>GRANT READ, WRITE ON DIRECTORY SATY_DIR TO SCOTT, HR;

Grant succeeded.

Elapsed: 00:00:00.08
sys@ORCL>

Once you have created the directory successfully and give the proper privileges to the users like Scott or Hr – you have completed one important component of today’s test. Still we are far to go. Now the second part is –

scott@ORCL>
scott@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
scott@ORCL>
scott@ORCL>
scott@ORCL>CREATE TABLE t
2 (
3 serialNo NUMBER(10),
4 fileName VARCHAR2(100),
5 xml XMLTYPE,
6 constraints pk_serialNo primary key(serialNo)
7 );

Table created.

Elapsed: 00:00:04.13
scott@ORCL>
scott@ORCL>
scott@ORCL>CREATE SEQUENCE x_seq
2 START WITH 1
3 INCREMENT BY 1;

Sequence created.

Elapsed: 00:00:00.31
scott@ORCL>
scott@ORCL>CREATE OR REPLACE PROCEDURE load_xml(
2 p_dir IN VARCHAR2,
3 p_filename IN VARCHAR2
4 )
5 IS
6 l_bfile BFILE := BFILENAME(p_dir, p_filename);
7 l_clob CLOB;
8 BEGIN
9 DBMS_LOB.createtemporary (l_clob, TRUE);
10
11 DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
12 DBMS_LOB.loadfromfile(l_clob, l_bfile, DBMS_LOB.getlength(l_bfile));
13 DBMS_LOB.fileclose(l_bfile);
14
15 INSERT INTO t(
16 serialNo,
17 fileName,
18 xml
19 )
20 VALUES (
21 x_seq.NEXTVAL,
22 p_filename,
23 XMLTYPE.createXML(l_clob)
24 );
25
26 COMMIT;
27
28 DBMS_LOB.freetemporary(l_clob);
29 END;
30 /

Procedure created.

Elapsed: 00:00:00.88
scott@ORCL>
scott@ORCL>EXEC load_xml(p_dir => 'SATY_DIR', p_filename => 'emp_oracle.xml');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.16
scott@ORCL>
scott@ORCL>set long 5000
scott@ORCL>
scott@ORCL>set pagesize 0
scott@ORCL>
scott@ORCL>select xml from t;



200
Whalen
4400
1987-09-17


201
Billy
4500
1985-06-10


202
Bireswar
9000
1978-06-10




Elapsed: 00:00:00.10
scott@ORCL>

Ok. So, we’ve initially load the data into the temp table t. But, we need to load the data from this temp table t to our target table revive_xml which will look like –

scott@ORCL>create table revive_xml
2 (
3 rev_emp_id number(4),
4 rev_f_name varchar2(40),
5 rev_salary number(10,2),
6 rev_jn_dt date,
7 constraints pk_rev_emp_id primary key(rev_emp_id)
8 );

Table created.

Elapsed: 00:00:00.40
scott@ORCL>

Ok. So, we have done another important part of our job. Let’s concentrate on our final mission –

scott@ORCL>insert into revive_xml(
2 rev_emp_id,
3 rev_f_name,
4 rev_salary,
5 rev_jn_dt
6 )
7 select cast(t1.EmployeeId as number(4)) EmployeeId,
8 t2.FirstName,
9 cast(t3.Salary as number(10,2)) Salary,
10 to_date(t4.JoiningDt,'YYYY-MM-DD') JoiningDt
11 from (
12 select rownum rn1,
13 extractValue(value(EmployeeId),'/Emp/Employee_ID') EmployeeId
14 from t,
15 table(xmlsequence(extract(xml, '/EmployeeList/Emp'))) EmployeeId
16 ) t1,
17 (
18 select rownum rn2,
19 extractValue(value(FirstName),'/Emp/First') FirstName
20 from t,
21 table(xmlsequence(extract(xml, '/EmployeeList/Emp'))) FirstName
22 ) t2,
23 (
24 select rownum rn3,
25 extractValue(value(Salary),'/Emp/Sal') Salary
26 from t,
27 table(xmlsequence(extract(xml, '/EmployeeList/Emp'))) Salary
28 ) t3,
29 (
30 select rownum rn4,
31 extractValue(value(HireDate),'/Emp/HireDate') JoiningDt
32 from t,
33 table(xmlsequence(extract(xml, '/EmployeeList/Emp'))) HireDate
34 ) t4
35 where t1.rn1 = t2.rn2
36 and t1.rn1 = t3.rn3
37 and t1.rn1 = t4.rn4;

3 rows created.

Elapsed: 00:00:00.16
scott@ORCL>
scott@ORCL>commit;

Commit complete.

Elapsed: 00:00:00.22
scott@ORCL>
scott@ORCL>
scott@ORCL>select * from revive_xml;

REV_EMP_ID REV_F_NAME REV_SALARY REV_JN_DT
---------- ---------------------------------------- ---------- ---------
200 Whalen 4400 17-SEP-87
201 Billy 4500 10-JUN-85
202 Bireswar 9000 10-JUN-78

scott@ORCL>

So, you have done it finally.

You can do it another way but that is limited to single record parsing –

scott@ORCL>with t
2 as (
3 select xmlType('
4
5
6 200
7 Whalen
8 4400
9 1987-09-17
10
11 ') xml from dual
12 )
13 SELECT rownum rn,
14 a.EmployeeId,
15 a.FirstName,
16 a.Salary,
17 a.JoiningDt
18 FROM t,
19 XMLTABLE('/EmployeeList'
20 PASSING t.xml
21 COLUMNS
22 EmployeeId varchar2(10) PATH '/EmployeeList/Emp/Employee_ID',
23 FirstName varchar2(20) PATH '/EmployeeList/Emp/First',
24 Salary number(10) PATH '/EmployeeList/Emp/Sal',
25 JoiningDt date PATH '/EmployeeList/Emp/HireDate'
26 ) a;

RN EMPLOYEEID FIRSTNAME SALARY JOININGDT
---------- ---------- -------------------- ---------- ---------
1 200 Whalen 4400 17-SEP-87

scott@ORCL>
scott@ORCL>

Hope this will solve your purpose.

Also you can refer to the following XML In Oracle link.

Regards.

Ref Cursor In SQL

There are number occasions, which i personally met when many of my juniors are looking for solution of using ref-cursor in SQL based on some certain requirement and they want to use it in SQL that can be embed into some JAVA based applications. I’ve looked into that and able to produce one such useful case which can handy (Though i’ll prefer different approach than this.) when i require this kind of work.

satyaki>
satyaki>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Elapsed: 00:00:00.00
satyaki>
satyaki>
satyaki>create table t_emp
2 as
3 select empno,
4 ename,
5 mgr,
6 sal
7 from emp
8 where 1=2;

Table created.

Elapsed: 00:00:00.05
satyaki>
satyaki>set lin 80
satyaki>
satyaki>desc t_emp;
Name Null? Type
----------------------------------------- -------- ----------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
MGR NUMBER(4)
SAL NUMBER(7,2)

satyaki>
satyaki>
satyaki>set lin 310
satyaki>
satyaki>select * from t_emp;

no rows selected

Elapsed: 00:00:00.00
satyaki>
satyaki>
satyaki>create or replace type etype as object
2 (
3 empno number,
4 ename varchar2(10),
5 mgr number,
6 sal number
7 );
8 /

Type created.

Elapsed: 00:00:01.03
satyaki>
satyaki>create or replace type t_etype as table of etype;
2 /

Type created.

Elapsed: 00:00:00.02
satyaki>
satyaki>create or replace function get_dept_emps(p_deptno in number)
2 return sys_refcursor
3 is
4 v_rc sys_refcursor;
5 begin
6 open v_rc for 'select empno, ename, mgr, sal from emp where deptno = :deptno' using p_deptno;
7 return v_rc;
8 end;
9 /

Function created.

Elapsed: 00:00:00.05
satyaki>
satyaki>
satyaki>create or replace function fetch_emps(deptno in number := null)
2 return t_etype
3 is
4 v_emptype t_etype := t_etype(); -- Declare a local table structure and initialize it
5 v_cnt number := 0;
6 v_rc sys_refcursor;
7 v_empno number;
8 v_ename varchar2(10);
9 v_mgr number;
10 v_sal number;
11 begin
12 v_rc := get_dept_emps(deptno);
13 loop
14 fetch v_rc into v_empno, v_ename, v_mgr, v_sal;
15 exit when v_rc%NOTFOUND;
16 v_emptype.extend;
17 v_cnt := v_cnt + 1;
18 v_emptype(v_cnt) := etype(v_empno, v_ename, v_mgr, v_sal);
19 end loop;
20 close v_rc;
21 return v_emptype;
22 end;
23 /

Function created.

Elapsed: 00:00:00.06
satyaki>
satyaki>
satyaki>select * from t_emp;

no rows selected

Elapsed: 00:00:00.00
satyaki>
satyaki>
satyaki>
satyaki>select * from t_emp;

no rows selected

Elapsed: 00:00:00.00
satyaki>
satyaki>
satyaki>insert into t_emp
2 select *
3 from table(fetch_emps(30));

4 rows created.

Elapsed: 00:00:00.02
satyaki>
satyaki>select * from t_emp;

EMPNO ENAME MGR SAL
---------- ---------- ---------- ----------
7654 MARTIN 7698 1815
7844 TURNER 7698 2178
7900 JAMES 7698 1379.4
7599 BILLY 7566 4500

Elapsed: 00:00:00.00
satyaki>
satyaki>commit;

Commit complete.

Elapsed: 00:00:00.00
satyaki>