check table exists before select to prevent error

Question

Hi! I'm seeking a way to check if a table exists in Oracle within the Alteryx Input tool. If the table exists, I aim to extract its data. Conversely, if it's absent, I intend to generate a field labeled "exists" containing the value 0.

Is this achievable? If not, selecting from a table, like 'xref_teachers,' in the event of its nonexistence would lead to an error. My primary goal is to append the pre-existing data from the table, if available,  with the contents of a spreadsheet into the final result. As you see, this would work if the table already exists. In my workflow, it does not - so i would want to skip it over and not generate an error (i could filter out if a field called exists contains a 0.;

I provided a screenshot to further clarify what I am trying to achieve.

Thanks so much!

CHKFORTABLE.PNG

jrgo · Answer

I've done this with other databases, but not in oracle, but if you're able to query the info_schema table/views (or equivalent in Oracle) have your initial input return a list of all the tables in the target DB.SCHEMA that you can then create logic that would ultimately create a SQL Statement that you can push into a Dynamic Input tool that will query the intended table if it exists or a fallback table if it doesn't.

From doing a quick Google search, it does look like you can query a VIEW to get a table list, but may require specific permissions to do so.