Can I use Varchar2(32767) or Varchar2 Table in IN of sql statement

0
votes

I have stored function which is returning Varchar2 (32767) and I want to use it in select statement under IN But it gives me error when i use it in Select under IN clause.

SELECT * FROM testcustomers1 where no_of_bu1 in(select myMaxLenFunc('test') from dual);

It gives me error

Error :- ORA-06502: PL/SQL: numeric or value error: character string buffer too small

If the return value is less than 4k it works fine but if it is greater than that it throws the above error.

Please suggest me if I use varchar2 table or Varchar2 are return in stored function how can i use it IN clause in select.

2
sqloracleplsql
what is the datatype of no_of_bu1?? and why can't use : no_of_bu1=myMaxLenFunc('test')... directly? - Tamil
datatype of no_of_bu1 is varchar2(16) and in the stored function in intend to return comma seperated values of no_of_bu1 based on my requirement. - Ravi Teja
Either take just the first 4000 characters or use a clob. - Gordon Linoff
Which Oracle version are you using? You could enable extended strings if you want to deal with varchar values up to 32k - a_horse_with_no_name
I can use 12g and extended data types but still IN is not accepting more than 4k - Ravi Teja

2 Answers

0
votes

You have the right idea using a collection instead of a string in the IN clause. Then you will not run into this problem. Try something like this:

CREATE OR REPLACE TYPE strings_t IS TABLE OF VARCHAR2 (4000)
/

CREATE OR REPLACE FUNCTION strings
   RETURN strings_t
   AUTHID DEFINER
IS
BEGIN
   RETURN strings_t ('abc', 'def', '123');
END;
/

CREATE TABLE t (s VARCHAR2 (100))
/

BEGIN
   INSERT INTO t
        VALUES ('abd');

   INSERT INTO t
        VALUES ('def');

   INSERT INTO t
        VALUES ('456');

   COMMIT;
END;
/

SELECT *
  FROM t
 WHERE t.s IN (SELECT COLUMN_VALUE FROM TABLE (strings ()))
/
0
votes

Your function is PL/SQL and can return a varchar2 string of more than 4000. This is illegal for SQL (if the MAX_STRING_SIZE parameter is of value STANDARD)

http://docs.oracle.com/cd/E11882_01/appdev.112/e17126/datatypes.htm

VARCHAR2 Maximum Size in PL/SQL: 32,767 bytes Maximum Size in SQL 4,000 bytes

So you need to find a way around. Since no_of_bu1 is a SQL column and cannot have more than 4000 bytes in content length you are save with this:

SELECT * FROM testcustomers1 
 where no_of_bu1 in(select substr(myMaxLenFunc('test'),1,4000) from dual);

Although I would truncate the string within the function.

If your DB is of Oracle 12.1 you can find out if your are STANDARD in SQL stringsize (i.e. 4000)

SELECT name, value
  FROM v$parameter
 WHERE name = 'max_string_size'