Optional parameter in oracle procedure
How can I use sysdate in the format of yyyymmdd? Note this will change the default date format for the entire session: But the better solution would be: What I was really looking for is some way to modify the procedure call. What I need is something like: You certainly can do that.
Here is the result: OK, what am I missing then. This is my full SP for now Oracle9i Enterprise Edition Release 9. Based on the script results, the procedure executed fine. The error s are from later in the script. Without seeing the script, it is difficult to identify what else may be going wrong. I rewrote your procedure a bit and simplified to just show what we are working on for the output and this is the result To debug procedures when you are working with them, it works best to simplify to the lowest common denominator.
Make that work, then start adding back in. Check your results as you go so that you know what is working and continue building until you get to where you want to be. Hope this has helped.
I think it would be helpful to see the script if it isn't too complicated. Something else in the script didn't work. I had to move the default to a set on the local variable or the procedure would not even compile without error.
Your on the right track. Just add in a bit at a time and make sure what you add works before going forward with more and you will get there very soon. The errors we were seeing were nothing. I wasn't returning anything to the ref cursor since I had clipped that portion out. I re-ran the procedure the way meluhk has it but it returns no data as it did for me before. It runs just fine but I don't get any date values see below: Disconnected from Oracle9i Enterprise Edition Release 9.
Here is my exact SP: I can't afford to pull out any more hair! I am begining to look like Bozo the clown! Sure, you can expect null output when you give it null input: It would work as expected. If you want to actually be able to pass the parameter and allow null to be passed, then you have to initialize it inside the procedure if it is null passed in. Comment from meluhk Date: Comment from JRamos Date: There is a difference between defaulting a parameter when nothing is passed in, and passing in null.
Note that the change I made will fail if you input data bigger than 8 characters I don't know what happened to may last post OK, that explains that and raises the hopefully final issue.
The application I am using to call this stored procedure custom app passes nulls when no data is specified. So now I need to trap for null and set it to the system date. Do I do where the variable is defined like we did the dats or do I do that somewhere else? How would that look? I think RCorfmon explained that very well. If the default value is a character string that contains embedded blanks or punctuation, or if it begins with a number for example, 6xxx , it must be enclosed in single, straight quotation marks.
NULL is assigned as the default value for the parameter and is used in error handling statements to return a custom error message for cases when the stored procedure is executed without a value for the SalesPerson parameter. The following example executes the stored procedure.
By continuing to browse this site, you agree to this use. Office Office Exchange Server. Not an IT pro? United States English Sign in. The content you requested has been removed. Specifying a Parameter Default Value. Specifying a Parameter Name.