Where field in (varchar?)

[ThatRickGuy]

Hi Guys,
I'm trying to pull a set of values out of a table. I have a list of primary keys in a coma delimited string. How can I use that string to retrieve the values I want? I tried the following, but I am getting an "ORA-01722: invalid number" error. The p_Assessment_IDs parameter contains the value "408,400,403,401,402,404".

PROCEDURE usp_Select (p_results_cursor OUT CURSOR_TYPE, p_ASSESSMENT_IDs IN VARCHAR) AS
 BEGIN
     OPEN p_results_cursor FOR
   SELECT ASSESSMENT_ID, ASSESSMENT_NAME, 
          ASSESSMENT_DESCRIPTION, ASSESSMENT_MIN_INTERVAL, 
          ASSESSMENT_MAX_INTERVAL, 
          ASSESSMENT_EXPECTED_INTERVAL, 
          MULTIPLE_CALLS_ALLOWED, MULTIPLE_CALLS_WINDOW, 
          MULTIPLE_CALLS_LIMIT_TO_DAY, SYSTEM_ID, 
          LAST_MODIFIED_TIMESTAMP, LAST_MODIFIED_USERNAME, 
          CYCLE_STATUS_ID, ASSESSMENT_NUMBER, GROUP_ID, 
          CREATED_TIMESTAMP, CREATED_USERNAME, 
          REDO_ASSESSMENT
     FROM HTS.ASSESSMENT
    WHERE ASSESSMENT_ID IN (p_ASSESSMENT_IDs) ;
    [green]--If I hard code the values, it works fine
    --WHERE ASSESSMENT_ID IN (408,400,403,401,402,404) ;[/green]
 END;

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[jaxtell]

I'd make a function that takes a comma delimited list and returns a collection. then use that collection in your select statement.

 

[ThatRickGuy]

bah, Oracle has a way of making me sad.

If it takes that much work, I'll just re-write the call to pass in a collection parameter.

But this is oracle... it is possible to pass in some type of collection parameter... right?

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[jaxtell]

it sure is

 

[ThatRickGuy]

Nothing is ever easy though, at least not in Oracle land...

So I added a nested table defenition to my header:

CREATE OR REPLACE PACKAGE HTS.PAK_ASSESSMENT AS 
 TYPE CURSOR_TYPE IS REF CURSOR;
 TYPE NUMBERARRAY_TYPE IS TABLE OF NUMBER;

 PROCEDURE usp_Select (p_results_cursor OUT CURSOR_TYPE, p_ASSESSMENT_IDs IN NUMBERARRAY_TYPE); 
END;

And changed the type to NUMBERARRAY_TYPE in the body, and when I try to compile I get a "PLS-00642: local collection types not allowed in SQL statements" error on the IN clause.

Which looks like it means that the collection type has to be defined at the Schema level, which means more maintenance.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[ThatRickGuy]

Hmm, the IN operator doesn't seem to like collections. I'm now getting "PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got HTS.NUMBERARRAY_TYPE"

Please don't tell me I have to set up a cursor to loop through a collection to get the values out. Please? MacGuyver is on re-runs and I'm running out of duct tape.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[jaxtell]

declare

my_c number_TABLE_TYPE :=number_TABLE_TYPE(1,2,3,4,5);
result_code varchar2(30);
begin
select 'success' into result_code from dual where 1 in (select * from table(my_c));
DBMS_OUTPUT.PUT_LINE( result_code );
end;

 

[ThatRickGuy]

I'm going to need a little description of what exactly that's doing.

If I'm following it correctly, you are creating a table type collection with the values 1-5. Then you're checking to see if the value 1 is in the collection my_c, and writing 'success' to some output?

If so, how do I get my string of numbers loaded into my_c?

I'm starting to think I'll just drop the whole line of thought and just hit the database once for each value. Sure, it'll be significantly more overhead, but it's all local network traffic, and it drastically simplifies things.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.