"SQL command not properly ended" error message when querying for table names in my Node application

Question

I'm getting the following error when executing the following query with node-oracledb:

SELECT OBJECT_NAME
FROM ALL_OBJECTS || '@' || :db
WHERE OWNER = :schema
    AND (
        OBJECT_TYPE = ''TABLE''
        OR OBJECT_TYPE = ''VIEW''
        OR OBJECT_TYPE = ''SYNONYM''
    )
ORDER BY OBJECT_NAME

If I execute this query in an Oracle console (substituting the placeholders for actual values, of course), it executes fine. However, when I execute this in my Node application, I get the following error:

"ORA-00933: SQL command not properly ended"

Is anyone able to assist with why I'm getting this error? I can confirm that my placeholders are definitely populating with the values I intended them to be populated with.

Thanks!

edit:

Even if I try do:

SELECT OBJECT_NAME
FROM ALL_OBJECTS || @ || :db

I still get the same error.

Answer 1

SELECT OBJECT_NAME
FROM ALL_OBJECTS || '@' || :db
WHERE OWNER = :schema
    AND (
        OBJECT_TYPE = 'TABLE'
        OR OBJECT TYPE = 'VIEW'
        OR OBJECT_TYPE = 'SYNONYM'
    )
ORDER BY OBJECT_NAME

• Firstly, there is a typo error in your query. You have a missing underscore in OBJECT TYPE OR OBJECT TYPE = 'VIEW'.

• You cannot execute a SQL with dynamic object names. SQL must have static object names. DATABASE LINK is a database object, you must provide the static name at run time. You can only provide placeholders as bind variables for literals.

If you want to make it dynamic, then you need to (ab)use EXECUTE IMMEDIATE in PL/SQL. You need to prepare the string dynamically and then execute it.

For example, In SQL*Plus:

var db varchar2(30);
var schema varchar2(30);
exec :db := 'database_name'
exec :schema := 'OWNER'

SET serveroutput ON
DECLARE
  v_sql         VARCHAR2(2000);
  v_object_name VARCHAR2(30);
BEGIN
  v_sql:= 'SELECT OBJECT_NAME
FROM ALL_OBJECTS@'||:db||' WHERE OWNER = :schema     
AND (        
OBJECT_TYPE = ''TABLE''
OR OBJECT_TYPE = ''VIEW''        
OR OBJECT_TYPE = ''SYNONYM''
)
ORDER BY OBJECT_NAME';
  dbms_output.put_line(v_sql);
  execute immediate v_sql into v_object_name using :db, :schema;
END;
/

Or, you can have DATABASE LINK as a static name:

First I create the database link:

SQL> CREATE DATABASE LINK TEST
  2    CONNECT TO SCOTT IDENTIFIED BY tiger USING 'pdborcl';

Database link created.

Added the following entry in tnsnames.ora file:

test =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL=TCP)(HOST=ocalhost)(PORT=1521))
    (CONNECT_DATA = 
      (SERVER=DEDICATED)
      (SERVICE_NAME=pdborcl.in.oracle.com)
    )
  )

Let's execute in SQL*Plus:

SQL> var schema varchar2(30);
SQL> exec :schema := 'SCOTT'

PL/SQL procedure successfully completed.

SQL> SELECT OBJECT_NAME
  2  FROM ALL_OBJECTS@test
  3  WHERE OWNER = :schema
  4      AND (
  5          OBJECT_TYPE = 'TABLE'
  6          OR OBJECT_TYPE = 'VIEW'
  7          OR OBJECT_TYPE = 'SYNONYM'
  8      )
  9  ORDER BY OBJECT_NAME
 10  /

OBJECT_NAME
------------------------------------------------------------
BONUS
DEPT
EMP
EMP_VIEW
SALGRADE