Forum Stats

  • 3,769,363 Users
  • 2,252,957 Discussions
  • 7,875,005 Comments

Discussions

Binding variable with schema name to procedure name

Marwick
Marwick Member Posts: 3 Red Ribbon
edited Feb 16, 2021 7:42AM in SQL & PL/SQL

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

Tagged:

Answers

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,594 Red Diamond

    The PL/SQL create procedure statement needs an EXECUTE IMMEDIATE just like your SQL create table statement.

    And this approach is flawed as far as source code maintainability and testing goes. It is not robust. It does not support readability. It is a maintenance nightmare. It does not support unit testing. It is a very silly approach.

    Rather consider using client side substitution variables instead.