0

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.

senshin
  • 10,022
  • 7
  • 46
  • 59
Marllon Nasser
  • 390
  • 4
  • 22
  • You can't do that. See here - https://asktom.oracle.com/pls/apex/f?p=100:11:17254297095744::::P11_QUESTION_ID:110612348061 – OldProgrammer Jan 28 '15 at 17:56
  • How is your `vCeco` string being generated, and from where? There are ways to unpack a string into values (not necessarily pretty; [this question](http://stackoverflow.com/questions/3819375/convert-comma-separated-string-to-array-in-pl-sql) is active at the moment), but you can also pass a collection of numbers which can be easier. – Alex Poole Jan 28 '15 at 18:13
  • Alex, I tryed to test in pl sql passing a normal string to the test window ie 4102, 3201, 8540. I also tryed to add commas '4102', '3201', '8540' but got the same error. – Marllon Nasser Jan 28 '15 at 18:25

2 Answers2

3

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.

Kim Berg Hansen
  • 1,979
  • 12
  • 12
  • Thanks for the answer but I'm sorry... I cant see how those solutions can help me.. sorry for been an idiot. I also tryed what Michael Broughton suggested but didn't work. Anyways, I cant understand how this can works on SQL but not on a PL/SQL FUNCTION... – Marllon Nasser Jan 28 '15 at 18:42
  • In SQL your comma-separated list is part of the statement and parsed as part of the statement. What you do in your PL/SQL function would be the same as if you in SQL did `select * from TEST where DEP_CODE in (:bindvariable)`. That would also fail with the same error. Your comma-separated list is *one* string value with the commas being part of the string, not part of the statement. If it did not work like that, then it would be impossible to search for string values in the data that contained commas within the string. You need to use a different method, what you are doing is not possible. – Kim Berg Hansen Jan 28 '15 at 18:49
1

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;
Michael Broughton
  • 4,045
  • 14
  • 12