Re: stored procedure and a list of values

Hi
The way you are passing excluded_ids_in parameter is wrong. Because the select statement generated would look like SELECT e.entity_code
FROM table1 e,

       table2 ae
WHERE e.entity_code = ae.entity_code
AND e.version_index = ae.version_index AND ae.sector_code = sector_code_in
AND e.entity_code not in ('4035,4002,4004,4025,4039')

which definitely will not appear in the table. Correct me if I am wrong, you want your select statement to look like is as follows. SELECT e.entity_code
FROM table1 e,

       table2 ae
WHERE e.entity_code = ae.entity_code
AND e.version_index = ae.version_index AND ae.sector_code = sector_code_in
AND e.entity_code not in ('4035','4002','4004','4025','4039')

So when you create your list the excluded_ids_in variable should look like ' ''4035'',''4002'',''4004'',''4025'',''4039'' ' . Hope this will solve your problem

thanks
bpr

mr_oatmeal wrote:

> Hi All,
>
> I'm trying to pass a list of values into a stored procedure. I used to
> have this code as embeded sql and it would work, but am trying to make it
> a stored procedure. I pass this comma delimited list for my where clause
> to reference it but it does not reference it. Can I not do this or am I
> just doing it wrong?
>
> Here's my declaration area: excluded_ids_in is my list
> ex values for excluded_ids_in: 4035,4002,4004,4025,4039
>
> CREATE OR REPLACE PROCEDURE sp_homepage_sectors (
> sectors_cursor IN OUT cv_types.genCurType,
> sector_code_in IN number,
> excluded_ids_in IN varchar,
> outer out varchar2)
>
>
> AND the select stmnt:
>
> SELECT e.entity_code
> FROM table1 e,
> table2 ae
> WHERE e.entity_code = ae.entity_code
> AND e.version_index = ae.version_index
> AND ae.sector_code = sector_code_in
> AND e.entity_code not in ('excluded_ids_in')
>
> I'm using Vignette's StoryServer (CRM software) and Oracle 8i.
> Storyserver Requires a certain format for calling stored procedures...
>
> If you need any more info please let me know...
>
> Thanks,
> mr_oatmeal
>
> --
> Posted via CNET Help.com
> http://www.help.com/
Received on Wed Nov 01 2000 - 23:04:14 CST