Skip to Main Content
  • Questions
  • Gen Objects DDL Statement Will Change using DDL command using SQLcl tool

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Allan.

Asked: September 13, 2017 - 3:22 am UTC

Last updated: September 12, 2022 - 3:03 am UTC

Version: Oracle 12c Standard

Viewed 1000+ times

You Asked

Hi Tom

I hit a problem, when gen DDL statement using DDL command in sqlcl tools. Follow with my environment:

(1) Redhat
(2) Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
(3) SQLcl: Release 4.2.0.17.097.0719 Production

In the database schema , it have a view and it have 2 trigger depend on it. When I use 'DDL XXX VIEW' command, it will gen the VIEW and it's dependence triggers DDL statement. It issue is the trigger DDL gen order will be difference, some times is :
VIEW XXX DDL -> TRIGGER A DDL -> TRIGGER B DDL.
VIEW XXX DDL -> TRIGGER B DDL -> TRIGGER A DDL.

Because we have a batch job to gen all objects DDL and compare have any changes everyday. This issue cause incorrect compare result.

Have any setting to fixed objects DDL statement gen order OR disable gen dependents objects










and Connor said...

I've had a look and the order does not appear to be deterministic.

I've asked the Product Manager for SQLcl to take a look.

I'll update this answer when I get more information.




Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

set DDL command is not working fine in SQLCL

Rajeshwaran, Jeyabal, September 05, 2022 - 6:27 am UTC

Team,

the set ddl command is not working fine to me, let me know if i got missing something here?

demo@PDB1> ddl emp

  CREATE TABLE "DEMO"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TS_DATA" ;
demo@PDB1> set ddl storage off
DDL Option STORAGE OFF
demo@PDB1> ddl emp

  CREATE TABLE "DEMO"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TS_DATA" ;
demo@PDB1> set ddl tablespace off
DDL Option TABLESPACE OFF
demo@PDB1> ddl emp

  CREATE TABLE "DEMO"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TS_DATA" ;
demo@PDB1> version
Oracle SQLDeveloper Command-Line (SQLcl) version: 22.2.1.0 build: 22.2.1.201.1456
demo@PDB1>


but when i do dbms_metadata.set_transform_param - it works fine

demo@PDB1> exec dbms_metadata.set_transform_param( dbms_metadata.SESSION_TRANSFORM,'STORAGE',false );

PL/SQL procedure successfully completed.

demo@PDB1> exec dbms_metadata.set_transform_param( dbms_metadata.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',false );

PL/SQL procedure successfully completed.

demo@PDB1> ddl emp

  CREATE TABLE "DEMO"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0)
   ) ;
demo@PDB1>

Connor McDonald
September 06, 2022 - 1:27 am UTC

Looks like a 22.2 regression - I'll log a bug

Liquibase and SQLCL

Rajeshwaran, Jeyabal, September 06, 2022 - 4:59 am UTC

Thanks for helping us on that.

Here is another one from using Liquibase with the SQLCL (22.2) version connecting to an Oracle database 21c (21.3)

got two schema A (source) and B (target)

on the source schema did this
a@PDB1> create table t1(x number);
 
Table T1 created.
 
a@PDB1> create table t2(x number, y date);
 
Table T2 created.
 
a@PDB1>
a@PDB1> create or replace procedure demo_call
 2 as
 3 l_cnt number;
 4 begin
 5 select count(*) into l_cnt from t2;
 6 dbms_output.put_line('Table t2 got '||l_cnt||' rows..');
 7 end;
 8* /
 
Procedure DEMO_CALL compiled
 
