oracle - how to handle null exception using dynamic query execute in bulk collect? -

i can execute below code :

execute immediate lv_search_query bulk collect  p_search; 

p_search collection.

lv_search dynamic query string variable.

if query execute , null value return in query. how handle below error.

error report - ora-06502: pl/sql: numeric or value error ora-06512: @ line 21 06502. 00000 - "pl/sql: numeric or value error%s" *cause: arithmetic, numeric, string, conversion, or constraint error occurred. example, error occurs if attempt made assign value null variable declared not null, or if attempt made assign integer larger 99 variable declared number(2). *action: change data, how manipulated, or how declared values not violate constraints.

how exception in collection & bulk collect or how handle it?

in such case when not use collection, oracle provides no_data_found exception handle if have no rows returns query. when use collection, no_data_found exception not handled. can work around below.

once execute statement:

execute immediate lv_search_query bulk collect  p_search; 

do check like:

 if p_search.count = 0   dbms_output.put_line('no records in test table');  end if; 

the above show collection empty.

----elaborative example:-----

as mentioned in comment getting error not becuase have null being returned execute immediate statement. because trying insert non-numeric character in type declare hold numeric or case that.

example1: check if collect has data or not

table created column number datatype

sql> desc test2;  name                                      null?    type  ----------------------------------------- -------- ----------------------------  num                                                number  sql> select * test2;  no rows selected 

block handle null in collection

 sql> set serverout on     sql> declare        type var table of number           index pls_integer;         v_var   var;         v_sql   varchar2 (100);     begin        v_sql := 'select num test2';         execute immediate v_sql bulk collect v_var;         if v_var.count = 0                  dbms_output.put_line ('no records in test table');        end if;     end;       /     no records in test table      pl/sql procedure completed.  sql>  

example 2: when try insert non-numeric character type declared handle numeric character. getting issue. can see here used exception block handle such issue. though exception raised, procedure comepleted.

 sql> desc test2;      name                                      null?    type      ----------------------------------------- -------- ----------------------------      col1                                               varchar2(10 char)   sql> select * test2;      col1     ----------     xxx     yyyw    sql> declare    type var table of integer       index pls_integer;     v_var   var;     v_sql   varchar2 (100); begin    v_sql := 'select col1 test2';         execute immediate v_sql bulk collect v_var;     if v_var.count = 0          dbms_output.put_line ('no records in test table');    end if; exception    when others          dbms_output.put_line (sqlerrm); end;    / ora-01722: invalid number ora-06512: @ line 11  pl/sql procedure completed.