Solved

Stored porcedure and variable(s) question...

Posted on 2012-03-12
11
409 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
11 Comments
 
LVL 77

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 77

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 35

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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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
 
LVL 35

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 77

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

626 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