CURSOR_TYPE Not supporting in Liquibase Update

General Discussion
1

Getting this error

Caused by: liquibase.exception.DatabaseException: ORA-00900: invalid SQL statement
[Failed SQL: (900) PROCEDURE PR_GET_ESOA_PAYMENT_NOS (P_CUR OUT CURSOR_TYPE)]

And also EXEC PRCOMPILE; not supporting.

2

Please provide the entire changeset so it can be evaluated.

3

–liquibase formatted sql
–changeset includeAll:raw runOnChange:true
SET DEFINE OFF;
CREATE OR REPLACE PACKAGE PKG_DT AS
TYPE CURSOR_TYPE IS REF CURSOR;

PROCEDURE PR_GET_ESOA_PAYMENT_NOS (P_CUR OUT CURSOR_TYPE);

PROCEDURE PR_TRF_IND (P_VCH_PAY IN VARCHAR2);

PROCEDURE PR_SUB_NOS (P_CUR OUT CURSOR_TYPE);

END PKG_DT;
/

CREATE OR REPLACE PACKAGE BODY PKG_DT
AS
PROCEDURE PR_GET_ESOA_PAYMENT_NOS (P_CUR OUT CURSOR_TYPE)
AS
BEGIN

OPEN P_CUR FOR
SELECT DISTINCT EIS_TRN.VCH_PAY
FROM EIS_TRN, EIS_TRN_PAYMENT_DET
WHERE EIS_TRN.NUM_PAY_BATCH_ID = EIS_TRN_PAYMENT_DET.NUM_PAY_BATCH_ID
AND EIS_TRN.VCH_PAY IS NOT NULL
AND EIS_TRN.CHR_PRODUCTION_TRANSFER_IND = ‘N’
AND EIS_TRN.CHR_ESOA_PMT_IND = ‘Y’;

END PR_GET_ESOA_PAYMENT_NOS;

PROCEDURE PR_TRF_IND (P_VCH_PAY IN VARCHAR2)
AS
BEGIN

UPDATE EIS_TRN
SET CHR_PRODUCTION_TRANSFER_IND = 'Y'
WHERE CHR_PRODUCTION_TRANSFER_IND = 'N' AND VCH_PAY IS NOT NULL AND VCH_PAY = P_VCH_PAY;

UPDATE EIS_TRN_SUBMISSION_HEADER
SET CHR_PRODUCTION_TRANSFER_IND = 'Y'
WHERE NUM_SUBMISSION_HEADER_ID IN ( SELECT NUM_SUBMISSION_HEADER_ID FROM EIS_TRN WHERE  VCH_PAY = P_VCH_PAY);

END PR_TRF_IND;

END PKG_DT;
/
COMMIT;
SHOW ERRORS;

EXEC PRCOMPILE;

SELECT object_name, status FROM user_objects WHERE status = ‘INVALID’ and object_type like ‘PACKA%’;

SHOW ERRORS;

SHOW ERRORS;

4

A couple of recommendations:

  1. “SET DEFINE OFF” and “SHOW ERRORS” are sqlplus commands, and do not apply in Liquibase.
  2. For PL/SQL you need to set endDelimiter=/ so the statements can be properly parsed, since Liquibase uses the semicolon by default.
  3. COMMIT is not needed for Oracle DDL, and not needed in Liquibase, each changeset is automatically a transaction.