web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Automate / Oracle Query to JSON P...
Power Automate
Unanswered

Oracle Query to JSON Parse Resulting in Null Array

(0) ShareShare
ReportReport
Posted on by

I'm building a flow to take an Oracle query and insert new rows into a SQL table. The use case for this stems from not having access to control the Oracle source data, so I need to get it out of Oracle and into SQL where I can do with it what I want.

 

Here's a summary of my flow:

beckajohns_0-1633204425063.png

 

It results in the following error at the last step of the flow:

The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression '@body('Parse_JSON')?['body']?['ResultSets']?['Table1']' is of type 'Null'. The result must be a valid array.

 

Here's the flow in more detail:

beckajohns_1-1633204549917.png

 

Here is the JSON schema:

{
 "type": "object",
 "properties": {
 "statusCode": {
 "type": "integer"
 },
 "headers": {
 "type": "object",
 "properties": {
 "Pragma": {
 "type": "string"
 },
 "Vary": {
 "type": "string"
 },
 "x-ms-request-id": {
 "type": "string"
 },
 "Strict-Transport-Security": {
 "type": "string"
 },
 "X-Content-Type-Options": {
 "type": "string"
 },
 "X-Frame-Options": {
 "type": "string"
 },
 "Cache-Control": {
 "type": "string"
 },
 "Set-Cookie": {
 "type": "string"
 },
 "x-ms-connection-gateway-object-id": {
 "type": "string"
 },
 "Timing-Allow-Origin": {
 "type": "string"
 },
 "x-ms-apihub-cached-response": {
 "type": "string"
 },
 "Date": {
 "type": "string"
 },
 "Content-Type": {
 "type": "string"
 },
 "Expires": {
 "type": "string"
 },
 "Content-Length": {
 "type": "string"
 }
 }
 },
 "body": {
 "type": "object",
 "properties": {
 "OutputParameters": {
 "type": "object",
 "properties": {}
 },
 "ResultSets": {
 "type": "object",
 "properties": {
 "Table1": {
 "type": "array",
 "items": {
 "type": "object",
 "properties": {
 "PRODUCTLISTPERCUSTOMER_ID": {
 "type": "integer"
 },
 "CUSTOMER_ID": {
 "type": "integer"
 },
 "DESCRIPTION": {
 "type": "string"
 },
 "CODE": {
 "type": "string"
 },
 "DATEINACTIVE": {
 "type": "string"
 }
 },
 "required": [
 "PRODUCTLISTPERCUSTOMER_ID",
 "CUSTOMER_ID",
 "DESCRIPTION",
 "CODE",
 "DATEINACTIVE"
 ]
 }
 }
 }
 }
 }
 }
 }
}

 

The output from the Parse JSON step is not null - I can see the array. So, why is the array passing through as null when I try to insert rows into my SQL table? I've tried initializing variables, appending to the array, etc. ... and I'm completely stumped at this point. Any thoughts? Thanks in advance for the help!

Categories:
  • v-xiaochen-msft Profile Picture
    Microsoft Employee on at

    Hi @beckajohns ,

     

    I did a similar test but did not encounter your problem.

    vxiaochenmsft_0-1633502888698.png

    {"body": {"ResultSets": {"Table1":[{"Var":"A"},{"Var":"B"}]}}}

    vxiaochenmsft_1-1633502911815.png

    {"body":{"ResultSets":{"Table1":[{"Var":"A"}]}}}

    Schema:

    {
     "type": "object",
     "properties": {
     "body": {
     "type": "object",
     "properties": {
     "ResultSets": {
     "type": "object",
     "properties": {
     "Table1": {
     "type": "array",
     "items": {
     "type": "object",
     "properties": {
     "Var": {
     "type": "string"
     }
     },
     "required": [
     "Var"
     ]
     }
     }
     }
     }
     }
     }
     }
    }

     

    Result:

    vxiaochenmsft_2-1633503011079.png

     

    Is it possible that there is a problem with your output?

    Could you try to use length() function to check if your array has items?

    vxiaochenmsft_3-1633503190276.png

     

    Best Regards,

    Wearsky

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Leaderboard > Power Automate

#1
11manish Profile Picture

11manish 298 Super User 2026 Season 2

#2
Mohsin Ali Profile Picture

Mohsin Ali 289

#3
Henil Profile Picture

Henil 125

Last 30 days Overall leaderboard