'how to drop a table that belong to a schema user in sys using substitution Oracle sql

I tried to drop a table tableA owned by schema tom in sys.

I was able to achieve that with the statement

drop table tom.tableA

Not sure how to use substitution. Not working with the following statements.

define schema='tom'
drop table &schema.tableA
sqloracleoracle-sqldeveloperdrop


Solution 1:[1]

From the "Using Substitution Variables" documentation:

If you want to append a period immediately after a substitution variable name, then use two periods together. For example, if "myfile" is defined as "reports" then the command:

SQL> spool &myfile..log

is the same as:

SQL> spool reports.log

In your case, you need two periods and also to remove the string literal quotes around the schema name:

DEFINE schema=tom
DROP TABLE &schema..tableA

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 MT0

Related Questions