Comments embedded. On Jun 12, 12:11 pm, John <johnysm...@gmail.com> 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 Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en -~----------~----~----~----~------~----~------~--~---
