Integration

Get Involved. Join the Conversation.

Topic

    Karthik Ramachandran
    Oracle Integration Cloud running SQL statement in Oracle ATP
    Topic posted July 23, 2019 by Karthik RamachandranRed Ribbon: 250+ Points, tagged Connection, Database, Integration 
    50 Views, 11 Comments
    Title:
    Oracle Integration Cloud running SQL statement in Oracle ATP
    Summary:
    Getting error when I ran sql statement in ATP using oracle integration cloud
    Content:

    Hi,

    I am executing sql statement using oracle integration cloud in ATP database. While adding connection and validating the SQL itself i am getting the error.

    SQL> 

      SELECT CITY, POSTAL_CODE, STATE_CODE, COUNTY
      FROM zeb_vertex_lookup
    WHERE NVL(POSTAL_CODE,0)   = NVL(#postalcode, NVL(POSTAL_CODE,0))
       AND NVL( STATE_CODE,0)      = NVL(#statecode , NVL( STATE_CODE,0)  )
       AND NVL( CITY ,0) = NVL(#city , NVL( CITY ,0))
    AND NVL( COUNTY,0) = NVL(#county , NVL( COUNTY,0))

    I have to use variable name with '#' so that it get displayed in the mapping. if I use ':' sql it is getting validated but i don't see it in the mapping.

    kindly please help.. 

    More Info on requirement -

    My SQL statement where clause should have search condition only if parameter has a value , if it null it should skip it.

    for example refer this pseudo code : 

    var a = select * from address 

    var b = where 1 = 1;

    if (postalcode is not null)

    b = b + 'postal_code =  ' + Postalcode + '

    if (city is not null)

    b = b + 'city = ' + City + '

     

     

     

     

    Image:

    Comment

     

    • Ankur Jain

      Hi Karthik,

      For complex queries, create package and call from DB adapter.

      Regards,

      Ankur

      • Karthik Ramachandran

        Ankur ... procedure or package won't return table or list of records.

        • Ankur Jain

          Karthik, you can use table type or sysref cur as an output in the proceure which return the list of records.

           

           

          • Karthik Ramachandran

            Hi Ankur,

            I tried the pl sql procedure with package. but i am missing something when I am doing mapping. Data are not coming in separate entity to map it. please see the screenshot & procedure.

            see below response -

            all values are coming together in county tag. 

            {
            "values": [
            {
            "county": "Westmoreland United States Pottawatomie KS 66549",
            "state": "CITY COUNTRY_NAME COUNTY STATE_CODE POSTAL_CODE",
            "city": "VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2"
            },
            {
            "county": "Blaine United States Pottawatomie KS 66549",
            "state": "CITY COUNTRY_NAME COUNTY STATE_CODE POSTAL_CODE",
            "city": "VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2"
            }
            ]
            }
             
            Please find the procedure code :
             

            create or replace Procedure
                            ZEB_PROC_ADDRESS_LKP(post_cd  in varchar2, state1  in varchar2, city1  in varchar2, county1  in varchar2, l_rc out sys_refcursor)
            AS

            begin

            open l_rc
               for
            SELECT  CITY,COUNTRY_NAME,COUNTY,STATE_CODE,POSTAL_CODE
            FROM zeb_vertex_lookup 
            WHERE NVL(POSTAL_CODE,0)   = NVL(post_cd, NVL(POSTAL_CODE,0)) AND NVL( STATE_CODE,0)  = NVL(state1 , NVL( STATE_CODE,0)  )
            AND NVL( CITY ,0) = NVL(city1 , NVL( CITY ,0)) AND NVL( COUNTY,0) = NVL(county1 , NVL( COUNTY,0));

            END;

            • Hemanth Lakkaraju

              This is mapping problem. Columns are repeatable elements and there is no indices in the mapping causing the issue. what is the expected input and output here for the mapping?

              • Karthik Ramachandran

                in mapping on the left side i don't see individual element. 

                If u see previous post, i created procedure and return recordset in cursor. When i map source to destination response. I don't see the individual element to map. Column return all the columns, i want to split and map it to postal code , county , state and city.. 

                • Hemanth Lakkaraju

                  Not sure I understand.

                  • Karthik Ramachandran

                    sql procedure returns cursor, which contains the record set. cursor is again a variable which hold the value.

                    when you select sql procedure in oic and create a map. the output of sql procedure is recordset which is rows & column. But because i don't see individual column i am not able to map to target response structure. Is there any other way to read that response.

                     

                    if you see current response structure and county column contain all the values, state column contain the name of the column & city column contain datatype. I know here mapping is incorrect. But i am not sure how to map individual column because i don't see the entity (postal code, city , state, county) in the source side of mapping.

                    {
                    "values": [
                    {
                    "county": "Westmoreland United States Pottawatomie KS 66549",
                    "state": "CITY COUNTRY_NAME COUNTY STATE_CODE POSTAL_CODE",
                    "city": "VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2"
                    },
                    {
                    "county": "Blaine United States Pottawatomie KS 66549",
                    "state": "CITY COUNTRY_NAME COUNTY STATE_CODE POSTAL_CODE",
                    "city": "VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2"
                    }
                    ]
                    }

                     

                    • Hemanth Lakkaraju

                      I understand the response part (response of the mapper). My question is about the request to mapper - the output of EBS call. Did you try for-ech on Column instead of Row?

                      • Karthik Ramachandran

                        this are steps in my OIC Integration.

                        1) Rest Service connection

                        2) Mapping request to Oracle ATP connection (invoking sql procedure)

                        3) Oracle ATP connection

                        4) Mapping response from Oracle ATP sql procedure to response restAPI.

                        I am facing issue in step 4, where i am trying to map Oracle ATP sql procedure output to rest api response structure. PL/SQL procedure doesn't display fields which we have to map.

                        Hope I am answering right question.

                         

                        • Hemanth Lakkaraju

                          What is the data you are getting from ATP invoke connection in response? Enable tracing for integration, capture the invoke response and share the data please.