Home » SQL & PL/SQL » SQL & PL/SQL » New at Creating Functions
New at Creating Functions [message #401147] Fri, 01 May 2009 17:35 Go to next message
OracleNorwood
Messages: 3
Registered: May 2009
Junior Member
Hi, I'm new to PL/SQL and currently learning creating functions. I'm running into some problems and looked over my function many times can't seem to find anything else to get my function to work correctly.

I am trying to create a function that accepts a date value and converts it into a string that reads in the format for example: Friday, the Thirteenth of February in the year Two Thousand Nine.

Here's what I have so far. I haven't finished the to_char conversion yet:

Create or replace function fncA_FormattedDate (
in_date in Date)
return VARCHAR2
is
v_date VARCHAR2;
Begin
v_date := to_char(in_date, 'Day');
return v_date;
End;
/

--Test query
select fncA_FormattedDate (sysdate)
from dual;

Can anyone please help me with the function? I think there may be something wrong with my datatypes.
Re: New at Creating Functions [message #401149 is a reply to message #401147] Fri, 01 May 2009 17:38 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You haven't told us what your problem is yet.Syntax error? Wrong output?

Ross Leishman
Re: New at Creating Functions [message #401150 is a reply to message #401149] Fri, 01 May 2009 17:40 Go to previous messageGo to next message
OracleNorwood
Messages: 3
Registered: May 2009
Junior Member
sorry, here's the error

ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "JHUYNH02.FNCA_FORMATTEDDATE", line 7
Re: New at Creating Functions [message #401152 is a reply to message #401150] Fri, 01 May 2009 18:00 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Might need to scope your declaration of v_date.

eg. VARCHAR2(9)

Ross Leishman
Re: New at Creating Functions [message #401154 is a reply to message #401147] Fri, 01 May 2009 18:26 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
  1  Create or replace function fncA_FormattedDate (
  2  in_date in Date)
  3  return VARCHAR2
  4  is
  5  v_date VARCHAR2;
  6  Begin
  7  v_date := to_char(in_date, 'Day');
  8  return v_date;
  9* End;
SQL> /

Warning: Function created with compilation errors.

SQL> show error
Errors for FUNCTION FNCA_FORMATTEDDATE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/8	 PLS-00215: String length constraints must be in range (1 ..
	 32767)


How did you get a clean compile?
use sqlplus along with CUT & PASTE to show us the whole session!
Re: New at Creating Functions [message #401156 is a reply to message #401154] Fri, 01 May 2009 18:42 Go to previous messageGo to next message
OracleNorwood
Messages: 3
Registered: May 2009
Junior Member
Here's my create function and test and the results...

Create or replace function fncA_FormattedDate (
in_date in Date)
return VARCHAR2
is
v_date date;
Begin
v_date := to_char(in_date, 'Day');
return v_date;
End;
/ 2 3 4 5 6 7 8 9 10

Function created.

SQL> select fncA_FormattedDate (sysdate)
from dual; 2
select fncA_FormattedDate (sysdate)
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "JHUYNH02.FNCA_FORMATTEDDATE", line 7
Re: New at Creating Functions [message #401157 is a reply to message #401147] Fri, 01 May 2009 18:56 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.

http://www.orafaq.com/forum/t/88153/0/

Post results from
SELECT * FROM V$VERSION;

Re: New at Creating Functions [message #401158 is a reply to message #401147] Fri, 01 May 2009 19:00 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
  1  Create or replace function fncA_FormattedDate (
  2  in_date in Date)
  3  return VARCHAR2
  4  is
  5  v_date varchar(21);
  6  Begin
  7  v_date := to_char(in_date, 'Day');
  8  return v_date;
  9* End;
SQL> /

Function created.

SQL> select fncA_FormattedDate (sysdate) from dual;

FNCA_FORMATTEDDATE(SYSDATE)
--------------------------------------------------------------------------------
Friday

SQL> 



>return VARCHAR2
>v_date date;
>return v_date;
Pay attention to datatypes.
Use correct datatypes.
Do not depend upon implicit datatype conversions.
VARCHAR2 is NOT same datatype as DATE
The function is defined to return VARCHAR2 but returns DATE

Many fine coding examples can be found at http://asktom.oracle.com



You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.

[Updated on: Fri, 01 May 2009 19:16]

Report message to a moderator

Re: New at Creating Functions [message #401175 is a reply to message #401158] Fri, 01 May 2009 23:53 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
I don't see any use of this function, where as it can be done directly in select clause.

SELECT TO_CHAR(SYSDATE,'DAY') FROM DUAL;


Thanks
Trivendra

[Updated on: Fri, 01 May 2009 23:53]

Report message to a moderator

Re: New at Creating Functions [message #401176 is a reply to message #401147] Sat, 02 May 2009 00:04 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I don't see any use of this function, where as it can be done directly in select clause.

What is your question?

SQL> SELECT TO_CHAR(SYSDATE,'DAY') FROM DUAL;

TO_CHAR(SYSDATE,'DAY')
------------------------------------
FRIDAY

SQL>  SELECT TO_CHAR(SYSDATE,'WW') from dual;

TO
--
18


With TO_CHAR() DATE datatype can be displayed in any format.
Previous Topic: Update String Concatenation
Next Topic: Delete duplicate rows but keep original
Goto Forum:
  


Current Time: Sat Dec 10 05:02:24 CST 2016

Total time taken to generate the page: 0.18332 seconds