Home » SQL & PL/SQL » SQL & PL/SQL » Check if value EXISTS in plsql table of record type (11G)
| Check if value EXISTS in plsql table of record type [message #654474] |
Wed, 03 August 2016 10:27  |
harshadsp
Messages: 100
Registered: August 2008
|
Senior Member |
|
|
Hello-
I have a code where I have created index by table of record type
DECLARE
TYPE r_04D IS RECORD(
id number,
polnum VARCHAR2(15),
trandt VARCHAR2(10),
trancd VARCHAR2(2),
rectyp VARCHAR2(2),
statecd VARCHAR2(2),
statenm VARCHAR2(2));
TYPE t_04D IS TABLE OF r_04D INDEX BY PLS_INTEGER;
w_04D t_04D;
j NUMBER := 0;
no_04D EXCEPTION;
ln NUMBER := 0;
The W_04D variable holds 2 values at run time that are CA and NY and I am having difficulties to check value for statecd in w_04D exists or not.
I want to check if the value for SUBSTR(W_newline, 44, 2) exists in w_04D variable for statecd element.
I tried something like this but not sure how to access the specific element
w_04D(i).statecd.exists = SUBSTR(W_newline, 44, 2)
Please advise.
|
|
|
|
| Re: Check if value EXISTS in plsql table of record type [message #654476 is a reply to message #654474] |
Wed, 03 August 2016 10:52  |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
Use an associative array. See the following example
SET SERVEROUTPUT ON
DECLARE
TYPE country_tab IS TABLE OF VARCHAR2(50)
INDEX BY VARCHAR2(5);
t_country country_tab;
BEGIN
-- Populate lookup
t_country('UK') := 'United Kingdom';
t_country('US') := 'United States of America';
t_country('FR') := 'France';
t_country('DE') := 'Germany';
-- Find country name for ISO code "DE"
DBMS_OUTPUT.PUT_LINE('ISO code "DE" = ' || t_country('DE'));
END;
/ |
|
|
|
| Re: Check if value EXISTS in plsql table of record type [message #654542 is a reply to message #654476] |
Fri, 05 August 2016 06:27 |
harshadsp
Messages: 100
Registered: August 2008
|
Senior Member |
|
|
Here is the code:-
DECLARE
W_input_dir VARCHAR2(100);
W_input_file VARCHAR2(50);
w_newline VARCHAR2(2000);
W_filest VARCHAR2(4);
W_filestcd CHAR(2);
input_file utl_file.file_type;
TYPE r_04D IS RECORD(
id number,
polnum VARCHAR2(15),
trandt VARCHAR2(10),
trancd VARCHAR2(2),
rectyp VARCHAR2(2),
statecd VARCHAR2(2),
statenm VARCHAR2(2));
TYPE t_04D IS TABLE OF r_04D INDEX BY PLS_INTEGER;
w_04D t_04D;
j NUMBER := 0;
no_04D EXCEPTION;
ln NUMBER := 0;
type t_statecd is table of varchar2(2);
g_statecd t_statecd;
BEGIN
dbms_output.enable(null);
W_input_dir := 'WCPOLS_INPUT_DIR';
W_input_file := '&FileName';
input_file := utl_file.fopen(W_input_dir, w_input_file, 'r', 32000);
W_filest := SUBSTR(SUBSTR(UPPER(W_input_file), 26),1,(INSTR(SUBSTR(UPPER(W_input_file), 26), '.TXT') - 1));
IF utl_file.is_open(input_file) THEN
LOOP
BEGIN
utl_file.get_line(input_file, w_newline);
IF substr(w_newline, 46, 3) = '04D' THEN
j := j + 1;
w_04D(j).id := j;
w_04D(j).polnum := SUBSTR(W_newline, 6, 14);
w_04D(j).trandt := SUBSTR(W_newline, 37, 5);
w_04D(j).trancd := SUBSTR(W_newline, 42, 2);
w_04D(j).rectyp := SUBSTR(W_newline, 46, 2);
w_04D(j).statecd := SUBSTR(W_newline, 44, 2);
w_04D(j).statenm := CASE SUBSTR(W_newline, 44, 2)
WHEN '01' THEN 'AL' WHEN '02' THEN 'AZ' WHEN '03' THEN 'AR' WHEN '04' THEN 'CA'
WHEN '05' THEN 'CO' WHEN '06' THEN 'CT' WHEN '07' THEN 'DE' WHEN '08' THEN 'DC'
END;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
END LOOP; --main loop
END IF; --InputFile is_open
utl_file.fclose(input_file);
input_file := utl_file.fopen(W_input_dir, w_input_file, 'r', 32000);
IF utl_file.is_open(input_file) THEN
LOOP
BEGIN
utl_file.get_line(input_file, w_newline);
IF SUBSTR(W_newline, 44, 2) = w_04D.statecd.EXISTS THEN <-- I need to check if SUBSTR(W_newline, 44, 2) is present in w_04D.statecd
dbms_output.put_line();
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
END LOOP; --main loop
END IF; --InputFile is_open
utl_file.fclose(input_file);
END;
the line
IF SUBSTR(W_newline, 44, 2) = w_04D.statecd.EXISTS THEN
|
|
|
|
|
|
|
|
|
|
| Re: Check if value EXISTS in plsql table of record type [message #654561 is a reply to message #654542] |
Fri, 05 August 2016 16:43 |
 |
Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
It looks like you are populating w_04D(j).statecd with two numbers from SUBSTR(W_newline, 44, 2) of each line of your file and populating w_04D(j).statecd with the two-letter state abbreviation using a case statement based on those same two numbers. Then you are doing another loop and extracting those same two numbers and checking to see if they exist in your w_04D(i).statecd. Since there are multiple columns in your w_04D and your index is by pls_integer, you cannot use exists, but you can loop through, as demonstrated below. It looks like the second part of your code is just checking whether the first part populated the collection correctly or not.
SCOTT@orcl_12.1.0.2.0> host type test_data.txt
abcdefghijklmnopqrstuvwxyzabcdefghijklmnopq0404D
abcdefghijklmnopqrstuvwxyzabcdefghijklmnopq0104D
abcdefghijklmnopqrstuvwxyzabcdefghijklmnopq0204D
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE DIRECTORY WCPOLS_INPUT_DIR AS 'c:\my_oracle_files'
2 /
Directory created.
SCOTT@orcl_12.1.0.2.0> DECLARE
2 W_input_dir VARCHAR2(100);
3 W_input_file VARCHAR2(50);
4 input_file utl_file.file_type;
5 w_newline VARCHAR2(2000);
6 TYPE r_04D IS RECORD(
7 id number,
8 polnum VARCHAR2(15),
9 trandt VARCHAR2(10),
10 trancd VARCHAR2(2),
11 rectyp VARCHAR2(2),
12 statecd VARCHAR2(2),
13 statenm VARCHAR2(2));
14 TYPE t_04D IS TABLE OF r_04D INDEX BY PLS_INTEGER;
15 w_04D t_04D;
16 j NUMBER := 0;
17 BEGIN
18 dbms_output.enable(null);
19 W_input_dir := 'WCPOLS_INPUT_DIR';
20 W_input_file := 'test_data.txt';
21 input_file := utl_file.fopen(W_input_dir, w_input_file, 'r', 32000);
22 -- POPULATE w_04D:
23 IF utl_file.is_open(input_file) THEN
24 LOOP
25 BEGIN
26 utl_file.get_line(input_file, w_newline);
27 IF substr(w_newline, 46, 3) = '04D' THEN
28 j := j + 1;
29 w_04D(j).id := j;
30 w_04D(j).polnum := SUBSTR(W_newline, 6, 14);
31 w_04D(j).trandt := SUBSTR(W_newline, 37, 5);
32 w_04D(j).trancd := SUBSTR(W_newline, 42, 2);
33 w_04D(j).rectyp := SUBSTR(W_newline, 46, 2);
34 w_04D(j).statecd := SUBSTR(W_newline, 44, 2);
35 w_04D(j).statenm := CASE SUBSTR(W_newline, 44, 2)
36 WHEN '01' THEN 'AL' WHEN '02' THEN 'AZ' WHEN '03' THEN 'AR' WHEN '04' THEN 'CA'
37 WHEN '05' THEN 'CO' WHEN '06' THEN 'CT' WHEN '07' THEN 'DE' WHEN '08' THEN 'DC'
38 END;
39 END IF;
40 EXCEPTION
41 WHEN NO_DATA_FOUND THEN
42 EXIT;
43 END;
44 END LOOP;
45 END IF;
46 utl_file.fclose(input_file);
47 input_file := utl_file.fopen(W_input_dir, w_input_file, 'r', 32000);
48 -- VERIFY POPULATION OF statecd and statenm in w_04D:
49 IF utl_file.is_open(input_file) THEN
50 LOOP
51 BEGIN
52 utl_file.get_line(input_file, w_newline);
53 FOR i IN 1 .. w_04D.COUNT LOOP
54 IF SUBSTR(W_newline, 44, 2) = w_04D(i).statecd THEN
55 dbms_output.put_line(w_04D(i).statecd || ' ' || w_04D(i).statenm || ' exists');
56 END IF;
57 END LOOP;
58 EXCEPTION
59 WHEN NO_DATA_FOUND THEN EXIT;
60 END;
61 END LOOP;
62 END IF;
63 utl_file.fclose(input_file);
64 END;
65 /
04 CA exists
01 AL exists
02 AZ exists
PL/SQL procedure successfully completed.
|
|
|
|
| Re: Check if value EXISTS in plsql table of record type [message #654562 is a reply to message #654561] |
Fri, 05 August 2016 17:02 |
|
Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following uses index by varchar2(2) to index by the two numbers that represent the states, which allows you to use exists, instead of looping through the whole collection. However, if you have more than one row from your file with the same two-digit statecd, then they will both have the same index value. So, this is only a good method if such values are unique in your file. Please see the comments along the left margin that mark the usage of the two-digit code values as index values.
SCOTT@orcl_12.1.0.2.0> DECLARE
2 W_input_dir VARCHAR2(100);
3 W_input_file VARCHAR2(50);
4 input_file utl_file.file_type;
5 w_newline VARCHAR2(2000);
6 TYPE r_04D IS RECORD(
7 id number,
8 polnum VARCHAR2(15),
9 trandt VARCHAR2(10),
10 trancd VARCHAR2(2),
11 rectyp VARCHAR2(2),
12 statecd VARCHAR2(2),
13 statenm VARCHAR2(2));
14 TYPE t_04D IS TABLE OF r_04D INDEX BY VARCHAR2(2);
15 w_04D t_04D;
16 -- DATA TYPE OF j IS CHANGED FROM NUMBER TO VARCHAR2(2):
17 j VARCHAR2(2);
18 BEGIN
19 dbms_output.enable(null);
20 W_input_dir := 'WCPOLS_INPUT_DIR';
21 W_input_file := 'test_data.txt';
22 input_file := utl_file.fopen(W_input_dir, w_input_file, 'r', 32000);
23 IF utl_file.is_open(input_file) THEN
24 LOOP
25 BEGIN
26 utl_file.get_line(input_file, w_newline);
27 IF substr(w_newline, 46, 3) = '04D' THEN
28 -- j IS POPULATED WITH TWO-DIGIT CODE AND THAT IS USED FOR THE INDEX:
29 j := SUBSTR(W_newline, 44, 2);
30 w_04D(j).id := j;
31 w_04D(j).polnum := SUBSTR(W_newline, 6, 14);
32 w_04D(j).trandt := SUBSTR(W_newline, 37, 5);
33 w_04D(j).trancd := SUBSTR(W_newline, 42, 2);
34 w_04D(j).rectyp := SUBSTR(W_newline, 46, 2);
35 w_04D(j).statecd := SUBSTR(W_newline, 44, 2);
36 w_04D(j).statenm := CASE SUBSTR(W_newline, 44, 2)
37 WHEN '01' THEN 'AL' WHEN '02' THEN 'AZ' WHEN '03' THEN 'AR' WHEN '04' THEN 'CA'
38 WHEN '05' THEN 'CO' WHEN '06' THEN 'CT' WHEN '07' THEN 'DE' WHEN '08' THEN 'DC'
39 END;
40 END IF;
41 EXCEPTION
42 WHEN NO_DATA_FOUND THEN
43 EXIT;
44 END;
45 END LOOP;
46 END IF;
47 utl_file.fclose(input_file);
48 input_file := utl_file.fopen(W_input_dir, w_input_file, 'r', 32000);
49 IF utl_file.is_open(input_file) THEN
50 LOOP
51 BEGIN
52 utl_file.get_line(input_file, w_newline);
53 -- j IS POPULATED WITH THE TWO-DIGIT CODE AND THAT IS USED AS THE INDEX VALUE TO CHECK IF IT EXISTS:
54 j := SUBSTR(W_newline, 44, 2);
55 IF w_04D.EXISTS(j) THEN
56 dbms_output.put_line(w_04D(j).statecd || ' ' || w_04D(j).statenm || ' exists');
57 END IF;
58 EXCEPTION
59 WHEN NO_DATA_FOUND THEN EXIT;
60 END;
61 END LOOP;
62 END IF;
63 utl_file.fclose(input_file);
64 END;
65 /
04 CA exists
01 AL exists
02 AZ exists
PL/SQL procedure successfully completed.
[Updated on: Fri, 05 August 2016 17:08] Report message to a moderator |
|
|
|
| Re: Check if value EXISTS in plsql table of record type [message #654604 is a reply to message #654562] |
Sun, 07 August 2016 11:54 |
harshadsp
Messages: 100
Registered: August 2008
|
Senior Member |
|
|
Barbara, thank you so much for your efforts, its really spot on. Now I understood that I couldn't check if value EXISTS if my table is of record type and I need to use index by varchar2 instead to use exists with state codes as a Index for that type.
But based on some more input files, I understand that I need to store 2 values in a index by varchar2 table. One is policy num and another is stated because on give input file's line I may check if the records state cd and policy num combination exists or not. So I need to have a varchar2 indexed table which would store 2 values polnum and state cd. Again with this I am facing problem to verify if the part of string from processing line exists in this table. I googled to check how to use exits on this bi directional table but its still not working. The below is sample code which I found and I may need this logic in my code.
declare
Type CAR_TABLE_ARRAY is varray(2) of varchar2(255);
TYPE CAR_TABLE_TYPE IS TABLE OF CAR_TABLE_ARRAY;
CAR_TABLE CAR_TABLE_TYPE;
begin
CAR_TABLE := CAR_TABLE_TYPE();
CAR_TABLE.EXTEND(10);
CAR_TABLE(1) := CAR_TABLE_ARRAY('DODGE','NY');
CAR_TABLE(2) := CAR_TABLE_ARRAY('FORD','CA');
CAR_TABLE(3) := CAR_TABLE_ARRAY('MUSTANG','DE');
CAR_TABLE(4) := CAR_TABLE_ARRAY('EDSEL','NJ');
CAR_TABLE(5) := CAR_TABLE_ARRAY('STUDEBAKER','PA');
DBMS_OUTPUT.put_line( CAR_TABLE(1)(1) );
DBMS_OUTPUT.put_line( CAR_TABLE(2)(1) );
DBMS_OUTPUT.put_line( CAR_TABLE(3)(1) );
DBMS_OUTPUT.put_line( CAR_TABLE(4)(1) );
DBMS_OUTPUT.put_line( CAR_TABLE(5)(1) );
DBMS_OUTPUT.put_line( CAR_TABLE(1)(2) );
DBMS_OUTPUT.put_line( CAR_TABLE(2)(2) );
DBMS_OUTPUT.put_line( CAR_TABLE(3)(2) );
DBMS_OUTPUT.put_line( CAR_TABLE(4)(2) );
DBMS_OUTPUT.put_line( CAR_TABLE(5)(2) );
--Now I need to check if DODGE and NY combination exists but not sure how to get this done
--something like this
if CAR_TABLE('DODGE')('NY').exists then
DBMS_OUTPUT.put_line('yes');
end if;
end;
This gives error "Invalid reference to variable 'VARCHAR2'" at line CAR_TABLE('DODGE')('NY').exists
I am planning to use a bi directional table with polnum and state values and will check if the polnum and state combination (which is coming from get_line of input file)exists in this table.
Any help is really appreciated, please advise. Thanks
|
|
|
|
|
|
| Re: Check if value EXISTS in plsql table of record type [message #654608 is a reply to message #654604] |
Sun, 07 August 2016 15:58 |
|
Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
SCOTT@orcl_12.1.0.2.0> host type test_data.txt
abcdePOLNUM1 tuvwxyzabcdefghijklmnopq0404D
abcdePOLNUM2 tuvwxyzabcdefghijklmnopq0104D
abcdePOLNUM3 tuvwxyzabcdefghijklmnopq0204D
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE DIRECTORY WCPOLS_INPUT_DIR AS 'c:\my_oracle_files'
2 /
Directory created.
SCOTT@orcl_12.1.0.2.0> DECLARE
2 W_input_dir VARCHAR2(100);
3 W_input_file VARCHAR2(50);
4 input_file utl_file.file_type;
5 w_newline VARCHAR2(2000);
6 TYPE r_04D IS RECORD(
7 id VARCHAR2(20),
8 polnum VARCHAR2(15),
9 trandt VARCHAR2(10),
10 trancd VARCHAR2(2),
11 rectyp VARCHAR2(2),
12 statecd VARCHAR2(2),
13 statenm VARCHAR2(2));
14 TYPE t_04D IS TABLE OF r_04D INDEX BY VARCHAR2(20);
15 w_04D t_04D;
16 j VARCHAR2(20);
17 BEGIN
18 dbms_output.enable(null);
19 W_input_dir := 'WCPOLS_INPUT_DIR';
20 W_input_file := 'test_data.txt';
21 input_file := utl_file.fopen(W_input_dir, w_input_file, 'r', 32000);
22 IF utl_file.is_open(input_file) THEN
23 LOOP
24 BEGIN
25 utl_file.get_line(input_file, w_newline);
26 IF substr(w_newline, 46, 3) = '04D' THEN
27 j := SUBSTR(W_newline, 44, 2) || ' ' || SUBSTR (W_newline, 6, 14);
28 w_04D(j).id := j;
29 w_04D(j).polnum := SUBSTR(W_newline, 6, 14);
30 w_04D(j).trandt := SUBSTR(W_newline, 37, 5);
31 w_04D(j).trancd := SUBSTR(W_newline, 42, 2);
32 w_04D(j).rectyp := SUBSTR(W_newline, 46, 2);
33 w_04D(j).statecd := SUBSTR(W_newline, 44, 2);
34 w_04D(j).statenm := CASE SUBSTR(W_newline, 44, 2)
35 WHEN '01' THEN 'AL' WHEN '02' THEN 'AZ' WHEN '03' THEN 'AR' WHEN '04' THEN 'CA'
36 WHEN '05' THEN 'CO' WHEN '06' THEN 'CT' WHEN '07' THEN 'DE' WHEN '08' THEN 'DC'
37 END;
38 END IF;
39 EXCEPTION
40 WHEN NO_DATA_FOUND THEN
41 EXIT;
42 END;
43 END LOOP;
44 END IF;
45 utl_file.fclose(input_file);
46 input_file := utl_file.fopen(W_input_dir, w_input_file, 'r', 32000);
47 IF utl_file.is_open(input_file) THEN
48 LOOP
49 BEGIN
50 utl_file.get_line(input_file, w_newline);
51 j := SUBSTR(W_newline, 44, 2) || ' ' || SUBSTR (W_newline, 6, 14);
52 IF w_04D.EXISTS(j) THEN
53 dbms_output.put_line(w_04D(j).statecd || ' ' || w_04D(j).polnum || ' exists');
54 END IF;
55 EXCEPTION
56 WHEN NO_DATA_FOUND THEN EXIT;
57 END;
58 END LOOP;
59 END IF;
60 utl_file.fclose(input_file);
61 END;
62 /
04 POLNUM1 exists
01 POLNUM2 exists
02 POLNUM3 exists
PL/SQL procedure successfully completed.
[Updated on: Sun, 07 August 2016 15:59] Report message to a moderator |
|
|
|
| Re: Check if value EXISTS in plsql table of record type [message #654640 is a reply to message #654608] |
Mon, 08 August 2016 05:57 |
harshadsp
Messages: 100
Registered: August 2008
|
Senior Member |
|
|
@Barbara-
Amazing!!! This is so perfect! I will use this logic in my program. Now I have understood how to index an array as per requirement. My problem was I was using index as integer and was not able to check EXISTS but your inputs have clarified my concerns. I really appreciate your time and efforts for providing the examples with code. It really helps me.
Thank you again!
|
|
|
|
Goto Forum:
Current Time: Thu May 15 03:10:48 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
