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], "Lovelady, Dennis E." <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>
>




------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/toad/

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    http://groups.yahoo.com/group/toad/join
    (Yahoo! ID required)

<*> To change settings via email:
    [email protected] 
    [email protected]

<*> To unsubscribe from this group, send an email to:
    [email protected]

<*> Your use of Yahoo! Groups is subject to:
    http://docs.yahoo.com/info/terms/