CODEWITHSUNDEEP
sqloracle-databaseplsql
Ravi Teja
Ravi Teja

Reputation: 5

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

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.

Upvotes: 0

Views: 2462

Answers (2)

hol
hol

Reputation: 8423

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'

Upvotes: 0

Steven Feuerstein
Steven Feuerstein

Reputation: 1974

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 ()))
/

Upvotes: 0

Related Questions