Prev: Urgent Multiple Requirements
Next: ORA-06502: PL/SQL: numeric or value error: character stringbuffer too small
From: Sanjeev on 7 Jun 2010 10:43
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: John Hurley on 7 Jun 2010 13:29
Sanjeev:

> 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.

CLOB
From: Steve Howard on 7 Jun 2010 22:02
On Jun 7, 10:43 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

I know this isn't an answer to your question, but this looks like a
really questionable design.
You want to insert into a single column a concatenated list of values
from another column?
From: joel garry on 8 Jun 2010 12:01
On Jun 8, 3:16 am, BChase <bsc7080xx...(a)myoracleportal.com> wrote:
> On Tue, 8 Jun 2010 00:53:28 +0000 (UTC), Mladen Gogala <gogala.mla...(a)gmail.com> wrote:
> >On Mon, 07 Jun 2010 07:43:39 -0700, Sanjeev wrote:
>
> >> 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.
>
> >CLOB and LONG. LONG is deprecated, both types are a pain in the neck or
> >lower.
>
> What about the XML Datatype... its CLOB sized... but formatted XML making it easier to pull data using built-ins. Thoughts ?

I agree with Steve, unless there is some specific reason to be talking
to another kind of system, relational constructs should be used. One
thing I work on is the database side of a web shopping app, and the
mixture of mysterious stuff in dll's that I don't get documentation
for, pushing data around in XML's _and_ fat client db access _and_
synchronizing Oracle and SS _and_ strange db-blind programming effects
is just a mess.

jg
--
@home.com is bogus.
"When they add Verizon, they will have to rebuild the entire insides
of the iPhone, because it is a new type of network."
"Oh, more complexity. Complexity is good." - editor of techmeme
interview on radio about new iPhone.
 | 
Pages: 1
Prev: Urgent Multiple Requirements
Next: ORA-06502: PL/SQL: numeric or value error: character stringbuffer too small