a@PDB1> lb genobject -type PROCEDURE -name DEMO_CALL -label run3
--Starting Liquibase at 15:12:20 (version 4.9.1 #0 built at 2022-05-03 17:23+2221)
 
Action successfully completed please review created file demo_call_procedure4.xml
a@PDB1>

However on the target database, when we executed the "UPDATESQL" command to verify the sql, saw this.
(see the below highlighted "<========" for details about "statement terminator", instead of "/", it was like this "/;" )
and due to that, when we run the "UPDATE" command from Liquibase it errored like below.
b@PDB1> lb updatesql -changelog demo_call_procedure4.xml -label run3
--Starting Liquibase at 15:12:42 (version 4.9.1 #0 built at 2022-05-03 17:23+2221)
 
-- Loaded 1 changeSets
-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: demo_call_procedure4.xml
-- Ran at: 05/09/22, 3:12 pm
-- Against: B@jdbc:oracle:thin:@pdb1
-- Liquibase version: 4.9.1
-- *********************************************************************
 
-- Lock Database
UPDATE B.DATABASECHANGELOGLOCK SET LOCKED = 1, LOCKEDBY = 'rajeyaba-3WH3DK3 (192.168.0.113)', LOCKGRANTED = SYSTIMESTAMP WHERE ID = 1 AND LOCKED = 0;
 
-- Changeset demo_call_procedure4.xml::460edfe7a31038fced8598504f73205153c31767::(A)-Generated
CREATE OR REPLACE EDITIONABLE PROCEDURE "DEMO_CALL"
as
l_cnt number;
begin
select count(*) into l_cnt from t2;
dbms_output.put_line('Table t2 got '||l_cnt||' rows..');
end;
/; "<========"
 
-- Logging Oracle Liquibase extension actions to
 DECLARE
 id varchar2(200) := '460edfe7a31038fced8598504f73205153c31767';
 rawAction clob;
 rawSxml clob;
 myrow varchar2(2000);
 action clob := '';
 sxml clob := '';
 dep varchar2(200) := '2370962643';
 author varchar2(200) := '(A)-Generated';
 filename varchar2(200) := 'demo_call_procedure4.xml';
 insertlog varchar2(200) := 'insert into DATABASECHANGELOG_ACTIONS (id,author,filename,sql,sxml,deployment_id) values (:id,:author,:filename,:action,:sxmowid into :out';
 updateaction varchar2(200) := 'update DATABASECHANGELOG_ACTIONS set sql = sql ||:action where rowid = :myrow ';
 updatesxml varchar2(200) := 'update DATABASECHANGELOG_ACTIONS set sxml = sxml ||:sxml where rowid = :myrow ';
 begin
action := utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(q'{Q1JFQVRFIE9SIFJFUExBQ0UgRURJVElPTkFCTEUgUFJPQ0VEVVJFICJERU1PX0NBTEwiIAXI7CmJlZ2luIApzZWxlY3QgY291bnQoKikgaW50byBsX2NudCBmcm9tIHQyOwpkYm1zX291dHB1dC5wdXRfbGluZSgnVGFibGUgdDIgZ290ICd8fGxfY250fHwnIHJvd3MuLicpOwplbmQ7Ci87}')));
 execute immediate insertlog using id,author,filename,action,sxml,dep returning into myrow;
end;
/       --;
 
INSERT INTO B.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE,UES ('460edfe7a31038fced8598504f73205153c31767', '(A)-Generated', 'demo_call_procedure4.xml', SYSTIMESTAMP, 7, '8:c75712424320df4ea22a223b8e6d4092', 'creobjectName=DEMO_CALL, ownerName=A', '', 'EXECUTED', NULL, 'run3', '4.9.1', '2370962643');
 
-- Release Database Lock
UPDATE B.DATABASECHANGELOGLOCK SET LOCKED = 0, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1;
 
 
b@PDB1>

