Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Binding variable with schema name to procedure name

MarwickFeb 16 2021 — edited Feb 16 2021

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

Comments
Post Details
Added on Feb 16 2021
1 comment
4,689 views