CODEWITHSUNDEEP
sqloracle-databasevariablessubstitution
stephen meckstroth
stephen meckstroth

Reputation: 345

oracle sql substitution variable for table prefix

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;
/

Upvotes: 0

Views: 778

Answers (1)

Patrick Bacon
Patrick Bacon

Reputation: 4660

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.

Upvotes: 1

Related Questions