| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> User Defined Character Function
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(*)
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(*)
My function gives me
select String_Char_In('N,G,P') from dual;
STRING_CHAR_IN('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.
Received on Fri Nov 10 2000 - 14:13:22 CST
 |
 |