OraFAQ Forum: SQL & PL/SQL » Check if value EXISTS in plsql table of record type

Home » SQL & PL/SQL » SQL & PL/SQL » Check if value EXISTS in plsql table of record type (11G)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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.

Report message to a moderator

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;
/

Report message to a moderator

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

gives error as component stated must be declared.

Report message to a moderator

Re: Check if value EXISTS in plsql table of record type [message #654544 is a reply to message #654542]

Fri, 05 August 2016 06:34

harshadsp
Messages: 100
Registered: August 2008

Senior Member

component STATECD must be declared

Report message to a moderator

Re: Check if value EXISTS in plsql table of record type [message #654557 is a reply to message #654544]

Fri, 05 August 2016 11:19

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

I need to check if SUBSTR(W_newline, 44, 2) is present in w_04D.statecd

Loop on the table.

Report message to a moderator

Re: Check if value EXISTS in plsql table of record type [message #654559 is a reply to message #654474]

Fri, 05 August 2016 13:40

Bill B
Messages: 1971
Registered: December 2004

Senior Member

You have setup an associative array but you NEVER filled it with the states. If you had you could simply do

if g_statecd.exists(SUBSTR(W_newline, 44, 2)) then
....
end if;

Report message to a moderator

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: 9090
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.

Report message to a moderator

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: 9090
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

Report message to a moderator

Re: Check if value EXISTS in plsql table of record type [message #654607 is a reply to message #654604]

Sun, 07 August 2016 15:48

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

SCOTT@orcl_12.1.0.2.0> DECLARE
  2  -- create record and associative array with index of varchar2 datatype:
  3    TYPE car_table_array IS RECORD
  4  	 (state  VARCHAR2(2),
  5  	  make	 VARCHAR2(20));
  6    TYPE car_table_type IS TABLE OF car_table_array INDEX BY VARCHAR2(25);
  7    car_table  car_table_type;
  8  -- variable to use to loop through index values to display them:
  9    idx	    VARCHAR2(25);
 10  BEGIN
 11  -- populate associative array with varchar2 index containing both state and make:
 12    car_table('NY DODGE').state := 'NY';
 13    car_table('NY DODGE').make := 'DODGE';
 14    car_table('CA FORD').state := 'CA';
 15    car_table('CA FORD').make := 'FORD';
 16    car_table('DE MUSTANG').state := 'DE';
 17    car_table('DE MUSTANG').make := 'MUSTANG';
 18    car_table('NJ EDSEL').state := 'NJ';
 19    car_table('NJ EDSEL').make := 'EDSEL';
 20    car_table('PA STUDEBAKER').state := 'PA';
 21    car_table('PA STUDEBAKER').make := 'STUDEBAKER';
 22  -- display all values, using idx variable and first and next:
 23    idx := car_table.FIRST;
 24    WHILE idx IS NOT NULL LOOP
 25  	 DBMS_OUTPUT.PUT_LINE (car_table(idx).state || ' ' || car_table(idx).make);
 26  	 idx := car_table.NEXT(idx);
 27    END LOOP;
 28  -- check if state NY and make DODGE exists:
 29    IF car_table.EXISTS('NY DODGE') THEN
 30  	 DBMS_OUTPUT.PUT_LINE ('yes');
 31    END IF;
 32  END;
 33  /
CA FORD
DE MUSTANG
NJ EDSEL
NY DODGE
PA STUDEBAKER
yes

PL/SQL procedure successfully completed.

Report message to a moderator

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: 9090
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!

Report message to a moderator

Previous Topic:	SQL connectivity test
Next Topic:	Query help

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 20:07:41 CDT 2024