RE: Oracle SQL problem determination

The main thing I see wrong is that you are putting colons in front of the INTO variables.  That may be your problem.  It should look like this instead:

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;


From: [email protected] [mailto:[email protected]] On Behalf Of Lovelady, Dennis E.
Sent: Wednesday, May 30, 2012 4:30 PM
To: [email protected]
Subject: [toad] Oracle SQL problem determination


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

---
[cid:[email protected]]          DTCC Confidential (Yellow)
Dennis Lovelady
813-470-2795
CONNECT... to the future.


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