I got this working by clearing few syntax errors and also by
correcting the hard coded timestamp format, I had to give milliseconds
as well.
create or replace procedure TEST_TIME_PROC (
v_SYS_ATTR_VALUE_ID NUMBER,
v_last_upd TIMESTAMP
) as
begin
merge into TEST_TIME t1
using (select
v_SYS_ATTR_VALUE_ID SYS_ATTR_VALUE_ID,
v_last_upd LAST_UPD
from dual
) t2
on (t1.SYS_ATTR_VALUE_ID = t2.SYS_ATTR_VALUE_ID)
when matched then
update set
--t1.SYS_ATTR_VALUE_ID = t2.SYS_ATTR_VALUE_ID,
t1.LAST_UPD = t2.last_upd
when not matched then
insert (
t1.SYS_ATTR_VALUE_ID,
t1.last_upd
) values (
t2.SYS_ATTR_VALUE_ID,
t2.last_upd
);
commit;
end;
/
declare
v_date TIMESTAMP(3):= '22-MAY-200907:48:53.275';
Begin
begin
TEST_TIME_PROC ( 12, v_date);
end;
begin
TEST_TIME_PROC ( 14, v_date);
end;
End;
/
drop procedure TEST_TIME_PROC;
/
On Jun 12, 1:40 pm, ddf <[email protected]> wrote:
> Comments embedded.
>
> On Jun 12, 12:11 pm, John <[email protected]> wrote:
>
> > Hi All,
>
> > I need to pass Date value to a procedure for inserting into a table.
>
> You're attempting to pass a TIMESTAMP, not a DATE.
>
> > But the date will be hard coded date as opposed to system date.
>
> Again, you are not passing any DATE values, these are TIMESTAMP
> values.
>
> > It
> > works for me when I tried with sysdate
>
> Can you show us the successful execution of this procedure using
> SYSDATE?
>
>
>
> > but fails when I am giving hard
> > coded date value, think I am missing some conversion here. Can some
> > one please help.
>
> > set define off;
>
> > create or replace procedure TEST_TIME_PROC (
> > v_SYS_ATTR_VALUE_ID NUMBER,
> > v_last_upd TIMESTAMP <--- not a date
> > ) as
>
> > last_upd TIMESTAMP := v_last_upd; <--- Why do this? You don't use this
> > variable you've just set
> > begin
> > merge into TEST_TIME t1
> > using (select
> > v_SYS_ATTR_VALUE_ID SYS_ATTR_VALUE_ID,
> > v_last_upd LAST_UPD
> > from dual
> > ) t2
> > on (t1.SYS_ATTR_VALUE_ID = t2.SYS_ATTR_VALUE_ID)
> > when matched then
> > update set
> > t1.SYS_ATTR_VALUE_ID = t2.SYS_ATTR_VALUE_ID
> > t1.LAST_UPD = v_last_upd
> > when not matched then
> > insert (
> > t1.SYS_ATTR_VALUE_ID,
> > t1.last_upd
> > ) values (
> > t2.SYS_ATTR_VALUE_ID,
> > v_last_upd
> > );
> > commit;
> > end;
> > /
>
> > declare
> > v_date TIMESTAMP := '31-DEC-99 12.00.00.000000000 AM';
>
> > Begin
>
> > begin
> > TEST_TIME_PROC ( 2778001, 'v_date');
> > end;
>
> > begin
> > TEST_TIME_PROC ( 2779001, 'v_date');
> > end;
>
> > End;
> > /
>
> > drop procedure TEST_TIME_PROC;
> > /
>
> > Gives me this error.
>
> > End;
> > Error report:
> > ORA-06550: line 7, column 1:
> > PLS-00905: object TX1.TEST_TIME_PROC is invalid
> > ORA-06550: line 7, column 1:
> > PL/SQL: Statement ignored
> > ORA-06550: line 11, column 1:
> > PLS-00905: object TX1.TEST_TIME_PROC is invalid
> > ORA-06550: line 11, column 1:
> > PL/SQL: Statement ignored
> > 06550. 00000 - "line %s, column %s:\n%s"
> > *Cause: Usually a PL/SQL compilation error.
> > *Action:
>
> First, the MERGE statement isn't PL/SQL friendly so you'll need to use
> dynamic SQL, I believe, to get this to even compile. Secondly, I
> can't get this to run after it does successfully get created (and I am
> probably missing something obvious). And it may also be that the
> MERGE command wasn't destined for execute immediate, either. You may
> need to roll up your sleeves and write a PL/SQL merge routine and
> leave the MERGE command to SQL*Plus.
>
> > Please help resolve this.
>
> > Thanks,
> > John
>
> David Fitzjarrell
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
