[OIC] How to use NVL in SQL query while using DB Adapter in Oracle Integration Cloud?

 

This post will show how to use NVL in SQL query while using DB Adapter in Oracle Integration Cloud.

 

We have below query as an example in which we are filtering on object type and getting count of each object type.

 

SELECT owner,object_name,object_type FROM all_objects WHERE object_type = NVL(#p_object_type,object_type)

• Start with a REST Trigger with query parameter as "P_OBJECT_TYPE" and below response payload:

{
    "result": [
        {
            "owner": "",
            "object_name": "",
            "object_type": ""
        }
    ],
    "status":"",
    "message":""
}

 

•  Then, add DB adapter and select Run a SQL Statement option
•  But, when we try to use it DB Adapter we get below error:

 

 

 

Error: Unable to create the request and response data definition for this SQL Query. Please fix the SQL Statement or try to switch to Perform table operation option on the Welcome page. Refer Oracle documentation for further information.

 

• To overcome this issue, we have to utilize Stored Procedure option. We can write standalone procedure or procedure in package. Here we will write below procedure:

CREATE OR REPLACE PROCEDURE get_data(
    p_in_object_type            IN     VARCHAR2 DEFAULT NULL,
    p_out_status                OUT    VARCHAR2,
    p_out_message               OUT    VARCHAR2,
    p_out_data                  OUT    SYS_REFCURSOR             
)
AS
   
BEGIN

    OPEN p_out_data FOR
    SELECT OWNER
        ,object_name
        ,object_type
    FROM all_objects
    WHERE object_type = NVL(p_in_object_type, object_type);

    p_out_status    :='Success';
    p_out_message   :='Data fetched successfully';
    
EXCEPTION
    WHEN OTHERS THEN
        p_out_status    :='Error';
        p_out_message   :='Exception: '||SQLERRM;
END get_data;

•  Now use this procedure in DB Adapter as below:

 

• Then map the fields as below. For each response field map the name field from DB Adapter response. 

• Mapper code will look like below now:

<xsl:template match="/" xml:id="id_11">

      <nstrgmpr:executeResponse xml:id="id_12">

            <nstrgdfl:response-wrapper xml:id="id_31">

                  <xsl:for-each xml:id="id_56" select="$GetData/nsmpr0:OutputParameters/nsmpr0:P_OUT_DATA/nsmpr0:Row">

                        <nstrgdfl:result xml:id="id_36">

                              <nstrgdfl:owner xml:id="id_37">

                                    <xsl:value-of xml:id="id_38" select="nsmpr0:Column/@name"/>

                              </nstrgdfl:owner>

                              <nstrgdfl:object_name xml:id="id_39">

                                    <xsl:value-of xml:id="id_40" select="nsmpr0:Column/@name"/>

                              </nstrgdfl:object_name>

                              <nstrgdfl:object_type xml:id="id_41">

                                    <xsl:value-of xml:id="id_42" select="nsmpr0:Column/@name"/>

                              </nstrgdfl:object_type>

                        </nstrgdfl:result>

                  </xsl:for-each>

                  <nstrgdfl:status xml:id="id_32">

                        <xsl:value-of xml:id="id_33" select="$GetData/nsmpr0:OutputParameters/nsmpr0:P_OUT_STATUS"/>

                  </nstrgdfl:status>

                  <nstrgdfl:message xml:id="id_34">

                        <xsl:value-of xml:id="id_35" select="$GetData/nsmpr0:OutputParameters/nsmpr0:P_OUT_MESSAGE"/>

                  </nstrgdfl:message>

            </nstrgdfl:response-wrapper>

      </nstrgmpr:executeResponse>

</xsl:template>

 

•  Now change the mapper code as below so that fields are correctly mapped:

<xsl:template match="/" xml:id="id_11">

      <nstrgmpr:executeResponse xml:id="id_12">

            <nstrgdfl:response-wrapper xml:id="id_31">

                  <xsl:for-each xml:id="id_56" select="$GetData/nsmpr0:OutputParameters/nsmpr0:P_OUT_DATA/nsmpr0:Row">

                        <nstrgdfl:result xml:id="id_36">

                              <nstrgdfl:owner xml:id="id_37">

                                    <xsl:value-of xml:id="id_38" select="nsmpr0:Column[@name='OWNER']"/>

                              </nstrgdfl:owner>

                              <nstrgdfl:object_name xml:id="id_39">

                                    <xsl:value-of xml:id="id_40" select="nsmpr0:Column[@name='OBJECT_NAME']"/>

                              </nstrgdfl:object_name>

                              <nstrgdfl:object_type xml:id="id_41">

                                    <xsl:value-of xml:id="id_42" select="nsmpr0:Column[@name='OBJECT_TYPE']"/>

                              </nstrgdfl:object_type>

                        </nstrgdfl:result>

                  </xsl:for-each>

                  <nstrgdfl:status xml:id="id_32">

                        <xsl:value-of xml:id="id_33" select="$GetData/nsmpr0:OutputParameters/nsmpr0:P_OUT_STATUS"/>

                  </nstrgdfl:status>

                  <nstrgdfl:message xml:id="id_34">

                        <xsl:value-of xml:id="id_35" select="$GetData/nsmpr0:OutputParameters/nsmpr0:P_OUT_MESSAGE"/>

                  </nstrgdfl:message>

            </nstrgdfl:response-wrapper>

      </nstrgmpr:executeResponse>

</xsl:template>

• Now, validate and test the integration.