We help IT Professionals succeed at work.

Procedure syntax

jknj72
jknj72 asked
on
180 Views
Last Modified: 2018-02-02
I have a package with a procedure that I am trying to call from my ASP.NET application. I think my problem is with the Dates im passing.
Here is my SQL Developer code.

create or replace package body spectrum_cribeast as

procedure rcgetspectrumdata(startdt in DATE, enddt in DATE,  v_userid in varchar2, rcdata out refcursor) as
  
NEWSTARTDATE date := TRUNC(startdt);
NEWENDDATE date := TRUNC(enddt);

  begin

    OPEN rcdata FOR    
    Select * from vwinspectioninfo Where INSPECTION_DT between TO_DATE(NEWSTARTDATE) and TO_DATE(NEWENDDATE) and INSPECTOR = v_userid;

  end rcgetspectrumdata;

end spectrum_cribeast;

Open in new window


My dates im passing in are in mm/dd/yyyy format and I can see in my Oracle table that the format is 01-JAN-17 format. How can I get the correct format to work so I can return a ref_cursor and get that data to my front end?  

Any help would be very appreciative
JK
Comment
Watch Question

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
Dates don't have formats.  Text has a format.


If your table has columns of type DATE, and your procedure has parameters of type DATE, then they are completely compatible,  no matter how you view their text representation.

If though, you pass in text to your DATE parameter, Oracle will try to convert it for you, that may fail with an error, it may succeed but generate the wrong value, or it might work.

If you are going to pass in text, then you should change the parameter type to VARCHAR2,  then internal to the procedure turn it into a DATE with a known and fixed format.

Alternatively pass in a DATE value to the DATE parameter

Also, inside your procedure you do this...

TO_DATE(NEWSTARTDATE) and TO_DATE(NEWENDDATE)

that doesn't make sense.  each of those values is already a DATE.
Just use the dates directly:

        OPEN rcdata FOR
            SELECT *
              FROM vwinspectioninfo
             WHERE inspection_dt BETWEEN newstartdate AND newenddate
             AND inspector = v_userid;

Author

Commented:
ok but I think Ive tried every variation or passing the dates in and using the functions to try and get them both in the same format. I will see if it will work.
FYI, they are passed in as DATEand in the table they are DATE
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
if you are passing in dates as DATE type (text that happens to look like a date  is NOT a date) , then the problem is solely in the use of TO_DATE()  around your date variables.

TO_DATE takes a VARCHAR2 as the first parameter.  So, if you give it a date, it will first do an implicit conversion of your date into varchar2, then it will do an implicit conversion of that text back into a date.  Again, this may or may not produce results you will want.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
This worked for me while nothing else did? Thank you for your help
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.