CODEWITHSUNDEEP
oracle-databaseplsqloracle-sqldeveloper
Steve
Steve

Reputation: 543

Defining variable for repeated use in PL/SQL anonymous blocks

I am using Oracle SQL Developer.

I have an anonymous block that drops a table if it exists.

I don't have permission to create procedures or functions, so I have to call the anonymous block repeatedly.

To simplify things, I would like to store the names of all the affected tables in variables at the beginning of my script, and then refer to the appropriate variables later. 

DEFINE v_InputTable = 'Table Name';

DECLARE
 InputTable VARCHAR2(80) := &v_InputTable;
BEGIN
 EXECUTE IMMEDIATE 'DROP TABLE ' || InputTable;
EXCEPTION
 WHEN OTHERS THEN
    IF SQLCODE != -942 THEN
       RAISE;
    END IF;
END;
/

When I try this, I get an error "PLS-00103: Encountered the symbol "TABLE" when expecting one of the following:..."

Can someone please explain what I am doing wrong.

Upvotes: 0

Views: 1341

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

Substitution variables are replaced in a fairly simplistic way. You have to enclose your references to strings within single quotes, where they become a literal. Instead of what you have, that becomes:

DECLARE
 InputTable VARCHAR2(80) := '&v_InputTable';

You don't really need the PL/SQL variable here, you can use the substitution variable directly in the dynamic statement (still within a quoted string):

DEFINE v_InputTable = 'Table Name';

BEGIN
 EXECUTE IMMEDIATE 'DROP TABLE &v_InputTable';
EXCEPTION
 WHEN OTHERS THEN
    IF SQLCODE != -942 THEN
       RAISE;
    END IF;
END;
/

You can also define a bind variable with the variable command, rather than define, but for this usage a substitution is probably simpler.

You could run that drop comand statically as plain SQL of course, but I guess you want to hide the table-not-found error.

The error suggests your defined value is actually literally 'Table Name', which is an invalid name anyway unless you treat it as a quoted identifier - and it's really better not to do that. But if you must, or are playing around to see how they work, remember the case is fixed too, and every reference to it has to be identical and quoted. If you are trying to drop (and then recreate?) a table with a space in the name, you'd need to wrap the value in double quotes:

BEGIN
 EXECUTE IMMEDIATE 'DROP TABLE "&v_InputTable"';

... or if you want a separate variable and concatenate the dynamic statement:

DECLARE
 InputTable VARCHAR2(80) := '"&v_InputTable"';

Upvotes: 1

Related Questions