I can't figure this out, I only want to substitute the &&QC_NUM portion, but I get prompted for a &&QC_NUM_Phase1 variable. I thought concating the sub-var with the rest of the string would fix it, but nope.
DECLARE
c INT;
BEGIN
SELECT COUNT(*)
INTO c
FROM user_tables
WHERE table_name = upper('QC_'||&&QC_NUM||'_Phase1');
IF c = 1 THEN
EXECUTE immediate 'drop table QC_'||&&QC_NUM||'_Phase1';
END IF;
END;
/
I am not able to duplicate your error in Oracle 11g (SQL Plus or SQL Developer).
I do receive these errors with you annnoymous block:
Error report - ORA-06550: line 8, column 7: PL/SQL: ORA-00904: "BOB": invalid identifier ORA-06550: line 4, column 3: PL/SQL: SQL Statement ignored ORA-06550: line 11, column 41: PLS-00201: identifier 'BOB' must be declared ORA-06550: line 11, column 5: PL/SQL: Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error.
When I surround your substitution variable with single quotations, then the substition variable is not interpreted as an identifier, but a string.
DECLARE
c INT;
BEGIN
SELECT COUNT(*)
INTO c
FROM user_tables
WHERE table_name = upper('QC_' ||'&&QC_NUM'||'_Phase1');
IF c = 1 THEN
EXECUTE immediate 'drop table QC_'||'&&QC_NUM'||'_Phase1';
END IF;
END;
This executes properly.
select 'asd'||&&var1||'asd' from dual;in oracle sql developer and it works as expected.