Hi All,
Please assist,
I am able to dynamically create tables to deploy on my environments (ETL,DEV & TST) using the code below . In other words create a table with the schema name based on the environment i am deploying
BEGIN
DECLARE
schema_postfix VARCHAR2(4);
BEGIN
SELECT SUBSTR(sys_context( 'userenv', 'current_schema' ), -4) into schema_postfix from dual;
EXECUTE IMMEDIATE 'CREATE TABLE LND_DEAL' || schema_postfix ||
'.CONDITION_STATUS (
ID NUMBER GENERATED ALWAYS AS IDENTITY NOT NULL,
CONDITION_STATUS VARCHAR2(255 BYTE) NOT NULL,
CONSTRAINT CONDITION_STATUS_PK PRIMARY KEY (ID)
)';
END;
END;
I am now trying to do the same when creating a stored procedures but failing with the code below
BEGIN
DECLARE
schema_postfix VARCHAR2(4);
BEGIN
SELECT SUBSTR(sys_context( 'userenv', 'current_schema' ), -4) into schema_postfix from dual;
CREATE OR REPLACE PROCEDURE LND_DEAL' || schema_postfix ||
'.SP_DM_REFERENCE_ASSET_VALUATION_REPORT
AS
BEGIN
DECLARE
ERR_NUM NUMBER;
ERR_MSG VARCHAR2(100);
V_COUNT NUMBER:=0;
......
......
END;
END;
Thanks in advance