SQL & PL/SQL

Hi all,

How to check whether a string exists in varray or not .

Version Details 


BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> get test
  1  DECLARE
  2     TYPE dnames_var IS VARRAY(7) OF VARCHAR2(30);
  3     dept_names dnames_var := dnames_var('Shipping','Sales','Finance','Payroll');
  4  BEGIN
  5    if dept_names.exists('Shipping')
  6    then
  7       dbms_output.put_line('Exists ................');
  8    end if ;
  9  /*   DBMS_OUTPUT.PUT_LINE('dept_names has ' || dept_names.COUNT
 10                          || ' elements now');
 11     DBMS_OUTPUT.PUT_LINE('dept_names''s type can hold a maximum of '
 12                           || dept_names.LIMIT || ' elements');
 13     DBMS_OUTPUT.PUT_LINE('The maximum number you can use with '
 14         || 'dept_names.EXTEND() is ' || (dept_names.LIMIT - dept_names.COUNT));
 15  */
 16* END;
 17  
 18  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 5


SQL> 

Any help in this regard is appreciated ...

Thanks,
P Prakash

Edited by: prakash on Apr 29, 2012 5:42 AM