RE: Re: Oracle SQL problem determination

Thanks for your response.  I would not advertise it as a date field if it were something else.  But I understand.

COMPANY          1                              N             VARCHAR2 (3 Char)                         Frequency          4
PAYGROUP         2                              N             VARCHAR2 (3 Char)                         Frequency          11
PAY_END_DT     3                              N             DATE                     Frequency          78

... among other things.

The answer seems to be about the use of colons.  That's burned me before; I don't know how I let it happen again.
Dennis Lovelady
813-470-2795
CONNECT... to the future.

From: [email protected] [mailto:[email protected]] On Behalf Of smlivesay
Sent: Wednesday, May 30, 2012 5:11 PM
To: [email protected]
Subject: [toad] Re: Oracle SQL problem determination



I have to ask this because I have been burned too many times...

Please do a "DESC ps_pay_calendar" and show us that pay_end_dt really is a DATE field and not VARCHAR2 or CHAR.

Thanks

Mike

--- In [email protected]<mailto:toad%40yahoogroups.com>, "Lovelady, Dennis E." <dlovelady1@...<mailto:dlovelady1@...>> wrote:
>
> Hi, Toadsters:
>
> I'm sure this is not strictly a TOAD question, but I hope that perhaps TOAD (10.6.0.42) can help me solve it.
>
> I have a table that contains, among other things, a date column PAY_END_DT, and the following PL/SQL to extract from it:
>
> SET SERVEROUTPUT ON;
>
> DECLARE
> begin_period DATE;
> nbr_periods NUMBER;
> BEGIN
> SELECT MIN(pay_end_dt), COUNT(*)
> INTO :begin_period, :nbr_periods
> FROM ps_pay_calendar
> WHERE company = 'DTC' AND paygroup = 'DCP';
> END;
>
> Obviously, the PL/SQL is significantly understated, but this is enough, on our system, to produce this error:
>
> Error at line 3
> ORA-06502: PL/SQL: numeric or value error: character string buffer too small
> ORA-06512: at line 5
>
> To me, this makes zero sense, and I don't know how to diagnose this and make it go away. Would some kind soul be able to point me in the right direction for this?
>
> Oh! If I run just that exact SQL (minus the INTO clause of course) I get this:
> MIN(PAY_END_DATE) | COUNT(*)
> 1/16/2011 | 52
>
> ---
> [Description: C:\Documents and Settings\dlovelad.CORP\Application Data\Microsoft\Signatures\Default_files\image004.png] DTCC Confidential (Yellow)
> Dennis Lovelady
> 813-470-2795
> CONNECT... to the future.
>
>
>
> <BR>__________________________________________________________
> <FONT size=2><BR>
> DTCC DISCLAIMER: This email and any files transmitted with it are
> confidential and intended solely for the use of the individual or
> entity to whom they are addressed. If you have received this email
> in error, please notify us immediately and delete the email and any
> attachments from your system. The recipient should check this email
> and any attachments for the presence of viruses. The company
> accepts no liability for any damage caused by any virus transmitted
> by this email.</FONT>
>



<BR>_____________________________________________________________
<FONT size=2><BR>
DTCC DISCLAIMER: This email and any files transmitted with it are
confidential and intended solely for the use of the individual or
entity to whom they are addressed. If you have received this email
in error, please notify us immediately and delete the email and any
attachments from your system. The recipient should check this email
and any attachments for the presence of viruses.  The company
accepts no liability for any damage caused by any virus transmitted
by this email.</FONT>