Schema name is dev in development and prod in production environment. When a stored procedure is created in development using dev schema, What should be the approach to migrate this procedure to prod environment. Below is a sample procedure created, What is the best(ideal) way to change the schema name in SELECT statement to prod from dev before moving it to production environment.
CREATE OR REPLACE PROCEDURE GET_EMPLOYEES(
DEPT_CD IN VARCHAR2(3),
RESULT_SET OUT SYS_REFCURSOR) AS
BEGIN
OPEN RESULT_SET FOR SELECT * FROM DEV.EMPLOYEE WHERE DEPT=DEPT_CD;
END
END GET_EMPLOYEES;
This procedure will be called from UI, to display the data in UI. In UI same problem is being handled by using configuration files. Please enlighten me what should be the approach for moving stored procedures. I have searched but could not find any solution.
what should be the approach for moving stored procedures
Don't reference schema names in your code.
If GET_EMPLOYEES is owned by DEV then there's no need to include the schema at all.
If the procedure is owned by a different schema create a synonym for EMPLOYEE table; in development the synonym would point to DEV.EMPLOYEE and it production it would point to PROD.EMPLOYEE.
Schema name is dev in development and prod in production environment
Incidentally, let's call this out for the bad practice it is. I remember having to wrestle with environments configured like this twenty years ago. It was a pain then and it's a pain now. But at least in the past we didn't know any better. There's no excuse for doing it now unless we are developing under a separate schema in the Production database (and that's pretty inexcusable).