"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.

Lalit Kumar B · Accepted Answer · 2015-11-16T06:50:37.680

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

Hey Lalit - you definitely can have placeholders for names. I'm writing a NodeJS application and I am using a prepared statement. The typo was a result of me not copy-pasting my code - I just wrote it from the screen for some reason. — Dave Cooper, Nov 16 '15 at 06:24
I might just quickly note that I'm trying to execute this from a NodeJS application using the node-oracledb driver. — Dave Cooper, Nov 16 '15 at 06:33
@DaveCooper Updated the answer with a complete working test case. You cannot use placeholders as bind variables for database objects in SQL. You can use them only for literals. — Lalit Kumar B, Nov 16 '15 at 06:50
Unfortunately I can't as it hasn't actually answered my question. This would be misleading to others trying to solve the same problem as me. — Dave Cooper, Nov 16 '15 at 08:02
@DaveCooper You mean you want to do it in pure SQL by passing database link name dynamically? That's not possible, you will have to prepare the sql as a string and then make sure the string has all the values correctly formed at run time. Could you post your prepared statement? — Lalit Kumar B, Nov 16 '15 at 08:20

score 0 · Answer 2 · answered Nov 16 '15 at 23:23

0

Managed to sort this one out. Turns out it was just my misunderstanding of how variable binding works. I thought I could bind to anything I wanted to in my query. Turns out you can't :P

I modified my query to look like this:

SELECT OBJECT_NAME
FROM ALL_OBJECTS@<HARDCODED DB NAME HERE>
WHERE OWNER = :schema
AND (
    OBJECT_TYPE = 'TABLE'
    OR OBJECT_TYPE = 'VIEW'
    OR OBJECT_TYPE = 'SYNONYM'
)
ORDER BY OBJECT_NAME;

And it all worked perfectly!

answered Nov 16 '15 at 23:23

Dave Cooper

10,494
4
30
50

Maybe I'm misunderstanding something but isn't that exactly what was suggested in the answer? Your query and the very last query in the answer looks the same. – Ola Ekdahl Nov 16 '15 at 23:40
Sort of - but it's definitely not directly addressing the question. There is no need for the (ab)use of "execute immediate" or anything like that. I personally think the answer, whilst informative, is a bit too convoluted. If his answer is clarified/tidied up, I'll mark it as the accepted answer. – Dave Cooper Nov 16 '15 at 23:44

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

2 Answers2