Oracle Date Time Conversion

Im sending a string onto my oracle stored procedure, however when I attempt to convert it to a Date and perform some operation on it I get 'numeric or value error: character string buffer too small'.  I have verified that the format im sending in is correct.
 (3/28/2011 3:33:15 PM), but when I try to convert using
to_date(pFinishDateTime,'mm/dd/yyyy hh:mi:ss am') I get the error.  
create or replace
PROCEDURE GET_CONFLICT_VOLUME 
(pJunction IN VARCHAR2,
pPipeline IN VARCHAR2,
pBatchCode IN VARCHAR2,
pStartDateTime  IN VARCHAR2,
pFinishDateTime IN VARCHAR2,
pConflictVolume OUT VARCHAR2) AS
BEGIN
  
  SELECT round(sum(rate * ((to_date(pFinishDateTime,'mm/dd/yyyy hh:mi:ss am') - to_date(pStartDateTime,'mm/dd/yyyy hh:mi:ss am'))* 24))) into pConflictVolume 
     FROM schedule_events
     WHERE rate > 0  AND
     shipper || '-' || product || '-' || cycle || phase = pBatchCode
     AND junction = 'xxx'
     AND pipeline = 'xxx'
     AND STATION = '000'
    ORDER BY start_date_time;

END GET_CONFLICT_VOLUME;

Open in new window

jmpattonAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
>>If I run a test in PL/SQL Developer it will work

Then it's a C# output parameter size issue.
0
 
slightwv (䄆 Netminder) Commented:
What are you calling the procedure with?  make sure the receiving variable is large enough to hold the results from the procedure.
0
 
jmpattonAuthor Commented:
calling with C#.  
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
HainKurtSr. System AnalystCommented:
I guess you dont need this line

   ORDER BY start_date_time;
0
 
jmpattonAuthor Commented:
If I run a test in PL/SQL Developer it will work if I dont have the 'AM or PM' at the end of the date string
0
 
slightwv (䄆 Netminder) Commented:
What does your C# parameter declaration look like?  Does it have room for possible 4000 characters?

Can you call the procedure from sqlplus?

I'm trying to make sure it is the actual procedure itself 'causing' the problem and not just 'reporting' the problem.
0
 
HainKurtSr. System AnalystCommented:
try adding some size

   pStartDateTime    IN     VARCHAR2(22),
   pFinishDateTime   IN     VARCHAR2(22),
0
 
jmpattonAuthor Commented:
Here is my c# parameter

OracleCommand command = new OracleCommand("GET_CONFLICT_VOLUME", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("pJunction", OracleDbType.Varchar2).Value = stationPipingjunction;
                command.Parameters.Add("pPipeline", OracleDbType.Varchar2).Value = stationPipingPipeline;
                command.Parameters.Add("pBatchCode", OracleDbType.Varchar2).Value = batchcode;
                command.Parameters.Add("pStartDateTime", OracleDbType.Varchar2).Value = startDateTime;
                command.Parameters.Add("pFinishDateTime", OracleDbType.Varchar2).Value = finishDateTime;
                command.Parameters.Add("pConflictVolume", OracleDbType.Varchar2).Direction = ParameterDirection.Output;
                command.ExecuteNonQuery();

                conflictVolume = command.Parameters["pConflictVolume"].Value.ToString();
0
 
slightwv (䄆 Netminder) Commented:
>>command.Parameters.Add("pConflictVolume", OracleDbType.Varchar2).Direction = ParameterDirection.Output;


What is the 'default' size for a Varchar2 using MSOfts data provider?

You need to specify a size.  I'm not sure how to do that with that data provider.

I strongly encourage you to migrate over to Oracle's ODP.Net.  It will save you a lot of headaches.
0
 
jmpattonAuthor Commented:
Well it will work in SQL Developer if I dont have AM/PM at the end of the string.  but Oracle shold be able to accomodate for that shouldnt it?
0
 
HainKurtSr. System AnalystCommented:
and try cast the rounded value to varchar
CREATE OR REPLACE PROCEDURE GET_CONFLICT_VOLUME (
   pJunction         IN     VARCHAR2,
   pPipeline         IN     VARCHAR2,
   pBatchCode        IN     VARCHAR2,
   pStartDateTime    IN     VARCHAR2 (22),
   pFinishDateTime   IN     VARCHAR2 (22),
   pConflictVolume      OUT VARCHAR2)
AS
BEGIN
   SELECT CAST (
             ROUND (
                SUM (
                   rate
                   * ( (TO_DATE (pFinishDateTime, 'mm/dd/yyyy hh:mi:ss am')
                        - TO_DATE (pStartDateTime, 'mm/dd/yyyy hh:mi:ss am'))
                      * 24))) AS VARCHAR2)
     INTO v_result
     FROM schedule_events
    WHERE     rate > 0
          AND shipper || '-' || product || '-' || cycle || phase = pBatchCode
          AND junction = 'xxx'
          AND pipeline = 'xxx'
          AND STATION = '000';
END GET_CONFLICT_VOLUME;

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
>>but Oracle shold be able to accomodate for that shouldnt it?

Can't speak to that.

This is a guess but try:

command.Parameters.Add("pConflictVolume", OracleDbType.Varchar2,4000).Direction = ParameterDirection.Output;

0
 
sdstuberCommented:
>>>  pStartDateTime    IN     VARCHAR2 (22)  

this is illegal syntax for parameter declaration


>>> INTO v_result

this won't work because v_result isn't declared in your procedure
0
 
jmpattonAuthor Commented:
Ok, I fixed the date/time error.  but now it is erroring out saying "numeric or value error: character string buffer too small".  So this goes back to the default size of the parameter being returned.  The parameter being returned though would be no longer than 6 characters
0
 
slightwv (䄆 Netminder) Commented:
>>Ok, I fixed the date/time error

There was a date/time error?  Also, 'what' fixed it?

>>but now it is erroring out saying "numeric or value error: character string buffer too small".

Isn't that the original error message from the initial question?
0
 
HainKurtSr. System AnalystCommented:
did you try this
CREATE OR REPLACE PROCEDURE GET_CONFLICT_VOLUME (
   pJunction         IN     VARCHAR2,
   pPipeline         IN     VARCHAR2,
   pBatchCode        IN     VARCHAR2,
   pStartDateTime    IN     VARCHAR2,
   pFinishDateTime   IN     VARCHAR2,
   pConflictVolume      OUT VARCHAR2
)
AS
BEGIN
   SELECT   CAST (
               ROUND(SUM(rate
                         * ( (TO_DATE (pFinishDateTime,
                                       'mm/dd/yyyy hh:mi:ss am')
                              - TO_DATE (pStartDateTime,
                                         'mm/dd/yyyy hh:mi:ss am'))
                            * 24))) AS VARCHAR2(100)
            )
     INTO   pConflictVolume
     FROM   schedule_events
    WHERE   rate > 0
            AND shipper || '-' || product || '-' || cycle || phase =
                  pBatchCode
            AND junction = 'xxx'
            AND pipeline = 'xxx'
            AND STATION = '000';
END;

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.