Dynamically generated SQL query in Retool

App Building
1

  • Goal: Hi guys. I would like to run .sql query in Retool.
    Can I somehow successfully perform next steps without any error messages?

  • Steps:
    I've created the variable 'dynamicSqlQuery' and have set initial value "select '111' from dual"
    I would like successfully run .sql but this code doesn't work:

.sql file:
"{{dynamicSqlQuery.value}}"

  • Details:
    When I run the query, I encounter the following behavior: [ORA-01036: illegal variable name/number"]
    Could you please provide guidance on how to correctly execute a dynamically generated SQL query in Retool? Are there best practices or specific methods to follow to ensure dynamic SQL queries are executed correctly without encountering binding or parsing issues?

  • Screenshots:
    image

    image
    image496×713 35.2 KB

  • App json export:

2

Try doing this as a JavaScript query that returns the statement, declare the statement in your SQL query and then EXEC(theStatement).

jsQuery:
return "select '111' from dual"
SQL:

DECLARE @statement NVARCHAR(max)
SET @statement = {{jsQuery.data}}
EXEC(@statement)

I see that you are trying to access a Oracle DB, so you might need to use a different declaration/execute syntax but I've been able to create dynamic queries (sanitized in the query as best as possible).

We have made some pretty interesting queries this way to be able to query through OPENQUERY to an Oracle linked server.

1 Like