Home » SQL & PL/SQL » SQL & PL/SQL » Problem in passing date as parameter (Oracle10g)
Problem in passing date as parameter [message #314143] Tue, 15 April 2008 21:18 Go to next message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
Problem in passing date
SQL>create or replace procedure pop(mis date)
as
co number;
co1 number;
cot number;
cot1 number;
error exception;
begin
Execute Immediate 'insert into emp_h
select * from temp_emp where hiredate='||mis||'';
Execute Immediate 'select count(rowid) into co from temp_emp where hiredate='||mis||'';
Execute Immediate 'select count(rowid) into co1 from emp_h where hiredate='||mis||'';
if(co=co1) then
delete from temp_emp where hiredate=mis;
dbms_output.put_line('success');
else
delete from  emp_h where hiredate=mis;
raise error;
end if;
commit;
exception
when error then
rollback;
dbms_output.put_line('error');
end;

SQL> exec pop('17-dec-1980');

begin pop('17-dec-1980'); end;

ORA-00904: "DEC": invalid identifier
ORA-06512: at "SCOTT.POP", line 9
ORA-06512: at line 1





Re: Problem in passing date as parameter [message #314144 is a reply to message #314143] Tue, 15 April 2008 21:21 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>'17-dec-1980'
Above NOT a DATE!
With Oracle, characters enclosed by single quote marks are STRINGS
'This a string 2008-04-15; not a date!'

When you want a DATE data type use TO_DATE function!

[Updated on: Tue, 15 April 2008 21:22] by Moderator

Report message to a moderator

Re: Problem in passing date as parameter [message #314145 is a reply to message #314144] Tue, 15 April 2008 21:33 Go to previous messageGo to next message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
create or replace procedure pop(mis date)
as
co number;
co1 number;
cot number;
cot1 number;
error exception;
begin
Execute Immediate 'insert into emp_h
select * from temp_emp where hiredate=to_date('||mis||',''YYYY-MM-DD'')';
Execute Immediate 'select count(rowid) into co from temp_emp where hiredate=to_date('||mis||',''YYYY-MM-DD'')';
Execute Immediate 'select count(rowid) into co1 from emp_h where hiredate=to_date('||mis||',''YYYY-MM-DD'')';
if(co=co1) then
delete from temp_emp where hiredate=mis;
dbms_output.put_line('success');
else
delete from  emp_h where hiredate=mis;
raise error;
end if;
commit;
exception
when error then
rollback;
dbms_output.put_line('error');
end;


SQL> exec pop('1980-12-17');

begin pop('1980-12-17'); end;

ORA-01840: input value not long enough for date format
ORA-06512: at "SCOTT.POP", line 9
ORA-06512: at line 1


Still error
Re: Problem in passing date as parameter [message #314147 is a reply to message #314145] Tue, 15 April 2008 21:45 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Instead of Executing the SQLs you generate, try displaying the generated SQL code with DBMS_OUTPUT instead. Then cut and paste that code into SQL*Plus or similar to see if you are generating valid SQL.

Ross Leishman
Re: Problem in passing date as parameter [message #314149 is a reply to message #314147] Tue, 15 April 2008 21:52 Go to previous messageGo to next message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
I didnt understand , can you please give one example.
Re: Problem in passing date as parameter [message #314150 is a reply to message #314143] Tue, 15 April 2008 21:58 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
to_date('||mis||',''YYYY-MM-DD'')'
Concatenating a string with a date variable causes its implicit conversion to VARCHAR2. You shall do it implicitely with proper date format. However you may avoid it, if you will follow the end of my post.

SQL> exec pop('1980-12-17');
Still string, although not causing the error in this session. But you shall change it too to keep it working in the future.

Do you have any reason, why you run the SQL statements in the procedure dynamically?
If so, declare a variable, assign it the statement you want to execute and investigate it (as Ross suggested).
In addition, the way you call SELECT dynamically is wrong. Syntax can be found in the documentation, available eg. online on http://tahiti.oracle.com/. Study PL/SQL User's Guide and Reference.

But, first, resume whether you need to call SQL statements dynamically. If you decide so, do not hardcode variables, BIND them. Search for this term in documentation.
Just a starter from 10gR2: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/overview.htm#sthref145
Last note: you may bind DATE, NUMBER and VARCHAR2 variables directly, you do not need to convert them to VARCHAR2 (as you need when hardcoding them).
Re: Problem in passing date as parameter [message #314167 is a reply to message #314150] Tue, 15 April 2008 22:40 Go to previous message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
Thanks flyboy and Ross Leishman its working fine with bind variable.
Previous Topic: hierarchy
Next Topic: comapre date with sysdate
Goto Forum:
  


Current Time: Sun Dec 04 12:44:25 CST 2016

Total time taken to generate the page: 0.17850 seconds