SP2-0734:Unknown command beginning "CONSTRAINT..."

Question

I am not sure, why below is throwing an error during the RLM deployment. However, same query is successfully executing in Oracle SQL developer.

I do not see any space in the query, and just to be in safer side I put "SET SQLBLANKLINES ON" if in case sql*plus client is being used but no luck. Here is my script

create table PROFILE (
ID INT PRIMARY KEY,
EXT_ID varchar2(150) NOT NULL,
ENV varchar2(50) NOT NULL,
INTERNAL_PROFILE_ID varchar2(150) NOT NULL,
STATUS_ID INT NOT NULL,
NUMBER_OF_DAYS INT NOT NULL,
TAGS varchar2(150),
APPLIED_DATE TIMESTAMP,
CREATED TIMESTAMP,
UPDATED TIMESTAMP,
CONSTRAINT PROFILE_INFO_VULN_ID_OPERATING_ENV_UNQ  UNIQUE (EXT_ID,ENV)
);

commit;

The error shows as

INFO: /opt/oraClient/11.2.0.4/rpm/bin/sqlplus -L -s USER:xyz @/rlmstage/bmc/rlm/rlm_8012912_106159671_1709878573/content/script.sql

01:16:33|INFO> SP2-0734: unknown command beginning "CONSTRAINT..." - rest of line ignored. 01:16:33|INFO> SP2-0042: unknown command ")" - rest of line ignored.

Answer 1

1. there is no VULN_ID column in your table definition
2. PROFILE_INFO_VULN_ID_OPERATING_ENV_UNQ is 38 chars and that may be not supported on older ORACLE versions

Answer 2

It looks like RLM deployment tool doesn't recognize constraint the way you put it.

If you can edit script.sql (I believe you can), then omit that clause entirely from create table and create unique constraint separately with alter table. Something like this:

SQL> CREATE TABLE profile
  2  (
  3     id                    INT PRIMARY KEY,
  4     ext_id                VARCHAR2 (150) NOT NULL,
  5     env                   VARCHAR2 (50) NOT NULL,
  6     internal_profile_id   VARCHAR2 (150) NOT NULL,
  7     status_id             INT NOT NULL,
  8     number_of_days        INT NOT NULL,
  9     tags                  VARCHAR2 (150),
 10     applied_date          TIMESTAMP,
 11     created               TIMESTAMP,
 12     updated               TIMESTAMP
 13  );

Table created.

SQL> ALTER TABLE profile
  2     ADD CONSTRAINT profile_unq UNIQUE (ext_id, env);

Table altered.

SQL>

By the way, these are DDL statements which implicitly commit changes to data dictionary. You don't have to commit manually.