Using Oracle Execute to run complicated SQL Select not returning any data

[rmartyn]

I’m new to SnapLogic and was told by one of our senior SL developers that instead of using the Oracle Select snap, I could use the Oracle Execute snap to run a complex SQL select statement. When I try to do that though, I get no data and I can’t figure out how to get a “snap”, such as a csv formatter, to “connect” to it. The pieces are next to each other in the pipeline, but there is nothing in the spot in between them showing they have fitted together.

the only way I have been able to get it to work is by creating a database view representing the complex sql statement, then using an Oracle Select snap to pull the data from the db view.

[vineesha]

Hi,

i’m attaching the screenshot of a simple pipeline using oracle execute and csv formatter.
After you drag and drop the CSV formatter make sure the output view of oracle execute is connected to the input view of CSV formatter as shown in the picture.
attaching the settings of oracle execute snap

let me know if you still have issues.

[rmartyn]

Hi Vineesha,

Thank you for responding. My SQL is quite a bit more complicated than your example. It includes several “With” statements and such. I’m just wondering if SnapLogic can’t handle that? It runs fine if I put it in SQL Developer.

[vineesha]

Hi,
For the comprehensive scripting functionality you should use stored procedure functionality in the Stored Procedure Snap.

[SusanB]

Hi Vineesha,
I have had success using complicated “WITH” clause in EXECUTE snaps.
If you have the Validate and Execute as the Snap Execution type and you click the save or validate icon, the status of the query will appear in the circle between the two snaps. You can click on the clipboard on the circle to see the returned results or the error if it is red. Change the Preview type to json to get all the specifics.
If the execute query is using pipeline parameters OR mapped output (variable) values, check to make sure that the SQL Statement* “=” icon is clicked on and green.
Also please consider: I have found that Oracle PL/SQL WITH clause does not work exactly like SQL Server WITH.
Hope this helps.

[rmartyn]

Hi Susan, thank you for your advice. I tried changing the execution type to validate and execute, but it still is not working. I also get nothing in the circle between the 2 snaps either.

[dmiller]

@rmartyn Oracle Execute is not connected to CSV Formatter. Open the Oracle Execute Snap, go to the Views tab and add an output view. Save the Snap then make sure the circle between the two Snaps is shaded in.

[rmartyn]

Thank you DMiller!!! That was the piece I was missing!