Liquibase update command ended up with error.
b@PDB1> lb update -changelog demo_call_procedure4.xml -label run3
--Starting Liquibase at 15:17:49 (version 4.9.1 #0 built at 2022-05-03 17:23+2221)
 
-- Loaded 1 changeSets
Running Changeset: demo_call_procedure4.xml::460edfe7a31038fced8598504f73205153c31767::(A)-Generated
Procedure DEMO_CALL compiled
 
LINE/COL ERROR
--------- -------------------------------------------------------------
8/1      PLS-00103: Encountered the symbol "/"
Errors: check compiler log
 
Action logged sucessfully.
Rolling back changeset.
Procedure "DEMO_CALL" dropped.
Action logged sucessfully.
 
######## ERROR SUMMARY ##################
Errors encountered:1
 
----------------------------
demo_call_procedure4.xml
---------
Procedure DEMO_CALL compiled
 
LINE/COL ERROR
--------- -------------------------------------------------------------
8/1      PLS-00103: Encountered the symbol "/"
Errors: check compiler log
 
----------------------------
 
 
b@PDB1>

the above scrips were run from SQLCL latest version (22.2) - connecting to an Oracle 21c (21.3) database
b@PDB1> version
Oracle SQLDeveloper Command-Line (SQLcl) version: 22.2.1.0 build: 22.2.1.201.1456
b@PDB1>

Liquibase and SQLCL

Rajeshwaran, Jeyabal, September 07, 2022 - 5:07 am UTC

Team,

Please ignore my above request, reached Oracle support and they confirmed it as a bug.

Here is the bug details and the workaround from Oracle support on this.
Its a bug and already tracked by SQLcl Development

Bug 34449082 : SECOND LB GENSCHEMA COMMAND IN SINGLE SQLCL SESSION BREAKS XML FILE & ADDS /

As a workaround do a genschema without -split.


Just verified the workaround and it works in the above case. Thanks.

a@PDB1> lb genschema -sql -label run3
--Starting Liquibase at 10:30:03 (version 4.9.1 #0 built at 2022-05-03 17:23+2221)


Export Flags Used:

Export Grants           false
Export Synonyms         false

[Method loadCaptureTable]:
        [Type - TYPE_SPEC]:                        163 ms
        [Type - TYPE_BODY]:                         56 ms
        [Type - SEQUENCE]:                          16 ms
        [Type - DIRECTORY]:                         11 ms
        [Type - CLUSTER]:                           19 ms
        [Type - TABLE]:                            380 ms
        [Type - MATERIALIZED_VIEW_LOG]:             13 ms
        [Type - MATERIALIZED_VIEW]:                  6 ms
        [Type - VIEW]:                              13 ms
        [Type - DIMENSION]:                         14 ms
        [Type - FUNCTION]:                          19 ms
        [Type - PROCEDURE]:                         96 ms
        [Type - PACKAGE_SPEC]:                      28 ms
        [Type - DB_LINK]:                           11 ms
        [Type - SYNONYM]:                           23 ms
        [Type - INDEX]:                             24 ms
        [Type - TRIGGER]:                           14 ms
        [Type - PACKAGE_BODY]:                      35 ms
        [Type - JOB]:                               14 ms

[Method loadCaptureTable]:                         955 ms
[Method processCaptureTable]:                      449 ms
[Method sortCaptureTable]:                          21 ms
[Method cleanupCaptureTable]:                        6 ms
[Method writeChangeLogs]:                          242 ms

a@PDB1>

b@PDB1> lb updatesql -changelog controller.xml  -label run3
--Starting Liquibase at 10:30:15 (version 4.9.1 #0 built at 2022-05-03 17:23+2221)

-- Loaded 2 changeSets
-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: controller.xml
-- Ran at: 07/09/22, 10:30 am
-- Against: B@jdbc:oracle:thin:@pdb1
-- Liquibase version: 4.9.1
-- *********************************************************************

-- Lock Database
UPDATE B.DATABASECHANGELOGLOCK SET LOCKED = 1, LOCKEDBY = 'rajeyaba-3WH3DK3 (192.168.0.113)', LOCKGRANTED = SYSTIMESTAMP WHERE ID = 1 AND LOCKED = 0;

-- Changeset t1_table.xml::4361fb8b8b996a029b63ec0d36060914614fa62a::(A)-Generated
-- object is the same nothing to do;

-- Logging Oracle Liquibase extension actions to
 DECLARE
 id varchar2(200) := '4361fb8b8b996a029b63ec0d36060914614fa62a';
 rawAction clob;
 rawSxml clob;
 myrow varchar2(2000);
 action clob := '';
 sxml clob := '';
 dep varchar2(200) := '2526815837';
 author varchar2(200) := '(A)-Generated';
 filename varchar2(200) := 't1_table.xml';
 insertlog varchar2(200) := 'insert into DATABASECHANGELOG_ACTIONS (id,author,filename,sql,sxml,deployment_id) values (:id,:author,:filename,:action,:sxml,:dep) returning rowid into :out';
 updateaction varchar2(200) := 'update DATABASECHANGELOG_ACTIONS set sql = sql ||:action where rowid = :myrow ';
 updatesxml varchar2(200) := 'update DATABASECHANGELOG_ACTIONS set sxml = sxml ||:sxml where rowid = :myrow ';
 begin
action := utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(q'{LS0gb2JqZWN0IGlzIHRoZSBzYW1lIG5vdGhpbmcgdG8gZG8=}')));
sxml := utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(q'{CiAgPFRBQkxFIHhtbG5zPSJodHRwOi8veG1sbnMub3JhY2xlLmNvbS9rdSIgdmVyc2lvbj0iMS4wIj4KICAgPFNDSEVNQT5CPC9TQ0hFTUE+CiAgIDxOQU1FPlQxPC9OQU1FPgogICA8UkVMQVRJT05BTF9UQUJMRT4KICAgICAgPENPTF9MSVNUPgogICAgICAgICA8Q09MX0xJU1RfSVRFTT4KICAgICAgICAgICAgPE5BTUU+WDwvTkFNRT4KICAgICAgICAgICAgPERBVEFUWVBFPk5VTUJFUjwvREFUQVRZUEU+CiAgICAgICAgIDwvQ09MX0xJU1RfSVRFTT4KICAgICAgICAgPENPTF9MSVNUX0lURU0+CiAgICAgICAgICAgIDxOQU1FPlk8L05BTUU+CiAgICAgICAgICAgIDxEQVRBVFlQRT5EQVRFPC9EQVRBVFlQRT4KICAgICAgICAgPC9DT0xfTElTVF9JVEVNPgogICAgICA8L0NPTF9MSVNUPgogICAgICA8REVGQVVMVF9DT0xMQVRJT04+VVNJTkdfTkxTX0NPTVA8L0RFRkFVTFRfQ09MTEFUSU9OPgogICAgICA8UEhZU0lDQUxfUFJPUEVSVElFUz4KICAgICAgICAgPEhFQVBfVEFCTEU+CiAgICAgICAgICAgIDxTRUdNRU5UX0FUVFJJQlVURVM+CiAgICAgICAgICAgICAgIDxTRUdNRU5UX0NSRUFUSU9OX0RFRkVSUkVEPjwvU0VHTUVOVF9DUkVBVElPTl9ERUZFUlJFRD4KICAgICAgICAgICAgICAgPFBDVEZSRUU+MTA8L1BDVEZSRUU+CiAgICAgICAgICAgICAgIDxQQ1RVU0VEPjQwPC9QQ1RVU0VEPgogICAgICAgICAgICAgICA8SU5JVFJBTlM+MTwvSU5JVFJBTlM+CiAgICAgICAgICAgICAgIDxNQVhUUkFOUz4yNTU8L01BWFRSQU5TPgogICAgICAgICAgICAgICA8VEFCTEVTUEFDRT5VU0VSUzwvVEFCTEVTUEFDRT4KICAgICAgICAgICAgICAgPExPR0dJTkc+WTwvTE9HR0lORz4KICAgICAgICAgICAgPC9TRUdNRU5UX0FUVFJJQlVURVM+CiAgICAgICAgICAgIDxDT01QUkVTUz5OPC9DT01QUkVTUz4KICAgICAgICAgPC9IRUFQX1RBQkxFPgogICAgICA8L1BIWVNJQ0FMX1BST1BFUlRJRVM+CiAgIDwvUkVMQVRJT05BTF9UQUJMRT4KPC9UQUJMRT4=}')));
 execute immediate insertlog using id,author,filename,action,sxml,dep returning into myrow;
