CODEWITHSUNDEEP
oracle-databaseplsqloracle10g
Jacob
Jacob

Reputation: 14731

Type table null value check

I have type of following in my plsql block 

 TYPE table_type IS TABLE OF VARCHAR2(200);
 v_tab_1  table_type;

Values are initialized as 

if (PROD_AMNT > INV_AMNT) then            

 v_tab_1 := table_type(PROD_AMNT, 'CURR');
......

Sometimes the above condition will not be true and there will be null values in v_tab_1

What is the best approach to check values does exist in v_tab_1?

I have tried as 

 if not(v_tab_1.EXISTS(v_tab_1.first)) then

However the above resulted in NO_DATA_FOUND exception

How to deal with this?

Upvotes: 0

Views: 4865

Answers (4)

Rodrigo Andreu
Rodrigo Andreu

Reputation: 11

Declare
    Type TBL_VARCHAR Is Table Of varchar2(10);
    tblFirst tbl_varchar; 
    tblSecond tbl_varchar; 
Begin
    -- Check if tblFirst is a null variable 
    Begin 
        If tblFirst.Count >= 0 Then
           tblSecond := tblFirst;
        End If;
    Exception       
        When Others Then 
            tblSecond := new tbl_varchar();
    End;

    -- Then I use tblSecond
    DBMS_OUTPUT.PUT_LINE(tblSecond.Count);
End;

Upvotes: 1

OraNob
OraNob

Reputation: 694

Force a single error value into the table type? And then check for this value.

if (PROD_AMNT > INV_AMNT) then            

 v_tab_1 := table_type(PROD_AMNT, 'CURR');

else

 v_tab_1 := table_type(0, 'ERR');

end if;

Upvotes: 1

user272735
user272735

Reputation: 10648

Either check if the variable is null or if the container is initialized but empty. E.g.:

declare
  type list_t is table of varchar2(10);
  v_list list_t;
begin
  --
  -- 1) v_list is a null variable
  --

  v_list := null;

  if false then
    v_list := list_t('foo', 'bar');
  end if;

  if v_list is not null then
    dbms_output.put_line('v_list = ' || v_list(1) || ';' || v_list(2));
  else
    dbms_output.put_line('v_list is a null variable');
  end if;

  --
  -- 2) v_list is an empty but initialized collection
  --

  v_list := list_t();

  if false then
    v_list := list_t('foo', 'bar');
  end if;

  if v_list.exists(v_list.first) then
    dbms_output.put_line('v_list = ' || v_list(1) || ';' || v_list(2));
  else
    dbms_output.put_line('v_list is an empty but initialized collection');
  end if;

end;
/

Upvotes: 2

Adel
Adel

Reputation: 1468

You can use the following way:

- IF CreatedCursor%NOTFOUND THEN..

Or

- IF YourField IS NOT NULL THEN..

or 

- if NVL( YourField , 'NA' ) = 'NA'...

Upvotes: 1

Related Questions