Prev: How to sum for last hour and last 24 hours without union all?
Next: OLM Functional Lead - St. Louis, MO
From: Sanjeev on 7 Jun 2010 10:44
Dear Gurus,

I have written below function

FUNCTION Get_Loc_Dtl
(
No IN VARCHAR2,
Date_Cond IN VARCHAR2
)
RETURN VARCHAR2
is
l_Start_Date DATE;
l_End_Date DATE;

l_Location_Detail VARCHAR2(32767) := NULL;
BEGIN

l_Start_Date := TO_DATE(Date_Cond||' '||'00:00:00','DD-MON-YYYY
HH24:MI:SS');
l_End_Date := TO_DATE(Date_Cond||' '||'23:59:59','DD-MON-YYYY
HH24:MI:SS');

FOR CUR_REC IN (
SELECT TO_CHAR(STARTTIME,'HH24:MI:SS')||' '||No||' '||LOCATION as
LOCATIONDETAIL
FROM Loc
WHERE SUB_No = No
AND STARTTIME BETWEEN l_Start_Date AND l_End_Date
ORDER BY STARTTIME
)
LOOP
l_Location_Detail := l_Location_Detail || '##' ||
CUR_REC.LOCATIONDETAIL;
END LOOP;
DBMS_OUTPUT.PUT_LINE('length : '||l_Location_Detail);
RETURN LTRIM(l_Location_Detail, '##');
END Get_Loc_Dtl;

My Actual need is to insert location value into a table i.e. command
as below

Table LocationDetail having below columns

LocId Number(10),
LocDetail VARCHAR2(4000),

insert into LocationDetail
Select 1 as LocId,
Get_Loc_Dtl('9833225665','05-Jan-2010') as LocDetail
from dual;

So, Is there any SQL datatype which will allow more than 4000
character so as to change data type of column 'LocDetail' in
LocationDetail table.

but when length of LOCATIONDETAIL goes beyond 4000 it is giving below
error
ORA-06502: PL/SQL: numeric or value error: character string buffer too
small

Could any one suggest me a solution for above same?

Thanking in Advance
Sanjeev
From: ddf on 7 Jun 2010 18:56
On Jun 7, 10:44 am, Sanjeev <sanjeev.atvan...(a)gmail.com> wrote:
> Dear Gurus,
>
> I have written below function
>
> FUNCTION Get_Loc_Dtl
> (
> No IN VARCHAR2,
> Date_Cond IN VARCHAR2
> )
> RETURN VARCHAR2
> is
> l_Start_Date DATE;
> l_End_Date DATE;
>
> l_Location_Detail VARCHAR2(32767) := NULL;
> BEGIN
>
> l_Start_Date := TO_DATE(Date_Cond||' '||'00:00:00','DD-MON-YYYY
> HH24:MI:SS');
> l_End_Date := TO_DATE(Date_Cond||' '||'23:59:59','DD-MON-YYYY
> HH24:MI:SS');
>
> FOR CUR_REC IN (
> SELECT TO_CHAR(STARTTIME,'HH24:MI:SS')||' '||No||' '||LOCATION as
> LOCATIONDETAIL
> FROM Loc
> WHERE SUB_No = No
> AND STARTTIME BETWEEN l_Start_Date AND l_End_Date
> ORDER BY STARTTIME
> )
> LOOP
> l_Location_Detail := l_Location_Detail || '##' ||
> CUR_REC.LOCATIONDETAIL;
> END LOOP;
> DBMS_OUTPUT.PUT_LINE('length : '||l_Location_Detail);
> RETURN LTRIM(l_Location_Detail, '##');
> END Get_Loc_Dtl;
>
> My Actual need is to insert location value into a table i.e. command
> as below
>
> Table LocationDetail having below columns
>
> LocId Number(10),
> LocDetail VARCHAR2(4000),
>
> insert into LocationDetail
> Select 1 as LocId,
> Get_Loc_Dtl('9833225665','05-Jan-2010') as LocDetail
> from dual;
>
> So, Is there any SQL datatype which will allow more than 4000
> character so as to change data type of column 'LocDetail' in
> LocationDetail table.
>
> but when length of LOCATIONDETAIL goes beyond 4000 it is giving below
> error
> ORA-06502: PL/SQL: numeric or value error: character string buffer too
> small
>
> Could any one suggest me a solution for above same?
>
> Thanking in Advance
> Sanjeev

Answered in another newsgroup. PLEASE do not cross=post as the same
group of people monitors all of the comp.databases.oracle newsgroups.


David Fitzjarrell
 | 
Pages: 1
Prev: How to sum for last hour and last 24 hours without union all?
Next: OLM Functional Lead - St. Louis, MO