SQLcl lb Using Changeset Execution Fails with '&' Used in Code Errors "Substitution Cancelled" the Changeset Still Marked as Ran in DB Changelog Table (Doc ID 3017119.1)

Last updated on OCTOBER 19, 2024

Applies to:

Symptoms

On : 23.4 version, SQLcl/SQL*Plus Commands

ACTUAL BEHAVIOR
---------------

If a PLSQL object such as a PROCEDURE contains an '&' , the code will not be added to the target schema using lb update. Warning "Substitution cancelled" will be shown instead and object will not be created.

This problem can easily be reproduced using a test case based on the SCOTT schema:

**STEP 1: In source database create the SCOTT schema:

@?/rdbms/admin/utlsampl.sql

**STEP 2: Connect to the source database as SCOTT and add a sample PROCEDURE:

SET define OFF
CREATE OR REPLACE PROCEDURE demo_proc AS
BEGIN
  DBMS_OUTPUT.put_line('Test?one=two&three');
END;
/

**STEP 3: Using SQLcl 23.4 lb generate the schema:

liquibase generate-schema

**STEP 4: On target database create the SCOTT schema without any objects:

GRANT connect,resource,unlimited tablespace TO scott IDENTIFIED BY tiger;

**STEP 5: On target database, connect as SCOTT and import schema with lb update:

SET define OFF
liquibase update -changelog-file controller.xml

----

Output excerpt:

"Running Changeset: demo_proc_procedure.xml::2303d7f1c42aa6f714a48557d62b3903bdac94cb::(SCOTT)-Generated
Substitution cancelled"

This is the issue - the procedure DEMO_PROC is not imported and added to the target database!

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.