Re: SQL in PL/SQL

Dear,

I think you should convert '1-DEC-2002' to nls_date_format.  
Default for oracle is 'DD-MON-YYYY'. So you can either use 
'01-DEC-02' or use to_date('01-DEC-2002','DD-MON-YYYY'). Same for 
to_date('31-DEC-2002','DD-MON-YYYY')

Thanks
Avinash

On Tue, 28 Jan 2003 Greg Griffiths wrote :
>Line 8 appears to be your line with the dates in, try using 
>TO_DATE to convert them, you may also want to do the same with 
>the number as well - which seems to be related to the error 
>message.
>
>At 21:04 27/01/03 +0000, you wrote:
>>I create a simple PL/SQL procedure to update some rows in Oracle 
>>9i. The
>>procedure is working through either SQLPLUS or TOAD. However, I 
>>used
>>Informatica to call this procedure. It return the error message 
>>like
>>these: " Backtrace message as the stack is unwounded by 
>>unhandled
>>exceptions", "ORA-06512 line 8 and ORA06512 column 
>>2","non-numeric
>>character was found where a numberic was expected". Please help 
>>me on the
>>syntax of this procedure. Is it missing something or Does it not 
>>connect
>>to right schema? Thanks in advance.
>>
>>Here is the coding:
>>
>>CREATE OR REPLACE PROCEDURE F_MONTH_UPD
>>IS
>>V_FISCAL_MONTH   NUMBER (2);
>>
>>BEGIN
>>
>>V_FISCAL_MONTH :=3;
>>
>>UPDATE CAMSSTG.NDW_BOP_DETAIL_M_TEST
>>SET FISCAL_MONTH = V_FISCAL_MONTH
>>WHERE MODIFICATION_DATE BETWEEN '1-DEC-2002' AND 
>>'31-DEC-2002';
>>
>>COMMIT;
>>
>>END F_MONTH_UPD;
>>/
>>---
>>Change your mail options at http://p2p.wrox.com/manager.asp or
>>to unsubscribe send a blank email to %%email.unsub%%.
>
>
>
>
>---
>Change your mail options at http://p2p.wrox.com/manager.asp or to 
>unsubscribe send a blank email to 
>%%email.unsub%%.





---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to [email protected].