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!

UTL_HTTP Request Failed

Discussion in 'SQL PL/SQL' started by ac.arijit, Apr 29, 2013.

  1. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi Experts,

    I got a bit weird issue here (although unresolved issue's are weird usually).

    I've put an AFTER INSERT Trigger on a table, which forms the HTTP request URL and then uses UTL_HTTP.request(UTL_URL.ESCAPE(<url>)) to send the request.
    Now this works perfectly fine when i insert a record into the table through an Insert Command from SqlPlus or any other DB tool, but when the same is done from an Oracle Apps Form, it throws the following error:
    Code (SQL):

    ORA-29273: HTTP Request failed
    ORA-06512: at "SYS.UTL_HTTP", line 1577
    ORA-00600: internal code error, arguments: [kgassg_2], [], [], [], [], [], []
     
     
  2. Bharat

    Bharat Community Moderator Forum Guru

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

    Can you please post the script here.
     
  3. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi Bharat,

    Sure,

    Here's the trigger script:
    Code (SQL):

    CREATE  OR  REPLACE TRIGGER apps.amrit_mejds_T1
    After   INSERT  ON  apps.mls_emp_job_dtls_stitching
    FOR Each ROW
    /*  =============================================================
     *                  By Arijit on 26-APR-2013                    *  
     *  ----------------------------------------------------------- *  
     *  This is an AFTER INSERT trigger fired at each insert on     *
     *  the base table APPS.MLS_EMP_JOB_DTLS_STITCHING to check     *
     *  the newly inserted record with respect to the completion    *
     *  of the allocated operations. This program block validates   *
     *  the performed move transaction's step and accordingly       *
     *  initiates the notification process for the operational      *
     *  employees over Short Message Service(SMS) media to convey   *
     *  the acknowledgement of the successful completion of the     *
     *  operation.                                                  *
     *  ----------------------------------------------------------- *  
     *  This notification process would notify each and every       *
     *  operational employee the operation rate, quantity and cost  *
     *  for the operation.                                          *
     *  ----------------------------------------------------------- *
     *                      PROGRAM FLOW                            *
     *                                                              *
     *  ELEMENTS        ---------------------->     CONDITIONS      *
     *  CONDITIONS      ---------true--------->     DATA COLLECTION *
     *  DATA COLLECTION ---------------------->     NOTIFICATION    *
     *  NOTIFICATION    ----success/failure--->     DATA HISTORY    *      
     *                                                              *  
     *  Elements:           Wip Entity Id                           *
     *                     ,Frm Step Id                             *
     *                     ,To Step Id                              *
     *                     ,Frm Opr Seq Num                         *
     *                     ,To Opr Seq Num                          *          
     *  Conditions:                                                 *
     *  Data Collection:    Transaction Id                          *
     *                     ,Transaction Date                        *
     *                     ,Po Num                                  *
     *                     ,Po Header Id                            *
     *                     ,Wip Entity Id                           *
     *                     ,Wip Entity Name                         *
     *                     ,Primary Item Id                         *
     *                     ,Organization Id                         *    
     *                     ,Operation Seq Num                       *
     *                     ,Operation Code                          *
     *                     ,Standard Operation Id                   *    
     *                     ,Rate                                    *
     *                     ,Quantity                                *
     *                     ,Operation Description                   *
     *                     ,Amount                                  *
     *                     ,Employee Id                             *
     *                     ,Employee Name                           *
     *                     ,Phone Num                               *
     *  Notification:                                               *
     *  Data History:   APPS.AMRIT_WIP_OP_NOTIFICATIONS             *
     *  ----------------------------------------------------------- *
    */

    DECLARE
    --  Local Data Members for Condition Elements
        From_Step_Id            NUMBER;
        From_Op_Seq_Num         NUMBER;
        To_Step_Id              NUMBER;
        To_Op_Seq_Num           NUMBER;

    --  Local Data Members for Notification Data Collection
        Transaction_Id          NUMBER;
        Transaction_Date        DATE;
        Organization_Id         NUMBER;
        Po_Num                  Varchar2(30);
        Po_Header_Id            NUMBER;
        Wip_Entity_Name         Varchar2(30);
        Wip_Entity_Id           NUMBER;
        Primary_Item_Id         NUMBER;
        Operation_Seq_Num       NUMBER;
        Standard_Operation_Id   NUMBER;
        Operation_Code          Varchar2(30);
        Operation_Description   Varchar2(100);
        Quantity                NUMBER;
        Rate                    NUMBER;
        Amount                  NUMBER;
        Currency_Code           Varchar2(30)    :=  'INR';
        Employee_Id             NUMBER;
        Employee_Name           Varchar2(60);
        Phone_Num               Varchar2(30);

    --  Getter method for the condition elements
        PROCEDURE   Get_Condition_Elements  IS
        BEGIN
        /*  Fetching the relevant attribute's information
         *  from the inserted record into the base table
         */
       
            Wip_Entity_Id   :=  :NEW.Wip_Entity_Id;
            From_Step_Id    :=  :NEW.Frm_Step_Id;
            From_Op_Seq_Num :=  :NEW.Frm_Opr_Seq_Num;
            To_Step_Id      :=  :NEW.To_Step_Id;
            To_Op_Seq_Num   :=  :NEW.To_Opr_Seq_Num;

        END Get_Condition_Elements;      

    --  Getter method for the notification data collection
        PROCEDURE   Get_Notification_Data   IS
        BEGIN
        /*  Fetching the newly inserted record
         *  attribute values for notification
         *  data collection
         */

            Transaction_Id      :=  :NEW.Transaction_Id;
            Transaction_Date    :=  :NEW.Transaction_Date;
            Wip_Entity_Id       :=  :NEW.Wip_Entity_Id;
            Operation_Seq_Num   :=  :NEW.Frm_Opr_Seq_Num;
            Quantity            :=  :NEW.Completed_Qty;
            Rate                :=  :NEW.Rate;
            Amount              :=  nvl(Quantity,0)*nvl(Rate,1);
            Employee_Id         :=  :NEW.Frm_Emp_Id;

        END Get_Notification_Data;

    --  Getter method for the Discrete job details
        PROCEDURE   Get_Job_Details(p_entity_id IN  NUMBER,p_op_seq_num IN NUMBER) IS
        BEGIN
        /*  Fetching the job details from the
         *  seeded master transactional pool
         *  for the valid assembly items at
         *  the manufacturing organization level
         */

            SELECT  DISTINCT
                    pha.po_header_id
                   ,pha.segment1    po_num
                   ,we.wip_entity_name  
                   ,wdj.organization_id
                   ,mif.inventory_item_id
                   ,bso.standard_operation_id
                   ,bso.operation_code
                   ,bso.operation_description
            INTO    Po_Header_Id
                   ,Po_Num
                   ,Wip_Entity_Name
                   ,Organization_Id
                   ,Primary_Item_Id
                   ,Standard_Operation_Id
                   ,Operation_Code
                   ,Operation_Description
            FROM    wip.wip_discrete_jobs           wdj
                   ,wip.wip_entities                we
                   ,po.po_distributions_all         pda
                   ,po.po_headers_all               pha
                   ,apps.mtl_item_flexfields        mif
                   ,bom.bom_operational_routings    bor
                   ,bom.bom_operation_sequences     bos
                   ,bom.bom_standard_operations     bso
            WHERE   we.wip_entity_id            =   p_entity_id
            AND     wdj.wip_entity_id           =   we.wip_entity_id
            AND     wdj.organization_id         =   we.organization_id
            AND     mif.organization_id         =   wdj.organization_id
            AND     mif.inventory_item_id       =   wdj.primary_item_id
            AND     UPPER(mif.inventory_item_status_code)   =   'ACTIVE'
            AND     nvl(mif.enabled_flag,'N')   =   'Y'
            AND     pda.wip_entity_id           =   wdj.wip_entity_id
            AND     pha.po_header_id            =   pda.po_header_id
            AND     bor.organization_id         =   mif.organization_id
            AND     bor.assembly_item_id        =   mif.inventory_item_id
            AND     bos.routing_sequence_id     =   bor.routing_sequence_id
            AND     bos.operation_seq_num       =   p_op_seq_num
            AND     bso.organization_id         =   bor.organization_id
            AND     bso.standard_operation_id   =   bos.standard_operation_id
            ;                          
        Exception
        /*  Handling all the standard exceptions
         *  raised by the sql query processor
         */
       
            WHEN    Others  THEN
            /*  Keeping all the supportive
             *  attributes non-initialized.
             */
               
                Po_Header_Id            :=  NULL;
                Po_Num                  :=  NULL;
                Wip_Entity_Name         :=  NULL;
                Organization_Id         :=  NULL;
                Primary_Item_Id         :=  NULL;
                Standard_Operation_Id   :=  NULL;
                Operation_Code          :=  NULL;
                Operation_Description   :=  NULL;

        END Get_Job_Details;    

    --  Getter method for the employee details
        PROCEDURE   Get_Employee_Details(p_person_id   IN  NUMBER)    IS
        BEGIN
        /*  Fetching the employee's contact details
         *  from the HRMS module employee's other
         *  details based on the parameterized employee
         */

            SELECT  papf.full_name
                   ,pp.phone_number
            INTO    Employee_Name
                   ,Phone_Num
            FROM    hr.per_all_people_f papf
                    LEFT    OUTER   JOIN    hr.per_phones       pp
                    ON  pp.parent_id    =   papf.person_id
                    AND pp.phone_type   =   'M'
                    AND UPPER(pp.parent_table)  =   'PER_ALL_PEOPLE_F'
                    AND trunc(sysdate)  BETWEEN trunc(nvl(pp.date_from,sysdate))
                                        AND     trunc(nvl(pp.date_to,sysdate))    
            WHERE   papf.person_id  =   p_person_id
            AND     trunc(sysdate)  BETWEEN trunc(nvl(papf.effective_start_date,sysdate))
                                    AND     trunc(nvl(papf.effective_end_date,sysdate))
            AND     nvl(papf.current_employee_flag,'N') =   'Y';        
        Exception
        /*  Handling the TOO_MAY_ROWS exception
         *  when there exists more than one rows
         *  for the requested record
         */

            WHEN    Too_Many_Rows   THEN
            BEGIN
            /*  Fetching the record with the highest
             *  effective start date
             */

                SELECT  pp.full_name
                       ,pp.phone_num
                INTO    Employee_Name
                       ,Phone_Num
                FROM   (SELECT  papf.full_name
                               ,pp.phone_number phone_num
                               ,trunc(pp.date_from)
                        FROM    hr.per_all_people_f papf
                                LEFT    OUTER   JOIN    hr.per_phones       pp
                                ON  pp.parent_id    =   papf.person_id
                                AND pp.phone_type   =   'M'
                                AND UPPER(pp.parent_table)  =   'PER_ALL_PEOPLE_F'
                                AND trunc(sysdate)  BETWEEN trunc(nvl(pp.date_from,sysdate))
                                                    AND     trunc(nvl(pp.date_to,sysdate))    
                        WHERE   papf.person_id  =   p_person_id
                        AND     trunc(sysdate)  BETWEEN trunc(nvl(papf.effective_start_date,sysdate))
                                                AND     trunc(nvl(papf.effective_end_date,sysdate))
                        AND     nvl(papf.current_employee_flag,'N') =   'Y'
                        ORDER   BY  2   DESC)   pp
                WHERE   rownum  <=  1;                            
            Exception
            /*  Handling all the standard exceptions
             *  raised during the sql processor execution
             */

                WHEN    Others  THEN
                /*  Keeping all the supportive
                 *  attributes non-initialized.
                 */
               
                    Employee_Name   :=  NULL;
                    Phone_Num       :=  NULL;
                   
            END;
       
        /*  Handling all the standard exceptions
         *  raised during the sql processor execution
         */

            WHEN    Others  THEN
            /*  Keeping all the supportive
             *  attributes non-initialized.
             */
               
                Employee_Name   :=  NULL;
                Phone_Num       :=  NULL;
                         
        END Get_Employee_Details;      

    --  Instantiator method to instantiate all the
    --  getter methods as sub-methods and get all
    --  the supportive attributes from the database
        PROCEDURE   Init_All_Getters    IS
        BEGIN
        /*  Verification of all the required
         *  notification data elements for
         *  fetching the supportive job details
         */

            IF  Wip_Entity_Id       IS  NOT NULL    
            AND Operation_Seq_Num   IS  NOT NULL    THEN

            /*  Instantiating the getter method for
             *  fetching job supportive details  
             */

                Get_Job_Details(Wip_Entity_Id,Operation_Seq_Num);
            END IF;
           
        /*  Verification of all the required
         *  notification data elements for
         *  fetching the supportive phone details
         */
         
            IF  Employee_Id IS  NOT NULL    THEN

            /*  Instantiating the getter method for
             *  fetching job supportive details  
             */

                Get_Employee_Details(Employee_Id);
            END IF;
           
        END Init_All_Getters;
       
    --  Display method to display the values of
    --  the data members related
        PROCEDURE   Display_All_Members IS
        BEGIN
        /*  Printing output of all the data members
         *  on the console
         */

            Dbms_Output.Put_Line('From_Step_Id: '||From_Step_Id);
            Dbms_Output.Put_Line('From_Op_Seq_Num: '||From_Op_Seq_Num);
            Dbms_Output.Put_Line('To_Step_Id: '||To_Step_Id);
            Dbms_Output.Put_Line('To_Op_Seq_Num: '||To_Op_Seq_Num);
            Dbms_Output.Put_Line('Transaction_Id: '||Transaction_Id);
            Dbms_Output.Put_Line('Transaction_Date: '||Transaction_Date);
            Dbms_Output.Put_Line('Organization_Id: '||Organization_Id);
            Dbms_Output.Put_Line('Po_Num: '||Po_Num);
            Dbms_Output.Put_Line('Po_Header_Id: '||Po_Header_Id);
            Dbms_Output.Put_Line('Wip_Entity_Name: '||Wip_Entity_Name);
            Dbms_Output.Put_Line('Wip_Entity_Id: '||Wip_Entity_Id);
            Dbms_Output.Put_Line('Primary_Item_Id: '||Primary_Item_Id);
            Dbms_Output.Put_Line('Operation_Seq_Num: '||Operation_Seq_Num);
            Dbms_Output.Put_Line('Standard_Operation_Id: '||Standard_Operation_Id);
            Dbms_Output.Put_Line('Operation_Code: '||Operation_Code);
            Dbms_Output.Put_Line('Operation_Description: '||Operation_Description);
            Dbms_Output.Put_Line('Quantity: '||Quantity);
            Dbms_Output.Put_Line('Rate: '||Rate);
            Dbms_Output.Put_Line('Amount: '||Amount);
            Dbms_Output.Put_Line('Currency_Code: '||Currency_Code);
            Dbms_Output.Put_Line('Employee_Id: '||Employee_Id);
            Dbms_Output.Put_Line('Phone_Num: '||Phone_Num);
         
        END Display_All_Members;
       
    --  Method invoker for the notification history
    --  maintenance of the WIP Operation level distribution
        PROCEDURE   Post_Notification_History   IS
        --  Local data containers
            Notification_Id                 NUMBER;
            Notification_Type   Constant    varchar2(30)    :=  'SMS';
            Message_Type        Constant    varchar2(30)    :=  'WIP_MV_OP';
            Message_Content                 varchar2(160);
            Content_Var1                    varchar2(20);
            Content_Var2                    varchar2(20);
            Content_Var3                    varchar2(20);
            Content_Var4                    varchar2(20);
            Content_Var5                    varchar2(20);
            STATUS              Constant    varchar2(30)    :=  'QUEUED';
            Process_Flag        Constant    varchar2(30)    :=  'N';                
                       
        BEGIN
        /*  Running a pre-verification process of the
         *  available data in the containers to form
         *  the HTTP Request URL compliant to the HTTP
         *  standards  
         */

            IF  Employee_Id     IS  NOT NULL
            AND Employee_Name   IS  NOT NULL
            AND Phone_Num       IS  NOT NULL
            AND Rate            IS  NOT NULL
            AND Quantity        IS  NOT NULL
            AND Amount          IS  NOT NULL
            AND Transaction_Id  IS  NOT NULL
            AND Wip_Entity_Id   IS  NOT NULL
            AND Po_Header_Id    IS  NOT NULL    THEN

            /*  Initializing the required attributes
             *  before the actual data storage
             */

            --  Notification Id
                SELECT  apps.amrit_wip_op_notif_s.NEXTVAL
                INTO    Notification_Id
                FROM    sys.dual;
           
            --  Message Content Variables
                Content_Var1    :=  substr(Employee_Name,1,20);
                Content_Var2    :=  substr(' AEPL. ',1,20);
                Content_Var3    :=  substr(' LOCK COMPLETED: '||Quantity,1,20);
                Content_Var4    :=  substr(' RATE: '||Rate,1,20);
                Content_Var5    :=  substr(' AMOUNT: '||Amount,1,20);
           
            --  Forming the main message content
                Message_Content :=  substr('Dear '||Content_Var1
                                           ||', Thank you  for registering with'||Content_Var2
                                           ||Content_Var3
                                           ||Content_Var4
                                           ||Content_Var5
                                          ,1
                                          ,160
                                          );

            /*  Starting the history maintenence data
             *  posting into the database for further
             *  processing of the notification messages
             *  which are posted with status QUEUED and
             *  require the post-processor to take control
             *  over these unprocessed notifications
             */

                INSERT  INTO    apps.amrit_wip_op_notifications
                (Notification_Id
                ,Notification_Type
                ,Message_Type
                ,Message_Content
                ,STATUS
                ,Transaction_Id
                ,Wip_Entity_Id
                ,Po_Header_Id
                ,Employee_Id
                ,Recipient
                ,Process_Flag
                )VALUES
                (Notification_Id
                ,Notification_Type
                ,Message_Type
                ,Message_Content
                ,STATUS
                ,Transaction_Id
                ,Wip_Entity_Id
                ,Po_Header_Id
                ,Employee_Id
                ,Phone_Num
                ,Process_Flag
                );
             
            END IF;
               
        END Post_Notification_History;

    --  Method invoker for the Notification Processor
        PROCEDURE   Init_Notifier   IS
            P_Recipient Varchar2(100);
            P_Message   Varchar2(160);
        BEGIN
        /*  Initializing the local data members
         */

            P_Recipient :=  Phone_Num;
            P_Message   :=  'Dear '||Employee_Name||', Thank you  for registering with AEPL. LOCK COMPLETED '||Quantity;

        /*  Initiating the notification processor
         *  to start and process the desired notification
         */

            apps.amrit_developers_toolkit.sendsms(P_Recipient,P_Message);                
         
        END Init_Notifier;

    BEGIN
    /*  Instantiating the getter method to fetch
     *  the newly inserted record condition elements
     *  into the local data members for reusability
     */

        Get_Condition_Elements;

    /*  Instantiating the validation program to
     *  validate the data before triggering the
     *  conditional notification program
     */

        IF  Wip_Entity_Id   =   11626565
        AND From_Step_Id    =   1
        AND To_Step_Id      =   3
        AND From_Op_Seq_Num =   60
        AND To_Op_Seq_Num   =   60  THEN
             
        /*  Instantiating the getter method
         *  to fetch the notification data
         *  from the newly inserted record
         *  into the local data members
         */

            Get_Notification_Data;
                       
        /*  Fetching the supportive attribute
         *  values from the database respective
         *  to the already collected data
         */
           
            Init_All_Getters;    

        /*  Displaying all the fetched data
         *  attributes for the corresponding
         *  values
         */

            Display_All_Members;
         
        /*  Posting notification data into
         *  the history storage pool before
         *  the notification processor starts
         */

            Post_Notification_History;
               
        /*  Instantiating the notification
         *  processor with the corresponding
         *  data
         */

            Init_Notifier;  
           
        END IF;
               
    END amrit_mejds_t1;
     
    And here's the script of apps.amrit_developers_toolkit.sendsms:
    Code (SQL):

            PROCEDURE SendSms(p_recipient   IN  Varchar2
                             ,p_message     IN  Varchar2
                             )  IS
                HTTP_Response   Varchar2(1000);
            BEGIN
                SELECT  utl_http.request(utl_url.escape('http://xxx.xxx.xxx.xxx:xxxx/bulksms/bulksms?username=xxx-xxxxx&password=xxxxx&type=0&dlr=1&destination='||p_recipient||'&source=xxxxx&message='||p_message))
                INTO HTTP_Response FROM dual;
               
                DBMS_OUTPUT.PUT_LINE('SMS Response : '||HTTP_Response);            
            END SendSms;