SQL Developer variable table name works but DB Weaver does not

[sfintz]

System information:

• Operating system (distribution) and version: Windows
• DBeaver version: Version 21.3.2.202112271055, Dec 27, 2021 release
• Additional extensions

Connection specification:

• Database name and version: Oracle Database Native
• Driver name; standard one
• Do you use tunnels or proxies (SSH, SOCKS, etc)?

Describe the problem you're observing:

SQL Developer variable table name works but DB Weaver does not

Steps to reproduce, if exist:

In SQL Developer the below works with an Oracle Database:

1. define TN_CP = ‘CP_202201’;
2. CREATE TABLE &TN_CP AS ( … );
3. SELECT * FROM &TN_CP ;

I want to do the same in DB Weaver but can not figure it out as #1 gets me the below error: SQL Error [900] [42000]: ORA-00900: invalid SQL statement

I then tried the below with @set and hit a snag at #3:

1. @set TN_CP = ‘CP_202201’;
2. CREATE TABLE ${TN_CP} AS ( … );
3. SELECT * FROM ${TN_CP} ;
   — THIS DOES NOT WORK as I get “SQL Error [903] [42000]: ORA-00903: invalid table name” BUT
   — select * from CP_202201; does work which tells me there is some issue with the table variable being set

Please help.

Include any warning/errors/backtraces from the logs

[Matvey16]

@sfintz Hello. Did you exucute statements 1, 2 and 3 one by one or as a whole script? If 1 and 2 worked, then can you please show me the query that leads to “SQL Error [903] [42000]: ORA-00903: invalid table name”, it can be found in query manager

[sfintz]

Hi @Matvey16 - I ran 1, 2, 3 one by one. In both scenarios, the errors are from the queries in # 3 (I only trimmed the actual table name for data privacy reasons). Please let me know if that helps get to the bottom of this. Thank you! @sfintz

[Matvey16]

@sfintz So the table was created succesfully? I need to see what query was sent to database and led to the error. You can find it in query manager

It will look like this

Also can you please type @echo ${TN_CP} in SQL editor and copy what is in the output?

[sfintz]

Hi @Matvey16 - I mis-wrote earlier as the "SQL Error [900] [42000]: ORA-00900: invalid SQL statement" in scenario #1 came from the SQL statement #1 (define TN_CP = ‘CP_202201’; which when not trimmed is define TN_CP = ‘cube_param_202201’; ). I could not proceed to #2 and #3 because of that. See below for the query manager snippet for that SQL statement #1 and let me know if that helps clarify better. @sfintz

[Matvey16]

@sfintz If I understood you correctly you are trying to create a variable with define command. But it works differently in DBeaver. You need to use @set for variables and also be careful with quotes or semicolon, because they will also be added to a variable string.

If I understood you correctly, your script will be something like this

@set TN_CP = CP_202201
CREATE TABLE ${TN_CP} AS ( ... );
SELECT * FROM ${TN_CP} ;

[sfintz]

Hi @Matvey16 -

I had to tweak what you provided above slightly to the below and it worked. Thank you so much!

@set TN_CP = CP_202201
CREATE TABLE ${TN_CP} AS ( SELECT 1 AS X FROM dual );
SELECT * FROM ${TN_CP} ;

Out of curiosity why is the define function not an option here? I thought that was an Oracle SQL database syntax element but apparently that is not the case.

@sfintz

[p91paul]

@sfintz if you refer to this https://blogs.oracle.com/opal/post/sql*plus-substitution-variables-define-variables-and-parameters-in-sql-queries define is sql plus syntax, so is specific to that oracle tool. That said, there might be other tools supporting that syntax for compatibility with sql plus, but it's not part of the oracle sql dialect.

[sfintz]

got it. thanks @p91paul .

@sfintz

[badger707]

@Matvey16 thanks for sharing example:
@set TN_CP = CP_202201 CREATE TABLE ${TN_CP} AS ( ... ); SELECT * FROM ${TN_CP} ;

I'm trying to create table where table name have current date in it, like "schema.my_table_03_03_2023". Have tried to set date to variable (current_date) and pass to query, however ${TN_CP} is returning NULL.
So question would be why this does not work, any thoughts or workarounds?