end;
/        --;

INSERT INTO B.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('4361fb8b8b996a029b63ec0d36060914614fa62a', '(A)-Generated', 't1_table.xml', SYSTIMESTAMP, 3, '8:5f6f0119815fbe5ab672965b2275f383', 'createSxmlObject objectName=T1, ownerName=A', '', 'EXECUTED', NULL, 'run3', '4.9.1', '2526815837');

-- Changeset demo_call_procedure.xml::826bf00a97fe837fe3bf97e24e63bc524c89352f::(A)-Generated
CREATE OR REPLACE EDITIONABLE PROCEDURE "DEMO_CALL"
as
l_cnt number;
begin
select count(*) into l_cnt from t1;
dbms_output.put_line('I am the patched version got... '||l_cnt||' rows..');
end;

-- Logging Oracle Liquibase extension actions to
 DECLARE
 id varchar2(200) := '826bf00a97fe837fe3bf97e24e63bc524c89352f';
 rawAction clob;
 rawSxml clob;
 myrow varchar2(2000);
 action clob := '';
 sxml clob := '';
 dep varchar2(200) := '2526815837';
 author varchar2(200) := '(A)-Generated';
 filename varchar2(200) := 'demo_call_procedure.xml';
 insertlog varchar2(200) := 'insert into DATABASECHANGELOG_ACTIONS (id,author,filename,sql,sxml,deployment_id) values (:id,:author,:filename,:action,:sxml,:dep) returning rowid into :out';
 updateaction varchar2(200) := 'update DATABASECHANGELOG_ACTIONS set sql = sql ||:action where rowid = :myrow ';
 updatesxml varchar2(200) := 'update DATABASECHANGELOG_ACTIONS set sxml = sxml ||:sxml where rowid = :myrow ';
 begin
action := utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(q'{Q1JFQVRFIE9SIFJFUExBQ0UgRURJVElPTkFCTEUgUFJPQ0VEVVJFICJERU1PX0NBTEwiIAphcwpsX2NudCBudW1iZXI7CmJlZ2luIApzZWxlY3QgY291bnQoKikgaW50byBsX2NudCBmcm9tIHQxOwpkYm1zX291dHB1dC5wdXRfbGluZSgnSSBhbSB0aGUgcGF0Y2hlZCB2ZXJzaW9uIGdvdC4uLiAnfHxsX2NudHx8JyByb3dzLi4nKTsKZW5kOw==}')));
sxml := utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(q'{Q1JFQVRFIE9SIFJFUExBQ0UgRURJVElPTkFCTEUgUFJPQ0VEVVJFICJCIi4iREVNT19DQUxMIiAKYXMKbF9jbnQgbnVtYmVyOwpiZWdpbiAKc2VsZWN0IGNvdW50KCopIGludG8gbF9jbnQgZnJvbSB0MTsKZGJtc19vdXRwdXQucHV0X2xpbmUoJ1RhYmxlIHQyIGdvdCAnfHxsX2NudHx8JyByb3dzLi4nKTsKZW5kOw==}')));
 execute immediate insertlog using id,author,filename,action,sxml,dep returning into myrow;
end;
/        --;

INSERT INTO B.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('826bf00a97fe837fe3bf97e24e63bc524c89352f', '(A)-Generated', 'demo_call_procedure.xml', SYSTIMESTAMP, 4, '8:5e0f57c43f459ff951fd6b340ad34f63', 'createOracleProcedure objectName=DEMO_CALL, ownerName=A', '', 'EXECUTED', NULL, 'run3', '4.9.1', '2526815837');

-- Release Database Lock
UPDATE B.DATABASECHANGELOGLOCK SET LOCKED = 0, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1;


b@PDB1>

