cancel
Showing results for 
Search instead for 
Did you mean: 

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

rmartyn
New Contributor II

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.

7 REPLIES 7

vineesha
Employee
Employee

Hi,

i’m attaching the screenshot of a simple pipeline using oracle execute and csv formatter.Screen Shot 2021-01-19 at 11.52.55 AM
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 snapScreen Shot 2021-01-19 at 11.53.01 AM
Screen Shot 2021-01-19 at 11.53.13 AM
let me know if you still have issues.

rmartyn
New Contributor II

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.

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

SusanB
New Contributor II

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.