holemania
asked on
Oracle - Create Procedure with Paramater
I'm struggling creating a simple stored procedure within Oracle. What am I missing from this query?
Basically in my report, I want user to be able to input a transaction date.
CREATE OR REPLACE PROCEDURE LABOR (LABOR_DATE IN DATE)
AS
BEGIN
SELECT E.ID, E.NAME, L.TRANSACTION_DATE, L.HOURS
FROM EMPLOYEE E LEFT OUTER JOIN LABOR L
ON E.ID = L.EMPLOYEE_ID
WHERE L.TRANSACTION_DATE = :LABOR_DATE
ORDER BY E.NAME;
END;
Basically in my report, I want user to be able to input a transaction date.
untested but you can declare a cursor as output something like:
CREATE OR REPLACE PROCEDURE LABOR (P_LABOR_DATE IN DATE, P_RESULTS OUT SYS_REFCURSOR)
AS
BEGIN
open p_results for
SELECT E.ID, E.NAME, L.TRANSACTION_DATE, L.HOURS
FROM EMPLOYEE E LEFT OUTER JOIN LABOR L
ON E.ID = L.EMPLOYEE_ID
WHERE L.TRANSACTION_DATE = P_LABOR_DATE
ORDER BY E.NAME;
END;
ASKER
Thanks so I added cursor output similar to what you provided. When I execute, it is giving me an error with "Invalid SQL Statement".
EXEC LABOR('12/01/2016');
>>1:EXEC LABOR('12/01/2016');
two things wrong:
First: '12/01/2016' is a string not a date. Either convert it to a date when you pass it in or change the procedure to accept a varchar2 and convert it inside the procedure.
Second: If you added my second parameter to the procedure, you don't pass a variable in that holds the result.
If testing with sqlplus or sql developer try this:
var myresults refcursor;
exec labor(to_date('12/01/2016' ,'MM/DD/YY YY'),:myre sults);
print myresults
two things wrong:
First: '12/01/2016' is a string not a date. Either convert it to a date when you pass it in or change the procedure to accept a varchar2 and convert it inside the procedure.
Second: If you added my second parameter to the procedure, you don't pass a variable in that holds the result.
If testing with sqlplus or sql developer try this:
var myresults refcursor;
exec labor(to_date('12/01/2016'
print myresults
Something else to consider. You need to know if TRANSACTION_DATE also has the time portion of the dates populated. In Oracle a DATE data type always has TIME built in. If the database is storing the actual time when a simple '=' won't return the correct data.
ASKER
I modified my stored procedure as followed:
So was hoping I can just call the SP as followed with my date.
EXEC LABOR ('12/04/2016');
CREATE OR REPLACE PROCEDURE LABOR (P_LABOR_DATE IN DATE, P_RESULTS OUT SYS_REFCURSOR)
AS
BEGIN
OPEN P_RESULRTS FOR
SELECT E.ID, E.NAME, L.TRANSACTION_DATE, L.HOURS
FROM EMPLOYEE E LEFT OUTER JOIN LABOR L
ON E.ID = L.EMPLOYEE_ID
WHERE TO_CHAR(L.TRANSACTION_DATE, 'MM/DD/YYYY') = P_LABOR_DATE
ORDER BY E.NAME;
END;
So was hoping I can just call the SP as followed with my date.
EXEC LABOR ('12/04/2016');
>>TO_CHAR(L.TRANSACTION_DA TE, 'MM/DD/YYYY') = P_LABOR_DATE
No. Do not do this. Compare dates with dates and strings with strings.
If L.TRANSACTION_DATE is a date, keep P_LABOR_DATE a date.
As you have it now, you are comparing a string to a date.
If L.TRANSACITION_DATE has an index, don't use any functions on it.
If it does have the time portion, change the parameter to VARCHAR2 and leave the call like you have it.
Then in the WHERE:
...
WHERE L.TRANSACTION_DATE >= to_date(P_LABOR_DATE,'MM/D D/YYYY') and L.TRANSACTION_DATE < to_date(P_LABOR_DATE,'MM/D D/YYYY')+1
...
>>So was hoping I can just call the SP as followed with my date.
Again, '12/04/2016' isn't a date. It is a string.
And you cannot. If you have a parameter that doesn't have a default value, it must have a place in the call.
You need a place for the results to go.
What are you wanting to do with the results of that query?
No. Do not do this. Compare dates with dates and strings with strings.
If L.TRANSACTION_DATE is a date, keep P_LABOR_DATE a date.
As you have it now, you are comparing a string to a date.
If L.TRANSACITION_DATE has an index, don't use any functions on it.
If it does have the time portion, change the parameter to VARCHAR2 and leave the call like you have it.
Then in the WHERE:
...
WHERE L.TRANSACTION_DATE >= to_date(P_LABOR_DATE,'MM/D
...
>>So was hoping I can just call the SP as followed with my date.
Again, '12/04/2016' isn't a date. It is a string.
And you cannot. If you have a parameter that doesn't have a default value, it must have a place in the call.
You need a place for the results to go.
What are you wanting to do with the results of that query?
ASKER
Even after changing the date, still getting invalid sql error.
I am planning to use this SP in a Crystal report with the result. So the date parameter will be for the user to select from calendar which will be in the format of "MM/DD/YYYY".
WHERE L.TRANSACTION_DATE = to_date(P_LABOR_DATE,'MM/DD/YYYY')
I am planning to use this SP in a Crystal report with the result. So the date parameter will be for the user to select from calendar which will be in the format of "MM/DD/YYYY".
I believe Crystal Reports can handle the OUT parameter coming back as a REF CURSOR.
>>Even after changing the date, still getting invalid sql error.
When calling with a single parameter of have you also changed the call.
I suggest you get this working with SQL Developer and leave Crystal out of things for right now.
Please post your current stored procedure and your test call code.
>>Even after changing the date, still getting invalid sql error.
When calling with a single parameter of have you also changed the call.
I suggest you get this working with SQL Developer and leave Crystal out of things for right now.
Please post your current stored procedure and your test call code.
ASKER
So the stored procedure is as followed.
I had also tried just by doing the following with the "Where" clause.
The call is as followed:
CREATE OR REPLACE PROCEDURE LABOR (P_LABOR_DATE IN DATE, P_RESULTS OUT SYS_REFCURSOR)
AS
BEGIN
OPEN P_RESULRTS FOR
SELECT E.ID, E.NAME, L.TRANSACTION_DATE, L.HOURS
FROM EMPLOYEE E LEFT OUTER JOIN LABOR L
ON E.ID = L.EMPLOYEE_ID
WHERE L.TRANSACTION_DATE = TO_DATE(P_LABOR_DATE, 'MM/DD/YYYY')
ORDER BY E.NAME;
END;
I had also tried just by doing the following with the "Where" clause.
WHERE L.TRANSACTION_DATE = P_LABOR_DATE
The call is as followed:
EXEC LABOR ('04-DEC-16')
>>The call is as followed:
You MUST provide the variable to hold the second parameter. Period.
You can keep trying to ignore it and it will keep failing.
>>So the stored procedure is as followed.
Your OUT variable name is P_RESULTS you are opening P_RESULRTS.
>>I had also tried just by doing the following with the "Where" clause.
Almost guaranteed to fail 100% of the time. You are telling Oracle to do an implicit data type conversion. Given the string format you are using, I expect it to fail.
>>L.TRANSACTION_DATE = TO_DATE(P_LABOR_DATE, 'MM/DD/YYYY')
You never did sate if TRANSACTION_DATE has the time portion of the date populated. Again, if it does, '=' will not work.
You MUST provide the variable to hold the second parameter. Period.
You can keep trying to ignore it and it will keep failing.
>>So the stored procedure is as followed.
Your OUT variable name is P_RESULTS you are opening P_RESULRTS.
>>I had also tried just by doing the following with the "Where" clause.
Almost guaranteed to fail 100% of the time. You are telling Oracle to do an implicit data type conversion. Given the string format you are using, I expect it to fail.
>>L.TRANSACTION_DATE = TO_DATE(P_LABOR_DATE, 'MM/DD/YYYY')
You never did sate if TRANSACTION_DATE has the time portion of the date populated. Again, if it does, '=' will not work.
ASKER
I tried providing the 2nd variable for the output, but still getting same error.
So I tried what you sugguested with the following:
The transaction_date is just a date when the transaction was created. There's no time and if I query it is listed as "04-DEC-16".
When I right click the SP to Run, this is the code it generated:
I tried executing the above but it connect to the database and disconnect right away.
So I tried what you sugguested with the following:
var myresults refcursor;
exec labor ('04-DEC-16', :myresults);
print myresults;
The transaction_date is just a date when the transaction was created. There's no time and if I query it is listed as "04-DEC-16".
When I right click the SP to Run, this is the code it generated:
DECLARE
P_LABOR_DATE DATE;
P_RESULTS SYS_REFCURSOR;
BEGIN
P_LABOR_DATE :=NULL;
LABOR(P_LABOR_DATE => P_LABOR_DATE,
P_RESULTS => P_RESULTS);
END;
I tried executing the above but it connect to the database and disconnect right away.
>>There's no time and if I query it is listed as "04-DEC-16".
That is the default Oracle date format. That doesn't tell you anything.
Try:
select TRANSACTION_DATE,'MM/D/YYY Y HH24:MI:SS') from labor where rownum<11;
>>I tried providing the 2nd variable for the output, but still getting same error.
Does just the select run? I don't expect any rows from the following SQL. I'm just looking for syntax errors.
SELECT E.ID, E.NAME, L.TRANSACTION_DATE, L.HOURS
FROM EMPLOYEE E LEFT OUTER JOIN LABOR L
ON E.ID = L.EMPLOYEE_ID
WHERE L.TRANSACTION_DATE = sysdate
ORDER BY E.NAME;
That is the default Oracle date format. That doesn't tell you anything.
Try:
select TRANSACTION_DATE,'MM/D/YYY
>>I tried providing the 2nd variable for the output, but still getting same error.
Does just the select run? I don't expect any rows from the following SQL. I'm just looking for syntax errors.
SELECT E.ID, E.NAME, L.TRANSACTION_DATE, L.HOURS
FROM EMPLOYEE E LEFT OUTER JOIN LABOR L
ON E.ID = L.EMPLOYEE_ID
WHERE L.TRANSACTION_DATE = sysdate
ORDER BY E.NAME;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Since I was passing date as string, I just need to also change my TRANSACTION_DATE to match. All good now thanks.
>> I just need to also change my TRANSACTION_DATE to match
If you wrapped L.TRANSACTION_DATE with a TO_CHAR, you shouldn't. If there is an index on that column, using TO_CHAR will not allow it to be used in any query. This can be a huge performance issue.
If you wrapped L.TRANSACTION_DATE with a TO_CHAR, you shouldn't. If there is an index on that column, using TO_CHAR will not allow it to be used in any query. This can be a huge performance issue.
...
WHERE L.TRANSACTION_DATE = LABOR_DATE
...
It is a good practice to use some identifier for parameters in case they also match a column name.
Open in new window
In PL/SQL you also need an INTO or some place to store the results. As-is, it still won't run.