b@PDB1> lb update -changelog controller.xml  -label run3
--Starting Liquibase at 10:35:58 (version 4.9.1 #0 built at 2022-05-03 17:23+2221)

-- Loaded 2 changeSets
Running Changeset: t1_table.xml::4361fb8b8b996a029b63ec0d36060914614fa62a::(A)-Generated

Action logged sucessfully.
Running Changeset: demo_call_procedure.xml::826bf00a97fe837fe3bf97e24e63bc524c89352f::(A)-Generated
Procedure DEMO_CALL compiled
Action logged sucessfully.
No Errors Encountered
b@PDB1> exec demo_call;
I am the patched version got... 0 rows..


PL/SQL procedure successfully completed.

b@PDB1>


Connor McDonald
September 12, 2022 - 3:03 am UTC

Thanks for coming back to us and updating the post

SQLCL and the SPOOL command output.

Rajeshwaran, Jeyabal, September 13, 2022 - 5:07 am UTC

Team,

see the below, spool command output from SQLCL is not similar to the SQL*Plus. why the command inputs are not available in the spool file generated from SQLCL, where as the SQL*Plus got those.

From SQLCL (version 22.2.1) got this
-----------------------------------------------
C:\Users\Rajeshwaran>sql /nolog


SQLcl: Release 22.2 Production on Mon Sep 12 16:09:59 2022

Copyright (c) 1982, 2022, Oracle. All rights reserved.

idle> conn demo/demo@pdb1
Connected.
demo@PDB1> spool a.txt
demo@PDB1> exec dbms_application_info.set_client_info('Hello_I am ');

PL/SQL procedure successfully completed.

demo@PDB1> select userenv('sid') from dual ;

USERENV('SID')
_________________
748

demo@PDB1> spool off
demo@PDB1> $ type a.txt

PL/SQL procedure successfully completed.


USERENV('SID')
_________________
748


demo@PDB1>

where as the SQL*Plus got this
---------------------------------------
C:\Users\Rajeshwaran>sqlplus /nolog

SQL*Plus: Release 21.0.0.0.0 - Production on Mon Sep 12 16:11:02 2022
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle. All rights reserved.

idle> conn demo/demo@pdb1
Connected.
demo@PDB1> spool b.txt
demo@PDB1> exec dbms_application_info.set_client_info('Hello_I am ');

PL/SQL procedure successfully completed.

demo@PDB1> select userenv('sid') from dual ;

USERENV('SID')
--------------
877

demo@PDB1> spool off
demo@PDB1> $ type b.txt
demo@PDB1> exec dbms_application_info.set_client_info('Hello_I am ');

PL/SQL procedure successfully completed.

demo@PDB1> select userenv('sid') from dual ;

USERENV('SID')
--------------
877

SQLCL and Liquibase on Reference Partitioned tables.

Rajeshwaran, Jeyabal, September 13, 2022 - 5:11 am UTC

Team,

we got few application tables that got reference partitioned in our Development database.
Testcase crafted below looks close to our application model.

grant connect,resource,create session to a identified by "a";
alter user a default tablespace users quota unlimited on users;

grant connect,resource,create session to b identified by "b";
alter user b default tablespace users quota unlimited on users;


conn a/a@pdb1

create table t1(x number generated by default as identity, y date,
       constraint t1_pk primary key(x) )
partition by range(y)
interval( numtoyminterval(1,'month') )
( partition p1 values less than
       ( to_date('01-jan-2022','dd-mon-yyyy') ) 
);

create table t2(x1 number generated by default as identity,x2 number not null, 
       constraint t2_pk primary key(x1) ,
       constraint t2_fk foreign key(x2)
             references t1(x) )
partition by reference(t2_fk);          

lb genschema -label run1

conn b/b@pdb1
tables
lb update -changelog controller.xml -label run1
tables 

conn a/a@pdb1 

alter table t2 add Z number;
lb genschema -label run2

conn b/b@pdb1
lb updatesql -changelog controller.xml -label run2


that final "lb updatesql" command produces the following ddl

-- Changeset t2_table.xml::cadc02c572f3991d731e71330e63a98857c6c54d::(A)-Generated
ALTER TABLE "T2" ADD ("Z" NUMBER)
/
  ALTER TABLE "T2" MODIFY ("X1" GENERATED BY DEFAULT AS IDENTITY
)
/
  ALTER TABLE "T2" DROP CONSTRAINT "T2_FK"
/
  ALTER TABLE "T2" ADD CONSTRAINT "T2_FK" FOREIGN KEY ("X2") REFERENCES "T1"("X") ENABLE;


dropping that FK is not possible, since we got reference partitioned using this FK constraint

so got a couple of question, please help us to clarify.
1) why do we get modify command on identity column here (what is the need for this ddl? (ALTER TABLE "T2" MODIFY ("X1" GENERATED BY DEFAULT AS IDENTITY) )
2) why the Liquibase is trying to drop and recreate the FK constraint, for a simple add column on the source schema?

LB UPDATE command ended up with error like this, any work around or fix available for this ?

b@PDB1> lb update -changelog controller.xml -label run2
--Starting Liquibase at 20:45:21 (version 4.9.1 #0 built at 2022-05-03 17:23+2221)

-- Loaded 2 changeSets
Running Changeset: t2_table.xml::cadc02c572f3991d731e71330e63a98857c6c54d::(A)-Generated
Table "T2" altered.


Table "T2" altered.


Error starting at line : 6 in command -
  ALTER TABLE "T2" DROP CONSTRAINT "T2_FK"
Error report -
ORA-14650: operation not supported for reference-partitioned tables
14650. 00000 -  "operation not supported for reference-partitioned tables"
*Cause:    Attempted to perform an operation on a
           reference-partitioned table that was not supported.
*Action:   Do not perform the unsupported operation.

Action logged sucessfully.
Rolling back changeset.
Table "T2" altered.
Action logged sucessfully.

######## ERROR SUMMARY ##################
Errors encountered:1

----------------------------
t2_table.xml
---------
Table "T2" altered.


Table "T2" altered.


Error starting at line : 6 in command -
  ALTER TABLE "T2" DROP CONSTRAINT "T2_FK"
Error report -
ORA-14650: operation not supported for reference-partitioned tables
14650. 00000 -  "operation not supported for reference-partitioned tables"
*Cause:    Attempted to perform an operation on a
           reference-partitioned table that was not supported.
*Action:   Do not perform the unsupported operation.

----------------------------

b@PDB1>

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library