CODEWITHSUNDEEP
sqloracle-database
Marllon Nasser
Marllon Nasser

Reputation: 390

IN operator different type of arguments

I have this table:

CREATE TABLE TEST
(
  DEP_CODIGO_UNIDAD            NUMBER(4),
  XPTO                         NUMBER(4)
)

And I want to do this as a function on pl:

select * from TEST where DEP_CODE in (4102, 3201, 8540,...,2010)

This works using SQL but as a PL function I receive the ora-01722 invalid number

My function looks like this:

FUNCTION REL_TEST(vCeco                 IN VARCHAR2,
                errorCode             OUT NUMBER) RETURN CURSOR IS
OPEN CURSOR (bla bla) ...
for
select * from TEST where DEP_CODE in (vCeco)

Any ideas? I'm using Oracle 11g by the way.

Upvotes: 0

Views: 104

Answers (2)

Michael Broughton
Michael Broughton

Reputation: 4055

If you are sending yourself a comma-separated list of values, maybe try dynamic sql? (I would also take issue with a function that also has an OUT parameter, but that's another style point)

FUNCTION rel_test( v_ceco  IN VARCHAR2
                   ,errorCode OUT NUMBER)
RETURN sys_refcursor
IS
   l_cursor sys_refcursor;
BEGIN
   OPEN l_cursor for 'SELECT * from TEST where dep_Code in ('||v_ceco||')';
   RETURN l_Cursor;
END;

Upvotes: 1

Kim Berg Hansen
Kim Berg Hansen

Reputation: 2019

When you do:

select * from TEST where DEP_CODE in (vCeco)

And your variable vCeco is a string with for example the value 4102, 3201, 8540, it will be as if you had written:

select * from TEST where DEP_CODE in ('4102, 3201, 8540')

(Just with the string being in a bind variable.)

The string value '4102, 3201, 8540' is then attempted to be implicitly converted into one number, which it can't since the commas are part of the value, and therefore the exception is raised.

There are many alternatives for doing a dynamic IN list. You will find a selection of methods here:

http://oracle-base.com/articles/misc/dynamic-in-lists.php

Pick whichever of those solutions that fit your setup best.

Upvotes: 3

Related Questions