Re: User Defined Character Function

sizhnor_at_my-deja.com wrote:
>
> My function work perfect but there is some
> problem! Can you think what is the work around or
> clue!
>
> SELECT count(*)
> FROM ISONE.T_CONTRACT A
> WHERE A.CONTRACT_STATUS IN ('N','G','P')
> ORDER BY A.CONTRACTTYPE_ID,
> A.CONTRACTID
>
> COUNT(*)
> ---------
> 3068
>
> select Count(*)
> FROM ISONE.T_CONTRACT A
> WHERE A.CONTRACT_STATUS IN (String_Char_In
> ('N,G,P'))
> ORDER BY A.CONTRACTTYPE_ID,
> A.CONTRACTID;
>
> COUNT(*)
> ---------
> 0
>
> My function gives me
>
> select String_Char_In('N,G,P') from dual;
>
> STRING_CHAR_IN('N,G,P')
> -----------------
> 'N','G','P'
>
> Whats the problem here! when I'm trying to use
> STRING_CHAR_IN('N,G,P') function in where cluse!
>
> ('N','P') supposed to be equal to (STRING_CHAR_IN
> ('N,G,P'))
>
> Whats the problem here!
>
> Do you think I need to used DBMS_SQL to make it
> dynamic like I'm trying to use!
>
> Script for String_Char_In Function
>
> FUNCTION String_Char_In( INPUTSTR VARCHAR2)
> RETURN VARCHAR2 IS
> OUT_STRING VARCHAR2(60):=NULL;
> STR_C CHAR(1):=NULL;
> STR VARCHAR2(60):=NULL;
> BEGIN
>
> STR:=RTRIM(LTRIM(INPUTSTR,''),'');
> FOR STR_SPACE IN 1.. LENGTH(STR) LOOP
> STR_C:= SUBSTR(STR,STR_SPACE,1);
> IF ((STR_C =',') OR (STR_C ='''')) THEN
> OUT_STRING:=OUT_STRING;
> ELSE
> IF (STR_SPACE = 1) THEN
> OUT_STRING:=''''|| STR_C||'''';
> ELSIF (STR_SPACE > 1) THEN
> OUT_STRING:= OUT_STRING||','||''''||STR_C||'''';
> END IF;
> END IF;
> END LOOP;
> RETURN ( OUT_STRING);
> EXCEPTION
> WHEN NO_DATA_FOUND THEN
> DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,100));
> WHEN OTHERS THEN
> DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,100));
> END String_Char_In;
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

Yes you do need dynamic sql - you might 'execute immediate' easier to code (8i+)

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)

"Early to bed and early to rise, 
 makes a man healthy, wealthy and wise." - some dead guy