Oracle object table example

Source code:

1. Create an object type

CREATE OR REPLACE
Type ty_usernum as object
(
Seq_call_usernum number,
Calltype number,
Areacode varchar2 (10 ),
Usernum varchar2 (20)
)
/

2. Create a collection type

Create or replace type ty_tbl_usernum is table of ty_usernum
/

3. Process

Create or replace procedure p (
P_authid IN VARCHAR2,
R_called OUT VARCHAR2,
R_cursor OUT sys_refcursor,
R_code OUT INT
)
IS
V_areacode t1.areacode % TYPE;
V_rowcount INT: = 0;
V_sysdate CHAR (4 );
V_week CHAR (1 );
V_random INT;
V_calltype t1.calltype % TYPE;
V_seq_call_usernum t1.seq _ call_usernum % TYPE;
V_usernum t1.usernum % TYPE;
V_ty_usernum ty_usernum
: = Ty_usernum (v_seq_call_usernum, v_calltype, v_areacode, v_usernum );
V_ty_tbl_usernum ty_tbl_usernum: = ty_tbl_usernum ();
BEGIN
BEGIN
SELECT areacode, calltype
INTO v_areacode, v_calltype
FROM t2
WHERE seq_areacalledno =
(SELECT seq_areacallno
FROM t3
WHERE seq_threadstatus = p_authid AND status = 1 );
EXCEPTION
WHEN NO_DATA_FOUND
THEN
Delete from t3
WHERE seq_threadstatus = p_authid;

COMMIT;
R_code: = 2;
RETURN;
END;

Select count (*)
Into v_rowcount
From T1
Where areacode = v_areacode;

If v_rowcount = 0
Then
Delete from T3
Where seq_threadstatus = p_authid;

Commit;
R_code: = 2;
Return;
End if;

Open r_cursor
SELECT seq_call_usernum, calltype, areacode, usernum
FROM (SELECT seq_call_usernum, calltype, areacode, usernum
FROM t1
WHERE areacode = v_areacode
AND status = 0
AND calltype = v_calltype
Order by dbms_random.value)
Where rownum <11;

Loop
Fetch r_cursor
Into v_seq_call_usernum, v_calltype, v_areacode, v_usernum;

Exit when r_cursor % notfound;

Update T1
Set calldate = sysdate,
Status = 1
Where seq_call_usernum = v_seq_call_usernum;

V_ty_usernum.seq_call_usernum: = v_seq_call_usernum;
V_ty_usernum.calltype: = v_calltype;
V_ty_usernum.areacode: = v_areacode;
V_ty_usernum.usernum: = v_usernum;
V_ty_tbl_usernum.extend;
V_ty_tbl_usernum (v_ty_tbl_usernum.count): = v_ty_usernum;
End loop;

Close r_cursor;

Open r_cursor
Select seq_call_usernum, calltype, areacode, usernum
From table (CAST (v_ty_tbl_usernum AS ty_tbl_usernum ));

COMMIT;
R_code: = 1;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
R_code: = 0;
RAISE;
END p;
/