In pl/sql if you are technical analyst working with record type/table type would be little difficult.
So in this blog we will see how to define table type with IN and OUT put as most of the Oracle standard API are using table type with IN OUT parameter.
Create package with type xx_test_rec and make table type xx_test_tbl from xx_test_rec.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| CREATE OR REPLACE PACKAGE xx_type_test
AS
TYPE xx_test_rec IS RECORD
(
p_id NUMBER,
req_num VARCHAR2 (100),
x_return_status VARCHAR2 (2000),
x_error_code VARCHAR2 (2000),
x_msg_data VARCHAR2 (2000)
);
TYPE xx_test_tbl IS TABLE OF xx_test_rec
INDEX BY BINARY_INTEGER;
PROCEDURE xx_proc (p_req_rec IN OUT NOCOPY xx_test_tbl);
END xx_type_test;
|
Create package body with input/output parameter. Loop input parameter and extract data from the input parameter, process the value and can assign value to output parameter.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
| /* Formatted on 4/5/2016 8:26:13 PM (QP5 v5.287) */
CREATE OR REPLACE PACKAGE BODY xx_type_test
AS
PROCEDURE xx_proc (p_req_rec IN OUT NOCOPY xx_test_tbl)
AS
l_req_tab xx_test_tbl;
l_req_rec xx_test_rec;
I NUMBER;
BEGIN
l_req_tab := p_req_rec;
I := l_req_tab.FIRST;
WHILE I IS NOT NULL
LOOP
l_req_rec := l_req_tab (I);
DBMS_OUTPUT.put_line ('output New 2.1 ' || l_req_rec.p_id);
l_req_rec.x_return_status := 'S';
l_req_rec.p_id := l_req_rec.p_id - 1;
DBMS_OUTPUT.put_line ('output New 2.2 ' || l_req_rec.p_id);
p_req_rec (I) := l_req_rec;
I := l_req_tab.NEXT (I);
END LOOP;
END xx_proc;
END xx_type_test;
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| DECLARE
l_req_tab_in xx_type_test.xx_test_tbl;
l_req_tab_out xx_type_test.xx_test_tbl;
I NUMBER;
BEGIN
FOR i IN 1 .. 2
LOOP
l_req_tab_in (i).p_id := 3 + i;
END LOOP;
xx_type_test.xx_proc (l_req_tab_in);
l_req_tab_out := l_req_tab_in;
I := l_req_tab_out.FIRST;
WHILE I IS NOT NULL
LOOP
DBMS_OUTPUT.put_line ('tax ' || l_req_tab_out (i).x_return_status);
DBMS_OUTPUT.put_line ('id ' || l_req_tab_out (i).p_id);
I := l_req_tab_out.NEXT (I);
END LOOP;
END;
|
