Escape character issue in sql

March 21, 2024

I want to execute an sql query to get the count() for multiple tables:

select 'BIN$41g1MKfbpeXgUyIEFawYRA==$0' from BIN441g1MKfbpeXgUyIEFawYRA==$0 having count(*)=0 union all

There’s similar lines after the above code.

But i am getting an error on the aforementioned line saying SQL command not properly ended

I am new to sql, so don’t have much idea but i am guessing it because of the table name containing = and $ symbols.

I tried out escape character solution but it didn’t work.

If anyone knows the solution, please help me resolve it. Thanks.

>Solution :

Are you sure you want to select data from tables that are dropped (and are now located in recycle bin)?

If so, you’ll have to enclose names into double quotes:

SQL> select * From BIN$DqnCFhm3MWLgY8lkAQog8w==$0;
select * From BIN$DqnCFhm3MWLgY8lkAQog8w==$0
                                        *
ERROR at line 1:
ORA-00933: SQL command not properly ended


SQL> select * From "BIN$DqnCFhm3MWLgY8lkAQog8w==$0";   --> double quotes

S_CODE
--------------------
270ZVLPIZ8
84DRTT6P73
GUK65MT9WA

SQL>

Perhaps you’d rather restore table(s) from recycle bin? Here’s how:

SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
AUDIT_LOG        BIN$DzNA6BcQDTHgY8lkAQpYBQ==$0 TABLE        2024-01-18:07:13:29
DELETE_LOG       BIN$CkOlTt0ZNMvgY8lkAQpyug==$0 TABLE        2023-11-16:11:40:45
<snip>

SQL> flashback table AUDIT_LOG to before drop;

Flashback complete.

SQL> select * From audit_log where rownum = 1;

no rows selected

SQL>