Home » SQL & PL/SQL » SQL & PL/SQL » Error while executing procedure (ORACLE 10G EE RHEL 5)
Error while executing procedure [message #418755] Tue, 18 August 2009 08:53 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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 #419163 is a reply to message #418779] Thu, 20 August 2009 03:47 Go to previous messageGo to next message
sanjay6713
Messages: 40
Registered: June 2009
Location: INDIA
Member
Hi All,
Thanks for the reply.
Gracefully i found the cause of the problem.I just declared the below variable before BEGIN section.

end1 DATE :=to_date(enddate,'DD-MON-YY');

That actually solved my problem.
Anyways thanks once again.

Sanjay.

[Updated on: Thu, 20 August 2009 03:50]

Report message to a moderator

Re: Error while executing procedure [message #419169 is a reply to message #419163] Thu, 20 August 2009 04:31 Go to previous messageGo to next message
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;
Re: Error while executing procedure [message #419174 is a reply to message #419169] Thu, 20 August 2009 05:04 Go to previous messageGo to next message
sanjay6713
Messages: 40
Registered: June 2009
Location: INDIA
Member
Wel sorry i forgot to mention it before.

I done some modification to the proc by replacing the datatype DATE with VARCHAR2 and used CURSOR to fetch more rows.

Here is my final sample proc.

CREATE OR REPLACE PROCEDURE proc1(startdate IN VARCHAR2,enddate IN VARCHAR2)
IS
start1 DATE :=to_date(startdate,'DD-MON-YY');
end1 DATE :=to_date(enddate,'DD-MON-YY');
NUM number;
CURSOR sample is
select enum into NUM from emp
WHERE creation BETWEEN start1 and end1;
BEGIN
FOR i in sample LOOP
DBMS_OUTPUT.PUT_LINE(i.enum);
END LOOP;
END;




Thanks for the reply btw Smile

Sanjay.S
Re: Error while executing procedure [message #419180 is a reply to message #419174] Thu, 20 August 2009 05:19 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please format and use code tags (not bold).

Regards
Michel
Previous Topic: Merge on Table with UNUSABLE unique index
Next Topic: Implementing row_number function using SQL (merged)
Goto Forum:
  


Current Time: Sat Dec 03 18:02:08 CST 2016

Total time taken to generate the page: 0.18748 seconds