1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Periodic Alert Error

Discussion in 'Oracle Apps Technical' started by jagadekara, Jan 20, 2014.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi All,

    I am doing Periodic Alert. After writing query when click on verify it is verified and when click on run then the following error occurred.

    APP-ALR-04108: SQL error ORA-01455: Converting column overflows integer datatype occurred while executing the user-defined SQL statement for this alert

    So please help on this....
     
  2. Mahalakshmi

    Mahalakshmi Guest

    Hi Jagadekara,

    Looks like the cause of this problem is the converted form of the specified expression
    was too large for the specified datatype.

    Try to define a larger datatype or correct the data. Also, please check the following. i found it while browsing.

    ORA-01455 converting column overflows integer datatype

    Cause: The converted form of the specified expression was too large for the specified datatype.

    Action: Define a larger datatype or correct the data.

    Thanks,
    Mahalakshmi
     
  3. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thanks Mahalakshmi,

    but
    I don't have any data type. its just a select query.

    Ex: select sum(invoice_amount) from ap_invoices_all
     
  4. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Jagadekara,

    Please go through the following Metalink Note:

    OERR: ORA 1455 converting column overflows integer datatype (Doc ID 18858.1)
     
  5. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Jagadekara,

    What is the Table structure that you are using in the SQL?
     
  6. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi Bharat,

    I am using AP_INVOICES_ALL table
     
  7. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Jagadekara,

    Is there any variable declaration part in the Alert ?
     
  8. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    I think Oracle Alert doesn't accept pl/sql blocks.
    So I didn't use variable declaration.

    I just used Select sum(invoice_amount) into &inv_amount from ap_invoices_all.
     
  9. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Are you using the same select statement in the Alert ?

    May be the output for Sum(invoice_amount) is more than the number format.

    Try with any condition like following:

    select sum(invoice_amount) into &inv_amount from ap_invoices_all where rownum<4;

    Then check whether the error is repeating again or not.
     
  10. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi Bharat,

    I want total invoice_amount. In my table it is more than 10digits. So what can I do?

    select sum(invoice_amount) from ap_invoices_all results 23458956895.

    So I need to send this through Alert.
     
  11. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi Jagadekara,

    Can you try with below statement.
    Code (SQL):
    SELECT TO_CHAR(SUM(invoice_amount)) FROM ap_invoices_all
    Regards
    Sambasiva Reddy.K
     
    jagadekara likes this.
  12. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Super Sambuduk, It is working fine. Thank you sooo much.....