Home » SQL & PL/SQL » SQL & PL/SQL » Function Problem
| Function Problem [message #40712] |
Fri, 01 November 2002 14:48  |
Rizwan Qazi
Messages: 135
Registered: August 2002
|
Senior Member |
|
|
I have the following function
FUNCTION RETURNER(DW_IN VARCHAR2) RETURN VARCHAR2 IS
DW_RET_VAL VARCHAR2(50);
BEGIN
SELECT DECODE( DW_IN,
'199901','JAN1999',
'199902','FEB1999',
'199903','MAR1999',
'199904','APR1999',
'199905','MAY1999',
'199906','JUN1999',
'199907','JUL1999',
'199908','AUG1999',
'199909','SEP1999',
'199910','OCT1999',
'199911','NOV1999',
'199912','DEC1999',
'200001','JAN2000',
'200002','FEB2000',
'200003','MAR2000',
'200004','APR2000',
'200005','MAY2000',
'200006','JUN2000',
'200007','JUL2000',
'200008','AUG2000',
'200009','SEP2000',
'200010','OCT2000',
'200011','NOV2000',
'200012','DEC2000',
'200101','JAN2001',
'200102','FEB2001',
'200103','MAR2001',
'200104','APR2001',
'200105','MAY2001',
'200106','JUN2001',
'200107','JUL2001',
'200108','AUG2001',
'200109','SEP2001',
'200110','OCT2001',
'200111','NOV2001',
'200112','DEC2001',
'200201','JAN2002',
'200202','FEB2002',
'200203','MAR2002',
'200204','APR2002',
'200205','MAY2002',
'200206','JUN2002',
'200207','JUL2002',
'200208','AUG2002',
'200209','SEP2002',
'200210','OCT2002',
'200211','NOV2002',
'200212','DEC2002',
'200301','JAN2003',
'200302','FEB2003',
'200303','MAR2003',
'200304','APR2003',
'200305','MAY2003',
'200306','JUN2003',
'200307','JUL2003',
'200308','AUG2003',
'200309','SEP2003',
'200310','OCT2003',
'200311','NOV2003',
'200312','DEC2003')
INTO DW_RET_VAL
FROM DUAL;
RETURN DW_RET_VAL;
END;
/
When I execute the function with the following parameter I get the error
SQL> exec:x:=returner('200204');
BEGIN :x:=returner('200204'); END;
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1
What can I do to fix this or execute it properly?
Rizwan
|
|
|
|
| Re: Function Problem [message #40713 is a reply to message #40712] |
Fri, 01 November 2002 14:57  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
Oracle doesn't know what sixe the decode() will return, so just substr() it.
CREATE OR REPLACE FUNCTION RETURNER(DW_IN VARCHAR2) RETURN VARCHAR2 IS
DW_RET_VAL VARCHAR2(50);
BEGIN
SELECT substr(DECODE( DW_IN,
'199901','JAN1999',
...
'200311','NOV2003',
'200312','DEC2003'), 1, 50)
INTO DW_RET_VAL
FROM DUAL;
RETURN DW_RET_VAL;
END;
/
select RETURNER('200204') from dual;
APR2002
The easy way to change the date representaion though is like this....
select to_char(
to_date('200204', 'yyyymm'),
'MONyyyy') from dual;
APR2002
|
|
|
|
|
|
| Re: Function Problem [message #40718 is a reply to message #40713] |
Sat, 02 November 2002 02:01 |
gaurav behl
Messages: 6
Registered: October 2002
|
Junior Member |
|
|
see it is illegal to use DECODE in pl/sql as far as
oracle 8i is concerned. but there is one exception to it. u can use DECODE in pl/sql in cursors(explicit).
best of luck
GAURAV
if you think that i am wrong then give me a justification at gaurav_sting@yahoo.com
|
|
|
|
|
|
| Re: Function Problem [message #40723 is a reply to message #40713] |
Sun, 03 November 2002 00:23 |
gaurav behl
Messages: 6
Registered: October 2002
|
Junior Member |
|
|
todd
but you cannot use decode in pl/sql directly as written in one of the oracle press books- EXAM GUIDE FOR ---DEVELOPING PROGRAM UNITS WITH PL/SQL.
the book prepares for the ocp developer II exam.
it also says that you can use decode in cursors in pl/sql.
please give me an example of code.
thanx.
|
|
|
|
| Re: Function Problem [message #40729 is a reply to message #40713] |
Sun, 03 November 2002 16:55 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
|
Well, I'm not sure what they mean by "in PL/SQL directly", but I would consider explicit and implicit cursors to certainly be PL/SQL. The only limitation on DECODE is that it has to appear in a SQL statement (which in PL/SQL would be either an explicit or implicit cursor), not as an assignment expression.
|
|
|
|
Goto Forum:
Current Time: Sun Apr 28 23:16:06 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
