RE: Implementing Stored Procedures
Lisa, This is what we did in my last project: 1. Developers had access to the application schema where they created and modified procedures/functions/triggers as needed 2. After their unit testing is done, the QA team would test it using the front-end application. 3. Once QA team approves the new/enhanced functionality, the developers would check in the code using PVCS and notify the DBA vith the PVCS version number and the object name. 4. The DBA tracks the object version numbers along with the application releases and implements the necessary changes in the production box. Developers did not have any rights on the production box. HTH! Prakash -Original Message- Sent: Thursday, May 31, 2001 1:02 PM To: Multiple recipients of list ORACLE-L Hi - I'd like some advice on implementing stored procedures containing application logic (ie. written by developers). We have several applications where the developers use stored procedures for much of their coding. We let the developers create or replace their procedures in a development environment under their own schema (with access to all application tables, etc.) to test the logic, but it currently requires a DBA to implement the proc under the application schema. It has gotten to be a very time-consuming job. We don't want to give out the schema owner password to the developers, nor do we know of a way they could add them as the schema owner without giving them more privileges than we want. I am curious of how others are handling stored procedure additions and modifications.Do you somehow allow developers this access? If so, how do you restrict them from damaging other things? If not, does the DBA do it? Does anyone have an automated way? Also, do you keep track of the original source code for the procedure, or do you extract it out of the database as needed? Thanks so much for your input - Lisa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bala, Prakash INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Implementing Stored Procedures
We put the code in a file on Unix (our server platform) and in the change request form, provide the file name and location. The DBA will log in as the schema owner and simply run the file. Any errors are reported back to developer who requested the change. All the SQL code is archived using SCCS. HTH Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art ! *4 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *4 Title: Implementing Stored Procedures We put the code in a file on Unix (our server platform) and in the change request form, provide the file name and location. The DBA will log in as the schema owner and simply run the file. Any errors are reported back to developer who requested the change. All the SQL code is archived usingSCCS. HTH Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art !
RE: Implementing Stored Procedures
There's an excellent tool by the company Kintana that automates the deployment of software (which can include stored packages and procedures, as well as shell scripts, SQL*Loader, whatever). The developer creates a release with the file names and version numbers in PVCS or ClearCase, and the tool extracts and runs the approprate files on other servers as determined by workflows -- so you can use this tool to go from dev to QA to production, etc. Very nice tool, I'd highly recommend it. And no, I am not affiliated, just a happy user. Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Bala, Prakash prakash.bala@cinTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] gular.com cc: Sent by: Fax to: [EMAIL PROTECTED] Subject: RE: Implementing Stored Procedures 05/31/2001 02:46 PM Please respond to ORACLE-L Lisa, This is what we did in my last project: 1. Developers had access to the application schema where they created and modified procedures/functions/triggers as needed 2. After their unit testing is done, the QA team would test it using the front-end application. 3. Once QA team approves the new/enhanced functionality, the developers would check in the code using PVCS and notify the DBA vith the PVCS version number and the object name. 4. The DBA tracks the object version numbers along with the application releases and implements the necessary changes in the production box. Developers did not have any rights on the production box. HTH! Prakash -Original Message- Sent: Thursday, May 31, 2001 1:02 PM To: Multiple recipients of list ORACLE-L Hi - I'd like some advice on implementing stored procedures containing application logic (ie. written by developers). We have several applications where the developers use stored procedures for much of their coding. We let the developers create or replace their procedures in a development environment under their own schema (with access to all application tables, etc.) to test the logic, but it currently requires a DBA to implement the proc under the application schema. It has gotten to be a very time-consuming job. We don't want to give out the schema owner password to the developers, nor do we know of a way they could add them as the schema owner without giving them more privileges than we want. I am curious of how others are handling stored procedure additions and modifications.Do you somehow allow developers this access? If so, how do you restrict them from damaging other things? If not, does the DBA do it? Does anyone have an automated way? Also, do you keep track of the original source code for the procedure, or do you extract it out of the database as needed? Thanks so much for your input - Lisa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bala, Prakash INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http
RE: Implementing Stored Procedures
Title: Implementing Stored Procedures One option (and I'm not suggesting that this is the BEST option by any means, but it IS an option =) might be to grant the developer(s) the 'CREATE ANY PROCEDURE' role, and then have them create their procedures/functions/packages etc as schema_name.object_name, with schema_name being the name of the application schema that the object will need to be in. -::YEX::- ))) -Original Message-From: Yttri, Lisa [mailto:[EMAIL PROTECTED]]Sent: Thursday, 31 May, 2001 13:02 PMTo: Multiple recipients of list ORACLE-LSubject: Implementing Stored Procedures Hi - I'd like some advice on implementing stored procedures containing application logic (ie. written by developers). We have several applications where the developers use stored procedures for much of their coding. We let the developers create or replace their procedures in a development environment under their own schema (with access to all application tables, etc.) to test the logic, but it currently requires a DBA to implement the proc under the application schema. It has gotten to be a very time-consuming job. We don't want to give out the schema owner password to the developers, nor do we know of a way they could add them as the schema owner without giving them more privileges than we want. I am curious of how others are handling stored procedure additions and modifications. Do you somehow allow developers this access? If so, how do you restrict them from damaging other things? If not, does the DBA do it? Does anyone have an automated way? Also, do you keep track of the original "source code" for the procedure, or do you extract it out of the database as needed? Thanks so much for your input - Lisa
RE: Implementing Stored Procedures
Title: Implementing Stored Procedures hi lisa, we do have staging area (dba -directory)inproductionwhere all the scripts ...(sql,forms,reports) are kept and with change request forms they are implemented... if any problems with the scripts they willl go to owner of change requester. And We use version control (PVCS, MS visual source safe) to track changes done to the prodecures and scripts. naren -Original Message-From: Yttri, Lisa [mailto:[EMAIL PROTECTED]]Sent: Friday, June 01, 2001 5:02 AMTo: Multiple recipients of list ORACLE-LSubject: Implementing Stored Procedures Hi - I'd like some advice on implementing stored procedures containing application logic (ie. written by developers). We have several applications where the developers use stored procedures for much of their coding. We let the developers create or replace their procedures in a development environment under their own schema (with access to all application tables, etc.) to test the logic, but it currently requires a DBA to implement the proc under the application schema. It has gotten to be a very time-consuming job. We don't want to give out the schema owner password to the developers, nor do we know of a way they could add them as the schema owner without giving them more privileges than we want. I am curious of how others are handling stored procedure additions and modifications. Do you somehow allow developers this access? If so, how do you restrict them from damaging other things? If not, does the DBA do it? Does anyone have an automated way? Also, do you keep track of the original "source code" for the procedure, or do you extract it out of the database as needed? Thanks so much for your input - Lisa
