Home » SQL & PL/SQL » SQL & PL/SQL » Date conversion with NCLOB (very urgent)
Date conversion with NCLOB (very urgent) [message #210238] Wed, 20 December 2006 00:13 Go to next message
nachiketa.iyengar
Messages: 5
Registered: November 2006
Location: INDIA
Junior Member
Hi,

I have this function which is supposed to return date in the format of 'dd/mm/yyyy'.

My database date format is 'mm/dd/yyyy'.

The problematic part is that one of the datatypes is of type NCLOB and as far as I know it is not possible to convert directly NCLOB to date. So I tried to return it as a string. It worked!

This function is called and the results are going to an excel through a macro. So Excel stores this string as it is and does not convert it into default date format of 'mm/dd/yyyy'.
No probs until here.

But, now I have a problem where I cannot use this fetched result(i.e.,work_termination_date in the form of a string) to manipulate(eg. add dates,add_months()).


I think the solution lies just in the part of the return variables and the datatype of the RETURN statement of the fn().


So please tell me what is the solution.This is very urgent.

Thanks in advance.

CREATE OR REPLACE function test_work_termination_date(sow_id1 number)
return DATE
is
variable_date NCLOB;
variable_count number;
variable_count1 number;
variable_sow_date DATE;
begin
select count(*) into variable_count1 from SCHEDULE_DESCRIPTIONS
where pk_sow_id=sow_id1 and LONG_TEXT is not null and pk_sub_sched_hd_id=3;
select count(*) into variable_count from REVISION_SCHEDULE_DESC
where sow_id=sow_id1 and SCHED_TEXT is not null and sub_sched_hd_id=3;
if (variable_count>0) then
select NVL(SCHED_TEXT,(select sched_text from
(select sched_text,version_no from revision_schedule_desc
where sow_id=sow_id1 and sub_sched_hd_id=3
and sched_text is not null order by version_no desc)
where rownum=1))
into variable_date
from REVISION_SCHEDULE_DESC,
(select sow_id,max(version_no) version_no
from REVISION_SCHEDULE_DESC
where REVISION_SCHEDULE_DESC.SUB_SCHED_HD_ID=3
and sow_id=sow_id1
group by sow_id)a
where REVISION_SCHEDULE_DESC.sow_id= a.sow_id
and REVISION_SCHEDULE_DESC.version_no=a.version_no and sub_sched_hd_id=3 ;
return variable_date;
elsif(variable_count1>0) then
select long_text into variable_date
from schedule_descriptions,(select pk_sow_id,max(PK_SCHED_DESC_ID) SCHED_DESC_ID
from SCHEDULE_DESCRIPTIONS
where SCHEDULE_DESCRIPTIONS.PK_SUB_SCHED_HD_ID=3
and pk_sow_id=sow_id1 group by pk_sow_id)b
where SCHEDULE_DESCRIPTIONS.pk_sow_id=b.pk_sow_id
and SCHEDULE_DESCRIPTIONS.pk_sched_desc_id= b.SCHED_DESC_ID ;
return variable_date;
else
select effective_date into variable_sow_date
from sow
where pk_sow_id=sow_id1;
return variable_sow_date;
end if;
exception
WHEN others then
return null;
end;
/


Output:


LINE/COL ERROR
-------- -----------------------------------------------------------------
28/1 PL/SQL: Statement ignored
28/8 PLS-00382: expression is of wrong type
37/1 PL/SQL: Statement ignored
37/8 PLS-00382: expression is of wrong type



Thanks,

Nachiketa Iyengar
Re: Date conversion with NCLOB (very urgent) [message #210240 is a reply to message #210238] Wed, 20 December 2006 00:23 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
return variable_date
should be changed to
return to_date(variable_date,<<put appropriate format for you>>)

Re: Date conversion with NCLOB (very urgent) [message #210245 is a reply to message #210240] Wed, 20 December 2006 00:38 Go to previous messageGo to next message
nachiketa.iyengar
Messages: 5
Registered: November 2006
Location: INDIA
Junior Member
Hi,

I have tried this earlier and I forgot to put it here. but still, the problem is when I return it as DATE, it converts it back to the system date format of 'mm/dd/yyyy'.

So I jus don't know what to do.
Please help me.

Thanks,
Nachiketa Iyengar
Re: Date conversion with NCLOB (very urgent) [message #210250 is a reply to message #210245] Wed, 20 December 2006 00:52 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Sorry can't understand what is the problem you are facing

Quote:

The problematic part is that one of the datatypes is of type NCLOB and as far as I know it is not possible to convert directly NCLOB to date. So I tried to return it as a string. It worked!

This function is called and the results are going to an excel through a macro. So Excel stores this string as it is and does not convert it into default date format of 'mm/dd/yyyy'.
No probs until here.


But, now I have a problem where I cannot use this fetched result(i.e.,work_termination_date in the form of a string) to manipulate(eg. add dates,add_months()).




so if you had converted nclob into to_Date while returning from function then date manipulation i.e add_months etc. etc... would have definitely worked

however if your problem is that you want to display the result in dd/mm/yyyy format then you either set nls_date_Format as 'dd/mm/yyyy' for your session or in query after doing all the date manipulation you can do to_char(<<your function that returns date from nclob>>,'dd/mm/yyyy') in the query


Re: Date conversion with NCLOB (very urgent) [message #210251 is a reply to message #210245] Wed, 20 December 2006 00:53 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
A date is NOT converted to any format. A date is a date. Only if you do a to_char (explicitly or implicitly by displaying it) it gets a format.
Solution: ALWAYS do an explicit to_char with a date-format if you want to display a date.
Previous Topic: Opposite of TRUNC
Next Topic: DROP ALL THE TABLES WITH A SINGLE QUERY
Goto Forum:
  


Current Time: Sat Dec 03 14:15:07 CST 2016

Total time taken to generate the page: 0.10410 seconds