PLSQL - "SP2-0552: bind variable not declared", but seems not a colon issue

Question

I try this plsql script from SQL Developer, and it returns SP2-0552: bind variable "MAN_ID" not declared

(I changed values and some names, so I hope there are no typo)

DECLARE
  TYPE FUNCTIONALITIES IS VARRAY(12) of VARCHAR2(10);
  FUNCS  FUNCTIONALITIES := FUNCTIONALITIES('Func1', 'Func2', 'Func3', 'Func4', 'Func5', 'Func6', 'Func7', 'Func8', 'Func9', 'Func10', 'Func11', 'Func12');
  MAN_ID  NUMBER(12);
  SW_PRO_ID  NUMBER(12);
  REL  VARCHAR2(10) := 'Release';
  EXT_B  VARCHAR2(10) := 'ExtensionB';

BEGIN

  select ID into MAN_ID from MANUFACTURERS where MANUFACTURER = 'EE';
  select ID into SW_PRO_ID from SW_PROFILE where MANUFACTURER_ID = :MAN_ID and RELEASE = :REL and EXTENSIONB = :EXT_B;

  FOR i IN 1..FUNC.COUNT LOOP
    EXECUTE IMMEDIATE
      'INSERT INTO SW_PROFILE_FEATURE (ID, SW_PROFILE_ID, FEATURE, CREATION_DATE)
          SELECT SQ_SW_PROFILE_FEATURE_ID.NEXTVAL, '''|| SW_PRO_ID ||''', '''|| FUNCS(i) ||''', SYSDATE FROM DUAL
        WHERE NOT EXISTS (SELECT NULL FROM RMM.SW_PROFILE_FEATURE WHERE SW_PROFILE_ID = '''|| SW_PRO_ID ||''' AND FEATURE = '''|| FUNCS(i) ||''')';

    EXECUTE IMMEDIATE
      'UPDATE SW_PROFILE_FEATURE SET SW_PROFILE_ID = '''|| SW_PRO_ID ||''', FEATURE = '''|| FUNCS(i) ||''', CREATION_DATE = SYSDATE
        WHERE ROWID IN (SELECT ROWID FROM SW_PROFILE_FEATURE WHERE SW_PROFILE_ID = '''|| SW_PRO_ID ||''' AND FEATURE = '''|| FUNCS(i) ||''')';

  END LOOP;

  COMMIT COMMENT 'Inserted new SW_PROFILE_FEATURE records';

EXCEPTION
WHEN OTHERS THEN
  IF SQLCODE != -1 THEN RAISE; END IF;
END;
/

I have to do a series of 12 inserts, which differ by the value of FEATURE field which cointains functionalities names. For some reasons, some of them could already be into the table, so in that case I have to update them, to be sure they'll be as expected at the end.

So I thought to put all 12 functionalities into a varray and then loop them with an "insert where not exists" and an "update where" matching primary keys.

I tried to look for similar issues over the internet, but most of people's issue was just putting a colon before the variable into the "select into" statement. That seems not my case.

(If you find also other issues with the script, please tell me)

Answer 1

:MAN_ID would be an externally defined bind variable. MAN_ID is a locally defined PL/SQL variable.

If you want to use the local variable then do not prefix it with :.

However, for EXECUTE IMMEDIATE statements you should wherever possible use bind variables and then provide them with a USING clause to pass the values from the PL/SQL scope to the SQL scope of the EXECUTE IMMEDIATE statement (and prevent SQL injection vulnerabilities):

DECLARE
  TYPE FUNCTIONALITIES IS VARRAY(12) of VARCHAR2(10);
  FUNCS  FUNCTIONALITIES := FUNCTIONALITIES('Func1', 'Func2', 'Func3', 'Func4', 'Func5', 'Func6', 'Func7', 'Func8', 'Func9', 'Func10', 'Func11', 'Func12');
  MAN_ID  NUMBER(12);
  SW_PRO_ID  NUMBER(12);
  REL  VARCHAR2(10) := 'Release';
  EXT_B  VARCHAR2(10) := 'ExtensionB';
BEGIN
  select ID
  into   MAN_ID
  from   MANUFACTURERS
  where  MANUFACTURER = 'EE';

  select ID
  into   SW_PRO_ID
  from   SW_PROFILE
  where  MANUFACTURER_ID = MAN_ID
  and    RELEASE         = REL
  and    EXTENSIONB      = EXT_B;

  FOR i IN 1..FUNC.COUNT LOOP
    EXECUTE IMMEDIATE
      'INSERT INTO SW_PROFILE_FEATURE (ID, SW_LVC_PROFILE_ID, FEATURE, CREATION_DATE)
          SELECT SQ_SW_PROFILE_FEATURE_ID.NEXTVAL, :SW_PRO_ID, :func, SYSDATE FROM DUAL
        WHERE NOT EXISTS (SELECT NULL FROM RMM.SW_PROFILE_FEATURE WHERE SW_PROFILE_ID = :SW_PRO_ID AND FEATURE = :func)'
      USING SW_PRO_ID, FUNCS(i), SW_PRO_ID, FUNCS(i);

    EXECUTE IMMEDIATE
      'UPDATE SW_PROFILE_FEATURE SET SW_LVC_PROFILE_ID = :SW_PRO_ID, FEATURE = :func, CREATION_DATE = SYSDATE
        WHERE ROWID IN (SELECT ROWID FROM SW_PROFILE_FEATURE WHERE SW_PROFILE_ID = :SW_PRO_ID AND FEATURE = :func)'
      USING SW_PRO_ID, FUNCS(i), SW_PRO_ID, FUNCS(i);

  END LOOP;

  COMMIT;
  -- Inserted new SW_PROFILE_FEATURE records;

EXCEPTION
WHEN OTHERS THEN
  IF SQLCODE != -1 THEN RAISE; END IF;
END;
/

You probably don't need to use EXECUTE IMMEDIATE and can do it all in a single MERGE statement:

MERGE INTO SW_PROFILE_FEATURE dst
USING (
  WITH functionalities (func) AS (
    SELECT 'Func1' FROM DUAL UNION ALL
    SELECT 'Func2' FROM DUAL UNION ALL
    SELECT 'Func3' FROM DUAL UNION ALL
    SELECT 'Func4' FROM DUAL UNION ALL
    SELECT 'Func5' FROM DUAL UNION ALL
    SELECT 'Func6' FROM DUAL UNION ALL
    SELECT 'Func7' FROM DUAL UNION ALL
    SELECT 'Func8' FROM DUAL UNION ALL
    SELECT 'Func9' FROM DUAL UNION ALL
    SELECT 'Func10' FROM DUAL UNION ALL
    SELECT 'Func11' FROM DUAL UNION ALL
    SELECT 'Func12' FROM DUAL
  )
  SELECT s.id,
         f.func
  FROM   manufacturers m
         INNER JOIN sw_profile s
         ON MANUFACTURER_ID = MAN_ID
         CROSS JOIN functionalities f
  WHERE  s.release      = 'Release'
  AND    s.extensionb   = 'ExtensionB'
  AND    m.manufacturer = 'EE'
) src
ON (dst.sw_profile_id = src.id AND dst.feature = src.func)
WHEN NOT MATCHED THEN
  INSERT (ID, SW_LVC_PROFILE_ID, FEATURE, CREATION_DATE)
  VALUES (SQ_SW_PROFILE_FEATURE_ID.NEXTVAL, src.id, src.func, SYSDATE)
WHEN MATCHED THEN
  UPDATE
  SET SW_LVC_PROFILE_ID = src.id,
      CREATION_DATE     = SYSDATE;