Solved

Stored porcedure and variable(s) question...

Posted on 2012-03-12
11
383 Views
Last Modified: 2012-03-12
I have a stored procedure that I would like to execute by using either a single date or 2 dates and am having s problem getting the syntax correct, maybe its not even possible...??

Calling the script like so.. not even sure if this is the correct syntax for executing the procedure..

PRODUCT_ORDER_DATA(,'12-jan-2012','13-jan-2012')"

Open in new window

OR
PRODUCT_ORDER_DATA('12-jan-2012',,)"

Open in new window



PROCEDURE PRODUCT_ORDER_DATA (p_ORDERDATE IN DATE DEFAULT NULL, p_ORDERDATE_BEG IN DATE DEFAULT NULL, p_ORDERDATE_END IN DATE DEFAULT NULL) AS
BEGIN

  IF p_ORDERDATE_BEG IS NULL AND p_ORDERDATE_END IS NULL THEN
... execute a single day script
ELSE
... execute a BETWEEN script
END IF;

Open in new window


So I was trying to use the above and wondered if this was the best way to approch the problem?

Looking for some suggestions or better ideas..

Thanks.

NG,
0
Comment
Question by:nike_golf
  • 3
  • 3
  • 2
  • +1
11 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37711141
Do not use implicit data type conversions.  Since the procedure is expecting a date, give it a date:


PRODUCT_ORDER_DATA(to_date('12-jan-2012','dd-mon-yyyy'),to_date('13-jan-2012','dd-mon-yyyy'))
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37711160
>>Looking for some suggestions or better ideas..

Missed this aspect of the question.

Depending on your exact situation, the logic seems sound.  You can probably use dynamic SQL and build the query ar run-time but this has drawbacks.
0
 
LVL 34

Expert Comment

by:johnsone
ID: 37711215
I am going a different way here, because I have a different interpretation of the question.

The way I understand it is that you want to send in either the first parameter, or parameters 2 and 3.

To call the procedure with just the first parameter:

PRODUCT_ORDER_DATA (TO_DATE(....));

Open in new window


No need to specify the second or third parameters, they have defaults.

To specify only 2 and 3, there are 2 ways:

PRODUCT_ORDER_DATA (NULL,TO_DATE(....), TO_DATE(...));
PRODUCT_ORDER_DATA (p_ORDERDATE_BEG=>TO_DATE(....), p_ORDERDATE_END=>TO_DATE(...));

Open in new window

0
 
LVL 8

Expert Comment

by:Christoffer Swanström
ID: 37711220
You have two alternatives.

1) Define the procedure are follows:

CREATE PROCEDURE PRODUCT_ORDER_DATA(date1 DATE, date2 DATE DEFAULT NULL) ...

Then in the code for the procedure you can check whether the second date value is NULL or not.

2) Create a package with two versions of the procedure, one taking one date and the other taking two as input:

CREATE PACKAGE xx IS

  PROCEDURE PRODUCT_ORDER_DATA(date1 DATE);
  PROCEDURE PRODUCT_ORDER_DATA(date1 DATE, date2, DATE)

...
0
 
LVL 13

Author Comment

by:nike_golf
ID: 37711296
The query is too complicated to build at runtime so that alternative I'll have to pass on.

johnsone, you are correct in your interpretation... however tosse makes a good point also in that the procedure really only needs 2 parameters.

tosse, is that not the same as what I was calling in the procedure?

IF p_ORDERDATE_BEG IS NULL AND p_ORDERDATE_END IS NULL THEN

Open in new window


I'm not familiar with packages, how does it make the decision on whether the variable is populated or not? Will this execute the Procedure "PRODUCT_ORDER_DATA" twice?

CREATE PACKAGE xx IS

  PROCEDURE PRODUCT_ORDER_DATA(date1 DATE);
  PROCEDURE PRODUCT_ORDER_DATA(date1 DATE, date2, DATE)

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 250 total points
ID: 37711467
If you want to stick with 2 variables and one procedure, then I would define it this way:

PROCEDURE PRODUCT_ORDER_DATA (p_ORDERDATE1 DATE, p_ORDERDATE2 DATE DEFAULT NULL) AS
BEGIN

  IF p_ORDERDATE1 IS NULL AND p_ORDERDATE2 IS NULL THEN
....raise_application_error
ELSE
   IF p_ORDERDATE2 IS NULL THEN
    ... execute a single day script
  ELSE
    ... execute a BETWEEN script
  END IF;
END IF;

Open in new window


If you went with the package route, you are overloading the procedure call and PL/SQL will make the decision on which procedure to use based on the number and type of parameters.  This could lead to a lot of code duplication between the 2 procedures, unless the 2 code paths are very different.
0
 
LVL 13

Author Comment

by:nike_golf
ID: 37711485
So when calling the procedure do I simply supply no variable or an empty variable?


PRODUCT_ORDER_DATA('12-jan-2012',,)

PRODUCT_ORDER_DATA('12-jan-2012','','')

PRODUCT_ORDER_DATA('12-jan-2012',NULL,NULL)



Thanks,
0
 
LVL 8

Accepted Solution

by:
Christoffer Swanström earned 250 total points
ID: 37711617
Your original version had three parameters, that would be a bit overkill in my opinion... Otherwise the option 1 I suggested is the same.

If I understand your requirements right, you want to either get order data either for one day, or for a range of dates. The easiest way to implement that could be:


CREATE PROCEDURE PRODUCT_ORDER_DATA(first_date DATE, last_date DATE DEFAULT NULL) IS

  eff_last_date DATE;

BEGIN

  IF
    last_date IS NULL
  THEN
    eff_last_date := first_date + 1;
  ELSE
    eff_last_date := last_date;
  ENDIF;

-- from now on use first_date and eff_last_date to retrieve orders
  ...

END;

Then the way you retrieve the order data should be identical, independent of whether two or one days were supplied. This way you don't need to duplicate a lot of code.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37711703
>>    eff_last_date := first_date + 1;

first_date + 1 might grab a wrong value if a record comes in exactly at midnight.  Be safe and do:

eff_last_date := first_date+1-1/86400;


I like the latest suggestion and have done something similar in the past.

Want to make it even more flexible?  Don't worry about which order they are in.

Since a between query needs the lesser value on the left, use least and greatest.

something like:

select somevalue from sometable
where somedatecolumn between least(first_date,eff_last_date) and greatest(first_date,eff_last_date)


Then the procedure will run even if you pass in:
PRODUCT_ORDER_DATA(to_date('13-jan-2012','dd-mon-yyyy'),to_date('12-jan-2012','dd-mon-yyyy'))
0
 
LVL 13

Author Closing Comment

by:nike_golf
ID: 37711784
Great solutions, thanks for the assistance.

NG,
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now