Forum Discussion

rmartyn's avatar
rmartyn
New Contributor II
5 years ago

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

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

  • JensDeveloper's avatar
    JensDeveloper
    Contributor II
    4 years ago

    Hi @amit.saroha ,

    You mean you want those files out? Then I can suggest you check the checkbox ‘ignore empty stream’.
    Because it will write those files to the error view (both an empty binary stream and a binary stream with CSV headers only).

    You can find the explanation also in the documentation here.

    Regards

    Jens

    • amit_saroha's avatar
      amit_saroha
      New Contributor III
      4 years ago

      I don’t want to error out the pipeline actually.

      • JensDeveloper's avatar
        JensDeveloper
        Contributor II
        4 years ago

        It doesn’t go out of the pipeline if you don’t want to.

        Example below: the csv parser his error data is set to ‘route error data to error view’. Then a error output will be showed above the parser after that you can define what to do with the error map the right fields and sent an e-mail for example.

        If this solution doesn’t help you there is an option 2:

        • Put a router snap after the csv parser and check if one field of the headers does contain data or not and then route it to an output.

        Option 3:
        If you don’t want those empty files use a filter snap with a condition that a field is not null and then you only get the filled snaps.

        Regards

        Jens

  • amit_saroha's avatar
    amit_saroha
    New Contributor III
    4 years ago

    @JensDeveloper - Thank you, but if I do as you suggested it will create an issue with the insert statement, right? How I can pass “S” in the last mapper in both cases when I have data inserted or not inserted.

    • JensDeveloper's avatar
      JensDeveloper
      Contributor II
      4 years ago

      Why should it create an issue because then you only have the files with filed data to insert right ?
      I can’t follow what your saying. You mean Succes after the last oracle insert? Or what do you mean?