Oracle PL/SQL to Databricks

[Ashay4u]

Does sqlglot support Oracle PL/SQL to databricks conversion as part of its Oracle dialect? If not, can we add that support?

[georgesittas]

SQLGlot already supports both Oracle SQL and Databricks, so there's definitely some coverage for transpilation between them.

You can give it a try and let us know if you encounter any issues with transpiling between them, or open a PR to implement missing functionality yourself. It's not currently in scope to improve the transpilation ourselves if there are no known issues.

[georgesittas]

This is out of scope for us, but yea you could use sqlglot's infrastructure to implement such transformations in your application.

[Ashay4u]

HI @georgesittas
Thanks for the response. I understand this use case is out of scope for you, but it would be really helpful is you can guide me as to how I can leverage the existing sqlglot infrastructure to handle the use case. A basic layout would also help. Like should I create a new Dialect, which classes should i inherit (like parser), how can i break the statements (currently Semicolon token is being used to identify end of query) and as such. Anything from your experience would be extremely valuable for me.

[georgesittas]

I'm not sure about what's exactly needed to fix this without digging deeper myself, but from your example it seems like there are a few items here. At a high level, you'll need to:

• Map DECLARE statements into SET var.name = ... statements. This might not be trivial, as you need to first implement parsing logic for the DECLARE statements that we don't currently have, in order to have a fine-grained AST to use for this mapping.
• Convert SET statements like SET @total_qty = (...) into the equivalent Databricks-compatible ones (e.g. need to change total_qty into var.total_qty I guess).
• Replace references to @vars into $vars or ${var.name}.
• Map the CREATE PROCEDURE statement into the CREATE WIDGET one (not very familiar with this).

[Ashay4u]

Thanks. I observed that the current code for _parse_create uses a reference as if create_token.token_type in (TokenType.FUNCTION, TokenType.PROCEDURE):
Does this in a way support create procedure as a valid statement? If yes, what should be used as query ending parameter for the create procedure statement?
Sorry for throwing more questions even when this isnt in your current scope, but I really like sqlglot and want to use it in my favor for this conversion

[georgesittas]

Does this in a way support create procedure as a valid statement

Yeah, take a look at _parse_user_defined_function.

what should be used as query ending parameter for the create procedure statement?

I'm not sure what you mean here, could you elaborate (maybe an example could help)?