Error while executing procedure [message #418755] |
Tue, 18 August 2009 08:53  |
sanjay6713
Messages: 40 Registered: June 2009 Location: INDIA
|
Member |
|
|
HI All,
I tried a sample procedure in oracle 10g and try to fetch some records by passing dynamic values through procedure but was unable to do that.
Below is the scenario.
CREATE OR REPLACE PROCEDURE proc1(enddate IN DATE)
IS
NUM number;
BEGIN
select enum into NUM from emp
WHERE creation BETWEEN '&startdate' and 'enddate';
DBMS_OUTPUT.PUT_LINE(NUM);
END;SQL> 2 3 4 5 6 7 8
9 /
Enter value for startdate: 01-JAN-09
old 6: WHERE creation BETWEEN '&startdate' and 'enddate';
new 6: WHERE creation BETWEEN '01-JAN-09' and 'enddate';
Procedure created.
SQL> exec proc1('03-MAR-09')
BEGIN proc1('03-MAR-09'); END;
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "SANJAY.PROC1", line 5
ORA-06512: at line 1
SQL>
How to go about from here.
Thanks,
Sanjay.S
|
|
|
Re: Error while executing procedure [message #418763 is a reply to message #418755] |
Tue, 18 August 2009 09:04   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
'03-MAR-09' is NOT a date, it is a string.
Read about TO_DATE function.
And search BEFORE posting, this is a FAQ.
Regards
Michel
From your previous topic:
Michel Cadot wrote on Mon, 27 July 2009 15:23 | Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.
...
|
[Updated on: Tue, 18 August 2009 09:07] Report message to a moderator
|
|
|
Re: Error while executing procedure [message #418779 is a reply to message #418755] |
Tue, 18 August 2009 09:22   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Additionally, 'enddate' is not a date too, it is string with the value 'enddate'.
If you want to use parameter instead, use identifier enddate (without quotes).
Carefully pick the parameter name, as it shall not be the same as name of any column in the table.
By the way, are you aware, that the value for &startdate will be asked only when creating the procedure, not when running it - so it may be hardcoded in the CREATE PROCEDURE statement. Or are you planning to re-create the procedure from time to time? This approach looks strange - but without exact requirement, it is hard to suggest more.
|
|
|
|
Re: Error while executing procedure [message #419169 is a reply to message #419163] |
Thu, 20 August 2009 04:31   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
All you've done there is to add more bugs to the code.
ENDDATE is already of type DATE. Doint a TO_DATE on it forces a TO_CHAR conversion, which will use your default date format mask. If this mask isn't DD-MON-YY then the TO_DATE will fail.
Your code should look like this:CREATE OR REPLACE PROCEDURE proc1 (p_enddate IN DATE) IS
NUM number;
BEGIN
select enum
into NUM
from emp
WHERE creation BETWEEN to_date('&startdate','dd-mon-yy')
and p_enddate;
DBMS_OUTPUT.PUT_LINE(NUM);
END;
|
|
|
|
|